2

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!

Improve this question
8
  • Please provide the structure of "orders" table. Normally date + time works. Commented Sep 29, 2022 at 16:41
  • @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 Commented Sep 29, 2022 at 16:52
  • Please consider following these suggestions. Commented Sep 29, 2022 at 16:54
  • 1
    Have you concidered adding a GENERATED ALWAYS colum? Commented Sep 29, 2022 at 17:46
  • 1
    @Sagireichen: no, you can add a generated column later as well. And it would automatically be populated with the result of the expression. Commented Sep 29, 2022 at 21:09

3 Answers 3

Reset to default
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

Improve this answer
1

Its trivial

UPDATE orders SET full_date = order_date+ order_time
Improve this answer
0

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;
Improve this answer
2
  • 1
    Why is a join necessary? Commented Sep 30, 2022 at 4:12
  • 1
    You dont need the join. Also, concider adding not null after update Commented Sep 30, 2022 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.