PostgreSQL create table if not exists

Question

In a MySQL script you can write   CREATE TABLE IF NOT EXISTS foo            other stuff      and then you can run the script many times without re-creating the table   How do you do this in PostgreSQL

User · Answer

Try this   CREATE TABLE IF NOT EXISTS app user     username varchar 45  NOT NULL    password varchar 450  NOT NULL    enabled integer NOT NULL DEFAULT  1     PRIMARY KEY  username

User · Answer

There is no CREATE TABLE IF NOT EXISTS    but you can write a simple procedure for that  something like   CREATE OR REPLACE FUNCTION execute TEXT  RETURNS VOID AS    BEGIN   EXECUTE  1  END     LANGUAGE plpgsql    SELECT    execute          CREATE TABLE sch foo                  i integer                WHERE    NOT exists          SELECT        FROM information schema tables      WHERE table name    foo        AND table schema    sch

User · Answer

This solution is somewhat similar to the answer by Erwin Brandstetter  but uses only the sql language   Not all PostgreSQL installations has the plpqsql language by default  this means you may have to call CREATE LANGUAGE plpgsql before creating the function  and afterwards have to remove the language again  to leave the database in the same state as it was before  but only if the database did not have the plpgsql language to begin with   See how the complexity grows   Adding the plpgsql may not be issue if you are running your script locally  however  if the script is used to set up schema at a customer it may not be desirable to leave changes like this in the customers database   This solution is inspired by a post by Andreas Scherbaum   -- Function which creates table CREATE OR REPLACE FUNCTION create table    RETURNS TEXT AS        CREATE TABLE table name          i int            SELECT  extended recycle bin created   TEXT         LANGUAGE  sql    -- Test if table exists  and if not create it SELECT CASE WHEN  SELECT true  BOOLEAN     FROM   pg catalog pg tables      WHERE  schemaname    public      AND    tablename     table name      THEN  SELECT  success   TEXT    ELSE  SELECT create table    END   -- Drop function DROP FUNCTION create table

User · Answer

I created a generic solution out of the existing answers which can be reused for any table   CREATE OR REPLACE FUNCTION create if not exists  table name text  create stmt text  RETURNS text AS     BEGIN  IF EXISTS       SELECT       FROM   pg catalog pg tables      WHERE    tablename    table name       THEN    RETURN  TABLE              table name              ALREADY EXISTS   ELSE    EXECUTE create stmt     RETURN  CREATED   END IF   END      LANGUAGE plpgsql    Usage   select create if not exists  my table    CREATE TABLE my table  id integer NOT NULL        It could be simplified further to take just one parameter if one would extract the table name out of the query parameter  Also I left out the schemas

User · Answer

There is no CREATE TABLE IF NOT EXISTS    but you can write a simple procedure for that  something like   CREATE OR REPLACE FUNCTION prc create sch foo table   RETURNS VOID AS    BEGIN  EXECUTE  CREATE TABLE    IF NOT EXISTS add for PostgreSQL 9 1     sch foo                       id serial NOT NULL                       demo column varchar NOT NULL                       demo column2 varchar NOT NULL                      CONSTRAINT pk sch foo PRIMARY KEY  id                       CREATE INDEX    IF NOT EXISTS add for PostgreSQL 9 5     idx sch foo demo column ON sch foo demo column                      CREATE INDEX    IF NOT EXISTS add for PostgreSQL 9 5     idx sch foo demo column2 ON sch foo demo column2                   WHERE NOT EXISTS SELECT   FROM information schema tables                          WHERE table schema    sch                               AND table name    foo              EXCEPTION WHEN null value not allowed THEN            WHEN duplicate table THEN            WHEN others THEN RAISE EXCEPTION        SQLSTATE  SQLERRM   END     LANGUAGE plpgsql

User · Answer

This feature has been implemented in Postgres 9 1  CREATE TABLE IF NOT EXISTS myschema mytable  i integer      For older versions  here is a function to work around it  CREATE OR REPLACE FUNCTION create mytable     RETURNS void   LANGUAGE plpgsql AS  func  BEGIN    IF EXISTS  SELECT FROM pg catalog pg tables                WHERE  schemaname    myschema                AND    tablename     mytable   THEN       RAISE NOTICE  Table myschema mytable already exists       ELSE       CREATE TABLE myschema mytable  i integer      END IF  END  func    Call  SELECT create mytable           -- call as many times as you want    Notes   The columns schemaname and tablename in pg tables are case-sensitive   If you double-quote identifiers in the CREATE TABLE statement  you need to use the exact same spelling  If you don t  you need to use lower-case strings  See   Are PostgreSQL column names case-sensitive   pg tables only contains actual tables  The identifier may still be occupied by related objects  See   How to check if a table exists in a given schema  If the role executing this function does not have the necessary privileges to create the table you might want to use SECURITY DEFINER for the function and make it owned by another role with the necessary privileges  This version is safe enough

[sql] PostgreSQL create table if not exists

Examples related to sql

Examples related to postgresql

Examples related to ddl

Examples related to create-table

Examples related to database-table