Thread: identity columns ?
Hello, Two questions - 1. M$ SQL Server has an identity column which generates a sequential number for you, we use this often for unique primary keys. Does postgres support anything like this ? 2. We're using pg-admin to work with postgres. It doesn't seem possible to modify tables in pg-admin once you've created them, you have to delete and add. Is there another pg client that supports modifying tables ? Tia, Chris
Donnerstag, 4. April 2002, 23:24 chris@netlabz.com wrote: > 1. M$ SQL Server has an identity column which generates a sequential number > for you, we use this often for unique primary keys. Does postgres support > anything like this ? select *,oid from your_table -Harald -- Harald Lux lux@lux.de Sandkaule 5-7 Tel.: +49 228 692325 D-53111 Bonn http://www.lux.de/
On Thu, 2002-04-04 at 22:24, Chris wrote: > Hello, > > Two questions - > > 1. M$ SQL Server has an identity column which generates a sequential number > for you, we use this often for unique primary keys. Does postgres support > anything like this ? We have the SERIAL datatype (an INTEGER field with an associated sequence). Don't use oids for referencing - they should be for database internal use only. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Thou will keep him in perfect peace, whose mind is stayed on thee, because he trusts in thee." Isaiah 26:3
Attachment
> 2. We're using pg-admin to work with postgres. It doesn't seem possible to
> modify tables in pg-admin once you've created them, you have to delete and
> add. Is there another pg client that supports modifying tables ?
Depends on what you want to change:
Drop a column -
Sorry - Drop table and recreate.
Add Column -
ALTER TABLE mytable ADD COLUMN mycolumn int4;
Change Column Name -
ALTER TABLE mytable RENAME COLUMN mycolumn TO newcolumn;
http://www.pgexplorer.com
GUI Postgres TOOL
Harald, Chris, > > 1. M$ SQL Server has an identity column which generates a > sequential number > > for you, we use this often for unique primary keys. Does postgres > support > > anything like this ? > > select *,oid from your_table NO!!!! This is wrong. DO NOT USE the oid for anything other than system purposes. It is NOT a user-friendly value for several reasons. Chris, the data type you want is SERIAL. Please read the section of the docs on sequences: http://www.postgresql.org/idocs/index.php?sql-createsequence.html ... as the SERIAL datatype simply automates the creation of a sequence. BTW, postgres sequences are both more powerful and more user-friendly than MS SQL Identity columns. I would also strongly suggest buying a beginner's postgresql book, such as "PostgreSQL: Introduction and Concepts" or Wrox Press' PostgreSQL book. -Josh Berkus
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 So this raises a question I was wondering, what use *is* the oid column in non-system tables? The only thing I could think of is for last-resort row uniqueness if the application somehow borked up. Are there any other reasons to even have this column in a user table? Joshua b. Jore http://www.greentechnologist.org On Fri, 5 Apr 2002, Josh Berkus wrote: > Harald, Chris, > > > > 1. M$ SQL Server has an identity column which generates a > > sequential number > > > for you, we use this often for unique primary keys. Does postgres > > support > > > anything like this ? > > > > select *,oid from your_table > > NO!!!! This is wrong. DO NOT USE the oid for anything other than > system purposes. It is NOT a user-friendly value for several reasons. > > Chris, the data type you want is SERIAL. Please read the section of > the docs on sequences: > http://www.postgresql.org/idocs/index.php?sql-createsequence.html > ... as the SERIAL datatype simply automates the creation of a sequence. > BTW, postgres sequences are both more powerful and more user-friendly > than MS SQL Identity columns. > > I would also strongly suggest buying a beginner's postgresql book, such > as "PostgreSQL: Introduction and Concepts" or Wrox Press' PostgreSQL > book. > > -Josh Berkus > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (OpenBSD) Comment: For info see http://www.gnupg.org iD8DBQE8rdkofexLsowstzcRAsDcAKDfShTlMHsD/atFKi0XcgQOODk+TQCg7Ka0 TO9O4+J007jd8v9W0oOlhYs= =8hjf -----END PGP SIGNATURE-----
One of the books you recommend, Introduction and concepts,
specifically notes using OID as a unique for primary and foreign keys.
Pg 80.
If there is a danger, Id like to know what it is, since I am using
the OID as a unique in a join to generate a list of records for
individual processing.
As long as you remember that you cannot set, or alter an OID
what's the diff?
L>
At 8:21 AM -0800 4/5/02, Josh Berkus wrote:
>Harald, Chris,
>
>> > 1. M$ SQL Server has an identity column which generates a
>> sequential number
>> > for you, we use this often for unique primary keys. Does postgres
>> support
>> > anything like this ?
>>
>> select *,oid from your_table
>
>NO!!!! This is wrong. DO NOT USE the oid for anything other than
> system purposes. It is NOT a user-friendly value for several reasons.
>
>Chris, the data type you want is SERIAL. Please read the section of
> the docs on sequences:
>http://www.postgresql.org/idocs/index.php?sql-createsequence.html
>... as the SERIAL datatype simply automates the creation of a sequence.
> BTW, postgres sequences are both more powerful and more user-friendly
> than MS SQL Identity columns.
>
>I would also strongly suggest buying a beginner's postgresql book, such
> as "PostgreSQL: Introduction and Concepts" or Wrox Press' PostgreSQL
> book.
>
>-Josh Berkus
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
--
searchmaster@apple.com
lev@apple.com
"Joshua b. Jore" <josh@greentechnologist.org> writes:
> So this raises a question I was wondering, what use *is* the oid column in
> non-system tables? The only thing I could think of is for last-resort row
> uniqueness if the application somehow borked up. Are there any other
> reasons to even have this column in a user table?
OID is not an unreasonable thing to use for relatively short-term row
identity. Some time ago I had a moderately complex C++-and-Tcl
application that had objects in memory representing rows fetched from
various tables; it used OIDs as a general-purpose way of remembering
which object was associated with which row. Without that I'd have had
to devise some way of dealing with table-specific primary keys.
What you don't really want to do is use OIDs for cross-references in the
database data itself, because that creates headaches when you want to
dump and reload the database.
There has been some talk of replacing the global OID counter with
per-table OID sequence generators --- if that happened, the problems
with dumping and reloading OIDs would largely go away, since it'd be
relatively painless for the system to automatically advance any table's
OID counter past all the OID values loaded into that table.
What you should definitely NOT do is imagine that OID is a globally
unique identifier. At best it is a unique identifier of a row within
its table, and even that is only reliable if you put a unique index on
OID onto that table. (The combination of table OID and row OID can
serve as a global identifier if you need one; but remember that you will
not be able to count on table OID remaining the same over dump/reload.)
regards, tom lane