I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as
char varying) and to keep that field as auto-increment as well.
Is there a way to achieve this?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
PostgreSQL does not have "auto-increment" fields in the sense of MySQL's
AUTO_INCREMENT, but I'm guessing you mean
If so, yes, what you describe is possible, but please, please don't do this.
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
1. The user never needs to know that you're just storing integers.
If you want other prefixes like
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
('PAT', 1) for queries, and reverse it for display to the user.
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
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.
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.