CODEWITHSUNDEEP
sqlpostgresql
ABHILESH SINGH
ABHILESH SINGH

Reputation: 25

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.

Upvotes: 0

Views: 833

Answers (2)

Ramin Faracov
Ramin Faracov

Reputation: 3303

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.

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions