25

I have a database in Postgresql, which was migrated from SQL Server (only data).
On SQL Server, a table from this database has these columns:

measure_id
datum
measure

where measure_id is auto-incremental primary key, datum is datetime and measure is float.
After migration in Postrgresql, measure_id is column of type bigint.

How can I change this column (measure_id) to bigserial and assign it as primary key, now that my table is full of data?

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
zetah
  • 355
  • 1
  • 3
  • 5

3 Answers3

34

Create a sequence and use it as the default value for the column:

create sequence measures_measure_id_seq
   owned by measures.measure_id;

alter table measures
   alter column measure_id set default nextval('measures_measure_id_seq');


commit;

That essentially what serial does.

See the manual for details:
http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

Starting with Postgres 10, the recommended way is to use standard compliant identity columns, rather than serial (or bigserial).

alter table measures
   alter measure_id add generated always as identity;

In both cases, you will have to adjust the sequence to match the current maximum value in the measure_id column:

select setval(pg_get_serial_sequence('measures', 'measure_id'), max(measure_id))
from measures;
8

I see that the question is already marked as solved, however, as pointed out in the comments, it is missing the case where there are existing records that already have some values set in the relevant column.

I'll list some other cases as well.

I'm doing this for a table called products which is present in the public schema. The auto-increment is being done for the id column of this table.

1. For a non-existing column

-- auto-increment constraint for a new column
 ALTER TABLE public.products
    ADD COLUMN id SERIAL PRIMARY KEY;

2. For an existing column that got no values in the table

-- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;

-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');

3. For an existing column that already got some values in the table

-- create sequence
CREATE SEQUENCE public_products_id_seq OWNED BY public.products.id;

-- set the current value of the sequence to the max value from that column
-- (id column in this scenario)
SELECT SETVAL('public_products_id_seq', (select max(id) from public.products), false)


-- use sequence for the target column
ALTER TABLE public.products ALTER COLUMN id SET DEFAULT nextval('public_products_id_seq');

Documentation references for CREATE SEQUENCE and SETVAL

Masroor
  • 181
  • 1
  • 2
3

I'm posting this because since PG10 serial syntax has been replaced by generated always|default as identity to make it SQL Standard compliant, so it changes things a bit. Now you don't have to define the sequence that Postgres internally uses to manage the autoincrement values.

For an existing column that already got some values in the table (PG10+)

-- Let's suppose we have a table like this
create table my_schema.my_table (
    my_serial_col integer primary key,
    another_column varchar
);

-- With this sentence we convert primary key column to autoincrement
alter table my_table alter my_serial_col add generated always as identity;


-- Let's check the name of the autogenerated sequence, just in case
select pg_get_serial_sequence('my_schema.my_table','my_serial_col');


-- Let's suppose the name of the sequence was my_schema.my_table_my_serial_col_seq1


-- This sentence updates the sequence to it's next value
select SETVAL('my_schema.my_table_my_serial_col_seq1', (select max(my_serial_col) from my_schema.my_table), true);

Note: last sentence's true value is used to tell the sequence that next time it's invoked it should give the next value to the one used to update it with the setval() function. So the last instruction would be equivalent to this one:

select SETVAL('my_schema.my_table_my_serial_col_seq1', (select max(my_serial_col)+1 from my_schema.my_table), false);
EAmez
  • 175
  • 1
  • 10