Posted by Toby A Inkster on 01/23/08 20:07

Dan99 wrote:

> So my postgre got upgraded from 7.4 to 8.1 and my phpPgAdmin also got
> upgraded. Now though, I am only able to use the edit/delete buttons on
> some of the tables when browsing.

When you want to update or delete a row, phpPgAdmin needs to be able to
unambiguously identify it using a primary key. I'm guessing that the
tables which don't have edit/delete buttons are those tables without a
primary key.

PostgreSQL 7.x had a feature called OIDs enabled by default. OIDs (Object
Identifiers) are a number which uniquely identify every row in your
database. OIDs are able to act as a default primary key when no other
unique column exists.

In PostgreSQL 8.x, OIDs are disabled by default. They can be enabled by
setting default_with_oids=on in postgresql.conf (however, this won't
affect existing tables -- just new ones you create), or by adding "WITH
OIDS" to your CREATE TABLE statement.

The solution is to make sure that all your tables have a primary key
designated. Luckily, you can designate a primary key without having to re-
create that table from scratch. You need to make sure that there is a
column or combination of columns which is per-row unique. For example in a
table of books, the book's ISBN number might make a good primary key. And
for a table keeping a tally of votes in a poll, and only allowing one vote
per poll per person, then the combination of user-id and poll-id would
work.

Then just:

ALTER TABLE my_books
ADD PRIMARY KEY (isbn);

ALTER TABLE my_poll_results
ADD PRIMARY KEY (userid, pollid);

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 24 days, 7:02.]

CSS to HTML Compiler
http://tobyinkster.co.uk/blog/2008/01/22/css-compile/

[Back to original message]