Обсуждение: inserting new records without OIDs
Ooops. I sent this yesterday to the wrong maillist address. Sorry. I'll repost here. MD -----Original Message----- When I create a new table that does not have OIDs (the Has OIDs checkbox is off and I'm using a serial column type to id my records), then I CANNOT insert new data records using the Edit Data window. There is no blank row (with an asterisk *) in which to enter new data. In other words, I've discovered that you MUST use OIDs to be able to insert new records with the Edit window. Is this normal/expected behavior or a bug or a feature? Matt Matt Doggett Spatial Climate Analysis Service Oregon State University 316 Strand Ag Hall Corvallis, OR 97331 (541)737-9153 mdoggett@coas.oregonstate.edu
Matt Doggett wrote: >Ooops. I sent this yesterday to the wrong maillist address. Sorry. I'll >repost here. > >MD >-----Original Message----- >When I create a new table that does not have OIDs (the Has OIDs checkbox >is off and I'm using a serial column type to id my records), then I >CANNOT insert new data records using the Edit Data window. There is no >blank row (with an asterisk *) in which to enter new data. In other >words, I've discovered that you MUST use OIDs to be able to insert new >records with the Edit window. > > Not true. Currently, you need either OID *or* a primary key. Obviously, that id column should get a pk. We'll extend the grid some time, to accept any other unique keys also. Still, defining a PK on every table is best practice. Defining a column as serial does *not* guarantee it's unique! Regards, Andreas
Ahhhh. You are correct. Turns out I was failing to define my serial 'id' attribute as a primary key. My bad. Thanks for the help. MD >-----Original Message----- >From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] >Sent: Tuesday, February 17, 2004 9:15 AM >To: mdoggett@coas.oregonstate.edu >Cc: [pgADMIN] >Subject: Re: [pgadmin-support] inserting new records without OIDs > >Matt Doggett wrote: > >>Ooops. I sent this yesterday to the wrong maillist address. Sorry. I'll >>repost here. >> >>MD >>-----Original Message----- >>When I create a new table that does not have OIDs (the Has OIDs checkbox >>is off and I'm using a serial column type to id my records), then I >>CANNOT insert new data records using the Edit Data window. There is no >>blank row (with an asterisk *) in which to enter new data. In other >>words, I've discovered that you MUST use OIDs to be able to insert new >>records with the Edit window. >> >> >Not true. Currently, you need either OID *or* a primary key. Obviously, >that id column should get a pk. >We'll extend the grid some time, to accept any other unique keys also. >Still, defining a PK on every table is best practice. >Defining a column as serial does *not* guarantee it's unique! > >Regards, >Andreas
> Not true. Currently, you need either OID *or* a primary key. Obviously, > that id column should get a pk. > We'll extend the grid some time, to accept any other unique keys also. > Still, defining a PK on every table is best practice. > Defining a column as serial does *not* guarantee it's unique! Remember that unique indexes can have multiple NULL values. Also, oids are not guaranteed unique. Chris
Christopher Kings-Lynne wrote: >> Not true. Currently, you need either OID *or* a primary key. >> Obviously, that id column should get a pk. >> We'll extend the grid some time, to accept any other unique keys >> also. Still, defining a PK on every table is best practice. >> Defining a column as serial does *not* guarantee it's unique! > > > Remember that unique indexes can have multiple NULL values. Shhh, didn't think of that. So we won't ever accept unique indices for this. > Also, oids are not guaranteed unique. AFAICS this only happens on wrap around, i.e. hopefully never. Being strict, we shouldn't rely on oid uniqueness, but in practice hopefully nobody will ever notice. Regards, Andreas
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 18 February 2004 10:13 > To: Christopher Kings-Lynne > Cc: mdoggett@coas.oregonstate.edu; [pgADMIN] > Subject: Re: [pgadmin-support] inserting new records without OIDs > > Christopher Kings-Lynne wrote: > > >> Not true. Currently, you need either OID *or* a primary key. > >> Obviously, that id column should get a pk. > >> We'll extend the grid some time, to accept any other unique keys > >> also. Still, defining a PK on every table is best practice. > >> Defining a column as serial does *not* guarantee it's unique! > > > > > > Remember that unique indexes can have multiple NULL values. > > Shhh, didn't think of that. So we won't ever accept unique > indices for this. Unique + not null should be OK, but only if the data was inserted with that constraint in place. > > Also, oids are not guaranteed unique. > > AFAICS this only happens on wrap around, i.e. hopefully > never. Being strict, we shouldn't rely on oid uniqueness, but > in practice hopefully nobody will ever notice. <grin> Regards, Dave.
> Shhh, didn't think of that. So we won't ever accept unique indices for > this. What we do in phpPgAdmin is that rows with NULL values in the unique key simply aren't editable - all other rows are though. In order, we prefer to use a PK (since it is indexed), then oid column (since it is complete), then unique. It's even better if they add a unique constraint to their oid column :) >> Also, oids are not guaranteed unique. > > > AFAICS this only happens on wrap around, i.e. hopefully never. Being > strict, we shouldn't rely on oid uniqueness, but in practice hopefully > nobody will ever notice. What phpPgAdmin does for oid edits is it begins a transaction, does the update, checks to see how many rows were affected. If more than one row affected, rollback and complain, otherwise commit. Chris