On Tue, Jun 19, 2012 at 07:49:20PM +0000, j...@tanga.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6699
> Logged by: Joe Van Dyk
> Email address: j...@tanga.com
> PostgreSQL version: 9.1.4
> Operating system: OSX
> Description:
>
> $ pg_restore -O -j 4 ~/tanga.dump -d tanga_dev_full_backup
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 417; 1259 66296 VIEW
> site_channels monkey
> pg_restore: [archiver (db)] could not execute query: ERROR: column
> "channels.start_at" must appear in the GROUP BY clause or be used in an
> aggregate function
> LINE 2: SELECT channels.id, channels.start_at, channels.end_at, ...
> ^
> Command was: CREATE VIEW site_channels AS
> SELECT channels.id, channels.start_at, channels.end_at, channels.title,
> channels.descriptio...
>
>
>
> site_channels view definition:
>
> View definition:
> SELECT channels.id, channels.start_at, channels.end_at, channels.title
> FROM channels
> LEFT JOIN channels_products cp ON cp.channel_id = channels.id
> LEFT JOIN buyable_products bp ON bp.id = cp.product_id
> GROUP BY channels.id;
>
> channels.id is a primary key.
Attached is a test case to reproduce the problem, courtesy of the
original reporter.
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
-Ryan
$ cat t.sql
create table foo (id serial primary key, name text);
insert into foo (name) values ('joe');
create view v as (select id, name from foo group by id);
$ createdb test
$ psql -f t.sql test
psql:t.sql:1: NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq"
for serial column "foo.id"
psql:t.sql:1: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"foo_pkey" for table "foo"
CREATE TABLE
INSERT 0 1
CREATE VIEW
$ pg_dump -Fc test > t.dump
$ dropdb test
$ createdb test
$ pg_restore -j4 -d test t.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 163; 1259 3870794 VIEW v joe
pg_restore: [archiver (db)] could not execute query: ERROR: column "foo.name"
must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT foo.id, foo.name FROM foo GROUP BY foo.id;
^
Command was: CREATE VIEW v AS
SELECT foo.id, foo.name FROM foo GROUP BY foo.id;
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.v" does not exist
Command was: ALTER TABLE public.v OWNER TO joe;
WARNING: errors ignored on restore: 2
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
