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)? Is there
any difference between an index on an integer column and one on a
varchar column?

I've been searching the internet and books for something to help on
this question but I haven't found. There are lot of good discussions on
choosing primary keys but nothing related to my issue.
Any suggestions or references may help.

Thanks in advance,
Rafael



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Mon, 2004-07-26 at 13:08, Rafael Charnovscki wrote:
Generally speaking, an int type will always win the performance race.
However, it may not provide the flexibility you need for certain type of
keys.

As long as the two types match, then the performance on joins should be
acceptable.

Index wise, the fields for a text type fields will almost always be
bigger than an int type would be, and will be faster. As long as the
pk/fk type match. Don't ref an int4 with an int8, etc...


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Rafael Charnovscki <charnovs (AT) unesc (DOT) rct-sc.br> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Thanks for the answers. They really helped me.
As I thougth, that is sort of issue related
to modeling, design, RDBMS and experience!

Best regards,
Rafael Charnovscki



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings