PostgreSQL Computed Column with Format ('TR-'+format([Id],'0000000000'))
Problem Description:
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 – 1
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
);