current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 10 down vote favorite
1

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?

share|improve this question
up vote 7 down vote accepted

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;
share|improve this answer
    
I'll play around with your suggestion. Thanks - I appreciate it. – user3591836 Sep 17 '14 at 18:25
    
If you are going to create a VIEW in order to copy the table structure, you might go all the way and use a MATERIALIZED VIEW instead. – Erwin Brandstetter Sep 17 '14 at 20:37
    
@ErwinBrandstetter Hm, that's true. Except that that doesn't accept an IF NOT EXISTS clause either; you can DROP MATERIALISED VIEW IF EXISTS, though. Without knowing the exact use case, it's hard to know if any of these options are actually relevant. – IMSoP Sep 17 '14 at 22:39
    
@ErwinBrandstetter be specific in the PostgreSQL version that supports feature because MATERIALIZED VIEW supports PostgreSQL version 9.3 and above – wingedpanther Sep 18 '14 at 5:06
    
@WingedPanther: Correct. I tend to assume the current version of Postgres for questions that can't be bothered to declare the actual version in use. – Erwin Brandstetter Sep 18 '14 at 13:17
up vote 2 down vote

If you are going to write a function for this, base it on system catalog table pg_class, not on views in the information schema or the statistics collector (which only exist if activated).

CREATE OR REPLACE FUNCTION create_table_qry(_tbl text
                                          , _qry text
                                          , _schema text = NULL)
  RETURNS bool AS
$func$
DECLARE
  _sch text := COALESCE(_schema, current_schema());
BEGIN

IF EXISTS (
   SELECT 1 
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = _sch
   AND    c.relname = _tbl
   ) THEN

   RAISE NOTICE 'Name is not free: %.%',_sch, _tbl;
   RETURN  FALSE;
ELSE
EXECUTE format('CREATE TABLE %I.%I AS %s', _sch, _tbl, _qry);

   RAISE NOTICE 'Table created successfully: %.%',_sch, _tbl;
   RETURN  TRUE;
END IF;

END
$func$  LANGUAGE plpgsql;

The function takes a table name and the query string, and optionally also a schema to create the table in (defaults to the current schema).

Note the correct use of = in the function header and := in the function body:

Also note how identifiers are escaped as identifiers. You can't use regclass, since the table does not exist, yet:

share|improve this answer
up vote 0 down vote

Try this,

create or replace function create_table(tblname text) returns text as
$$ 
BEGIN
$1 = trim($1);
IF not EXISTS (select relname from pg_stat_user_tables where relname =$1) THEN
execute 'create table '||$1||' as select * from tbl'; -- <put your query here>
return ''||$1||' Created Successfully !!';
else
return  ''||$1||' Already Exists !!';
END IF;
END
$$
language plpgsql 

create or replace function create_table_qry(tblname text,qry text) returns text as
$$ 
BEGIN
$1 = trim($1);
IF not EXISTS (select relname from pg_stat_user_tables where relname =$1) THEN
execute 'create table '||$1||' as '||$2||'';
return ''||$1||' Created Successfully !!';
else
return  ''||$1||' Already Exists !!';
END IF;
END
$$
language plpgsql
share|improve this answer
    
There are a couple of weak spots in the function. To much for a comment, I added another answer. – Erwin Brandstetter Sep 18 '14 at 14:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.