CODEWITHSUNDEEP
postgresql
Tom Raganowicz
Tom Raganowicz

Reputation: 2470

PostgreSQL PRIMARY KEY limit index length - prefix on VARCHAR

I've got a table

CREATE TABLE IF NOT EXISTS metadata (
    user_id   int not null,
    entry    VARCHAR(4096) not null
);

I am trying to add a PRIMARY KEY, limiting its size on entry column to 16 bytes. I don't want to index full 2730 (B-TREE limit) characters on the entry field due to storage overhead.

ALTER TABLE metadata ADD PRIMARY KEY (user_id, (entry::varchar(16)));

unfortunately such syntax isn't allowed

SQL Error [42601]: ERROR: syntax error at or near "("

nor is the:

ALTER TABLE metadata ADD PRIMARY KEY (user_id, entry(16)) -- That would probably work on MySQL

It seems that I can create a normal index just fine

create index some_index on metadata(user_id, (entry::varchar(16)));

Is there any way to limit column size in a PRIMARY INDEX?

Upvotes: 0

Views: 52

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 127426

You're not allowed to use an expression in your primary key definition. That means you need an additional column for this piece of data and that can be done by using a generated column. This column can than be used in your primary key:

ALTER TABLE metadata ADD COLUMN pkey_entry CHAR(16) GENERATED ALWAYS AS ( SUBSTRING(entry, 16)) STORED;
ALTER TABLE metadata ADD CONSTRAINT pkey_metadata PRIMARY KEY (user_id, pkey_entry);

Upvotes: 2

Related Questions