We read every piece of feedback, and take your input very seriously.
To see all available qualifiers, see our documentation.
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
We appear to output these in show create table statements in hash order or similar.
show create table
P0: output a consistent order across commits P1: output the same order that was entered at table creation time
The text was updated successfully, but these errors were encountered:
We previously were tracking the issue here: #2673
Sorry, something went wrong.
I don't believe this is an issue with dolt. The customer was introspecting their postgres schema using the following query:
select tc.table_name, conname, string_agg(col.column_name, ',') from ( select conname, contype, unnest(conkey) as cols from pg_catalog.pg_constraint ) constr join information_schema.table_constraints tc on constraint_name = conname join ( select table_name, table_schema, column_name, ordinal_position from information_schema.columns order by ordinal_position ) col on col.table_name = tc.table_name and col.table_schema = tc.table_schema and col.ordinal_position = cols where contype = 'u' group by tc.table_name, conname;
Testing this query out, I spun up a postgres 11 instance and created a table with a unique constraint:
> CREATE table t (pk int PRIMARY key, col1 int, col2 int, CONSTRAINT col1_col2_uq UNIQUE (col1, col2)); CREATE TABLE > \d t; Table "public.t" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- pk | integer | | not null | col1 | integer | | | col2 | integer | | | Indexes: "t_pkey" PRIMARY KEY, btree (pk) "col1_col2_uq" UNIQUE CONSTRAINT, btree (col1, col2)
Then I ran the above query:
> select tc.table_name, conname, string_agg(col.column_name, ',') from ( select conname, contype, unnest(conkey) as cols from pg_catalog.pg_constraint ) constr join information_schema.table_constraints tc on constraint_name = conname join ( select table_name, table_schema, column_name, ordinal_position from information_schema.columns order by ordinal_position ) col on col.table_name = tc.table_name and col.table_schema = tc.table_schema and col.ordinal_position = cols where contype = 'u' group by tc.table_name, conname; table_name | conname | string_agg ------------+--------------+------------ t | col1_col2_uq | col2,col1
Notice how the string_agg column lists the column names in the wrong order.
string_agg
Adding an order by clause in the string_agg seems to fix this problem:
> select tc.table_name, conname, string_agg(col.column_name, ',' order by col.ordinal_position) from ( select conname, contype, unnest(conkey) as cols from pg_catalog.pg_constraint ) constr join information_schema.table_constraints tc on constraint_name = conname join ( select table_name, table_schema, column_name, ordinal_position from information_schema.columns order by ordinal_position ) col on col.table_name = tc.table_name and col.table_schema = tc.table_schema and col.ordinal_position = cols where contype = 'u' group by tc.table_name, conname; table_name | conname | string_agg ------------+--------------+------------ t | col1_col2_uq | col1,col2
druvv
No branches or pull requests
We appear to output these in
show create table
statements in hash order or similar.P0: output a consistent order across commits
P1: output the same order that was entered at table creation time
The text was updated successfully, but these errors were encountered: