Search Postgresql Archives

UNION and pg_restore

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

================================================



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux