'Postgresql move data to new table and change column names
I am new to Postgresql or SQL in general. These databases are in DBeaver and all connections are set. We have a table in an old database that we want to better label the fields in the columns to make things easier for querying. To make an example, suppose this is the current format of the table. Which I will call Table1.
| field_id | level_id |
|---|---|
| A1 | 1 |
| A2 | 1 |
| A3 | 1 |
Now we created an excel table to have the new type of values that we want and they look like this. Which I will call Table2
| field_id | new_field_id | level_id | new_level_id |
|---|---|---|---|
| A1 | a1 | 1 | 1 |
| A2 | a2 | 1 | 2 |
| A3 | a3 | 1 | 3 |
This table (Table2) has been loaded to a database correctly. We want to migrate the data from Table2 to another table in another database, which looks like Table1. The combination of field_id and level_id creates a unique combination. I want to copy the data from the Table2 with the new fields to Table3 in another database using the update function. The column names Table3 should be field_id and level_id instead of new_field_id etc. So it must look like Table1 with the new updated rows. I also want to copy the data make sure it is matching that unique combination of field_id and level_id. I have already created an empty table with the column names. so far I have tried,
UPDATE Table3 tb3
SET tb3.field_id = tb2.new_field_id, tb3.level_id = tb2.new_level_id
FROM ( SELECT new_level_id, new_field_id FROM Table2) as tb2
INNER JOIN tb2 on tb3.field_id = tb2.field_id AND tb3.level_id = tb2.level_id
WHERE (tb3.field_id, tb3.level_id) = (tb3.new_field_id, tb2.new_level_id)
So far I am getting a "Syntax error near tb3". Am I completely missing the point.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
