CREATE TABLE if not exists based on a SELECT statement

Question

I want to create a table if it does not exists based on a select statement in PostgreSQL 9.2. When I use the below query, i get an error as mentioned below.

Query:

CREATE TABLE IF NOT EXISTS ccdb_archival.bills
SELECT *, now() AS archival_date
FROM ccdb.bills
WHERE bill_date::date >= current_date - interval '3 years' AND bill_date::date < current_date - interval '8 years';

Error:

ERROR:  syntax error at or near "SELECT"
LINE 2:  SELECT *, now() AS archival_date

Can someone suggest how can I achieve this.

You can upgrade to 9.5. The possibility to use `if not exists` with `create table as select` was added with that version — , Mar 03 '16 at 09:32
Take a look a [this](http://stackoverflow.com/a/25897594/3541845) — haihui, Mar 03 '16 at 09:58
I need an extra column in the new table apart from the existing columns from my old table. — Yousuf Sultan, Mar 03 '16 at 10:00

score 2 · Accepted Answer · answered Mar 03 '16 at 10:14

I did get an alternate for this. I used the below mentioned code.

CREATE OR REPLACE FUNCTION ccdb_archival.ccdb_archival()
 RETURNS void AS
$BODY$
BEGIN

CREATE TABLE IF NOT EXISTS ccdb_archival.bills (LIKE ccdb.bills INCLUDING ALL);
    BEGIN
        ALTER TABLE ccdb_archival.bills ADD COLUMN archival_date timestamp;
    EXCEPTION
        WHEN duplicate_column THEN RAISE NOTICE 'column archival_date already exists in ccdb_archival.bills.';
    END;

INSERT INTO ccdb_archival.bills
SELECT *, now() AS archival_date
FROM ccdb.bills
WHERE bill_date::date >= current_date - interval '3 years' AND bill_date::date < current_date - interval '8 years';


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TABLE if not exists based on a SELECT statement

1 Answers1