3

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)))
);
Felipe Renovato
  • 57
  • 5

1 Answers1

4

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
);