Re: Indexes and sequences

On Jan 19, 2008 5:04 PM, Jeff Willden <jeff(at)pavanell(dot)com> wrote:

> I'm cleaning up a database that someone else made and have a couple
> questions. When I look at a table in pgAdmin it shows the DDL below.
> Do I need an index on the groupid_seq sequence? Doesn't the sequence
> already include one? Also, even if I need it, it doesn't need to be a
> unique index because the sequence already ensures uniqueness, right?
> On top of that there's a primary key constraint that also ensures
> uniqueness. Isn't there a bunch of redundant stuff here?
>

A "sequence" does not include an index, no. A sequence is NOT a column, but
rather a separate data entity that increments. The value of a sequence can
be inserted into a column, but the column and the sequence are NOT the same
thing. Indexes are created on columns.

You do need to create an index, and it should be a unique index if you want
uniqueness. Since the DEFAULT value is taken from the sequence, the values
will be unique as long as you do not EVER specify the value of the column in
an insert or update. That is a dangerous assumption, so specify a unique
index.

The primary key will automatically create a unique index if one does not
exist.

There is a section in the manual about "serial" data types that will answer
many of these questions in more detail.

Hopefully that helps.

Sean

>
> CREATE TABLE buddygroup
> (
> groupid integer NOT NULL DEFAULT nextval('groupid_seq'::text),
> userid integer NOT NULL,
> title character varying(255) NOT NULL,
> CONSTRAINT buddygroup_pkey PRIMARY KEY (groupid)
> )
> WITH OIDS;
> ALTER TABLE buddygroup OWNER TO postgres;
>
> -- Index: bg_groupid_idx
>
> -- DROP INDEX bg_groupid_idx;
>
> CREATE UNIQUE INDEX bg_groupid_idx
> ON buddygroup
> USING btree
> (groupid);
>
> -- Index: bg_userid_idx
>
> -- DROP INDEX bg_userid_idx;
>
> CREATE INDEX bg_userid_idx
> ON buddygroup
> USING btree
> (userid);
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>