On Jan 19, 2008 5:04 PM, Jeff Willden <
jeff@pavanell.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