CODEWITHSUNDEEP
postgresqlcreate-table
Yousuf Sultan
Yousuf Sultan

Reputation: 3257

CREATE TABLE if not exists based on a SELECT statement

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.

Upvotes: 0

Views: 1443

Answers (1)

Yousuf Sultan
Yousuf Sultan

Reputation: 3257

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;

Upvotes: 2

Related Questions