Search code examples
postgresqlgenerated-columns

PostgreSQL Computed Column with Format ('TR-'+format([Id],'0000000000'))

I am new to Postgres and I need to create computed column to format request number as below

TR-000000001
TR-000000011
TR-000000111

in SQL server i handled it as follow

('TR-'+format([Id],'0000000000'))

and it works fine

How can I do this using PostgreSQL

Solution

  • As documented in the manual the concatenation operator in standard SQL (and PostgreSQL) is || - the + is for adding numbers.

    The syntax to define a generated columns is also documented in the manual and follows the pattern:

     <column name> <data type> generated always as (<expression>) stored

    To convert a number to a string with leading 0, you can use the lpad() function.

    Putting this all together, you are looking for something like:

    create table the_table 
(
  id int primary key,
  formatted_id text generated always as ('TR-'||lpad(id::text, 10, '0')) stored
);