Re: using sequences

Ernest E Vogelsinger wrote:

> A sequence is a funny thing. If you SELECT nextval('sequence_name'), it
> will return a value that is guaranteed unique (for this sequence), across
> all parallel accesses and transactions that may do the same at almost the
> same moment. SELECT currval('sequence_name') however is connection-bound,
> which means it will _always_ return the last value that has been obtained
> _by_this_connection_ (regardless of transactions).
>
> If you consider this you will see the need that you _first_ execute
> nextval() at least once, before currval() can be queried - it's simply not
> defined before. And that's what the message says anyway.
>
> If you have a serial field, you may safely
> INSERT INTO TABLE (cols) VALUE (vals)
> SELECT currval('table_id_sequence') as "row_id"
> and you will retrieve the serial ID that has been obtained by the previous
> insert.

Ah, now it makes perfect sense. However, for sake of experiment, when I
try just that, I get an error message that I'm inserting a duplicate
primary key.

Here's my table:

Table "public.news"
+-[ RECORD 1 ]--------------------
| Column | news_id
| Type | integer
| Modifiers | not null default nextval('public.news_news_id_seq'::text
+-[ RECORD 2 ]----------------
| Column | news_date
| Type | timestamp without time zone
| Modifiers | not null
+-[ RECORD 3 ]--------------------
| Column | expire_date
| Type | date
| Modifiers | not null
+-[ RECORD 4 ]---------------------
| Column | news_title
| Type | character varying(64)
| Modifiers | not null default ''
+-[ RECORD 5 ]-----------------------
| Column | news_info
| Type | text
| Modifiers | not null
+-[ RECORD 6 ]----------------------
| Column | user_id
| Type | integer
| Modifiers | not null
+-----------+-----------

And here's my INSERT statement:

be_db=# INSERT INTO news (news_date, expire_date, news_title, news_info,
user_id) VALUES (NOW(),'6/14/2003','sometitle here','some news here',1);

And here's the error message:

ERROR: Cannot insert a duplicate key into unique index news_pkey

What do you make of that? Thanks for helping me understand better about
sequences.

Erik