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