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?sqlpostgresql
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.
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:
[email protected]$ 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.
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
[email protected] ~ $ 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.
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.
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
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);