ERROR: Unique index of distributed table must contain the hash distribution column. when do migration from postgresql to postgresql-xl

Question

I dump sql from postgresql by pg_dump and get below table define:

CREATE TABLE administrator (
    mtime bigint,
    email text,
    group_id bigint,
    user_account text NOT NULL
);
ALTER TABLE ONLY administrator ADD CONSTRAINT administrator_pkey PRIMARY KEY (user_account);

And I setup postgresql-xl then import the sql file it return:

ERROR: Unique index of distributed table must contain the hash distribution column.

The Postgres-XL manual says:

If DISTRIBUTE BY is not specified, columns with UNIQUE constraint will be chosen as the distribution key. If no such column is specified, distribution column is the first eligible column in the definition. If no such column is found, then the table will be distributed by ROUNDROBIN.

If this is ture why my sql file import still return Error?

I think when you created that table it automatically chosen mtime as the distribution column. Hence you can't add a rimary key. Try and make the primary key definition part of the create table. I think it will work that way. — Balazs Gunics, Nov 02 '19 at 10:42

score 0 · Answer 1 · answered Feb 23 '20 at 23:18

Drop Table administrator ;

CREATE TABLE administrator (
    mtime bigint,
    email text,
    group_id bigint,
    user_account text NOT NULL
) DISTRIBUTE BY HASH(user_account);

ALTER TABLE administrator ADD CONSTRAINT administrator_pkey PRIMARY KEY (user_account);

will fix the problem.

ERROR: Unique index of distributed table must contain the hash distribution column. when do migration from postgresql to postgresql-xl

1 Answers1