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

Question

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

With which part exactly of [the documented syntax](https://www.postgresql.org/docs/current/sql-altertable.html) do you have a problem? — , Dec 05 '22 at 08:55
return syntax error with keyword (format) - ('TR-'+format([Id],'0000000000')) — Ahmed Mostafa, Dec 05 '22 at 09:08

score 1 · Accepted Answer · answered Dec 05 '22 at 10:49

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

1 Answers1