Re: Using indexes and keys

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.