Discussion:
Feature request - Edit data and tables without a primary key
Jan-Peter Seifert
2012-07-12 11:20:15 UTC
Permalink
Hello,

I have a feature request for the 'Edit data' window that is available for each table in pgAdmin.

As the OID column is not guaranteed to contain unique OIDs it would be nice to have additional/other fallback options ( via check box in pgAdmin's general options? ) to identify rows for editing in tables without primary keys.
I'd suggest serials ( serial/serial4 and bigserial/serial8 ) or unique columns/constraints.
Another drawback of OID columns is that you can't add them afterwards.

Thank you very much,

Peter
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Thom Brown
2012-07-12 11:31:36 UTC
Permalink
Post by Jan-Peter Seifert
Hello,
I have a feature request for the 'Edit data' window that is available for each table in pgAdmin.
As the OID column is not guaranteed to contain unique OIDs it would be nice to have additional/other fallback options ( via check box in pgAdmin's general options? ) to identify rows for editing in tables without primary keys.
I'd suggest serials ( serial/serial4 and bigserial/serial8 ) or unique columns/constraints.
Another drawback of OID columns is that you can't add them afterwards.
Use of the CTID system column would work.
--
Thom
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Guillaume Lelarge
2012-07-14 12:57:08 UTC
Permalink
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
Hello,
I have a feature request for the 'Edit data' window that is available for each table in pgAdmin.
As the OID column is not guaranteed to contain unique OIDs
Oops, sorry? each OID is unique.
Post by Jan-Peter Seifert
it would be nice to have additional/other fallback options ( via check box in pgAdmin's general options? ) to identify rows for editing in tables without primary keys.
Post by Jan-Peter Seifert
I'd suggest serials ( serial/serial4 and bigserial/serial8 )
You can't be sure a serial (serial2, serial4, and serial8) column will
only contain unique values.
Post by Jan-Peter Seifert
or unique columns/constraints.
Yes, this would be interesting to add. I thought we already did it, but
I gues I was wrong. I'll add a ticket to work on this later.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
Another drawback of OID columns is that you can't add them afterwards.
You can add OID columns to an already existing table since 8.4. But it
doesn't mean you want to (and actually I don't want to add OID columns
to my tables).
Post by Jan-Peter Seifert
Use of the CTID system column would work.
I don't see how that could work. CTIDs obviously are unique, but anyone,
updating a line will get another CTID for this line. If you try to
update it afterwards, in the best case, it errors out because there is
no line at this CTID. In the worst case, you update a completely
different line.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Thom Brown
2012-07-14 13:01:45 UTC
Permalink
Post by Guillaume Lelarge
Post by Thom Brown
Use of the CTID system column would work.
I don't see how that could work. CTIDs obviously are unique, but anyone,
updating a line will get another CTID for this line. If you try to
update it afterwards, in the best case, it errors out because there is
no line at this CTID. In the worst case, you update a completely
different line.
Why can't you just use "RETURNING ctid" at the end of the statement to
get the new one and update the grid with that value?
--
Thom
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Guillaume Lelarge
2012-07-14 13:09:57 UTC
Permalink
Post by Thom Brown
Post by Guillaume Lelarge
Post by Thom Brown
Use of the CTID system column would work.
I don't see how that could work. CTIDs obviously are unique, but anyone,
updating a line will get another CTID for this line. If you try to
update it afterwards, in the best case, it errors out because there is
no line at this CTID. In the worst case, you update a completely
different line.
Why can't you just use "RETURNING ctid" at the end of the statement to
get the new one and update the grid with that value?
At the end of which statement? how do you want to make it work?
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Dave Page
2012-07-14 13:06:09 UTC
Permalink
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
Hello,
I have a feature request for the 'Edit data' window that is available
for each table in pgAdmin.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
As the OID column is not guaranteed to contain unique OIDs
Oops, sorry? each OID is unique.
No, the counter can wrap.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
it would be nice to have additional/other fallback options ( via check
box in pgAdmin's general options? ) to identify rows for editing in tables
without primary keys.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
I'd suggest serials ( serial/serial4 and bigserial/serial8 )
You can't be sure a serial (serial2, serial4, and serial8) column will
only contain unique values.
Post by Jan-Peter Seifert
or unique columns/constraints.
Yes, this would be interesting to add. I thought we already did it, but
I gues I was wrong. I'll add a ticket to work on this later.
That method will work only if the columns involved are all not null
constrained.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
Another drawback of OID columns is that you can't add them afterwards.
You can add OID columns to an already existing table since 8.4. But it
doesn't mean you want to (and actually I don't want to add OID columns
to my tables).
Post by Jan-Peter Seifert
Use of the CTID system column would work.
I don't see how that could work. CTIDs obviously are unique, but anyone,
updating a line will get another CTID for this line. If you try to
update it afterwards, in the best case, it errors out because there is
no line at this CTID. In the worst case, you update a completely
different line.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
)
http://www.postgresql.org/mailpref/pgadmin-support
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Guillaume Lelarge
2012-07-14 13:09:07 UTC
Permalink
Post by Jan-Peter Seifert
On 12 July 2012 12:20, Jan-Peter Seifert
Post by Jan-Peter Seifert
Hello,
I have a feature request for the 'Edit data' window that
is available for each table in pgAdmin.
Post by Jan-Peter Seifert
As the OID column is not guaranteed to contain unique OIDs
Oops, sorry? each OID is unique.
No, the counter can wrap.
Oh yeah, you're right. So we shouldn't allow the use of OID in the edit
data window.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Jan-Peter Seifert
2012-07-14 13:43:03 UTC
Permalink
Hello,

thank you very much for your feedback everyone!
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
it would be nice to have additional/other fallback options ( via
check box in pgAdmin's general options? ) to identify rows for
editing in tables without primary keys.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
I'd suggest serials ( serial/serial4 and bigserial/serial8 )
You can't be sure a serial (serial2, serial4, and serial8) column will
only contain unique values.
Post by Jan-Peter Seifert
or unique columns/constraints.
Yes, this would be interesting to add. I thought we already did it, but
I gues I was wrong. I'll add a ticket to work on this later.
That method will work only if the columns involved are all not null
constrained.
Sorry - must have been in a hurry ...

Then serials with a unique constraint or unique constraints with 'all
necessary' 'NOT NULL'-constraints maybe?
Post by Jan-Peter Seifert
You can add OID columns to an already existing table since 8.4. But it
doesn't mean you want to (and actually I don't want to add OID columns
to my tables).
That's good - then at least you don't have to recreate the table in
order to edit it with pgAdmin ...
Otherwise we have no need for OID columns ...

Best regards,

Peter
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Guillaume Lelarge
2012-07-14 14:01:14 UTC
Permalink
Post by Jan-Peter Seifert
Hello,
thank you very much for your feedback everyone!
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
it would be nice to have additional/other fallback options ( via
check box in pgAdmin's general options? ) to identify rows for
editing in tables without primary keys.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
I'd suggest serials ( serial/serial4 and bigserial/serial8 )
You can't be sure a serial (serial2, serial4, and serial8) column will
only contain unique values.
Post by Jan-Peter Seifert
or unique columns/constraints.
Yes, this would be interesting to add. I thought we already did it, but
I gues I was wrong. I'll add a ticket to work on this later.
That method will work only if the columns involved are all not null
constrained.
Sorry - must have been in a hurry ...
Then serials with a unique constraint or unique constraints with 'all
necessary' 'NOT NULL'-constraints maybe?
Well, if you only want to have unique columns with NOT NULL constraint,
you pretty much have a primary key constraint.

We could allow updating/removing lines if they do have a . Or we can
BEGIN; UPDATE/DELETE; then check how many lines are updated/deleted, and
COMMIT if there is only one, and ROLLBACK if there are more. I just
don't see how to do that with a nice UI.

We can also only allow the update/remove of lines who have unique
columns not NULL.
Post by Jan-Peter Seifert
Post by Jan-Peter Seifert
You can add OID columns to an already existing table since 8.4. But it
doesn't mean you want to (and actually I don't want to add OID columns
to my tables).
That's good - then at least you don't have to recreate the table in
order to edit it with pgAdmin ...
Otherwise we have no need for OID columns ...
Adding OID field will recreate the table. This isn't something you'll do
on huge tables.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Jan-Peter Seifert
2012-07-14 13:54:17 UTC
Permalink
Hello,

another thing came to my mind.

pgAdmin III 1.14 does seem to commit the changes as soon as the new
value has been entered.
Wouldn't it be nice to display a warning or do a rollback if more than
one row had been updated instead of one or none then?

Thank you very much,

Peter
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Guillaume Lelarge
2012-07-14 14:01:51 UTC
Permalink
Post by Jan-Peter Seifert
Hello,
another thing came to my mind.
pgAdmin III 1.14 does seem to commit the changes as soon as the new
value has been entered.
Wouldn't it be nice to display a warning or do a rollback if more than
one row had been updated instead of one or none then?
That could be a good way to do it.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Continue reading on narkive:
Loading...