SEO

#1 (**permalink**) 04-10-2008, 12:52 AM

Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
NULL to SERIAL or is this implicit and unnecessary?

ie.

CREATE TABLE new_table (
id SERIAL UNIQUE NOT NULL,
description TEXT NOT NULL
);

Or should I just use below because Serial type implies this.

CREATE TABLE new_table (
id SERIAL,
description TEXT NOT NULL
);

Regards,
David

---------------------------(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

#2 (**permalink**) 04-10-2008, 12:52 AM

On Sat, May 28, 2005 at 14:27:17 -0300,
David Pratt <fairwinds@eastlink.ca> wrote:
> Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
> NULL to SERIAL or is this implicit and unnecessary?

Serials no longer generate a uniqie index by default. So in practice
you will normally want to declare them as PRIMARY KEYs. However there
are cases where you don't need this and the index is extra overhead.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#3 (**permalink**) 04-10-2008, 12:52 AM

On Saturday, May 28, 2005, at 03:27 PM, Bruno Wolff III wrote:

> On Sat, May 28, 2005 at 14:27:17 -0300,
> David Pratt <fairwinds@eastlink.ca> wrote:
>> Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
>> NULL to SERIAL or is this implicit and unnecessary?
>
> Serials no longer generate a uniqie index by default. So in practice
> you will normally want to declare them as PRIMARY KEYs. However there
> are cases where you don't need this and the index is extra overhead.
>

Alright. so would it be better form for me to to this in a
create_tables.sql

CREATE TABLE new_table (
id SERIAL,
description TEXT NOT NULL
);

And then in a create_primary_keys.sql do this for the tables requiring
it.

ALTER TABLE new_table ADD CONSTRAINT new_table_pkey PRIMARY KEY (id);

Does the PRIMARY KEY declaration ensure that the id values are unique?
Serial should always give me an incremented value that's different so I
am assuming it is unnecessary to use UNIQUE. Am I correct?

Regards,
David

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

#4 (**permalink**) 04-10-2008, 12:52 AM

On Sat, May 28, 2005 at 16:18:30 -0300,
David Pratt <fairwinds@eastlink.ca> wrote:
>
> On Saturday, May 28, 2005, at 03:27 PM, Bruno Wolff III wrote:
>
> >On Sat, May 28, 2005 at 14:27:17 -0300,
> > David Pratt <fairwinds@eastlink.ca> wrote:
> >>Pretty basic question. Is it necessary to add NOT NULL or UNIQUE NOT
> >>NULL to SERIAL or is this implicit and unnecessary?
> >
> >Serials no longer generate a uniqie index by default. So in practice
> >you will normally want to declare them as PRIMARY KEYs. However there
> >are cases where you don't need this and the index is extra overhead.
> >
>
> Alright. so would it be better form for me to to this in a
> create_tables.sql
>
> CREATE TABLE new_table (
> id SERIAL,

Its simpler to use:
id SERIAL PRIMARY KEY,

> description TEXT NOT NULL
> );
>
> And then in a create_primary_keys.sql do this for the tables requiring
> it.
>
> ALTER TABLE new_table ADD CONSTRAINT new_table_pkey PRIMARY KEY (id);
>
> Does the PRIMARY KEY declaration ensure that the id values are unique?
> Serial should always give me an incremented value that's different so I
> am assuming it is unnecessary to use UNIQUE. Am I correct?

PRIMARY KEY implies UNIQUE and NOT NULL and in Postgres will result in a
unique index being created to enforce this. It will also make id the
default column in new_table for foreign key references to that table.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#5 (**permalink**) 04-10-2008, 12:52 AM

On May 28, 2005, at 1:27 PM, David Pratt wrote:

> CREATE TABLE new_table (
> id SERIAL UNIQUE NOT NULL,
> description TEXT NOT NULL
> );
>
> Or should I just use below because Serial type implies this.
>
> CREATE TABLE new_table (
> id SERIAL,
> description TEXT NOT NULL
> );

The first one because it is possible for the serial to wrap around or
some other bad thing could happen if the serial is accidently reset.

But if id is really the primary key, then I would use SERIAL PRIMARY
KEY which implies UNIQUE NOT NULL. From the CREATE TABLE documentation:

The primary key constraint specifies that a column or columns of a
table may contain only unique (non-duplicate), nonnull values.
Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT
NULL, but identifying a set of columns as primary key also provides
metadata about the design of the schema, as a primary key implies that
other tables may rely on this set of columns as a unique identifier
for rows.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#6 (**permalink**) 04-10-2008, 12:52 AM

On Sat, May 28, 2005 at 16:18:30 -0300,
David Pratt <fairwinds@eastlink.ca> wrote:
>
> Serial should always give me an incremented value that's different so I
> am assuming it is unnecessary to use UNIQUE. Am I correct?

Unless someone uses setval to lower the value or if you change the
configuration of the sequence to allow it to wrap around.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Adding Serial Type