I am not sure if I should post this here or in a potgre group, but I
will start here. I recently got a new server and thus when I copied
all the files to the new machine, I logically decided to upgrade
everything to the newest versions. 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. I am not sure what is the difference between the tables
that allows or disallows these buttons to be there. Is it at all
possible to get these buttons back? (originally on the other server,
all tables had these buttons). The only thing I can think of is that
there is a configuration setting that needs to be changed or that
something in the conversion process between 7.4 to 8.1 changed
something. The version of phpPgAdmin I have is 4.0.1

An anyone can provide me for this would be greatly apreciated.
Let me know if you think this is a postgre problem and I will instead
post in that group.

Thank you,
Daniel

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/

On Jan 23, 3:07 pm, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> 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/

Would it be enough to do something like
SELECT * INTO table2 FROM table WITH OIDS
so as to duplicate the table this time using OIDS, the reason why I
ask this is because a lot of the tables do not have a single unique
identifying tag (one of the downfalls of the system I inherited from
previous webmasters) In other words I am looking for a way to enable
OIDS on existing tables or to recreate the tables exactly with OIDS

Thanks,
Daniel

Dan99 wrote:

> Would it be enough to do something like SELECT * INTO table2 FROM table
> WITH OIDS

I've not tried that, but I imagine it will work. Relying on OIDs isn't a
great idea though, because:

a) they're a non-standard feature of PostgreSQL, so if you need
to move to a different database, you won't be able to use
them; and

b) PostgreSQL seems to be phasing them out, so if you *don't* move
to a different database, you may not be able to use them in the
future.

--
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 25 days, 2:38.]

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

On Jan 24, 10:28 am, Toby A Inkster <usenet200...@tobyinkster.co.uk>
wrote:
> Dan99 wrote:
> > Would it be enough to do something like SELECT * INTO table2 FROM table
> > WITH OIDS
>
> I've not tried that, but I imagine it will work. Relying on OIDs isn't a
> great idea though, because:
>
> a) they're a non-standard feature of PostgreSQL, so if you need
> to move to a different database, you won't be able to use
> them; and
>
> b) PostgreSQL seems to be phasing them out, so if you *don't* move
> to a different database, you may not be able to use them in the
> future.
>
> --
> 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 25 days, 2:38.]
>
> CSS to HTML Compiler
> http://tobyinkster.co.uk/blog/2008/01/22/css-compile/

I know this is starting to get way off of php, but you sound like you
know what you are talking about so I hope you dont mind ing me out
a bit more. Changing the config file worked good for creating new
tables with the oids, but I want to replicate all the existing tables
so that they have oids. I ran the following query on a test table
that does not have OIDs after changing the config:
SELECT * INTO table2 FROM table
This works well except for the fact that it doesnt copy anything but
the data. For example any indexes, constraints, or triggers do not
copy to the new table? How would I go about making an exact duplicate
of a table?

Thanks again,
Daniel

Dan99 wrote:

> SELECT * INTO table2 FROM table
> This works well except for the fact that it doesnt copy anything but the
> data. For example any indexes, constraints, or triggers do not copy to
> the new table? How would I go about making an exact duplicate of a
> table?

CREATE TABLE foo (LIKE bar INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
INSERT INTO foo SELECT * FROM bar;

This will copy the table structure, data and constraints, but not indexes
or triggers. If you want to make sure that indexes and triggers are
copied, probably the best route is to use the pg_dump backup tool that
comes with PostgreSQL to dump the table in its entirety to a flat SQL
file, which can then be opened with a text editor to change the table name
and then restored.

I suggest continuing this discussion in comp.databases.postgresql.

--
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 22:33.]

Looking Ahead to PHP 6
http://tobyinkster.co.uk/blog/2008/01/29/php6/