Category: Postgres

node-pg-migration is a useful library. You can add a constraint of a foreign key with a simple references property on the column. For example, you have table client_session with publishable_key column and you want to reference publishable_key column in workspace table where it is not a primary key (just a unique key). The following usage with object: schema, name, won’t work:

 pgm.createConstraint({
    schema: "seam",
    name: "client_session",
  }, "client_session_publishable_key_fkey", {
    foreignKeys: {
      columns: "publishable_key",
      references: {
        schema: "seam",
        name: "workspace",
      },
    },
  })

 If you write a migration for Postgress and Node.js using node-pg-migration (e.g., table 1) and want to create a foreign key reference / constraint (createConstraint or references on createTable) on a non-primary key of the other table (table 2), then references by an object containing schema and table name, do not work. This is probably a bug in node-pg-migration. It tries to match primary keys only, but our publishable_key is not a PK in workspace.

It looks like node-pg-migration is not smart enough to add the column/field name like it does for primary keys. You can use a string (seam.table2(col)) as the references value, not an object, or just use raw SQL to bypass node-pg-migration behavior, e.g.,

 pgm.sql(`
    ALTER TABLE seam.client_session
    ADD CONSTRAINT client_session_publishable_key_fkey
    FOREIGN KEY (publishable_key) REFERENCES seam.workspace(publishable_key);
  `)

Or in the node-pg-migration method:

 pgm.createConstraint({
    schema: "seam",
    name: "client_session",
  }, "client_session_publishable_key_fkey", {
    foreignKeys: {
      columns: "publishable_key",
      references: "seam.workspace(publishable_key)"
    },
  })
},

I recently needed to update the check constraint on a Postgres table column. This check does format validation similar to regex. This check was part of the CREATE TABLE statement meaning it was created with the table.

However, ALTER COLUMN is not supporting updates of checks. So what we need to do is to drop the constraint and create a new one. dropConstraint requires the name of the constraint, but the name is not obvious. This is because check was create with createTable. To find the name, there’s a SQL query we can run:

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';