I have 2 large tables that I need to combine into a single table--for statistical analysis. The two tables have some variations in column named, otherwise I would just use an INSERT
statement. So I have been able to identify the common columns in both tables, but I cannot seem to write a query to extract those common columns from each table and then add them to a new table. Here is what I have so far.
I was able to get the common columns between tables using an INTERSECT
query.
(SELECT attname
FROM pg_attribute
WHERE attrelid = 'public.iabr42fl'::regclass
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum)
INTERSECT
(SELECT attname
FROM pg_attribute
WHERE attrelid = 'public.iabr23fl'::regclass
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum);
I incorporated this query into a postgresql function called z_common_columns
and then I tried to create a table from it. But whenever I do this, I hit a memory error WARNING: out of shared memory.
.
CREATE TABLE indiadb AS
SELECT z_common_columns() from iabr23fl;
Is there a good way to select out the set of common columns on both tables and then append them to a single table that is created from those common columns? Thanks.