PostgreSQL: Create table if not exists AS
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;
postgres: how to create table if not exists but ONLY if the schemas match?
You can specify the schema name in the table creation statement
CREATE TABLE IF NOT EXISTS myshema.mytable AS ...
Related Topics
How to Use Inno Setup to Update a Database Using .SQL Script
Speed of Paged Queries in Oracle
How to Run a SQL Plus Script in Powershell
Finding Free Slots in a Booking System
SQL Count* Group by Bigger Than,
Does SQL Server Allow Constraint Violations in a Transaction as Long as It's Not Committed Yet
How to Create a Blank/Hardcoded Column in a SQL Query
SQL How to Select the Most Recent Date Item
Postgresql: Create Table If Not Exists As
T-SQL Conditional Where Clause
SQL Joining Three Tables, Join Precedence
SQL Server Index - Any Improvement for Like Queries
The Wait Operation Timed Out. Asp
To Find Infinite Recursive Loop in Cte
Inserting Image into Blob Oracle 10G
How to Join the Most Recent Row in One Table to Another Table