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 ...
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;
how to to first check whether the table exist or not before creating it in postgresql?
PostgreSQL uses the following syntax:
CREATE TABLE IF NOT EXISTS mytable (
-- Column definitions...
)
CREATE TABLE WITH NO DATA is very slow
WITH NO DATA
still executes the query, it just ignores the result.
The better way to do that would be to avoid CREATE TABLE ... AS
:
CREATE TABLE my_temp_table (LIKE my_enormous_table);
That also allows you to use the INCLUDING
clause to copy default values, storage parameters, constraints and other things from the original table:
CREATE TABLE my_temp_table (LIKE my_enormous_table
INCLUDING CONSTRAINTS INCLUDING DEFAULTS);
Related Topics
How to Get Second Largest or Third Largest Entry from a Table
Concatenate Two Database Columns into One Resultset Column
Way to Try Multiple Selects Till a Result Is Available
Sorting Null Values After All Others, Except Special
Disable Rails SQL Logging in Console
SQL Syntax Term for 'Where (Col1, Col2) < (Val1, Val2)'
Call a Set-Returning Function with an Array Argument Multiple Times
Concatenate Results from a SQL Query in Oracle
SQL Server Date Format Function
Insert Into... Merge... Select (SQL Server)
SQL Server Operating System Error 5: "5(Access Is Denied.)"