If I create a table and assign the column type for what will be the primary
key the value of "Serial", do I need to explicitly create an index in
addition?

I was thinking that a Serial, would automatically be a Unique Index wouldn't
it? I also set that field to the primary key... does that ensure an index is
created?

Just a little confused...

Also, while I am on the topic, does it do any good to create indexes on a
timestamp field?

Thanks in advance,
Alan



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

Hi,

You can also use a SEQUENCE which is unique and will add a PK constraint to
your field.

Not sure about the date thing because with sequence you would already have a
PK for that row ? A bit confused too ...

e.g.:

CREATE SEQUENCE main_cats_main_cat_id_seq
START 1
INCREMENT 1
MAXVALUE 9223372036854775807
MINVALUE 1
CACHE 1;


CREATE TABLE main_cats (
main_cat_id integer DEFAULT nextval('"main_cats_main_cat_id_seq"'::text)
NOT NULL,
lang_id integer NOT NULL,
main_cat_name text NOT NULL,
mc_order_nro smallint,
main_cat_show boolean NOT NULL,
sub_cats boolean NOT NULL,
last_mod timestamp without time zone
);

BR,

Aarni


On Saturday 03 January 2004 10:27, you wrote:
--
Aarni Ruuhimäki, pj
Sukellusseura Vesikot r.y.

www.vesikot.fi
info (AT) vesikot (DOT) fi

-------------------------------------------------
Microsoft should change into vacuum cleaner
business where people actually want products
that suck.
-------------------------------------------------
This is a bugfree broadcast to you from a linux system.
-------------------------------------------------
Viestissä mahdollisesti esitetyt mielipiteet
saattavat olla kirjoittajan henkilökohtaisia
näkemyksiä, eivätkä välttämättä edusta
Sukellusseura Vesikot r.y.:n virallista kantaa.

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

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

Hi,

You can also use a SEQUENCE which is unique and will add a PK constraint to
your field.

Not sure about the date thing because with sequence you would already have a
PK for that row ? A bit confused too ...

e.g.:

CREATE SEQUENCE main_cats_main_cat_id_seq
START 1
INCREMENT 1
MAXVALUE 9223372036854775807
MINVALUE 1
CACHE 1;


CREATE TABLE main_cats (
main_cat_id integer DEFAULT nextval('"main_cats_main_cat_id_seq"'::text)
NOT NULL,
lang_id integer NOT NULL,
main_cat_name text NOT NULL,
mc_order_nro smallint,
main_cat_show boolean NOT NULL,
sub_cats boolean NOT NULL,
last_mod timestamp without time zone
);

BR,

Aarni


On Saturday 03 January 2004 10:27, you wrote:
--
-------------------------------------------------
Aarni Ruuhimäki | Megative Tmi | KYMI.com
-------------------------------------------------
This is a bugfree broadcast to you from a linux system.
-------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Aarni Ruuhimäki schrieb:

values of a field.
Those fields stay fully editable (not like e.g. in MS-Access Autovalues).
It doesn't imply an index or an unique constraint on that field hence no
PK either.


an collumn that has PK features in the sense of the text book but its
unprotected.
A collumn that is marked PRIMARY KEY would go further and let the
DB-System force that no command would violate the PK features.


Try running your code snippet. You get a sequence and a table.
Then run:

INSERT INTO main_cats VALUES ( 42, 1, 'Tom', 2, FALSE, FALSE, NOW());
INSERT INTO main_cats VALUES ( 42, 1, 'Jerry', 2, FALSE, FALSE, NOW());

INSERT INTO main_cats
(
lang_id, main_cat_name, mc_order_nro, main_cat_show, sub_cats, last_mod
)
VALUES ( 1, 'Donald', 2, FALSE, FALSE, NOW());

Only the last INSERT used the sequence at all.

Now lets see what is in the table:
db_test=# SELECT main_cat_id, main_cat_name, last_mod FROM main_cats;
main_cat_id | main_cat_name | last_mod
-------------+---------------+----------------------------
42 | Tom | 2004-01-03 15:48:14.417267
42 | Jerry | 2004-01-03 15:48:52.191047
1 | Donald | 2004-01-03 15:55:13.685495

main_cat_id has double entries 42 and therefore can't have PK or UNIQUE
constraints.
It's even worse. The sequence doesn't know that 42 already exists, so it
will later generate another one.


main_cat_id integer DEFAULT nextval('"main_cats_main_cat_id_seq"'::text)
or
main_cat_id serial

just tells PG to count the sequence one up and take this value as default if no value is explicitly given in an insert.

You have to set the constraints explicitely.

main_cat_id serial PRIMARY KEY

implies that main_cat_id is NOT NULL and UNIQUE.
Though it doesn't catch issues with manually provided or edited values.
You could still enter ids that arent reached by the sequence, yet and you can change ids that were generated by the sequence as long it doesn't violate NOT NULL and UNIQUE.


cheers
Andreas


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"Alan T. Miller" <amiller (AT) hollywood101 (DOT) com> writes:
Either PRIMARY KEY or UNIQUE implies an index (for any column datatype).
They even put out notices to tell you so.

Not in recent releases --- a SERIAL column is just an integer with a
special default value.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Aarni Ruuhimäki schrieb:

values of a field.
Those fields stay fully editable (not like e.g. in MS-Access Autovalues).
It doesn't imply an index or an unique constraint on that field hence no
PK either.


an collumn that has PK features in the sense of the text book but its
unprotected.
A collumn that is marked PRIMARY KEY would go further and let the
DB-System force that no command would violate the PK features.


Try running your code snippet. You get a sequence and a table.
Then run:

INSERT INTO main_cats VALUES ( 42, 1, 'Tom', 2, FALSE, FALSE, NOW());
INSERT INTO main_cats VALUES ( 42, 1, 'Jerry', 2, FALSE, FALSE, NOW());

INSERT INTO main_cats
(
lang_id, main_cat_name, mc_order_nro, main_cat_show, sub_cats, last_mod
)
VALUES ( 1, 'Donald', 2, FALSE, FALSE, NOW());

Only the last INSERT used the sequence at all.

Now lets see what is in the table:
db_test=# SELECT main_cat_id, main_cat_name, last_mod FROM main_cats;
main_cat_id | main_cat_name | last_mod
-------------+---------------+----------------------------
42 | Tom | 2004-01-03 15:48:14.417267
42 | Jerry | 2004-01-03 15:48:52.191047
1 | Donald | 2004-01-03 15:55:13.685495

main_cat_id has double entries 42 and therefore can't have PK or UNIQUE
constraints.
It's even worse. The sequence doesn't know that 42 already exists, so it
will later generate another one.


main_cat_id integer DEFAULT
nextval('"main_cats_main_cat_id_seq"'::text)
or
main_cat_id serial

just tells PG to count the sequence one up and take this value as
default if no value is explicitly given in an insert.

You have to set the constraints explicitely.

main_cat_id serial PRIMARY KEY

implies that main_cat_id is NOT NULL and UNIQUE.
Though it doesn't catch issues with manually provided or edited values.
You could still enter ids that arent reached by the sequence, yet and
you can change ids that were generated by the sequence as long it
doesn't violate NOT NULL and UNIQUE.


cheers
Andreas



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

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