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