How to add a column to a table with populated data? [POSTGRES]

Question

I was googling this question, but couldn't find anything that works.

I'm to tying to add a column to a table in postgres and then to populate the new column with data for the entire column (see example below)

original table orders:

order_id|order_date|order_time
--------+----------+----------
 1|2015年01月01日| 11:38:36
 2|2015年01月01日| 11:57:40
 3|2015年01月01日| 12:12:28
 4|2015年01月01日| 12:16:31
 5|2015年01月01日| 12:21:30
 6|2015年01月01日| 12:29:36
 7|2015年01月01日| 12:50:37
 8|2015年01月01日| 12:51:37
 9|2015年01月01日| 12:52:01
 10|2015年01月01日| 13:00:15

Say, I would like to make a column which is a timestamp by combining full_date.

this calls for: ALTER TABLE orders ADD COLUMN full_date timestamp;

Now, I would like to alter the original table by adding the data for the full_date column, so it would look like this:

order_id|order_date|order_time|full_date |
--------+----------+----------+-----------------------+
 1|2015年01月01日| 11:38:36|2015年01月01日 11:38:36.000|
 2|2015年01月01日| 11:57:40|2015年01月01日 11:57:40.000|
 3|2015年01月01日| 12:12:28|2015年01月01日 12:12:28.000|
 4|2015年01月01日| 12:16:31|2015年01月01日 12:16:31.000|
 5|2015年01月01日| 12:21:30|2015年01月01日 12:21:30.000|
 6|2015年01月01日| 12:29:36|2015年01月01日 12:29:36.000|
 7|2015年01月01日| 12:50:37|2015年01月01日 12:50:37.000|
 8|2015年01月01日| 12:51:37|2015年01月01日 12:51:37.000|
 9|2015年01月01日| 12:52:01|2015年01月01日 12:52:01.000|
 10|2015年01月01日| 13:00:15|2015年01月01日 13:00:15.000|

The only solution I managed to find, is by creating a new table from the orders table and then dropping the original table.

I tried INSERT INTO orders(full_date) SELECT order_date + order_time FROM orders but it didn't work.

My question is, how do I populate the full_date data into the newly created column directly into the original orders table without creating a new table and then dropping the original.

Thank you in advance for any input!

• Sahap Asci: Please provide the structure of "orders" table. Normally date + time works.
• Sagi reichen: @SahapAsci, Thanks for the input. What do you mean by the structure of the 'orders' table? id = primary key order_date = date order_time = time full_date = timestamp
• mustaccio: Please consider following these suggestions.
• Lennart - Slava Ukraini: Have you concidered adding a GENERATED ALWAYS colum?
• user1822: @Sagireichen: no, you can add a generated column later as well. And it would automatically be populated with the result of the expression.

Answer 1

You can add a generated column:

alter table orders
 add column ts timestamp
 generated always as (dt + tm) stored;

Additional things to concider:

• Don't add a column at all. It does not add anything that cannot be expressed today (beside precision perhaps)
• Replace the current columns.

alter table orders
 alter column ts drop expression;
alter table orders
 drop column dt;
alter table orders
 drop column tm;

Fiddle

Answer 2

Its trivial

UPDATE orders SET full_date = order_date+ order_time

Answer 3

I managed to solve this issue by using Update Join:

Just in case anyone in the future would be interested, this did the trick:

BEGIN;
ALTER TABLE orders ADD COLUMN full_date TIMESTAMP;
UPDATE orders o1
SET full_date = o2.order_date + o1.order_time
FROM orders o2
WHERE o1.order_id = o2.order_id;
COMMIT;

• user234725: Why is a join necessary?
• Lennart - Slava Ukraini: You dont need the join. Also, concider adding not null after update