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):
pg_restore: [archiver (db)] Error from TOC entry 164; 1259 47618 VIEW view_1
test_user
pg_restore: [archiver (db)] could not execute query: ERROR: column reference
"?column?" is ambiguous
LINE 2: ...2 FROM tab_1 UNION SELECT a.id_1, a.id_2, a.id_3, b."?column...
^
Command was: CREATE VIEW view_1 AS
SELECT tab_1.id_1, tab_1.id_2, tab_1.id_3, tab_1.data_1, tab_1.data_2 FROM
tab_1 UNION SELECT a.id...
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;
id_1 | id_2 | id_3 | data_1 | data_2
------+------+------+--------+--------
1 | 2 | 3 | 4 | 5
6 | 7 | 8 | -999 | -999
7 | 8 | 9 | -999 | -999
(3 rows)
testing=> \d+ view_1
View "test.view_1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id_1 | integer | | plain |
id_2 | integer | | plain |
id_3 | integer | | plain |
data_1 | integer | | plain |
data_2 | integer | | plain |
View definition:
SELECT tab_1.id_1, tab_1.id_2, tab_1.id_3, tab_1.data_1, tab_1.data_2
FROM tab_1
UNION
SELECT a.id_1, a.id_2, a.id_3, b."?column?" AS data_1, b."?column?" AS
data_2
FROM ( SELECT tab_2.id_1, tab_2.id_2, tab_2.id_3
FROM tab_2) a
CROSS JOIN ( SELECT (-999), (-999)) b;
testing=> SELECT version();
version
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.6 on x86_64-apple-darwin11.4.0, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658)
(LLVM build 2336.11.00), 64-bit
(1 row)
================================================
