PostgreSQL, CREATE TABLE AS with predefined column(s)

Question

For a first time I find very handy way for importing "last year data" to "this year data".
This works well:

DROP TABLE IF EXISTS mytable;
CREATE TABLE mytable AS
SELECT col1, col2, col3, col4 
  FROM dblink('host=localhost port=xxxx user=xxxx password=xxxx dbname=mylastyeardb',
              'SELECT col1, col2, col3, col4
               FROM mytable 
               WHERE TRIM(col1)<>'''' ')
               AS x(col1 text, col2 text, col3 text, col4 text);

ALTER TABLE mytable ADD COLUMN cols_id SERIAL PRIMARY KEY;

Since 'cols_id' from old table is not appropriate for a new table maybe some of experienced users know how to setup a table in CREATE TABLE AS that it have 'cols_id' as (serial) primary key nice ordered and as a first column. Maybe such way I can avoid using of second (ALTER) command?

Any other advice for showed situation will be welcome too.

The immediate solution is to first create the table then do an insert into ... select but a foreign table would be a lot easier to deal with. — user330315
– user330315, Commented Jan 7, 2018 at 18:58
I understand that. That's why I ask for solution with CREATE TABLE AS and SELECT query. It seem's a bit strange to be able to create and fill a table but unable to define it's columns. — Wine Too
– Wine Too, Commented Jan 7, 2018 at 19:03

Vao Tsun · Accepted Answer · 2018-01-09 09:19:04Z

you either create table, defining its structure (with all handy shortcuts and options in one statement), or create table as select, "inheriting" [partially] the structure. Thus if you want primary key, you will need alter tabale any way...

To put id as first column in one statement, you can simply use a dummy value, eg sequential number:

t=# create table s as select row_number() over()  as id,chr(n) from generate_series(197,200) n;
SELECT 4
t=# select * from s;
 id | chr
----+-----
  1 | Å
  2 | Æ
  3 | Ç
  4 | È
(4 rows)

Of course after that you still need to create sequence, assign its value as default to the id column and add primary key on ot. Which makes it even more statements then you have ATM...

Thanks Vao. Since I have to do something I do those more complicated steps with separately creating a table first (if not exists), TRUNCATE it, setval for always ensure index 1 and then INSERT INTO etc...

Collectives™ on Stack Overflow

PostgreSQL, CREATE TABLE AS with predefined column(s)

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related