From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | David Salisbury <salisbury(at)globe(dot)gov> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: create table as select... with auto increment id ? |
Date: | 2011-07-26 00:45:35 |
Message-ID: | 4E2E0E2F.2080808@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2011-07-26 03:15:45 | Re: [HACKERS] Error calling PG_RETURN_NULL() |
Previous Message | Peter Geoghegan | 2011-07-26 00:45:18 | Re: create table as select... with auto increment id ? |