How to set auto increment primary key in PostgreSQL?

Question

I have a table in PostgreSQL with many columns, and I want to add an auto increment primary key.

I tried to create a column called id of type BIGSERIAL but pgadmin responded with an error:

ERROR: sequence must have same owner as table it is linked to.

Does anyone know how to fix this issue? How do I add or create an auto-incrementing primary key in PostgreSQL without recreating the table?

Answer 1

Try this command:

ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

Try it with the same DB-user as the one you have created the table.

Answer 2

Auto incrementing primary key in postgresql:

Create your table:

CREATE TABLE epictable
(
    mytable_key    serial primary key,
    moobars        VARCHAR(40) not null,
    foobars        DATE
);

Insert values into your table:

insert into epictable(moobars,foobars) values('delicious moobar','2012-05-01')
insert into epictable(moobars,foobars) values('WorldWideBlag','2012-05-02')

select * from your table:

select * from epictable

mytable_key  |        moobars        |  foobars   
-------------+-----------------------+------------
           1 | delicious moobar      | 2012-05-01
           2 | WorldWideBlag         | 2012-05-02
(2 rows)

Observe that mytable_key column has been auto incremented.

ProTips:

You should always be using a primary key on your table because postgresql internally uses hash table structures to increase the speed of inserts, deletes, updates and selects. If a primary key column (which is forced unique and non-null) is available, it can be depended on to provide a unique seed for the hash function. If no primary key column is available, the hash function becomes inefficient as it selects some other set of columns as a key.

If you want more control over the behavior of the serial key, then see postgresql sequences.

Answer 3

Create an auto incrementing primary key in postgresql, using a custom sequence:

Step 1, create your sequence:

create sequence splog_adfarm_seq
    start 1
    increment 1
    NO MAXVALUE
    CACHE 1;
ALTER TABLE fact_stock_data_detail_seq
OWNER TO pgadmin;

Step 2, create your table

CREATE TABLE splog_adfarm
(
    splog_key    INT unique not null,
    splog_value  VARCHAR(100) not null
);

Step 3, insert into your table

insert into splog_adfarm values (
    nextval('splog_adfarm_seq'), 
    'Is your family tree a directed acyclic graph?'
);

insert into splog_adfarm values (
    nextval('splog_adfarm_seq'), 
    'Will the smart cookies catch the crumb?  Find out now!'
);

Step 4, observe the rows

el@defiant ~ $ psql -U pgadmin -d kurz_prod -c "select * from splog_adfarm"

splog_key |                            splog_value                             
----------+--------------------------------------------------------------------
        1 | Is your family tree a directed acyclic graph?
        2 | Will the smart cookies catch the crumb?  Find out now!
(3 rows)

The two rows have keys that start at 1 and are incremented by 1, as defined by the sequence.

Bonus Elite ProTip:

Programmers hate typing, and typing out the nextval('splog_adfarm_seq') is annoying. You can type DEFAULT for that parameter instead, like this:

insert into splog_adfarm values (
    DEFAULT, 
    'Sufficient intelligence to outwit a thimble.'
);

For the above to work, you have to define a default value for that key column on splog_adfarm table. Which is prettier.

Answer 4

serial is the old way to auto generate unique values and it is not part of the SQL standard.

After PostgreSQL 10, you can use generated as identity, it is compliant with SQL standard:

CREATE TABLE t1 (id integer primary key generated always as identity);

or

CREATE TABLE t1 (id integer primary key generated by default as identity); 

The difference between by default and always:

• The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) values into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error.
• The GENERATED BY DEFAULT also instructs PostgreSQL to generate a value for the identity column. However, if you supply a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.

For more information.

Answer 5

If you want to do this in pgadmin, it is much easier. It seems in postgressql, to add a auto increment to a column, we first need to create a auto increment sequence and add it to the required column. I did like this.

1) Firstly you need to make sure there is a primary key for your table. Also keep the data type of the primary key in bigint or smallint. (I used bigint, could not find a datatype called serial as mentioned in other answers elsewhere)

2)Then add a sequence by right clicking on sequence-> add new sequence. If there is no data in the table, leave the sequence as it is, don't make any changes. Just save it. If there is existing data, add the last or highest value in the primary key column to the Current value in Definitions tab as shown below.

3)Finally, add the line nextval('your_sequence_name'::regclass) to the Default value in your primary key as shown below.

Make sure the sequence name is correct here. This is all and auto increment should work.

Answer 6

If you want to use numbers in a sequence, define a new sequence with something like

CREATE SEQUENCE public.your_sequence
    INCREMENT 1
    START 1
    MINVALUE 1
;

and then alter the table to use the sequence for the id:

ALTER TABLE ONLY table ALTER COLUMN id SET DEFAULT nextval('your_sequence'::regclass);

Answer 7

I have tried the following script to successfully auto-increment the primary key in PostgreSQL.

CREATE SEQUENCE dummy_id_seq
    START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

CREATE table dummyTable (
    id bigint DEFAULT nextval('dummy_id_seq'::regclass) NOT NULL,
    name character varying(50)
);

EDIT:

CREATE table dummyTable (
    id SERIAL NOT NULL,
    name character varying(50)
)

SERIAL keyword automatically create a sequence for respective column.

Answer 8

Steps to do it on PgAdmin:

• CREATE SEQUENCE sequnence_title START 1; // if table exist last id
• Add this sequense to the primary key, table - properties - columns - column_id(primary key) edit - Constraints - Add nextval('sequnence_title'::regclass) to the field default.

Answer 9

Maybe I'm a bit of late to answer this question, but I'm working on this subject at my job :)

I wanted to write column 'a_code' = c1,c2,c3,c4...

Firstly I opened a column with the name ref_id and the type serial. Then I solved my problem with this command:

update myschema.mytable set a_code=cast('c'||"ref_id" as text) 

Answer 10

You can use below code for auto increment

Create table public.EmployeeDapper
(
    Id int not null generated by default as identity(increment by 1 start 1 
    minvalue 1 maxvalue 2147483647 cache 1),

    Name varchar(50) not null,
    Age int not null,
    Position varchar(50)not null
)