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.
CREATE TEMP TABLE tbl1 ( <columns from above, without id> );
COPY tbl1 FROM '/path/to/file1';
CREATE TEMP TABLE tbl2 ( <columns from above, without id> );
COPY tbl2 FROM '/path/to/file2';
Alternatively, to preserve the input tables across sessions, you could use unlogged tables.
For best performance create and fill the target with CREATE TABLE AS
and add the PK constraint in the same transaction:
BEGIN;
CREATE SEQUENCE tbl3_tbl3_id_seq;
CREATE TABLE tbl3 AS
SELECT nextval('tbl3_tbl3_id_seq'::regclass)::int AS tbl3_id, *
FROM (TABLE tbl1 UNION TABLE tbl2 ) sub;
ALTER TABLE tbl3
ADD CONSTRAINT tbl3_pkey PRIMARY KEY(tbl3_id)
, ALTER COLUMN tbl3_id SET DEFAULT nextval('tbl3_tbl3_id_seq'::regclass);
ALTER SEQUENCE tbl3_tbl3_id_seq OWNED BY tbl3.tbl3_id;
COMMIT;
Replace all occurrences of "tbl3" with our desired table name.
Detailed explanation in this related answer:
I added a serial
column (tbl3_id
) as surrogate PK to the target table. Adding the actual PK constraint at the end (of the same session) is the fastest way.
Related:
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.
After some clarification (in comments and chat), it seems that:
ChildA
and ChildB
are subtypes.
- a
VIPUser
has a many-to-many relationship with all Child
entities ("VIPUser "uses" Child").
- there is a many-to-many relationship between
VIPUser
and ChildType
(stored in Parent
), essentially what types of Child
a `VIPuser can use ("VIPUser "can use" a child of ChildType").
Then the relationships between entities can be shown in the diagram
(I renamed Parent
to VIPCanUse
):
ChildType
VIPUser / \
\ / \
\ / \
VIPCanUse \
\ Child
\ / |
\ / ------------
\ / | |
\ / ChildA ChildB
VipUses
Best Answer
In your query:
you are trying to mix data and meta-data. information_schema contains only meta-data so you won't find
:uniqueid
in there. You will have to extract meta-data first, and then iterate over that some how. Assuming all columns are named id (which is a bad choice, but nothing you can avoid if I get it right), something like the following pseudo-code:will print the first table where there is an id = :uniqueid
Another option is to generate a union all for all tables: