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

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


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
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.