|
Greetings, list,
I have a VIEW I use to combine both acquired and missing field observations. Because of they way these data will be used, missing values can't be NULL. So I make them some outrageous integer value, like -999. I put the full VIEW together using UNION.
As shown in the test case below, CREATE VIEW slightly changes the appearance of the query it stores as my VIEW definition. Now, "?column?" appears as a column reference in one of the SELECTs to be UNIONed. This did not present a problem until I tried
to restore the database from a pg_dump custom-format file. Then I got the following kind of error (this one comes from trying to restore the test data given below):
My question is, then, how is it that the query embodied in "view_1" below executes fine, but cannot seem to be restored? Is this telling me my query is dumb? If so, any advice on how to easily derive "view_1" from "tab_1" and "tab_2" below, without baffling
pg_restore, would be welcome.
Thanks,
Bryan
===================================
The following test case mirrors my own:
CREATE TABLE tab_1 (
-- Acquired observations.
id_1 int,
id_2 int,
id_3 int,
data_1 int,
data_2 int
);
INSERT INTO tab_1
VALUES (1,2,3,4,5);
CREATE TABLE tab_2 (
-- Missing observations.
id_1 int,
id_2 int,
id_3 int
);
INSERT INTO tab_2
VALUES (6,7,8), (7,8,9);
CREATE OR REPLACE VIEW view_1 AS (
SELECT * FROM tab_1
UNION
SELECT * FROM
(SELECT * FROM tab_2) a
CROSS JOIN
(SELECT -999,-999) b
);
================================================
testing=> SELECT * FROM view_1 ORDER BY id_1;
testing=> \d+ view_1
testing=> SELECT version();
version
================================================
|
