Postgres insert data from one table to another where column names match

Question

Abstract

I have one main table with columns named A through Z. I have a dozen other tables that also have strictly letter-named columns (same data types too), just not all columns from A to Z. (e.g. one tables may contain columns A,C,H while another contains columns A,B,C,X,Y,Z etc)

Assuming main table has defaults for all columns A through Z, is there a way to select every other tables' records into it without specifying each column name?

I know it is best to be explicit, but in reality I'm dealing with hundreds of fields, not 26, so I'm hoping there's a better way that's eluded me.

Concrete

I am trying to store annual data in one table (has additional year column). Each year, all existing fields remain from the previous year, but new fields may be added. I have imported each year's data into its own table (25 of them) and would like to consolidate. Most recent year/main table contains hundreds of columns.

I found a similar question here Insert into table from another with different columns count (MySql) but answer lists all columns explicitly in insert.

You should be inserting a new row for each year rather than new columns. — Gordon Linoff
– Gordon Linoff, Commented Jan 10, 2017 at 0:52
@e4c5 not helpful as I don't have control over the data collection or structure. Before I normalize, I'd like to consolidate, and I'm hoping to keep the solution in postgres. — Marc DePoe
– Marc DePoe, Commented Jan 10, 2017 at 0:58
@gordon perhaps you misunderstand. For each year, there is a table filled with data for that year. I plan to have one table contain all data for all years, so naturally it needs a "year" column. — Marc DePoe
– Marc DePoe, Commented Jan 10, 2017 at 1:06
@GordonLinoff - meant appending rows (i.e., stacking annual datasets). To add columns is restructuring a table which is not advised if in production requiring offline time. You want to stack your data in long format not wide, so add rows. Keep only one year column. Queries are easier to write, tables easier to maintain, storage more efficient. — Parfait
– Parfait, Commented Jan 10, 2017 at 1:43

Chris Travers · Accepted Answer · 2017-01-10 08:26:11Z

Understanding that there could be various reasons to do this, I think you would better off be looking at something like table inheritance to provide a unified query interface for previous tables. There are of course difficulties here. But that is probably a better solution if you can. Note the adding new fields would be very different if you go with inheritance.

The next option you would have would be to create an SQL mapping for each table to the archive table. So if you have an additional year int field, you need to fill you could:

CREATE FUNCTION mytable_to_archive(mytable) returns archive language sql as
$$
select $1.*, null::int as year;
$$ IMMUTABLE;

Note for performance reasons, this just takes in a tuple, and returns a tuple. No db lookups. Then you could:

INSERT INTO archive
SELECT (mytable_to_archive(mytable)).* from mytable;

Collectives™ on Stack Overflow

Postgres insert data from one table to another where column names match

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related