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

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

Thanks,
Sean

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


- -- A few ideas:

CREATE DATABASE sean;

\c sean

BEGIN;

SET client_min_messages = 'error';

CREATE SEQUENCE asdf1_seq;
CREATE TABLE asdf1 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf1_seq'),
foo TEXT
);

CREATE VIEW asdf AS SELECT *, 'ASDF-'||id AS asdf FROM asdf1;

INSERT INTO asdf1(foo) VALUES ('a view');

SELECT * FROM asdf;

CREATE SEQUENCE asdf2_seq;

CREATE TABLE asdf2 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf2_seq'),
asdf TEXT,
foo TEXT
);

CREATE FUNCTION update_asdf()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $_$ BEGIN NEW.asdf = 'ASDF-'||NEW.id; RETURN NEW; END $_$;

CREATE TRIGGER asdf_trigger BEFORE INSERT OR UPDATE ON asdf2
FOR EACH ROW EXECUTE PROCEDURE update_asdf();

INSERT INTO asdf2(foo) VALUES ('a trigger');

SELECT * FROM asdf2;


CREATE SEQUENCE asdf3_seq;

CREATE TABLE asdf3 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf3_seq'),
asdf TEXT NOT NULL DEFAULT 'ASDF-' || currval('asdf3_seq'),
foo TEXT
);

INSERT INTO asdf3(foo) VALUES ('currval hack');

SELECT * FROM asdf3;

CREATE SEQUENCE asdf4_seq;

CREATE TABLE asdf4 (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf4_seq'),
asdf TEXT,
foo TEXT
);

CREATE RULE asdf_rule AS ON INSERT TO asdf4
DO ALSO UPDATE asdf4 SET asdf='ASDF-'||id
WHERE id=currval('asdf4_seq');

INSERT INTO asdf4(foo) VALUES ('a rule');

SELECT * FROM asdf4;

ROLLBACK;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200710101159
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFHDPbivJuQZxSWSsgRA//fAJ9N91CgJJuMJJcxvyIbgiXYHPfCnQCg5Ahi
OdQTM77u4ZZuWDD6I3TqyIE=
=yCf4
-----END PGP SIGNATURE-----

Sean Davis skrev:
> 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?

What do you mean by "key"? Normally this word indicates something you
use to look up data by. In that case you will probably want an index on
it. So your indexing will be slower, not faster.

Nis

Greg Sabino Mullane wrote:
>
> -- A few ideas:
>
> CREATE DATABASE sean;
>
> \c sean
>
> BEGIN;
>
> SET client_min_messages = 'error';
>
> CREATE SEQUENCE asdf1_seq;
> CREATE TABLE asdf1 (
> id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf1_seq'),
> foo TEXT
> );
>
> CREATE VIEW asdf AS SELECT *, 'ASDF-'||id AS asdf FROM asdf1;
>
> INSERT INTO asdf1(foo) VALUES ('a view');
>
> SELECT * FROM asdf;
>
> CREATE SEQUENCE asdf2_seq;
>
> CREATE TABLE asdf2 (
> id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf2_seq'),
> asdf TEXT,
> foo TEXT
> );
>
> CREATE FUNCTION update_asdf()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS $_$ BEGIN NEW.asdf = 'ASDF-'||NEW.id; RETURN NEW; END $_$;
>
> CREATE TRIGGER asdf_trigger BEFORE INSERT OR UPDATE ON asdf2
> FOR EACH ROW EXECUTE PROCEDURE update_asdf();
>
> INSERT INTO asdf2(foo) VALUES ('a trigger');
>
> SELECT * FROM asdf2;
>
>
> CREATE SEQUENCE asdf3_seq;
>
> CREATE TABLE asdf3 (
> id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf3_seq'),
> asdf TEXT NOT NULL DEFAULT 'ASDF-' || currval('asdf3_seq'),
> foo TEXT
> );
>
> INSERT INTO asdf3(foo) VALUES ('currval hack');
>
> SELECT * FROM asdf3;
>
> CREATE SEQUENCE asdf4_seq;
>
> CREATE TABLE asdf4 (
> id INT NOT NULL PRIMARY KEY DEFAULT nextval('asdf4_seq'),
> asdf TEXT,
> foo TEXT
> );
>
> CREATE RULE asdf_rule AS ON INSERT TO asdf4
> DO ALSO UPDATE asdf4 SET asdf='ASDF-'||id
> WHERE id=currval('asdf4_seq');
>
> INSERT INTO asdf4(foo) VALUES ('a rule');
>
> SELECT * FROM asdf4;
>
> ROLLBACK;
>

Thanks, Greg.  These are great and answer my question in much more
detail than I had hoped.

Sean