How to union tables and have a field with UNIQUE constraint? - Mailing list pgsql-sql

From Katona Gabor
Subject How to union tables and have a field with UNIQUE constraint?
Date
Msg-id Pine.GSO.4.10.10112281351460.8286-100000@dragon
Whole thread Raw
Responses Re: How to union tables and have a field with UNIQUE constraint?
List pgsql-sql
I have the followinig SQL code (of course the tables contain more useful
data). When I load it into PostgreSQL via \i foo.txt it produces the
folloving error massage:
UNIQUE constraint matching given keys for referenced table "t" not found The task I want to do with this code: Making a
tablet from t1 and t2 using
 
the id and the foo column. Table t shoud have one id and one foo column,
holding all the rows from t1 and t2, this is why I use UNION. As you can see,
the id column of t1 and t2 is PRIMARY KEY, therefore UNIQUE and the prepared
sequences provide that no rows can share the same id even after UNION.
 How can I correct the code to work? Is there any working way of doing such
things?

CREATE SEQUENCE t1_id_seq MINVALUE 1 MAXVALUE 49;
CREATE TABLE t1 (id INTEGER PRIMARY KEY DEFAULT nextval('t1_id_seq'),foo TEXT);
CREATE UNIQUE INDEX t1_id_key ON t1 (id);

CREATE SEQUENCE t2_id_seq MINVALUE 50;
CREATE TABLE t2 (       id INTEGER PRIMARY KEY DEFAULT nextval('t2_id_seq'),       foo TEXT,       bar TEXT);
CREATE UNIQUE INDEX t2_id_key ON t2 (id);

CREATE VIEW t AS SELECT id,foo FROM t1 UNION SELECT id,foo FROM t2;

CREATE TABLE uniont (       tid INTEGER REFERENCES t;       info TEXT);




pgsql-sql by date:

Previous
From: "Tom Kreiner"
Date:
Subject: Getting Hour From a Time in Different Time Zone
Next
From: rickd@open-works.com (Rick Dayao)
Date:
Subject: Conditional test