Search code examples
sqlpostgresqlsqldatatypes

Postgres data type for a fixed size string

What is the best Postgres datatype to use for a primary key that holds values of fixed size strings?

(for instance - values are exactly 6 chars of the alphabet [0-z,a-z,A-Z]).

Should I use char[6] (is it even appropriate to use as a primary key?) Should I use bigserial and do convertion from number to base62 in the application?

Solution

  • You would do this with something like this:

    create table t (
    tId char(6) primary key,
    . . .
    constraint chk_t_tId check (tId ~ '^[0-9a-zA-Z]{6}$')
);

    There is no problem having the id as a six character string.