How to create custom sequences in PostgreSQL?-CodePudding

I would like to know how I can do something equivalent to what I did with the "code" column of the following table in SQL Server and implement it in PostgreSQL.

CREATE TABLE test(
    id INT IDENTITY(1,1) NOT NULL,
    code AS ('P'   RIGHT('0000'   CONVERT(VARCHAR, id), (4)))
);

CodePudding user response：

Postgres uses SQL standard compliant identity columns. So identity(1,1) won't work.

Generated columns can be done in a similar manner. Padding a number with a number of zeros can be done using lpad()

CREATE TABLE test
(
  id INT generated always as identity not null,
  code text generated always AS ('P'||lpad(id::text, 4, '0')) stored
);