Lists: | pgsql-general |
---|
From: | Alexandr Popov <alexandr(dot)popov(at)nexui(dot)md> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problems with pg_dump ? |
Date: | 2008-06-06 13:25:44 |
Message-ID: | 200806061625.44990.alexandr.popov@nexui.md |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello,
I found small problem and doesn't know where to dig.
Ok let's start.
postgresql 8.2.7
OS - linux
Create table and view.
CREATE TABLE t1 (
a integer NOT NULL,
b integer NOT NULL
);
CREATE OR REPLACE VIEW v1 AS SELECT DISTINCT a,b, -1 AS d, -1 AS e FROM t1;
After this i dump database with pg_dump and have following output.
--- pg_dump output start ---
--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE t1 (
a integer NOT NULL, b integer NOT NULL
);
ALTER TABLE public.t1 OWNER TO postgres;
--
-- Name: v1; Type: VIEW; Schema: public; Owner: postgres
--
CREATE VIEW v1 AS
SELECT DISTINCT t1.a, t1.b, -1 AS d, -1 AS e FROM t1 ORDER BY t1.a, t1.b, -1::integer, -1::integer;
ALTER TABLE public.v1 OWNER TO postgres;
--- pg_dump output end ---
And when i try to restore this thing on new DB i get following error while creating view
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
Any help will be appreciated
--
Alexandr Popov
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alexandr Popov <alexandr(dot)popov(at)nexui(dot)md> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problems with pg_dump ? |
Date: | 2008-06-06 16:28:48 |
Message-ID: | 4453.1212769728@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Alexandr Popov <alexandr(dot)popov(at)nexui(dot)md> writes:
> CREATE OR REPLACE VIEW v1 AS SELECT DISTINCT a,b, -1 AS d, -1 AS e FROM t1;
> After this i dump database with pg_dump and have following output.
> CREATE VIEW v1 AS
> SELECT DISTINCT t1.a, t1.b, -1 AS d, -1 AS e FROM t1 ORDER BY t1.a, t1.b, -1::integer, -1::integer;
Sigh ... this is a bug in my patch of a few months ago:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00081.php
It needs to be parenthesizing those constants, because as-is the ::
binds more tightly than the -, and "-(1::integer)" is a run-time
negation, not a simple constant.
regards, tom lane