4

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?

  • 3
    Why would you want to do that? If every key value has the prefix PAT there is no sense in storing it. Create a serial column and a view that formats the number as desired. Additionally: the value of a primary key has no meaning whatsoever. You should not care about the values in that column, the only thing you need to care about is that they are unique. – a_horse_with_no_name Jul 26 '14 at 8:34
  • Thanx buddy, But can't we just make any varchar as auto-increament..? – itsME Jul 26 '14 at 9:38
  • 1
    "can't we just make any varchar as auto-increament" - that is not the correct question. What you are trying to achieve simply doesn't make sense. – a_horse_with_no_name Jul 26 '14 at 10:55
6

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.

|improve this answer|||||
  • Thanx for you generous response....! I really appreciate it...! _@ – itsME Jul 26 '14 at 11:30
3

You are mixing requirements for representation and storage. That's a common misconception for people coming from spreadsheet programs, where you typically do both at once.

Just store a serial column. The underlying integer column occupies 4 bytes and is very efficient for various purposes in the DB. In comparison, 'PAT0000001' as text or varchar occupies 11 bytes, is subject to collation and not quite as efficient overall.

You don't seem to need a multicolumn primary key. That would be needless complication. Go with a simple single-column surrogate primary key, even if you should have various "types" of patients.

CREATE TABLE patient (
   patient_id serial PRIMARY KEY
 , col1 text NOT NULL
 , col2 date NOT NULL
   ... -- more columns
);

For presentation needs, create a view:

CREATE VIEW patient_pretty AS
SELECT to_char(1, '"PAT"FM0000000') AS patient_code, col1, col2
FROM   patient;

I suggest to use a different column name consistently for the formatted ID to avoid confusion. patient_code in my example.

The to_char() expression is the only slightly tricky thing here. Note the FM modifier to avoid a leading blank (placeholder for a possible negative sign).

Now, when you want pretty display, use the view patient_pretty as dop-in replacement for table patient - or just the expression.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.