Skip site navigation (1) Skip section navigation (2)

Re: Varchar or integer primary key?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rafael Charnovscki <charnovs(at)unesc(dot)rct-sc(dot)br>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Varchar or integer primary key?
Date: 2004-07-26 20:27:48
Message-ID: 9317.1090873668@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Pradeepkumar, Pyatalo (IE10)Date: 2004-07-27 02:29:38
Subject: Re: help with conversion functions
Previous:From: Scott MarloweDate: 2004-07-26 20:23:09
Subject: Re: Varchar or integer primary key?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group