Question 90601 in PostgreSQL Question posted in
The official documentation can be found here.

how to generate a table in postgresql so that one column's value can be generated based on another column value

I’m trying to make a table in which I can generate a column value based on another column value.

For instance:

id (primary key)(identity)(int) temp_id (varchar) desc (varchar)
1 temp1 hello
2 temp2 hello brother

temp_id column’s value should be set automatically like (‘temp’ + id) based on id’s value of that row.

I tried to go through this example but was unable to achieve my goal.

Tags:

2

Answers


  1. 0

    You can use a generated column:

    create table the_table 
(
  id int primary key generated always as identity,
  temp_id text generated always as ('temp'||id::text) stored,
  "desc" text
);

    But why store this at all? A view that returns that expression would be more efficient.

    Login or Signup to reply.
  2. 0

    Usually, in such cases, it is not necessary to update any field, it is enough to generate and display this field every time you write select.

    select *, 'temp' || id::text as temp_id from table

    Or you can create view only one time and use this anytime anywhere:

    create view tableview as 
select *, 'temp' || id::text as temp_id from table;

select * from tableview;

    If you need this field only in table then you can use generate always field for during creating this table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search