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

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.