monolayer-pg

Appearance

Identity Columns ​

An identity column is a special column that is generated automatically from an implicit sequence. It can be used to generate key values.

It's PostgreSQL native implementation of serial and bigserial data types in other SQL implementations.

The following column data types can be used as identity columns:

There are two types identity columns: GENERATED ALWAYS AS and GENERATED BY DEFAULT AS. They mainly differ in whether the database will allow you to override the generated value when inserting data.

  • GENERATED ALWAYS AS columns are values always generated by the database, and you cannot override the generated value when inserting a new row (expect when using the OVERRIDING SYSTEM VALUE clause).

  • GENERATED BY DEFAULT AS columns values are generated by the database it be overridden by an explicit value value.

Generated always as identity column ​

ts
import { table, text } from "@monolayer/pg/schema";

const users = table({
  columns: {
    id: integer().generatedAlwaysAsIdentity(), 
  },
});

Generated by default as identity column ​

ts
const accounts = table({
  columns: {
    id: integer().generatedByDefaultAsIdentity(), 
  },
});