Postgresql – Change existing column in PG to auto-incremental primary key

migrationpostgresqlpostgresql-9.3

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?

Best Answer

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

Related Solutions

Sql-server – Extra column when migrating Sybase databases to single SQL Server database

Turns out SSMA just ignores any extra columns you create after migrating the schema - so I could set the default for the project_id columns before migrating data from each Sybase database and have them populated with those defaults.

PostgreSQL – retrieving max primary key column to insert a new row – any alternatives

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT.

Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT:

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Edit,

I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes. You'd need currval or another one

Best Answer

Related Solutions

Sql-server – Extra column when migrating Sybase databases to single SQL Server database

PostgreSQL – retrieving max primary key column to insert a new row – any alternatives

Related Question