Home > mailing lists

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

Create table if not exists ... how ??

From

Jennifer Trey

Date:

19 July 2010, 16:33:49

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

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

From

Raymond O'Donnell

Date:

19 July 2010, 16:39:27

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.

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

From

Pavel Stehule

Date:

19 July 2010, 16:41:21

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 <jennifer.trey@gmail.com>:
> 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
>

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

From

Peter Geoghegan

Date:

19 July 2010, 16:43:05

>
>> 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

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

From

Jennifer Trey

Date:

19 July 2010, 16:53:51

You all make it sound so easy :)

How do I write the above using a function?

Cheers, Jen

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

From

Joe Conway

Date:

19 July 2010, 16:58:52

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

Attachment

signature.asc

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

From

Jennifer Trey

Date:

19 July 2010, 17:43:20

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 <mail@joeconway.com> 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

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

From

Steve Atkins

Date:

19 July 2010, 17:57:40

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
notthe next time. 
>
> Should I just let Java throw and exception and catch it ? Write a function for this would be optimal, although I have
noidea 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

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

From

Said Ramirez

Date:

19 July 2010, 18:00:13

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 <mail@joeconway.com> 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
>>
>>
>

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

From

Joe Conway

Date:

19 July 2010, 18:06:18

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

Attachment

signature.asc

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

From

Scott Marlowe

Date:

19 July 2010, 18:19:57

On Mon, Jul 19, 2010 at 10:33 AM, Jennifer Trey <jennifer.trey@gmail.com> 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.

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

From

"Igor Neyman"

Date:

19 July 2010, 20:54:37


> -----Original Message-----
> From: Joe Conway [mailto:mail@joeconway.com]
> Sent: Monday, July 19, 2010 12:59 PM
> To: Jennifer Trey
> Cc: pgsql-general@postgresql.org
> 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

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

From

Joe Conway

Date:

19 July 2010, 20:55:48

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

Attachment

signature.asc

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

From

Jennifer Trey

Date:

20 July 2010, 09:19:10

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 ?

http://www.postgresql.org/docs/8.1/interactive/errcodes-appendix.html

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

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

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

From

Sam Mason

Date:

20 July 2010, 13:27:55

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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

From

Jennifer Trey

Date:

20 July 2010, 19:24:44

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.

http://www.postgresql.org/docs/8.1/interactive/errcodes-appendix.html

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

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

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

From

Sam Mason

Date:

21 July 2010, 02:01:06

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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general