Re: [GENERAL] Create table if not exists ... how ??

2010-07-20 Thread Jennifer Trey
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-20 Thread 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

[GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Jennifer Trey
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread 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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Pavel Stehule
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Peter Geoghegan
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: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Jennifer Trey
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 ??

2010-07-19 Thread Joe Conway
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Jennifer Trey
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Steve Atkins
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Said Ramirez
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Joe Conway
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Scott Marlowe
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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread Igor Neyman
-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

Re: [GENERAL] Create table if not exists ... how ??

2010-07-19 Thread 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