PostgreSQL: Create table if not exists AS

sql postgresql create-table
user3591836 picture user3591836 · Sep 17, 2014 · Viewed 37.8k times · Source

I'm using PostgreSQL and am an SQL beginner. I'm trying to create a table from a query, and if I run:

CREATE TABLE table_name AS
   (....query...)

it works just fine. But then if I add 'if not exists' and run:

CREATE TABLE IF NOT EXISTS table_name AS
   (....query...)

using exactly the same query, I get:

ERROR: syntax error at or near "as"

Is there any way to do this?

Answer

IMSoP picture IMSoP · Sep 17, 2014

CREATE TABLE AS is considered a separate statement from a normal CREATE TABLE, and until Postgres version 9.5 (see changelog entry) didn't support an IF NOT EXISTS clause. (Be sure to look at the correct version of the manual for the version you are using.)

Although not quite as flexible, the CREATE TABLE ... LIKE syntax might be an alternative in some situations; rather than taking its structure (and content) from a SELECT statement, it copies the structure of another table or view.

Consequently, you could write something like this (untested); the final insert is a rather messy way of doing nothing if the table is already populated:

CREATE OR REPLACE VIEW source_data AS SELECT * FROM foo NATURAL JOIN bar;

CREATE TABLE IF NOT EXISTS snapshot LIKE source_data;

INSERT INTO snapshot
SELECT * FROM source_data
WHERE NOT EXISTS ( SELECT * FROM snapshot );

Alternatively, if you want to discard previous data (e.g. an abandoned temporary table), you could conditionally drop the old table, and unconditionally create the new one:

DROP TABLE IF EXISTS temp_stuff;

CREATE TEMPORARY TABLE temp_stuff AS SELECT * FROM foo NATURAL JOIN bar;

Related questions

PostgreSQL create table if not exists

In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL?

Read More
Adding named foreign key constraints in a SQL Create statement

I currently have: CREATE TABLE galleries_gallery ( id INT NOT NULL PRIMARY KEY IDENTITY, title NVARCHAR(50) UNIQUE NOT NULL, description VARCHAR(256), templateID INT NOT NULL REFERENCES galleries_templates(id), jsAltImgID INT NOT NULL REFERENCES libraryImage(id) jsAltText NVARCHAR(500), dateCreated SMALLDATETIME …

Read More
postgresql syntax error when creating a table

Hey everyone I need some help with creating tables. I have the script below and it creates a few tables. When i try to run the script it give me this error: psql:script.sql:10: ERROR: syntax error at or …

Read More