How to set auto increment primary key in PostgreSQL?
I have a table in PostgreSQL with 22 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 a create an auto-incrementing primary key in PostgreSQL without recreating the table again?
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.
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.
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.
链接地址: http://www.djcxy.com/p/95204.html