How to store the result of a query in an existing column using PostgreSql

Question

I have the following table

user

**id, name**, timestamp, place, time

the id with the name form the PK

I want to store the result of the following query in the time column which is already exist but contain no data

SELECT "time"(timestamp) FROM user as time

so if I had for example the following data

id: 1, name: Ann, timestamp: "2002-08-30 08:24:00", Place:US time:

and if if result of the following query

 SELECT "time"(timestamp) FROM user

is 08:24:00

I would like to store that in the time column and I want to do that for all the users in the table

I tried the following

SELECT "time"(timestamp) FROM user as time

but it only displays the result as an output and I would like it to be stored

is there a way to do that ?

thanks

Will both columns continue to exists or are you just migrating from a timestamp field to a time field? If you keep them both do they have to stay in sync or will the time field also sometimes have another value then the time part of the timestamp? — Eelke
– Eelke, Commented Jul 11, 2012 at 4:54

mu is too short · Accepted Answer · 2012-07-11 04:39:28Z

2

If you just want to set the "time" of each row to the time-of-day of its "timestamp" then:

update "user" set time = timestamp::time;

answered Jul 11, 2012 at 4:39

mu is too short

436k71 gold badges862 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1