[bug?] target table field order changed, not like original table

[roytan883]

My source DB has table like this:

create table share_media_v2.base(
  id                        VARCHAR(64) PRIMARY KEY NOT NULL,
  category                  VARCHAR(64) NOT NULL,
  ct                        TIMESTAMP(3) WITHOUT TIME ZONE NOT NULL DEFAULT LOCALTIMESTAMP(3),
  ut                        TIMESTAMP(3) WITHOUT TIME ZONE NOT NULL DEFAULT LOCALTIMESTAMP(3)
);

after run pgquarrel, I got diff.sql for target DB, but the table fields order has changed to :

CREATE TABLE share_media_v2.base (
category character varying(64) NOT NULL,
ct timestamp(3) without time zone DEFAULT LOCALTIMESTAMP(3) NOT NULL,
id character varying(64) NOT NULL,
ut timestamp(3) without time zone DEFAULT LOCALTIMESTAMP(3) NOT NULL
);

this will lead some code has bug like:

INSERT INTO share_media_v2.base VALUES ('id-1', 'type1');

[eulerto]

@roytan883 this is not a bug. The DDL is correct. You can claim it is a new feature. pgquarrel orders table columns by name to compare columns (of course in the case of new tables it does not need that order). Also, your INSERT should specify the columns to not depend of column order. What if you need to remove a column and add it again (say to change a datatype)? PostgreSQL does not have a command to shift column order.

[roytan883]

@eulerto I think at least if the source table is totally new, should keep the original fields order.

remove a column and add it again

I think it is another case.

[roytan883]

The problem is that：pgquarrel works great, but it can't handle POLICY. So after use diff.sql, I must manually pg_dump source and target DB, and use other diff tools to find POLICY changes. In the case, the fields order is differ, so lots of unnecessary differ show.

[eulerto]

The problem is that：pgquarrel works great, but it can't handle POLICY. So after use diff.sql, I must manually pg_dump source and target DB, and use other diff tools to find POLICY changes. In the case, the fields order is differ, so lots of unnecessary differ show.

I added POLICY support a few days ago (commit ec06f49a94365464dd0f49c7c2ed5ad1c45cae23). This is a different issue.

[roytan883]

@eulerto So it is a bug？I git clone the latest code, and set policy = true, but still can not get POLICY although it is totally new POLICY in source DB

[eulerto]

@roytan883 open another issue. It is a totally different problem. Please provide a small test case.