Re: column "oid" does not exist

Matthew and Karen Brown wrote:

> I get the error message in the subject whenever I attempt to use an
> application that supports ODBC to access a user table in my database.
> When I create a table using "WITH OIDS", I have no problems viewing
> the table with any application. But when I use "WITHOUT OIDS", the
> only two applications that I have found that will work are MSQuery and
> MSAccess. (I am trying to use Avery DesignPro and OpenOffice without
> success.)
>
Yes, that makes sense.

> The things that I have tried (beside pounding my head against a wall)
> are just about all of the options in the configuration dialog and, of
> course, different applications. My use of PostgreSQL here is dependent
> upon my being able to get ODBC to work reliably. Why does it even care
> about selecting the OID field? All of the notes I have seen seem to
> indicate that its necessity is largely gone and certainly not relevant
> for anything that I am doing.
>
PostgreSQL does not support updateable cursors. To work around this
problem, the ODBC driver uses readonly cursors for its operations. In
order for updates to still work, the ODBC driver adds the OID field to
all of your queries, and then uses that field to perform the update on a
seperate query.

> Why would some apps be able to run a simple select query without
> generating this error while others cannot? I assume that the
> difference is in the way the ODBC calls are made by the client app. If
> this is so, why does not the driver handle this circumstance more
> elegantly?

If you open your query as "static readonly", there is no need for the
OID field, and the query is not changed before being sent to the
database server. That must be the difference between them.

As for why ODBC cannot do that himself, I don't know.

>
> In a possibly related issue, when I select from a view using these
> same apps, I get the error "colum ctid does not exist". Is this the
> same issue?
>
exactly the same issue, for exactly the same reason. A view doesn't have
the ctid and oid fields, and so you cannot select in read-write mode
from a view.

> I would like to follow the suggestions related to not using OIDs, but
> this one has me stumped.
>
Sorry. No easy way out that I can see.

> Grateful for any help,
>
> -- Matt

--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/