I have multiple tables that all have the same columns, but in different order. I want to merge them all together. I've created an empty table with the standard columns in the order I would like. I've tried inserting with
insert into master_table select * from table1;
but that doesn't work because of the differing column order - some of the values end up in the wrong columns. What is the best way to create one table out of them all in the order specified in my empty master table?
If you are dealing with many columns and many tables, you can use the information_schema to get the columns. You can loop through all the tables you want to insert from and run this in a plpgsql procedure, replacing table1 with a variable:
EXECUTE (
SELECT
'insert into master_table
(' || string_agg(quote_ident(column_name), ',') || ')
SELECT ' || string_agg('p.' || quote_ident(column_name), ',') || '
FROM table1 p '
FROM information_schema.columns raw
WHERE table_name = 'master_table');
) missing somewhere. [still counting ...]
|| worries by using the format() function.just indicate the proper order in the select
instead of
select *
if you want 3 field on second posiition.
select field1, field3, field2
or you can use the INSERT sintaxis
INSERT INTO master_table (field1, field3, field2)
SELECT *
