Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well.

Is there a way to achieve this?

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL.

If so, yes, what you describe is possible, but please, please don't do this.

A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g.

CREATE TABLE blah(
   id serial primary key
);

is actually shorthand for:

CREATE SEQUENCE blah_id_seq;

CREATE TABLE blah (
    id integer primary key default nextval('blah_id_seq'::regclass)
);

ALTER SEQUENCE blah_id_seq OWNED BY blah.id;

You can use this knowledge to cast the nextval return to a character type, e.g.

CREATE TABLE thisiswrong (
    id text primary key default CAST(nextval('thisiswrong_id_seq'::regclass) AS text)
);

but again, I beg of you, do not do this. It's horrible. It's wrong. You will regret it. Whatever you are trying to do, there is a better way to do it than this.

I have intentionally not shown how to generate a formatted field like PAT000001, but you can use any expression in a DEFAULT, not just a CAST. So look at the to_char function or the format function for how to do this if you insist.

The correct way to do what you want to do is not to do it. Get the application to display codes like PAT000001 when it sees a primary key value for patient_id like 1. The user never needs to know that you're just storing integers.

If you want other prefixes like PAT, DOC, etc, use a composite primary key, e.g.

CREATE TABLE saner_ish (
    categorycode varchar(3) CHECK (length(categorycode) = 3),
    patient_id integer default nextval('saner_ish_patient_id'::regclass),
    PRIMARY KEY (categorycode, patient_id)
);

(possibly with an enum type, an IN list, or whatever for validation).

Then in the application turn PAT00001 into ('PAT', 1) for queries, and reverse it for display to the user.

StackExchange Database Administrators Q#72452, answer score: 8