UNION and pg_restore - Mailing list pgsql-general
| From | Bryan Lee Nuse |
|---|---|
| Subject | UNION and pg_restore |
| Date | |
| Msg-id | BA8E345938B48C42A2F124851A65C00B39E507DC@BL2PRD0210MB349.namprd02.prod.outlook.com Whole thread Raw |
| Responses |
Re: UNION and pg_restore
|
| List | pgsql-general |
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_userpg_restore: [archiver (db)] could not execute query: ERROR: column reference "?column?" is ambiguousLINE 2: ...2 FROM tab_1 UNION SELECT a.id_1, a.id_2, a.id_3, b."?column...^Command was: CREATE VIEW view_1 ASSELECT 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 | 56 | 7 | 8 | -999 | -9997 | 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_2FROM tab_1UNIONSELECT a.id_1, a.id_2, a.id_3, b."?column?" AS data_1, b."?column?" AS data_2FROM ( SELECT tab_2.id_1, tab_2.id_2, tab_2.id_3FROM tab_2) aCROSS 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)
================================================
pgsql-general by date: