Cannot create primary key using already created index-CodePudding

I have a table ideas with columns idea_id, element_id and element_value.

Initially, I had created a composite primary key(ideas_pkey) using all three columns but I started facing size limit issues with the index associated with the primary key as the element_value column had a huge value.

Hence, I created another unique index hashing the column with possible large values

CREATE UNIQUE INDEX ideas_pindex ON public.ideas USING btree (idea_id, element_id, md5(element_value))

Now I deleted the initial primary key ideas_pkey and wanted to recreate it using this newly created index like so

alter table ideas add constraint ideas_pkey PRIMARY KEY ("idea_id", "element_id", "element_value") USING INDEX ideas_pindex;

But this fails with the following error

ERROR:  syntax error at or near "ideas_pindex"
LINE 2: ...a_id", "element_id", "element_value") USING INDEX ideas_...
                                                             ^
SQL state: 42601
Character: 209

What am I doing wrong?

CodePudding user response：

A primary key index can't be a functional index. You can instead just have a unique index on your table, or create another column storing the md5() of your larger column and use it in the PK.

That being said, there is also another error in your query: If you want to specify an index name, you can't specify the PK columns (they are derived from the underlying index). And if you want to specify the pk columns, you can't specify the index name/definition, as it will be automatically created. See the doc