On Wed, 24 Apr 2019 at 06:50, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Per my comment at https://postgr.es/m/20190422225129.GA6126@alvherre.pgsql > I think that pg_dump can possibly cause bogus partition definitions, > when the users explicitly decide to join tables as partitions that have > different column ordering than the parent table. Any COPY or INSERT > command without an explicit column list that tries to put tuples in the > table will fail after the restore.
Yeah, pg_dump itself is broken here, never mind dreaming up some other user command. We do use a column list when doing COPY, but with --inserts (not --column-inserts) we don't include a column list. All it takes is: postgres=# create table listp (a int, b text) partition by list(a); CREATE TABLE postgres=# create table listp1 (b text, a int); CREATE TABLE postgres=# alter table listp attach partition listp1 for values in(1); ALTER TABLE postgres=# insert into listp values(1,'One'); INSERT 0 1 postgres=# \q $ createdb test1 $ pg_dump --inserts postgres | psql test1 ... ERROR: invalid input syntax for type integer: "One" LINE 1: INSERT INTO public.listp1 VALUES ('One', 1); That settles the debate on the other thread... -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services