Problems with pg_dump ?

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
Thread:
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gary Fu 2008-06-06 13:35:16 Re: how to clean up temporary schemas (how to sync the system table with pg_dump)
Previous Message Merlin Moncure 2008-06-06 12:12:18 Re: Annoying messages when copy sql code to psql terminal