Skip site navigation (1) Skip section navigation (2)

Bug #849: pg_restore bug on views with union, PostgreSQL 7.2.3

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #849: pg_restore bug on views with union, PostgreSQL 7.2.3
Date: 2002-12-17 13:05:08
Message-ID: 20021217130508.CCC68475AE4@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Dariusz Knociski (dknoto(at)next(dot)com(dot)pl) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
pg_restore bug on views with union, PostgreSQL 7.2.3

Long Description
I have problem with restoring views with union and without
attribute names in select, without union all works OK because
pg_dump for "sum(attr)" generate "sum(attr) as attr", with union generate "sum(attr) as sum".

Sample Code
I have created view with query in PostgreSQL 7.2.3 :

CREATE VIEW
	"smnad_200212"
    (
    	nadawca,
        licznik,
        kwota
    )
AS
(
    SELECT
    	o.nadawca,
        sum(o.count),
		(sum(o.kwota))::numeric(15,2)
	FROM
     	oplaty_bif_nad_yyyymmdd o
	WHERE
    	(text(o.data) ~~ '2002-12-%'::text)
    GROUP BY o.nadawca
)
UNION
(
	SELECT
    	'Razem:' AS nadawca,
    	sum(o.count),
		(sum(o.kwota))::numeric(15,2)
    FROM
    	oplaty_bif_nad_yyyymmdd o
	WHERE
    	(text(o.data) ~~ '2002-12-%'::text)
);

and then I have made backup with pg_dump.
pg_dump created query:

CREATE VIEW
	"smnad_200212"
as
(
	(
    	SELECT
        	o.nadawca,
            sum(o.count) AS sum,                   -- 
            (sum(o.kwota))::numeric(15,2) AS sum   -- Error
        FROM
        	oplaty_bif_nad_yyyymmdd o
        WHERE
        	(text(o.data) ~~ '2002-12-%'::text)
        GROUP BY o.nadawca
    )
	UNION
    (
    	SELECT
        	'Razem:' AS nadawca,
            sum(o.count) AS sum,                   -- 
            (sum(o.kwota))::numeric(15,2) AS sum   -- Error
        FROM
        	oplaty_bif_nad_yyyymmdd o
        WHERE
        	(text(o.data) ~~ '2002-12-%'::text)
    )
);

In these query have very important SQL bug, attributes "sum" duplicated.


No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-12-17 14:31:59
Subject: Re: Bug #849: pg_restore bug on views with union, PostgreSQL 7.2.3
Previous:From: pgsql-bugsDate: 2002-12-17 13:02:27
Subject: Bug #849: pg_restore bug on views with union, PostgreSQL 7.2.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group