I just installed PostgreSQL 8.1 and PG Admin 1.6.1 .

These versions have a sequential column, that is not part of the table, identifying the rows.

Is there any method of accessing those numbers and identifying them with elements within the table??

Bob Pawley

Bob Pawley wrote:
> I just installed PostgreSQL 8.1 and PG Admin 1.6.1 .
>
> These versions have a sequential column, that is not part of the
> table, identifying the rows.
>
> Is there any method of accessing those numbers and identifying them
> with elements within the table??

Are you sure it's not just numbering the rows as it displays them?

--
Richard Huxton
Archonet Ltd

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

That's what they are doing.

That is also what I am looking for, if it is accessable. If so, I can use
that information to add a sequential numerical element to my information
that doesn't have the restrictions of a serial column.

Bob
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 9:39 AM
Subject: Re: [GENERAL] PG Admin


> Bob Pawley wrote:
>> I just installed PostgreSQL 8.1 and PG Admin 1.6.1 .
>>
>> These versions have a sequential column, that is not part of the
>> table, identifying the rows.
>>
>> Is there any method of accessing those numbers and identifying them
>> with elements within the table??
>
> Are you sure it's not just numbering the rows as it displays them?
>
> --
> Richard Huxton
> Archonet Ltd
>


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

Bob Pawley wrote:
> That's what they are doing.
>
> That is also what I am looking for, if it is accessable. If so, I can
> use that information to add a sequential numerical element to my
> information that doesn't have the restrictions of a serial column.
>
> Bob
>
Hi Bob,

Well, if you create your tables WITH OIDs then each row has a OID
associated with it.

What exactly are the restrictions of a serial column? It's just
standard integer value with a default value that calls the nextval function.

Later,

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration


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

When a row is deleted the serial number and oid are also deleted. The
sequence then has gaps which are inadmissible.

Bob
----- Original Message -----
From: "Tony Caduto" <tony_caduto@amsoftwaredesign.com>
To: "Bob Pawley" <rjpawley@shaw.ca>; <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 10:43 AM
Subject: Re: [GENERAL] PG Admin


> Bob Pawley wrote:
>> That's what they are doing.
>>
>> That is also what I am looking for, if it is accessable. If so, I can use
>> that information to add a sequential numerical element to my information
>> that doesn't have the restrictions of a serial column.
>>
>> Bob
>>
> Hi Bob,
>
> Well, if you create your tables WITH OIDs then each row has a OID
> associated with it.
>
> What exactly are the restrictions of a serial column? It's just standard
> integer value with a default value that calls the nextval function.
>
> Later,
>
> --
> Tony Caduto
> AM Software Design
> http://www.amsoftwaredesign.com
> Home of PG Lightning Admin for Postgresql
> Your best bet for Postgresql Administration
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>


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

On 4 Dec 2006 at 15:40, Bob Pawley wrote:

> When a row is deleted the serial number and oid are also deleted. The
> sequence then has gaps which are inadmissible.

This is an issue which has come up at various times on this list in
the past - it may be worth having a look through the archives. Here's
one recent thread:

http://archives.postgresql.org/pgsql...8/msg00535.php


--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------



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

On Mon, 2006-12-04 at 17:53, Raymond O'Donnell wrote:
> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>
> > When a row is deleted the serial number and oid are also deleted. The
> > sequence then has gaps which are inadmissible.
>
> This is an issue which has come up at various times on this list in
> the past - it may be worth having a look through the archives. Here's
> one recent thread:
>
> http://archives.postgresql.org/pgsql...8/msg00535.php

Yeah. The requirement for no gaps is a real no starter for any kind of
large data set.

Imagine modelling all the people in the US. 350 million entries. No
big deal. I can create that in minutes.

You each one give a number. Again, no big deal. A couple of minutes.

For everyone that dies, you remove the name. No big deal. a couple
seconds to run a delete.

For every one that is born, you add it to the list, giving it the next
number. again, no big deal. My workstation could probably handle the
load.

Now, turn that on its head. Every time you delete someone, you have to
renumber the data set, and for everyone added you have to make sure
there are no gaps.

Suddenly, you've got a problem that could bring even big iron to its
knees. All because some buearocrat (sp) hadn't the imagination to think
of non-sequential numbering systems.

While there are occasional systems where it is reasonable to actually
have no sequential gaps, most of the time the only justification is "I
don't like them." If that is the case, you should rethink your design.
If you're stuck with them because of some idiotic rule from on high,
then at least abstract the numbers to some degree to improve performance
and keep you from having to update about half of an entire table several
times a minute.

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

http://archives.postgresql.org/

I'm doing something similar - using a control table, dropping and creating
the serial column and updating in a manner that does the job.

It works - barely. I am seeking a more elegent and stable method. Having a
simple update recognizing the row numbers (in version 1.6.1) would be
better - perhaps.

Bob


----- Original Message -----
From: "Raymond O'Donnell" <rod@iol.ie>
To: <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 3:53 PM
Subject: Re: [GENERAL] PG Admin


> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>
>> When a row is deleted the serial number and oid are also deleted. The
>> sequence then has gaps which are inadmissible.
>
> This is an issue which has come up at various times on this list in
> the past - it may be worth having a look through the archives. Here's
> one recent thread:
>
> http://archives.postgresql.org/pgsql...8/msg00535.php
>
>
> --Ray.
>
>
> ----------------------------------------------------------------------
>
> Raymond O'Donnell
> Director of Music, Galway Cathedral, Galway, Ireland
> rod@iol.ie
> ----------------------------------------------------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


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

Your missing the point.

I am creating a design system for industrial control.

The control devices need to be numbered. The numbers need to be sequential.
If the user deletes a device the numbers need to regenerate to again become
sequential and gapless.

Bob
----- Original Message -----
From: "Scott Marlowe" <smarlowe@g2switchworks.com>
To: "Raymond O'Donnell" <rod@iol.ie>
Cc: "pgsql general" <pgsql-general@postgresql.org>
Sent: Monday, December 04, 2006 4:09 PM
Subject: Re: [GENERAL] PG Admin


> On Mon, 2006-12-04 at 17:53, Raymond O'Donnell wrote:
>> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>>
>> > When a row is deleted the serial number and oid are also deleted. The
>> > sequence then has gaps which are inadmissible.
>>
>> This is an issue which has come up at various times on this list in
>> the past - it may be worth having a look through the archives. Here's
>> one recent thread:
>>
>> http://archives.postgresql.org/pgsql...8/msg00535.php
>
> Yeah. The requirement for no gaps is a real no starter for any kind of
> large data set.
>
> Imagine modelling all the people in the US. 350 million entries. No
> big deal. I can create that in minutes.
>
> You each one give a number. Again, no big deal. A couple of minutes.
>
> For everyone that dies, you remove the name. No big deal. a couple
> seconds to run a delete.
>
> For every one that is born, you add it to the list, giving it the next
> number. again, no big deal. My workstation could probably handle the
> load.
>
> Now, turn that on its head. Every time you delete someone, you have to
> renumber the data set, and for everyone added you have to make sure
> there are no gaps.
>
> Suddenly, you've got a problem that could bring even big iron to its
> knees. All because some buearocrat (sp) hadn't the imagination to think
> of non-sequential numbering systems.
>
> While there are occasional systems where it is reasonable to actually
> have no sequential gaps, most of the time the only justification is "I
> don't like them." If that is the case, you should rethink your design.
> If you're stuck with them because of some idiotic rule from on high,
> then at least abstract the numbers to some degree to improve performance
> and keep you from having to update about half of an entire table several
> times a minute.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>


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

On Dec 4, 2006, at 3:53 PM, Raymond O'Donnell wrote:

> On 4 Dec 2006 at 15:40, Bob Pawley wrote:
>
>> When a row is deleted the serial number and oid are also deleted. The
>> sequence then has gaps which are inadmissible.
>
> This is an issue which has come up at various times on this list in
> the past - it may be worth having a look through the archives. Here's
> one recent thread:
>
> http://archives.postgresql.org/pgsql...8/msg00535.php

Or, better, http://archives.postgresql.org/pgsql-general/2006-09/
msg00948.php

I'm sure that PG Admin just generates the numbers in the GUI as it
displays them (as they're meaningless as persistent data).

Cheers,
Steve


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