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

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