How can I write columns from one table based on the order in another?-CodePudding

I have two tables that I want to combine that are from the same data source, but my understanding is that the column order matters when inserting for Redshift, and they're not ordered in the same way.

So I have tables X and Y, like this:

Table X:

column_a
column_b
column_c
column_d

Table Y:

column_c
column_a
column_b

I want to add the data from table X into table Y where their names/types match, and it doesn't matter that I'll lose column d. I've been trying to use pg_get_cols to use a list of the column names from table Y to do the select from table X, but this answer makes me think that won't be possible.

I have about 200-300 table pairs to combine like this, so I'm looking for a solution to avoid having to write out the columns manually that many times!

Thanks

CodePudding user response：

Most SQL languages support a "Union" query. A "Union" stacks data ontop of each other creating more rows of similar data. For Example:

SELECT
  column_a,
  column_b, 
  colulm_c 
FROM TABLE Y
UNION ALL
SELECT
  column_a,
  column_b, 
  colulm_c 
FROM TABLE X

The output from this query would be three columns of data from both tables. In essence the data gets combined by stacking the results of the first query (TABLE Y) on top of the results from the second query (TABLE X).

The important thing to remember is this: Each column of both queries must contain compatible data types.

CodePudding user response：

Postgres implements the information schema.

Try playing around with :-

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

You'll get table names, column names, data types, whether they're allowed to be NULL, length of character types, etc.