Update SERIAL value after import

Question

I have defined a serial PRIMARY KEY, like this:

CREATE TABLE auth_event(
    id serial PRIMARY KEY,
    time_stamp TIMESTAMP,
    client_ip VARCHAR(512),
    user_id INTEGER REFERENCES auth_user (id) ON DELETE CASCADE,
    origin VARCHAR(512),
    description TEXT
);

The dump that I am importing has explicit values for the serial field, like this:

INSERT INTO "auth_event" VALUES(6,'2012-12-03 21:50:49','127.0.0.1',181,'auth','User 181 Logged-in');
INSERT INTO "auth_event" VALUES(7,'2012-12-04 07:37:43','127.0.0.1',181,'auth','User 181 Logged-in');
INSERT INTO "auth_event" VALUES(8,'2012-12-05 11:42:28','127.0.0.1',181,'auth','User 181 Logged-in');
INSERT INTO "auth_event" VALUES(9,'2012-12-07 08:01:59','127.0.0.1',181,'auth','User 181 Logged-in');
INSERT INTO "auth_event" VALUES(10,'2012-12-07 16:32:58','127.0.0.1',181,'auth','User 181 Logged-in');
INSERT INTO "auth_event" VALUES(11,'2012-12-11 15:41:24','127.0.0.1',181,'auth','User 181 Logged-in');
INSERT INTO "auth_event" VALUES(12,'2012-12-11 22:55:08','127.0.0.1',181,'auth','User 181 Logged-in');

That means, according to this that the sequence value (the one related to auth_event.id) will not get updated.

And now comes my problem: the framework that I am using is not giving values to the serial field. That means that postgres is using the default value, and since it has not been updated, a collission occurs.

How can I update the sequence value related to auth_event.id after importing?

score 2 · Accepted Answer · 2013-01-17T11:01:47.830

2

select setval(pg_get_serial_sequence('auth_event' , 'id'), (select max(id) from auth_event) );

edited Jan 17 '13 at 11:01

answered Jan 17 '13 at 10:15

nearly but you don't need the "+ 1". Sequences increment before they return the next val. ;) – Philip Couling Jan 17 '13 at 10:26
@couling: are you sure about that? I have seen the +1 used in other places (http://stackoverflow.com/a/3698777/647991) – blueFast Jan 17 '13 at 10:38
Just to verify: how would I get the current value of a certain sequence? – blueFast Jan 17 '13 at 10:39
I already tried that: `select currval(pg_get_serial_sequence('auth_event' , 'id'));` It just gives: `ERROR: currval of sequence "auth_event_id_seq" is not yet defined in this session` – blueFast Jan 17 '13 at 10:49
Ok, so the answer to my question here seems to be NO: I can not get the current value of a sequence (without modifying it). Is this correct? – blueFast Jan 17 '13 at 10:57
By the way, @couling was right: the `+1` is not needed. And also: it is better to use `pg_get_serial_sequence('auth_event' , 'id')` instead of the literal name, to avoid false assumptions about sequence name. Can you update your answer before I accept it? – blueFast Jan 17 '13 at 10:59
@gonvaled: yes, because there is no "current" value unless you get the "next" value. – Jan 17 '13 at 10:59
Mmmm, but there must be: nextval knows it! – blueFast Jan 17 '13 at 11:01
In a philosophical sense there's no such thing as a sequence's current value. It's almost impossible to define in a multi threaded database. In multi threaded systems the idea of now *(relative to another thread's sense of now)* is meaningless (similar to twins paradox http://en.wikipedia.org/wiki/Twin_paradox). A sequence's purpose is give unique values to multiple threads with minimal locking. Thus to a sequence the idea of "now" can't be bound to a single thread. A thread can not ask "what is this sequence's value now?". Some DBMS offer it, but I think it's a bit to dirty for PostgreSQL. – Philip Couling Jan 17 '13 at 13:45
@couling: I don't think any DBMS that supports sequences (which is nearly all of them) gives you an *accurate* value when asking for the "current" values (without obtaining one first). – Jan 17 '13 at 13:59
@gonvaled: you can get a "hint" on what the current value is, by running `select * from sequence_name`. It will return a "last_value" column. But that is not necessarily an accurate state of the latest value due to caching and concurrency. – Jan 17 '13 at 14:00
OK, so what about this: I want to get the nextval of the sequence, but without actually updating the sequence. I understand that this value so obtained is useless, since next time I access the database it may have changed. But it is good enough for display purposes. – blueFast Jan 17 '13 at 14:41
what the sequence is doesn't really matter so much as what the max value int he table is. Best is to lock the table (so no new inserts can happen or try to happen) then select max(serialcolname) from the table, and setval based on that. – Scott Marlowe Jan 18 '13 at 07:58

Update SERIAL value after import

1 Answers1