Thread: Port Bug Report: Command "CREATE TABLE AS with SELECT ... UNION" don't work
Port Bug Report: Command "CREATE TABLE AS with SELECT ... UNION" don't work
From
Unprivileged user
Date:
============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================
Your name : Angelo CONFLITTI
Your email address : mvca@yahoo.com
Category : unknown
Severity : non-critical
Summary: Command "CREATE TABLE AS with SELECT ... UNION" don't work
System Configuration
--------------------
Operating System : Linux 2.2.5 (RedHat 6.0)
PostgreSQL version : 6.5
Compiler used : egcs-2.91.66
Hardware:
---------
i486 24MB RAM, on intranet ethernet, no X-window
Versions of other tools:
------------------------
gmake 3.77, flex 2.5.4
--------------------------------------------------------------------------
Problem Description:
--------------------
Excuse for my english.
Table description (name=PARTNERS):
id char(6),
cogn char(20),
ids1 char(6),
ids2 char(6),
importo int8
The query:
CREATE TABLE pippo AS
SELECT a.id,a.cogn,b.id AS id1,b.importo AS imp1,null AS id2,b.importo AS imp2
FROM partners a, parteners b
WHERE a.id=b.ids1
UNION
SELECT a.id,a.cogn,null AS id1,null AS imp1,b.id AS id2,importo AS imp2
FROM partners a, partners b
WHERE a.id=b.ids2
The error message of Postgre is "ERROR: parser: parse error at or near "union".
With the PostgreSQL 6.4.2 it's work.
In PostgreSQL 6.5, if I use the command SELECT ... INTO TABLE, it's work.
A little difference between Postgre6.5 and Postgre6.4.2: if I have, in a table, a unknown field type (this is generated
froma "SELECT ... null AS imp2 ... INTO TABLE ..." or "CREATE TABLE AS...")
and run a SELECT SUM(...) of the unknown field the PostgreSQL 6.5 answer: "ERROR: Aggregate type mismatch sum() works
onnumeric, not on unknown";
with PostgreSQL 6.4.2 the SUM() function of the unknown field work.
Thank you for your precious PostgreSQL!! :-))
It's a usefull program. I hope to repay your enterprise.
Enjoy.
--------------------------------------------------------------------------
Test Case:
----------
--------------------------------------------------------------------------
Solution:
---------
--------------------------------------------------------------------------
Re: [PORTS] Port Bug Report: Command "CREATE TABLE AS with SELECT ... UNION" don't work
From
Bruce Momjian
Date:
Added to TODO:
* CREATE TABLE x AS SELECT 1 UNION SELECT 2 fails
> Problem Description:
> --------------------
> Excuse for my english.
> Table description (name=PARTNERS):
> id char(6),
> cogn char(20),
> ids1 char(6),
> ids2 char(6),
> importo int8
>
> The query:
> CREATE TABLE pippo AS
> SELECT a.id,a.cogn,b.id AS id1,b.importo AS imp1,null AS id2,b.importo AS imp2
> FROM partners a, parteners b
> WHERE a.id=b.ids1
> UNION
> SELECT a.id,a.cogn,null AS id1,null AS imp1,b.id AS id2,importo AS imp2
> FROM partners a, partners b
> WHERE a.id=b.ids2
>
> The error message of Postgre is "ERROR: parser: parse error at or near "union".
> With the PostgreSQL 6.4.2 it's work.
> In PostgreSQL 6.5, if I use the command SELECT ... INTO TABLE, it's work.
>
> A little difference between Postgre6.5 and Postgre6.4.2: if I have, in a table, a unknown field type (this is
generatedfrom a "SELECT ... null AS imp2 ... INTO TABLE ..." or "CREATE TABLE AS...")
> and run a SELECT SUM(...) of the unknown field the PostgreSQL 6.5 answer: "ERROR: Aggregate type mismatch sum() works
onnumeric, not on unknown";
> with PostgreSQL 6.4.2 the SUM() function of the unknown field work.
>
> Thank you for your precious PostgreSQL!! :-))
> It's a usefull program. I hope to repay your enterprise.
>
> Enjoy.
>
>
> --------------------------------------------------------------------------
>
> Test Case:
> ----------
>
>
> --------------------------------------------------------------------------
>
> Solution:
> ---------
>
>
> --------------------------------------------------------------------------
>
>
>
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026