create table as select... with auto increment id ?

On 26 July 2011 01:17, David Salisbury <salisbury@globe.gov> wrote:

I work with rails developers and they are fussy about having an
auto incrementing "id" field.  Is there a way I can eak that out
of the above type statement, or am I stuck with creating the
table and no short cuts?

create table freaky ( id autoincrement, and-a-long-list int,
and-perfect-order float )
insert into freaky as select "abunchofstuff-in-same-order"

I think I know the answer ;-<

http://wiki.postgresql.org/wiki/FAQ#How_do_I_create_a_serial.2Fauto-incrementing_field.3F

Hope that helps

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On 07/25/2011 05:17 PM, David Salisbury wrote:

We all know i can

create table freaky as select "abunchofstuff".

I work with rails developers and they are fussy about having an
auto incrementing "id" field. Is there a way I can eak that out
of the above type statement, or am I stuck with creating the
table and no short cuts?

create table freaky ( id autoincrement, and-a-long-list int,
and-perfect-order float )
insert into freaky as select "abunchofstuff-in-same-order"

I think I know the answer ;-<

Not exactly as you wanted, but you can do the following:

create table twserid(id serial primary key, f1 text not null);
create table like_twserid(like twserid including all);

contrib_regression=# \d twserid
Table "public.twserid"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('twserid_id_seq'::regclass)
f1 | text | not null
Indexes:
"twserid_pkey" PRIMARY KEY, btree (id)

contrib_regression=# \d like_twserid
Table "public.like_twserid"
Column | Type | Modifiers
--------+---------+------------------------------------------------------
id | integer | not null default nextval('twserid_id_seq'::regclass)
f1 | text | not null
Indexes:
"like_twserid_pkey" PRIMARY KEY, btree (id)

Then something like:
insert into like_twserid select * from twserid;
SELECT setval('twserid_id_seq', (select max(id) from like_twserid));

HTH,

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support