Re: [GENERAL] Create table if not exists ... how ??
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
Re: [GENERAL] Create table if not exists ... how ??
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
[GENERAL] 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
Re: [GENERAL] Create table if not exists ... how ??
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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
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.t...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
You all make it sound so easy :) How do I write the above using a function? Cheers, Jen
Re: [GENERAL] 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 signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Create table if not exists ... how ??
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 m...@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: [GENERAL] Create table if not exists ... how ??
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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
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 m...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
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 Description: OpenPGP digital signature
Re: [GENERAL] Create table if not exists ... how ??
On Mon, Jul 19, 2010 at 10:33 AM, Jennifer Trey jennifer.t...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
-Original Message- From: Joe Conway [mailto:m...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
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 Description: OpenPGP digital signature
