CODEWITHSUNDEEP
postgresql
Wine Too
Wine Too

Reputation: 4655

PostgreSQL, CREATE TABLE AS with predefined column(s)

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.

Upvotes: 0

Views: 1290

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51466

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...

Upvotes: 1

Related Questions