Quantcast

Create table if not exists ... how ??

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Create table if not exists ... how ??

Jennifer.T
I can't figure out the correct syntax...

I have this, but it just keeps complaining about the IF 

IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes')

THEN

CREATE TABLE post_codes
(
  area character varying(10) NOT NULL,
  district character varying(10) NOT NULL,
  sector character varying(10) NOT NULL,
  CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE post_codes OWNER TO postgres;

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ERROR:  syntax error at or near "IF"
LINE 1: IF NOT EXISTS (SELECT table_name FROM information_schema.tab...
        ^

********** Error **********

ERROR: syntax error at or near "IF"
SQL state: 42601
Character: 1


How should this be written ? 

Thanks, Jen

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Raymond O'Donnell
On 19/07/2010 17:33, Jennifer Trey wrote:

> I can't figure out the correct syntax...
>
> I have this, but it just keeps complaining about the IF
>
> IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
> table_name = 'post_codes')
>
> THEN
>
> CREATE TABLE post_codes
> (
>    area character varying(10) NOT NULL,
>    district character varying(10) NOT NULL,
>    sector character varying(10) NOT NULL,
>    CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
> )
> WITH (
>    OIDS=FALSE
> );
> ALTER TABLE post_codes OWNER TO postgres;
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - - -
>
> ERROR:  syntax error at or near "IF"
> LINE 1: IF NOT EXISTS (SELECT table_name FROM information_schema.tab...
>          ^

> How should this be written ?

I don't think you can use the "IF" like this in a normal query. You
could write a pl/pgsql function instead to do this..

Ray.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Pavel Stehule
In reply to this post by Jennifer.T
Hello

you can use IF statement only inside plpgsql function. CREATE TABLE
doesn't support clause IF.

Regards

Pavel Stehule

2010/7/19 Jennifer Trey <[hidden email]>:

> I can't figure out the correct syntax...
> I have this, but it just keeps complaining about the IF
> IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
> table_name = 'post_codes')
> THEN
> CREATE TABLE post_codes
> (
>   area character varying(10) NOT NULL,
>   district character varying(10) NOT NULL,
>   sector character varying(10) NOT NULL,
>   CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE post_codes OWNER TO postgres;
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - -
> ERROR:  syntax error at or near "IF"
> LINE 1: IF NOT EXISTS (SELECT table_name FROM information_schema.tab...
>         ^
> ********** Error **********
> ERROR: syntax error at or near "IF"
> SQL state: 42601
> Character: 1
>
> How should this be written ?
> Thanks, Jen
>

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Peter Geoghegan
In reply to this post by Raymond O'Donnell
>
>> How should this be written ?
>
> I don't think you can use the "IF" like this in a normal query. You could
> write a pl/pgsql function instead to do this..

You can write such a query inline in 9.0, by use of DO...but you
probably just want to define a function for now
--
Regards,
Peter Geoghegan

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Jennifer.T
In reply to this post by Pavel Stehule
You all make it sound so easy :)

How do I write the above using a function? 

Cheers, Jen
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Joe Conway
In reply to this post by Jennifer.T
On 07/19/2010 09:33 AM, Jennifer Trey wrote:

> I can't figure out the correct syntax...
>
> I have this, but it just keeps complaining about the IF
>
> IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
> table_name = 'post_codes')
>
> THEN
>
> CREATE TABLE post_codes
Probably better to do:

DROP TABLE IF EXISTS post_codes;
CREATE TABLE post_codes(...);

See:
  http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


signature.asc (917 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Jennifer.T
No.... I don't want to drop it ... there is valuable data in there! I only want to create it if it doesn't already exist... likely going to happen first time the application will run. I want to create the table then and populate. But not the next time.

Should I just let Java throw and exception and catch it ? Write a function for this would be optimal, although I have no idea what the correct syntax is.

Cheers, Jen



On Mon, Jul 19, 2010 at 5:58 PM, Joe Conway <[hidden email]> wrote:
On 07/19/2010 09:33 AM, Jennifer Trey wrote:
> I can't figure out the correct syntax...
>
> I have this, but it just keeps complaining about the IF
>
> IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
> table_name = 'post_codes')
>
> THEN
>
> CREATE TABLE post_codes

Probably better to do:

DROP TABLE IF EXISTS post_codes;
CREATE TABLE post_codes(...);

See:
 http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

sramirez
Assuming you know the schema name, you could always check the catalog table, something like
select count(*) from pg_tables where schemaname= 'foo' and tablename='bar'
If it returns, then you know a table by the name foo.bar exists. if not you can create it.
  -Said


Jennifer Trey wrote:

> No.... I don't want to drop it ... there is valuable data in there! I only
> want to create it if it doesn't already exist... likely going to happen
> first time the application will run. I want to create the table then and
> populate. But not the next time.
>
> Should I just let Java throw and exception and catch it ? Write a function
> for this would be optimal, although I have no idea what the correct syntax
> is.
>
> Cheers, Jen
>
>
>
> On Mon, Jul 19, 2010 at 5:58 PM, Joe Conway <[hidden email]> wrote:
>
>> On 07/19/2010 09:33 AM, Jennifer Trey wrote:
>>> I can't figure out the correct syntax...
>>>
>>> I have this, but it just keeps complaining about the IF
>>>
>>> IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
>>> table_name = 'post_codes')
>>>
>>> THEN
>>>
>>> CREATE TABLE post_codes
>> Probably better to do:
>>
>> DROP TABLE IF EXISTS post_codes;
>> CREATE TABLE post_codes(...);
>>
>> See:
>>  http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html
>>
>> HTH,
>>
>> Joe
>>
>> --
>> Joe Conway
>> credativ LLC: http://www.credativ.us
>> Linux, PostgreSQL, and general Open Source
>> Training, Service, Consulting, & 24x7 Support
>>
>>
>

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Steve Atkins
In reply to this post by Jennifer.T

On Jul 19, 2010, at 10:43 AM, Jennifer Trey wrote:

> No.... I don't want to drop it ... there is valuable data in there! I only want to create it if it doesn't already exist... likely going to happen first time the application will run. I want to create the table then and populate. But not the next time.
>
> Should I just let Java throw and exception and catch it ? Write a function for this would be optimal, although I have no idea what the correct syntax is.
>
> Cheers, Jen

Try something like this:

    create or replace function build_foo_table() returns void as $$
      create table foo (bar int);
    $$ language sql;

    select case when (select count(*) from information_schema.tables where table_name='foo')=0 then build_foo_table() end;

    drop function build_foo_table();

Cheers,
  Steve


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Joe Conway
In reply to this post by Jennifer.T
On 07/19/2010 10:43 AM, Jennifer Trey wrote:
> No.... I don't want to drop it ... there is valuable data in there! I
> only want to create it if it doesn't already exist... likely going to
> happen first time the application will run. I want to create the table
> then and populate. But not the next time.


Sorry -- didn't understand that from your original post. How 'bout
something like:

8<---------------------
CREATE OR REPLACE FUNCTION
conditional_create_table(schemaname text, tablename text, create_sql
text, tbl_owner text)
RETURNS text AS $$
  DECLARE
    tbl_cnt int;
    fqtn text := schemaname || '.' || tablename;
  BEGIN
    SELECT COUNT(*) INTO tbl_cnt FROM information_schema.tables
    WHERE table_schema= schemaname AND table_name=tablename;

    IF tbl_cnt < 1 THEN
        EXECUTE 'CREATE TABLE ' || fqtn || create_sql;
        EXECUTE 'ALTER TABLE ' || fqtn || ' OWNER TO ' || tbl_owner;
        RETURN 'CREATE';
    ELSE
        RETURN 'SKIP';
    END IF;
  END
$$ LANGUAGE plpgsql STRICT;


SELECT conditional_create_table(
'public',
'post_codes',
'(area character varying(10) NOT NULL,
  district character varying(10) NOT NULL,
  sector character varying(10) NOT NULL,
  CONSTRAINT post_codes_pkey
  PRIMARY KEY (area, district, sector)
 ) WITH (OIDS=FALSE)',
'postgres'
);
 conditional_create_table
--------------------------
 CREATE
(1 row)

SELECT conditional_create_table(
'public',
'post_codes',
'(area character varying(10) NOT NULL,
  district character varying(10) NOT NULL,
  sector character varying(10) NOT NULL,
  CONSTRAINT post_codes_pkey
  PRIMARY KEY (area, district, sector)
 ) WITH (OIDS=FALSE)',
'postgres'
);
 conditional_create_table
--------------------------
 SKIP
(1 row)

contrib_regression=# \d public.post_codes
          Table "public.post_codes"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 area     | character varying(10) | not null
 district | character varying(10) | not null
 sector   | character varying(10) | not null
Indexes:
    "post_codes_pkey" PRIMARY KEY, btree (area, district, sector)

8<---------------------


Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


signature.asc (917 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Scott Marlowe-2
In reply to this post by Jennifer.T
On Mon, Jul 19, 2010 at 10:33 AM, Jennifer Trey <[hidden email]> wrote:

> I can't figure out the correct syntax...
> I have this, but it just keeps complaining about the IF
> IF NOT EXISTS (SELECT table_name FROM information_schema.tables where
> table_name = 'post_codes')
> THEN
> CREATE TABLE post_codes
> (
>   area character varying(10) NOT NULL,
>   district character varying(10) NOT NULL,
>   sector character varying(10) NOT NULL,
>   CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
> )
> WITH (
>   OIDS=FALSE
> );

How about something like this:

create function create_table_if_not_exists () returns bool as
$$
BEGIN
        BEGIN
                Create table test001 (i int, t text);
        Exception when duplicate_table THEN
                RETURN FALSE;
                END;
        RETURN TRUE;
END;
$$
Language plpgsql;


When you run it the first time, it comes back true, then false after
that.  It's easy enough to wrap that function in another function that
does the data loading.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Igor Neyman
In reply to this post by Joe Conway
 

> -----Original Message-----
> From: Joe Conway [mailto:[hidden email]]
> Sent: Monday, July 19, 2010 12:59 PM
> To: Jennifer Trey
> Cc: [hidden email]
> Subject: Re: Create table if not exists ... how ??
>
> On 07/19/2010 09:33 AM, Jennifer Trey wrote:
> > I can't figure out the correct syntax...
> >
> > I have this, but it just keeps complaining about the IF
> >
> > IF NOT EXISTS (SELECT table_name FROM
> information_schema.tables where
> > table_name = 'post_codes')
> >
> > THEN
> >
> > CREATE TABLE post_codes
>
> Probably better to do:
>
> DROP TABLE IF EXISTS post_codes;
> CREATE TABLE post_codes(...);
>
> See:
>   http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html
>
> HTH,
>
> Joe
>
> --
> Joe Conway
> credativ LLC: http://www.credativ.us
> Linux, PostgreSQL, and general Open Source Training, Service,
> Consulting, & 24x7 Support
>

Joe,

What you suggest is completely different from what OP asked.

Jen wants to avoid getting error on CREATE TABLE in case her table
already exists (but proceed with CREATE TABLE, if it doesn't).
What you suggest, will drop the table (IF EXISTS), and then create it
"anew" - what if there is already data in the table?

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Joe Conway
On 07/19/2010 01:54 PM, Igor Neyman wrote:
> What you suggest is completely different from what OP asked.
>
> Jen wants to avoid getting error on CREATE TABLE in case her table
> already exists (but proceed with CREATE TABLE, if it doesn't).
> What you suggest, will drop the table (IF EXISTS), and then create it
> "anew" - what if there is already data in the table?

Read on -- we are way past that already...

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


signature.asc (917 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Jennifer.T
Thanks guys. 

Joe, I tried ( and learned! ) from your syntax. I didn't have pgsql language installed but I googled it and figured that part out. 

There was an issue with using your way though, you see the constraints relation also needs to be considered, as if a constraint key already exist, for any other table, not neccessary for hte table we are creating, then we are going to get an error, which won't be covered by the count.

So I finally tried Scott's way because it will catch an exception, and I believe the constraint key exception is included in there. Although I am not sure, because he is catching a duplicate_table exception ? What is the most generic exception in postgres ? Throwable in Java ? 

create or replace function create_table_if_not_exists (create_sql text) returns bool as $$
BEGIN
BEGIN
                EXECUTE create_sql;

Exception when duplicate_table THEN
RETURN false;
END;
        RETURN true;
       
END;
$$
Language plpgsql;

SELECT create_table_if_not_exists ('CREATE TABLE post_codes
(
  area character varying(10) NOT NULL,
  district character varying(10) NOT NULL,
  sector character varying(10) NOT NULL,
  CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE post_codes OWNER TO postgres;')


Thank you all, Jen
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Create table if not exists ... how ??

Sam Mason
On Tue, Jul 20, 2010 at 10:18:59AM +0100, Jennifer Trey wrote:
> What is the most generic exception in postgres ? Throwable in Java ?

AFAIR, from programming Java many moons ago, you really don't want to go
about catching the most general exception.  The ThreadDeath exception
for instance is derived from Error rather than Exception for this
reason.

That said, maybe you want the "magic" exception type OTHERS, i.e:

  EXCEPTION WHEN OTHERS THEN

PG doesn't have as flexible hierarchy as Java, but a match is considered
to have occurred upto the first zero in the error code.  So you could
also use syntax_error_or_access_rule_violation or transaction_rollback.

--
  Sam  http://samason.me.uk/

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...