UNIQUE index must contain all columns in the distribution key of relation "test_table"

[senique]

UNIQUE index must contain all columns in the distribution key of relation "test_table"

[kworr]

Yeah, I was suspecting Greenplum but wasn't sure about that.

First of all I find it totally wrong to move uniqueness from (code) to (id, code, name) in the included example as it totally changes the purpose of the table. Before the change you can have only one unique code for the table, and after the change number of unique codes is limited by a number of permutations between id and name. code is not unique anymore.

I'm not quite sure how Greenplum works, but fellows at stackoverflow got into the same situation: https://stackoverflow.com/questions/40987460/how-should-i-deal-with-my-unique-constraints-during-my-data-migration-from-postg It looks like database doesn't claims that the schema is not correct, it rather brings your attention to the fact that you are not using Greenplum effectively. Keys with extra constraints SHOULD be copied to each node in the cluster therefore nullifying any benefits of using Greenplum.

From my point of view this table can be made Greenplum-friendly by removing id, leaving code the primary key and limiting code to text or smaller varchar value to optimize index size. Greenplum will use field for distributing data across the nodes.

PS: "I was suspecting Greenplum" is based on the Google search for the error message. It doesn't happen for Postgres, only for Greenplum.

[kworr]

Probably I'm still missing something. This example gives me no errors for Percona 5.7.16 / Postrgesql 10.2. What's your database versions? Python version?

[senique]

Error case: MySQL 5.7.20-log /PostgreSQL 8.2.15 (Greenplum Database 4.3.7.1 build 1)

Awesome, due to your reply, I test this for MySQL 5.7.20-log /PostgreSQL 8.2, it works.
Your suspect is exactly right.

[senique]

Thank u for ur reply.