0

Context

I have a PostgreSQL database structure with a table1 defined with many project specific fields (which are empty now, but which will get written through an application):

table1

I have another table, let's say table3, already populated with (many) records of base data:

table3

I'd like to append, in a column-wise manner, all the columns from table3 into table1.
These columns do not exists yet in table1.

The desired results would by as follows:

table3 columns appended into table1

Question

How to achieve this in a nice way?

What I've tried so far

I have tried to follow this, but without success because the columns defined in table3 do not exist in table1:

UPDATE table1 a
SET (fid, attrib1, attrib2, attrib3, ...)
    =
    (b.fid, b.attrib1, b.attrib2, b.attrib3, ...)
FROM   table3 b
;

I also tried using an `INSERT` statement:
INSERT INTO table1
SELECT fid, attrib1, attrib2, attrib3, ...
FROM table3;

But here I face this (obvious error):

ERROR: INSERT has more expressions than target columns

The third way I was thinking about was to ALTER table1 and add all new columns, one by one, which would be tedious because of the datatype definition (which I don't want to care about because it's already defined in table3). There is probably a smarter way of doing that.

s.k
  • 362
  • 1
  • 5
  • 24
  • The desired results would by as follows: Do you want to obtain the rowset with shown structure and data, or you really want to alter the structure of Table1 and add more columns with the data into it? – Akina Apr 17 '20 at 11:41
  • Does table1.id and table3.fid are primary/unique? Does some value for these fields may exist in one of tables and not exist in another? if true - what result do you need for such value? – Akina Apr 17 '20 at 11:43
  • Yes, id and fid used to be serial (pkey) but table1 is empty now, so id would be generated automatically(?). And the new table1.fid column can simply be an integer for the moment. Yes, I'd like table1 to be updated with the column-wise structure from table3 and their respective data. All original table1 columns will be updated after the desired concatenate operation occurred, but I will probably have to refresh table1 when table3 gets updated in the future. I don't know yet. – s.k Apr 17 '20 at 11:48
  • 1
    What you want to do is contrary to data normalization. I don't see a suitable way to implement this - especially when you need to update the data from new version of table3 content... – Akina Apr 17 '20 at 11:57
  • Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. Or even better as a little fiddle –  Apr 17 '20 at 13:44

0 Answers0