This is the way to create a new table in PostgreSQL,
CREATE TABLE foo ( foo_id int PRIMARY KEY, bar text, );
How do I do this if it needs to check if the table already exists and to proceed with the
CREATE only if one is not found?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
It needs to check if the table already exists and create only if not found?
There is no standards-compliant method at all, but PostgreSQL 9.1+ supports a common extension with the
IF NOT EXISTS clause,
CREATE TABLE IF NOT EXISTS foo ( foo_id int PRIMARY KEY );
If the table (
foo in this case) doesn't exist, you'll get only a
NOTICE (not an
NOTICE: relation "foo" already exists, skipping
Note: while the DDL
IF NOT EXISTS syntax is non-standard, other databases like MySQL may also support it, and SQL Server's transact SQL supports a variant of it
IF NOT EXISTS (query) ... DDL ... GO