0

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

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Mike Moss
  • 3
  • 3
  • So the issue is to get the common column names when both tables have lots of columns, but not in same order? I guess you could query system views like f.e. [SVV_COLUMNS](https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_COLUMNS.html) – LukStorms Jan 05 '22 at 11:05
  • @LukStorms Yeah that's it, and specifically to get a list that can be used to select the matching columns. – Mike Moss Jan 05 '22 at 11:26
  • Maybe something similar as [this](https://stackoverflow.com/a/42153621/4003419), but then by using system views available in redshift. – LukStorms Jan 05 '22 at 11:49

2 Answers2

0

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.

Read more here: https://www.w3schools.com/sql/sql_ref_union.asp

Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89
  • Thanks Michael, sorry I didn't mention in my initial post (I'll amend it now), I'm looking for a way to do this programmatically in the query as there are about 300 tables I have to do like this, so I'm trying to avoid writing out the column names each time. – Mike Moss Jan 05 '22 at 11:02
0

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.

Paul Alan Taylor
  • 10,474
  • 1
  • 26
  • 42
  • Thanks Paul, I've managed to pull a list of column names but I don't know how to select using a list, the question I linked to appears to be asking that, and the responder said this isn't possible, have you seen it done? – Mike Moss Jan 05 '22 at 11:18