PostgreSQL Discussion - Serial / auto increment data type

Author

Serial / auto increment data type

Typing80wpm@aol.com

2005-04-27, 8:24 pm

I am just beginning to teach myself Postgresql and Access. I notice that PG
has the "serial" type of field which increments automatically. I notice
that when I do external links in Access through ODBC, that Access wants a field
or fields which will be unique.

Here is my question. Would I be wise to define each and ever table with a
serial id, so that I may always be guaranteed something uniqe, to satisfy
Access. It seems to me like no harm can be done, since if you dont need the
serial id, you dont need it, but it is always there if you do need it, and it is
harder to add such a field down the line, after the fact.

Thanks!

Scott Marlowe

2005-04-27, 8:24 pm

On Wed, 2005-04-27 at 15:43, Typing80wpm@aol.com wrote:
> I am just beginning to teach myself Postgresql and Access. I notice
> that PG has the "serial" type of field which increments
> automatically. I notice that when I do external links in Access
> through ODBC, that Access wants a field or fields which will be
> unique.
>
> Here is my question. Would I be wise to define each and ever table
> with a serial id, so that I may always be guaranteed something uniqe,
> to satisfy Access. It seems to me like no harm can be done, since if
> you dont need the serial id, you dont need it, but it is always there
> if you do need it, and it is harder to add such a field down the line,
> after the fact.

Since 7.4 or 7.3 (I forget which) serial has NOT guaranteed uniqueness.
you have to add unique or primary key to the declaration like so:

create table foo (bar serial unique, otherfield int);

Or replace "unique" with "primary key"...

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

Tony Caduto

2005-04-27, 8:24 pm

Yes, that is the way to do it.

I migrated several large Access DB to postgres and most of the tables that where done by the
non developers didn't have a primary key defined, so the easiest way was just to add a new field to every table and make it a serial.

I believe you would have to do this even if you were using MS SQL server as the backend.

> Here is my question. Would I be wise to define each and ever table with
> a serial id, so that I may always be guaranteed something uniqe, to
> satisfy Access. It seems to me like no harm can be done, since if you
> dont need the serial id, you dont need it, but it is always there if you
> do need it, and it is harder to add such a field down the line, after
> the fact.
>
> Thanks!

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com

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

Sponsored Links