Hi,

Is a long field ok for a primary key, or should i use
a unique integer?:

create table parts (
manufacturers_code char(40) primary key,
description char(40),
man_id int references manufacturers (man_id)
);


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Dear Russell Shaw ,

Hello

Unique Integer is the most preferable thing as this retrive your data
at faster.
All said , if your select queries would use this primary key for
identifying tuples then better use integer


--
Regards,
Vishal Kashyap

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
I Know you believe my words so logon to Jabber.org
and add vishalkashyap (AT) jabber (DOT) org to your roster.
~*~*~*~*~*~*~*~*
I am usually called as Vishal Kashyap
but my Girlfriend calls me as Vishal CASH UP.
This is because others identify me because of my generosity
but my Girlfriend identify me because of my CASH.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Russell Shaw <rjshaw (AT) iprimus (DOT) com.au> writes:
There's nothing wrong with using a character field as primary key,
but I'd advise you to think twice about defining it as char(40).
Almost certainly you want varchar(40), so as not to be wasting huge
amounts of space on padding blanks. For that matter, where did
the "40" come from in the first place? Is there a really good
application-driven reason to limit the codes or descriptions to 40
characters? If your answer reveals that the number was picked out
of the air, I'd suggest dropping the constraint entirely. Text or
unconstrained varchar is a better choice, though it's not completely
SQL-standard.

One thing you should think carefully about before using externally
supplied data as a primary key is "what happens if the manufacturer's
code changes"? You'll have to update not only this table, but all
references to it from other tables. It's usually considered good
practice to choose primary keys that will *never* change, and that
essentially means that they can't have any externally-imposed meaning.
The popularity of auto-generated serial numbers as primary keys comes
from this consideration.

regards, tom lane

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

Tom Lane wrote:
I'd assumed databases would save the number of blanks needed in any
position, and restore/add the blanks when the field was retrieved.

I assumed that for some reason it would be faster or more efficient
than something of unfixed length or else why does it exist? Maybe it
is only kept for compatability purposes?

That is something hard to decide on. If a part becomes obsolete, i
thought maybe i could fill in an "obsolete" field so that any report
that is generated using it will alert me to change to a new part in
anything that uses it.

I'm using postgresql from php script embedded in html (apache on debian, etc).


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Russell Shaw <rjshaw (AT) iprimus (DOT) com.au> writes:
In Postgres, if the field is really wide (like kilobytes) then
compression will kick in and pretty much eliminate runs of spaces,
or runs of anything else for that matter. But I doubt it would get
applied to 40-byte fields.

Didn't you notice the contradiction to your previous assumption? Either
the field is fixed-width or it's not, you don't get to have it both
ways. It is true that there are certain optimizations that can be
applied to fixed-width fields, but they are relatively minor in
Postgres. (In databases that do overwrite-in-place, it can be a
significant win to ensure that *all* the fields of a record are fixed
width and so the total record size is fixed, but Postgres doesn't do
that anyway.) In any case, CHAR(n) is never considered a fixed-width
type in Postgres, because N is measured in characters not bytes and so
the physical width is variable anyway, at least when using
variable-width character set encodings.

Bottom line is that there are no efficiency advantages to CHAR(n) in
Postgres, although there can be some in old-line databases. You should
only use it if your data actually has a semantic constraint to a fixed
width --- postal codes are a common example of something that really is
appropriate for CHAR(n).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html