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

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

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.

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(dot)trey(at)gmail(dot)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
>

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

You all make it sound so easy :)

How do I write the above using a function?

Cheers, Jen

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

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(at)joeconway(dot)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
>
>

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(at)joeconway(dot)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
>>
>>
>

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

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

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

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

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

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

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/