CODEWITHSUNDEEP
postgresql
user1354934
user1354934

Reputation: 8841

How to index 32-character text id

I have 32 character long text IDs from another system that I want to store and be able to query fast. Here is an example: rl3j6cUkVf06W6fZCqmqn6o3RzeCh186

What type of index would be best suited? I am reading about full text indexing, but is that really what I need?

Upvotes: 0

Views: 713

Answers (1)

Gabriel Furstenheim
Gabriel Furstenheim

Reputation: 3452

You are not looking for a full text index, but for a normal index. That is, you are looking for the key.

You can either choose to have it as primary key:

create table a (
    id char(32) not null primary key
)

Or have a custom primary key and use this key in a unique index

create table a (
    id bigserial primary key,
    external_id char(32) not null unique
)

Both of them will optimize for queries like select * from a where id = $1.

In case these values are not unique, you need to explicitely create the index:

create table a (
    id bigserial primary key,
    external_id char(32) not null
);
create index on a (external_id);

Upvotes: 1

Related Questions