Re: References to SERIAL

"Brett W. McCoy" wrote:
>On Sat, 30 Dec 2000, Thomas SMETS wrote:
>
>> If i create a "internal pk" buy defining on a table a field SERIAL.
>> How do I reference this field in the other table to set the field
>> possible value ?
...
>
>You mean as a foreign key? You would do something like
>
>create table books_authors (
> book integer references book(book_pk)
> on delete no action,
...

If you need to know which value was used for the SERIAL field, there are
two ways:

1. Use currval('book_book_pk_seq') to get the last value used in this
session.

bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select currval('junk_id_seq');
currval
---------
1
(1 row)

2. Use the OID which is returned by a successful INSERT statement to look
up the newly-created row from the table:

bray=# insert into junk (name) values ('Fred');
INSERT 1780993 1
bray=# select * from junk where oid = 1780993 ;
id | name
----+------
1 | Fred
(1 row)

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Give to him that asketh thee, and from him that would
borrow of thee turn not away."
Matthew 5:42