SQL: how to find if a table exists?

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:

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?

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

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

select tablename from pg_tables?