Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 INTERSECTquery.

    (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.

share|improve this question
    
Could you add an example of the inputs and expected outputs? Are the data types also the same? –  Kirk Roybal Jul 10 '14 at 19:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.