Enrico Stahn

The next months are full of hard work because we have to master a huge migration task. We migrate all SQL-Statements to an ORM Layer. The application is already a bit long in the tooth and has approximately 8000 queries. The database itself has some strange design issues and some of them are incompatible with the ORM layer. One of this issues is the lack of a primary key at some tables. In PostgreSQL we can solve this with the following steps:

• Add a column with type integer to your table
• Create a sequence
• Update the column table with sequence values
• Set the necessary column properties (e.g. default, not null, etc.

Example for table “foo” and column “id”:

ALTER TABLE "public"."foo"   ADD COLUMN "id" INTEGER;
CREATE SEQUENCE "public"."foo_id_seq";
UPDATE foo SET id = nextval('"public"."foo_id_seq"');
ALTER TABLE "public"."foo"
  ALTER COLUMN "id" SET DEFAULT nextval('"public"."foo_id_seq"');
ALTER TABLE "public"."foo"
  ALTER COLUMN "id" SET NOT NULL;
ALTER TABLE "public"."foo" ADD UNIQUE ("id");
ALTER TABLE "public"."foo" DROP CONSTRAINT "foo_id_key" RESTRICT;
ALTER TABLE "public"."foo" ADD PRIMARY KEY ("id");