Re: Varchar or integer primary key?

Rafael Charnovscki <charnovs(at)unesc(dot)rct-sc(dot)br> writes:
> I have a 6-digit integer number for a patient identifier which
> I intend to use as a primary key (PK) for a table. It also will
> be a foreign key in other tables. Do I have to use an integer data
> type as a domain for this PK or could I use a varchar(6)?

You don't *have to* use an integer column, but I would expect it to be
more compact and faster than the varchar alternative. How much faster
is hard to say without a lot more information about what you'll be doing
with it. You might try setting up a test database and experimenting.

The only really serious gotcha in this area is to make sure foreign key
columns are declared with exactly the same datatype as the referenced PK
column. Postgres will generally let you get away with using slightly
different types (for instance, int versus bigint or char versus varchar)
but you'll pay dearly in performance.

regards, tom lane