Thread: SQL: how to find if a table exists?

I need to programmatically create a table if it does not already exists.

Is there an SQL statement that will allow me to query a DB to see if a
table exists?

Thanks,

Jc

Look into the pg_class system table for a matching 'relame', the code
below can be installed as a plpgsql function to add a 'table_exists()'
function which returns boolean:

 CREATE OR REPLACE FUNCTION table_exists(NAME) RETURNS BOOLEAN AS '
    DECLARE
        tab ALIAS FOR $1;
        rec RECORD;
    BEGIN
        SELECT INTO rec *
            FROM  pg_class c
            WHERE c.relname = tab;
        IF NOT FOUND THEN
            RETURN false;
        ELSE
            RETURN true;
        END IF;
    END;
 ' LANGUAGE 'plpgsql';

This actually matches index names too, but works for my uses... This
system table is documented at:

 http://www.postgresql.org/idocs/index.php?catalog-pg-class.html

Lee.

Jean-Christian Imbeault writes:
 > I need to programmatically create a table if it does not already exists.
 >
 > Is there an SQL statement that will allow me to query a DB to see if a
 > table exists?

> Is there an SQL statement that will allow me to query a DB to see if a
> table exists?

yes, try:
select 1 from pg_class where relkind='r' and
relname='the-name-of-your-table';

On Saturday 07 Sep 2002 2:36 pm, Jean-Christian Imbeault wrote:
> I need to programmatically create a table if it does not already exists.
>
> Is there an SQL statement that will allow me to query a DB to see if a
> table exists?

select * from pg_tables

is about what you want. There are a bunch of other useful system-tables - see
the manual for details. Also you might want to start psql with -E and see
what \dt does.

- Richard Huxton

> Is there an SQL statement that will allow me to query a DB to see if a
> table exists?
select tablename from pg_tables?