Re: Creating a table

Basil,

I primary key and a serial / bigserial are two different things. Any
field or combination of fields may be a primary key.

Serial / bigserial behind the scenes are really integer and bigint with
a sequence. You can use integer / bigint and then create the sequence if
you want, it is just more steps. You are also correct you should mark
the field as "Not Null"

So to have a autonumber field that is also a primary key, you need three
things:
1) A integer / bigint field
2) A sequence on that field
3) A primary key set to that field

Items one and two can be combined into one step by using serial /
bigserial.

Once you create a serial / bigserial or any other field you can go to
the constraints tab and then select "Primary Key" and then click the
<Add> button. The program that I use creates the key name as
"table_name_field_name_seq", then go to the "Column" tab and add the
column(s) that you want to use as the primary key. With a serial /
bigserial you would normally just select one that one field. You would
select more than one field if you are creating a composite key.

So, with all that info, I just use the serial / bigserial because it is
faster to setup and I don't have to create the sequences manually. If
you ever want to port this to a different database, then you would just
run a search and replace on the dump file, which is just sql if you
choose the plain format which I suggest doing, to replace serial /
bigserial with whatever new type of field you want to change it to.

When you dump the database, it dump's the sequences also and restores
them when you load from the backup. One thing that you might find
usefull, if you are doing a bunch of testing, is to create a function
that you can call to reset your sequences. This is useful if you are
testing a lot and then deleting your data, because when you are ready to
go live and want to restart the sequence it is easy to do by calling
once function instead of manually editing all the sequences. I am
thinking of adding that code to the talk on writing functions.

I think I have covered everything that you asked, but if not, just send
me another email, I am happy to answer questions.

Lloyd

-----Original Message-----
From: seapug-owner(at)postgresql(dot)org [mailto:seapug-owner(at)postgresql(dot)org]
On Behalf Of Basil Bourque
Sent: Sunday, September 26, 2010 7:22 PM
To: seapug(at)postgresql(dot)org
Subject: Re: [seapug] Creating a table

On Sep 16, 2010, at 15:38, Albin, Lloyd P wrote:

> For learning you can create
> all your queries in public

Thank you for the clear answer.

I have another question: What is the best way in pgAdmin3 (Postgres 9)
to define a primary key column?

I learned how to add a constraint defining the primary key. But that
does not seem to cause a sequence number to be generated when I use
pgAdmin's Edit Data feature to create a row.

I read about the "serial" & "bigserial" pseudo-data type to define an
auto-incrementing integer.
http://www.postgresql.org/docs/9/interactive/datatype-numeric.html

That page suggests 2 steps:

(1) Define the datatype of the column to "serial" (or 'bigserial') A
"NOT NULL" constraint is automatically added.

(2) Add a constraint of PRIMARY KEY.

Is that the best practice? Normally I'd prefer writing more standard SQL
than "SERIAL", but that single word is so much shorter than the
alternative.

What happens when you use pgDump to re-create a database? Do I manually
reset the sequences?

I'd much rather use UUIDs as my primary key, but my app development
toolkit & database driver does not support UUIDs.

--Basil Bourque

--
Sent via seapug mailing list (seapug(at)postgresql(dot)org) To make changes to
your subscription:
http://www.postgresql.org/mailpref/seapug