[sql] How to set auto increment primary key in PostgreSQL?

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?

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.

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.

Auto incrementing primary key in postgresql:

Step 1, create your table:

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

Step 2, insert values into your table like this, notice that mytable_key is not specified in the first parameter list, this causes the default sequence to autoincrement.

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

Step 3, select * from your table:

el@voyager$ psql -U pgadmin -d kurz_prod -c "select * from epictable"

Step 4, interpret the output:

mytable_key  |        moobars        |  foobars   
-------------+-----------------------+------------
           1 | delicious moobars     | 2012-05-01
           2 | world wide interblags | 2012-05-02
(2 rows)

Observe that mytable_key column has been auto incremented.

ProTip:

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 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.

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.

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) 

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);