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

Re: Using nextval(seq) in more than one column

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Using nextval(seq) in more than one column
Date: 2007-10-10 12:42:21
Message-ID: 20071010124221.GK9541@a-kretschmer.de (view raw or flat)
Thread:
Lists: pgsql-novice
am  Wed, dem 10.10.2007, um  8:18:33 -0400 mailte Sean Davis folgendes:
> I am trying to design some tables that have keys that look like:
> 
> ASDF-####
> 
> where #### should be derived from a sequence.  However, I would like the 
> primary key to be an integer for speed of indexing, etc.  I don't see a 
> way using standard DDL followed by inserts to have the #### be the same 
> number as the integer primary key.  Is that the case?
> 
> In other words, I would like the rows to look like:
> 
> 1   ASDF-1   ....
> 2   ASDF-2   ....

Do you mean something like this:

test=# create table asdf (id serial primary key, foo text);
NOTICE:  CREATE TABLE will create implicit sequence "asdf_id_seq" for serial column "asdf.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "asdf_pkey" for table "asdf"
CREATE TABLE
test=*# insert into asdf (foo) values ('foo1');
INSERT 0 1
test=*# insert into asdf (foo) values ('foo2');
INSERT 0 1
test=*# insert into asdf (foo) values ('foo3');
INSERT 0 1
test=*# select id, 'ASDF-'||id::text, foo from asdf;
 id | ?column? | foo
----+----------+------
  1 | ASDF-1   | foo1
  2 | ASDF-2   | foo2
  3 | ASDF-3   | foo3
(3 rows)



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

In response to

pgsql-novice by date

Next:From: Nis JørgensenDate: 2007-10-10 15:28:07
Subject: Re: Using nextval(seq) in more than one column
Previous:From: Sean DavisDate: 2007-10-10 12:18:33
Subject: Using nextval(seq) in more than one column

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