procedure for postgres to create table if not exist

1 vote

2 answers

14316 views

                          i want to create table if that doesn't, i tried below code:

    create or replace function create_table() returns void as
    $$
    begin
        if not exists(select * 
                      from   pg_tables 
                      where  schemaname = 'Public'
                      and    tablename = 'test') 
        then
                
            create table test
            (
                the_id int not null,
                name text
            );
    
        end if;
    
    end;
    $$
    language 'plpgsql';

While executing this procedure first time:

    select creat_table();

table gets created but when I execute it again I get the below error: 

    ERROR:  relation "test" already exists
    CONTEXT:  SQL statement "create table test
            (
                the_id int not null,
                name text
            )"
    PL/pgSQL function create_table() line 8 at SQL statement
    ********** Error **********
    
    ERROR: relation "test" already exists
    SQL state: 42P07
    Context: SQL statement "create table test
            (
                the_id int not null,
                name text
            )"
    PL/pgSQL function create_table() line 8 at SQL statement

How to achieve this, and also I want to call this procedure from Informatica pre-sql target session property so i want to call procedure with table name as parameter. 

                        

Asked by user3253227 (11 rep)

Jan 17, 2018, 08:05 AM
Last activity: Jan 17, 2018, 07:50 PM

procedure for postgres to create table if not exist

Related Questions