John,
> If I need to lookup on a combined key, how do I do that ?
To SELECT or JOIN based on a multi-column key, you need to use WHERE
conditions for each column:
...FROM tablea JOIN tableb ON (tablea.c1 = tableb.c1 AND tablea.c2 =
tableb.c2)
If you want to make sure and use the multi-column index on that key,
make sure to keep the columns in the same order as they were in the
key declaration.
> The CREATE TABLE syntax doesn't seem to allow me to combine the
> columns into a single key name.
Why not?
CREATE TABLE tablea (
col1 INT4 NOT NULL,
col2 INT4 NOT NULL,
CONSTRAINT tablea_pk PRIMARY KEY (col1, col2)
);
> Additionally, if I combine two VARCHAR columns are the following keys
> unique ?
>
> ...
> a VARCHAR(10),
> b VARCHAR(10),
> PRIMARY KEY (a,b),
> ...
>
> a="abc", b="def"
>
> a="ab", b="cdef"
Yes, they are unique. Postgres does not concatinate fields to make a
multi-column key.
-Josh
P.S. For anyone just tuning into the thread, keep in mind that
multi-column keys are considerably more trouble than they're worth 75%
of the time.
