Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

pgsql-general by date

Next:From: Jeff DavisDate: 2011-07-26 03:15:45
Subject: Re: [HACKERS] Error calling PG_RETURN_NULL()
Previous:From: Peter GeogheganDate: 2011-07-26 00:45:18
Subject: Re: create table as select... with auto increment id ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group