Re: cannot restore a view after a dump

I didn't put it in, pg_dump dit it for me... it seems that when a view has a
distinct, the dumped view has automatically the order by. that's what
triggered the whole problem.

But the query is supposed to be the same with the order by, because of the
distinct, so pg_dump is not wrong.

It looks like a parsing problem to me (as it sometimes work and sometimes not
with exactly the same query)


On Tuesday 08 April 2008 15:24:33 Sergio Gabriel Rodriguez wrote:
> May be your problem is ORDER BY in a view, try to delete ORDER BY clause
>
> Sergio.
>
> On Tue, Apr 8, 2008 at 4:11 AM, Marc Cousin <mcousin@sigma.fr> wrote:
> > Hi,
> >
> >  Sorry to post again, but I feel this issue is a bit strange and I'd like
> > to understand it. The problem is that I've got the same query that runs
> > sometimes, and fails with a syntax error at other times... It's the first
> > time I've seen it, and I've been using PostgreSQL for a while now ...
> >
> >  Thanks in advance.
> >
> >  On Friday 04 April 2008 15:21:52 Marc Cousin wrote:
> >  > I've forgotten to add this information :
> >  >
> >  > Version :
> >  > infocentre_dte=# SELECT * from version();
> >  >                                          version
> >  > ----------------------------------------------------------------------
> >  >----- --------------- PostgreSQL 8.3.1 on x86_64-pc-linux-gnu, compiled
> >  > by GCC cc (GCC) 4.2.3 (Debian 4.2.3-2)
> >  >
> >  > It's from a x86_64 debian sid...
> >  >
> >  >
> >  >
> >  > I've narrowed it down to a simple test case... it doesn't seem to be
> >  > linked with pg_dump but with the parsing of the query :
> >  >
> >  > SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[])
> >  > THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE
> >  > NULL::integer END AS "UC-SIMM" FROM (SELECT
> >  > winaudit_management_systeme_memoire.computer,
> >  > group_array((winaudit_management_systeme_memoire.devicenumber)::text)
> >  > AS devicenumber FROM winaudit.winaudit_management_systeme_memoire
> >  > GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER
> >  > BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN
> >  > (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
> >  > ERREUR:  pour SELECT DISTINCT, ORDER BY, les expressions doivent
> >  > apparaître dans la liste SELECT
> >  >
> >  > infocentre_dte=# SET lc_messages to 'C';
> >  > SET
> >  >
> >  > infocentre_dte=# SELECT DISTINCT CASE WHEN (memoire.devicenumber =
> >  > '{1,2}'::text[]) THEN 2 WHEN (memoire.devicenumber = '{1}'::text[])
> >  > THEN 1 ELSE NULL::integer END AS "UC-SIMM" FROM (SELECT
> >  > winaudit_management_systeme_memoire.computer,
> >  > group_array((winaudit_management_systeme_memoire.devicenumber)::text)
> >  > AS devicenumber FROM winaudit.winaudit_management_systeme_memoire
> >  > GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER
> >  > BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN
> >  > (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
> >  > UC-SIMM
> >  > ---------
> >  >        2
> >  >
> >  > (2 rows)
> >  >
> >  >
> >  > The SQL is exactly the same (it's the same query I've run twice with
> >  > the up arrow in psql ...)
> >  >
> >  >
> >  >
> >  > I've continued playing with it : adding the create view works then,
> >  > than after some time fails again :
> >  >
> >  > infocentre_dte=# CREATE VIEW v_test_marc AS
> >  >     SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[])
> >  > THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE
> >  > NULL::integer END AS "UC-SIMM" FROM (SELECT
> >  > winaudit_management_systeme_memoire.computer,
> >  > group_array((winaudit_management_systeme_memoire.devicenumber)::text)
> >  > AS devicenumber FROM winaudit.winaudit_management_systeme_memoire
> >  > GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER
> >  > BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN
> >  > (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
> >  > CREATE VIEW
> >  > infocentre_dte=# DROP VIEW v_test_marc ;
> >  > DROP VIEW
> >  > infocentre_dte=# CREATE VIEW v_test_marc AS
> >  >     SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[])
> >  > THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE
> >  > NULL::integer END AS "UC-SIMM" FROM (SELECT
> >  > winaudit_management_systeme_memoire.computer,
> >  > group_array((winaudit_management_systeme_memoire.devicenumber)::text)
> >  > AS devicenumber FROM winaudit.winaudit_management_systeme_memoire
> >  > GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER
> >  > BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN
> >  > (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
> >  > CREATE VIEW
> >  > infocentre_dte=# DROP VIEW v_test_marc ;
> >  > DROP VIEW
> >  > infocentre_dte=# CREATE VIEW v_test_marc AS
> >  >     SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[])
> >  > THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE
> >  > NULL::integer END AS "UC-SIMM" FROM (SELECT
> >  > winaudit_management_systeme_memoire.computer,
> >  > group_array((winaudit_management_systeme_memoire.devicenumber)::text)
> >  > AS devicenumber FROM winaudit.winaudit_management_systeme_memoire
> >  > GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER
> >  > BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN
> >  > (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
> >  > CREATE VIEW
> >  > infocentre_dte=# DROP VIEW v_test_marc ;
> >  > DROP VIEW
> >  > infocentre_dte=# DROP VIEW v_test_marc ;
> >  > ERROR:  view "v_test_marc" does not exist
> >  > infocentre_dte=# CREATE VIEW v_test_marc AS
> >  >     SELECT DISTINCT CASE WHEN (memoire.devicenumber = '{1,2}'::text[])
> >  > THEN 2 WHEN (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE
> >  > NULL::integer END AS "UC-SIMM" FROM (SELECT
> >  > winaudit_management_systeme_memoire.computer,
> >  > group_array((winaudit_management_systeme_memoire.devicenumber)::text)
> >  > AS devicenumber FROM winaudit.winaudit_management_systeme_memoire
> >  > GROUP BY winaudit_management_systeme_memoire.computer) memoire ORDER
> >  > BY CASE WHEN (memoire.devicenumber = '{1,2}'::text[]) THEN 2 WHEN
> >  > (memoire.devicenumber = '{1}'::text[]) THEN 1 ELSE NULL::integer END;
> >  > ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in
> >  > select list
> >
> >  --
> >  Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> >  To make changes to your subscription:
> >  http://www.postgresql.org/mailpref/pgsql-admin