Thread: Automatically update sequence

Hi all,

Is it possible to automatically update the sequence after the literal
value was used in INSERT statement?

For instance,

CREATE SEQUENCE test_table_id;    -- test_table_id = 1
CREATE TABLE test_table (
    id INTEGER NOT NULL DEFAULT nextval('test_table_id') CHECK (id >= 0),
    column1 VARCHAR(255),
    PRIMARY KEY (id)
)

INSERT INTO test_table (id, column1) VALUES (1000, 'something');

Here, I used literal value for ID (1000), and I want my sequence to be
updated to 1000 automatically (i.e. without calling setval).

I'm trying to achieve something similar to MySQL's auto_increment feature.

Regards,
Ognjen

On Fri, Mar 28, 2008 at 11:54 AM, Ognjen Blagojevic <ognjen@etf.bg.ac.yu> wrote:
> Hi all,
>
>  Is it possible to automatically update the sequence after the literal
>  value was used in INSERT statement?
>
>  For instance,
>
>  CREATE SEQUENCE test_table_id;    -- test_table_id = 1
>  CREATE TABLE test_table (
>     id INTEGER NOT NULL DEFAULT nextval('test_table_id') CHECK (id >= 0),
>     column1 VARCHAR(255),
>     PRIMARY KEY (id)
>  )
>
>  INSERT INTO test_table (id, column1) VALUES (1000, 'something');
>
>  Here, I used literal value for ID (1000), and I want my sequence to be
>  updated to 1000 automatically (i.e. without calling setval).
>
>  I'm trying to achieve something similar to MySQL's auto_increment feature.

You'll probably want to look at currval and nextval.

Sean

On Friday 28 March 2008, Ognjen Blagojevic <ognjen@etf.bg.ac.yu> wrote:
> Here, I used literal value for ID (1000), and I want my sequence to be
> updated to 1000 automatically (i.e. without calling setval).

You could probably use a trigger to do it, but it's not a great idea.

What you should do is fetch the next value from the sequence yourself and
then use it in the insert. Or, if you're pre-loading existing data, load it
and then set the sequence value afterwards.

--
Alan

Alan Hodgson wrote:
> What you should do is fetch the next value from the sequence yourself and
> then use it in the insert. Or, if you're pre-loading existing data, load it
> and then set the sequence value afterwards.

I must use it in insert since it is an export from MySQL database.

I understand that I can update the sequence afterwards, but I was just
wondering is it possible that Postgres updates it automatically after I
insert the data?

Regards,
Ognjen