How to increment a table column using postgreSQL

Question

I would like to add some new values to ic_countries (plunet_english column), but I would also like to increment the id (landid). The table is set to unique id, but not auto increment.

Sadly my approach below provides an error around lastval()+1 as undefined. I have tried to add a function that increments starting from a hard coded value. Even that seems unobtainable.

INSERT INTO ic_countries (landid, plunet_english)
SELECT lastval()+1, name
FROM country
WHERE name NOT IN 
    (
    SELECT plunet_english
    FROM ic_countries
    )

Vivek S. · Accepted Answer · 2015-03-16 06:08:53Z

1

If you want to set the auto-increment in an existing table then do this

get the next number for field landid by using SELECT MAX(landid) + 1 FROM ic_countries if its 100 then

create a sequence

CREATE SEQUENCE seq_ic_countries_landid START WITH 100;

assign this sequence for the table

ALTER TABLE ic_countries ALTER COLUMN landid SET DEFAULT nextval('seq_ic_countries_landid');

now you can insert like below :

INSERT INTO ic_countries ( plunet_english)
SELECT  name --landid will be automatically increment
FROM country
WHERE name NOT IN 
    (
    SELECT plunet_english
    FROM ic_countries
    )

edited Mar 16, 2015 at 6:08

answered Mar 12, 2015 at 9:03

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Frank Heikens Over a year ago

max(landid)+1 fails when you have concurrent queries do the same thing: They all return the same result.

Andomar Over a year ago

Good answer, +1! @FrankHeikens: your comment seems entirely out of place for the question, it doesn't look like a very concurrent environment at all.

user330315 Over a year ago

@FrankHeikens: the max() is only used to initialize the sequence properly.

user3078690 Over a year ago

This will work great. I am using this table as a simulated spreadsheet so my logic did not go far enough to treat the table in its natural postgresql environment. Thanks

Collectives™ on Stack Overflow

How to increment a table column using postgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related