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 only takes a minute to sign up.

The id column is filled with a row number, so it could be used to perform batch operations if the full operation becomes too slow.All columns except the id column should be considered for the unique check.

The basic problem is simple, but the best solution depends on details you did not provide. Size of the tables / available disk space? Available RAM? Complete table definition showing data types, constraints and indexes? Is there concurrent access, and if yes, what's the situation exactly? Can you afford to create a new table or does one of the two need to stay in place? (Due to concurrent access, dependencies, etc.) Is performance important?
– Erwin BrandstetterNov 4 '15 at 13:10

How should those columns be treated that you have hidden from use (the ones indicated by ...)? What do you want to do if there are rows with the same combination of name and code in both tables. Which values for the other columns should be used?
– a_horse_with_no_nameNov 4 '15 at 13:18

@ErwinBrandstetter and i edited to provide more details
– EwertonNov 4 '15 at 13:33

Do you need to preserve the id column at all? Or can we just add a new serial number?
– Erwin BrandstetterNov 4 '15 at 13:36

I dont need to preserve the ID column, other columns could be created if needed
– EwertonNov 4 '15 at 13:38

2 Answers
2

The basic problem can be solved with various simple queries. Considering all columns:

CREATE TABLE tbl3 AS
TABLE tbl1
UNION TABLE tbl2;

Given this additional information:

All columns except the id column should be considered for the unique check.

And:

I don't need to preserve the ID column.

Just drop the id column, then you can proceed with the simple query above.

I would import to temporary tables (much faster, less overhead) and only write the final result (tbl3) to a regular table - in one session because temporary tables are dropped automatically at the end of the session.

Before you do it, test whether double precision is the best data type for all those columns. Chances are, some of them could be integer (cheaper for whole numbers) or must really be numeric (loss-less). If so, adapt your temp tables to begin with.

I think it's not as easy as that. Unfortunately Ewerton didn't tell us how exactly those columns indicated by .... should be treated. I suspect those should not be considered for the unique check.
– a_horse_with_no_nameNov 4 '15 at 13:19

All columns, except the ID, should be considered for unique check, i edited the question
– EwertonNov 4 '15 at 13:34

Sorry @ErwinBrandstetter but i dont understand yet, the operation consists in two steps? First create the temp tables (tbl1, and tbl2) then execute the union script? if so, what i need to replace in te '/path/to/file1' ?
– EwertonNov 4 '15 at 15:30

1

I think i understand now. I Already have the two tables imported, so just the union script is enough for my case. Thanks, it was a master answer.
– EwertonNov 4 '15 at 17:34