Обсуждение: org.postgresql.util.PSQLException - column does not exist
Good day all,
I have a small java app that has been running against and oracle backend for some time.
Recently we have decided to move the postgres as our database of choice.
Right now we are porting and testing all our apps against the postgres backend.
So far it's going pretty well. I have been able to fix all the issues we have encountered so far.
Already I have had to change the apps source in a few places due to data type mismatches and this has gone smoothly.
Now the app runs until it checks a specific table, then complains that a column is missing.
I am pretty sure this is a driver issue rather than a postgres issue, but I was hoping somebody here could point me in the right direction.
here is the the error, the query and the table structures.
Many thanks.
billing=> \dS deposit_link;
Table "jbilling.deposit_link"
Column | Type | Modifiers
------------------+------------------+-----------
id | bigint | not null
deposit_type_id | bigint | not null
order_details_id | bigint | not null
amount | double precision | not null
Indexes:
"deposit_link_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"deposit_link_fk_1" FOREIGN KEY (deposit_type_id) REFERENCES deposit_type(id)
"deposit_link_fk_2" FOREIGN KEY (order_details_id) REFERENCES order_details(id)
billing=> \dS order_details
Table "jbilling.order_details"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
id | bigint | not null
mdn | bigint | not null
delivery_date | timestamp without time zone | not null
product_package | bigint | not null
user_id | bigint | not null
status | character varying(5) | not null
Indexes:
"order_details_pkey" PRIMARY KEY, btree (id)
"order_details_mdn_key" UNIQUE CONSTRAINT, btree (mdn, status, delivery_date)
"order_details_idx_1" btree (status)
Foreign-key constraints:
"order_details_fk_1" FOREIGN KEY (status) REFERENCES order_details_status(status)
Referenced by:
TABLE "deposit_link" CONSTRAINT "deposit_link_fk_2" FOREIGN KEY (order_details_id) REFERENCES order_details(id)
TABLE "mlh_group" CONSTRAINT "mlh_group_fk_1" FOREIGN KEY (pilot_order_details_id) REFERENCES order_details(id)
TABLE "mlh_group" CONSTRAINT "mlh_group_fk_2" FOREIGN KEY (member_order_details_id) REFERENCES order_details(id)
TABLE "mlh_quotes" CONSTRAINT "mlh_quotes_fk_1" FOREIGN KEY (pilot_order_details_id) REFERENCES order_details(id)
TABLE "order_link" CONSTRAINT "order_link_fk_1" FOREIGN KEY (order_details_id) REFERENCES order_details(id)
billing=> SELECT dl.amount FROM deposit_link dl, order_details od WHERE od.mdn = 1146255650307 AND od.id = dl.order_details_id;
amount
--------
(0 rows)
DEBUG 2013-07-18 14:48:43,221 [main] : QUERY: SELECT dl.amount FROM deposit_link dl, order_details od WHERE od.mdn = 1146255650307 AND od.id = dl.order_details_id
ERROR 2013-07-18 14:48:43,251 [main] : org.postgresql.util.PSQLException: ERROR: column "mdn" does not exist
Position: 39
org.postgresql.util.PSQLException: ERROR: column "mdn" does not exist
Position: 39
Like I say, any help pointers would be greatly appreciated.
--
Wayne Oliver
Wayne Oliver <wayn0.ml@gmail.com> writes:
> [ query works when done by hand, not so much when done by java program ]
I'd bet good money that there's more than one table named "order_details",
some of which have an "mdn" column while others don't. Most likely the
tables are in different schemas and you get the different results
because you and the program are running with different search_path
settings. But I've seen at least one case where the person wasn't even
connecting to the same database as his program was.
You could try something like
\d *.order_details
to check for the same-table-different-schemas theory.
regards, tom lane
On Mon, Jul 22, 2013 at 10:50 AM, Wayne Oliver <wayn0.ml@gmail.com> wrote: > ERROR 2013-07-18 14:48:43,251 [main] : org.postgresql.util.PSQLException: > ERROR: column "mdn" does not exist > Position: 39 What driver version are you using? It appears to me the error message has changed in recent versions, so I suggest an upgrade of the driver. What if you try to add an alias to the column? Luca
On 22 Jul 2013, at 3:11 PM, Luca Ferrari <fluca1978@infinito.it> wrote: > On Mon, Jul 22, 2013 at 10:50 AM, Wayne Oliver <wayn0.ml@gmail.com> wrote: >> ERROR 2013-07-18 14:48:43,251 [main] : org.postgresql.util.PSQLException: >> ERROR: column "mdn" does not exist >> Position: 39 > > What driver version are you using? It appears to me the error message > has changed in recent versions, so I suggest an upgrade of the driver. > What if you try to add an alias to the column? > I believe it's the latest one, postgresql-9.2-1003.jdbc4.jar Waynes-MacBook:~ wayne$ java -version java version "1.6.0_51" Java(TM) SE Runtime Environment (build 1.6.0_51-b11-456-11M4508) Java HotSpot(TM) 64-Bit Server VM (build 20.51-b01-456, mixed mode) tried using an alias and still nothing. (SELECT AS) Really don't know where to turn :-s Thanks for the input. -- Wayne
On 22 Jul 2013, at 6:09 PM, Giulio Calacoci <giulio.calacoci@2ndquadrant.it> wrote:
Il 22/07/2013 10:50, Wayne Oliver ha scritto:Good day all,Are you using some kind of persistence like Hibernate ?DEBUG 2013-07-18 14:48:43,221 [main] : QUERY: SELECT dl.amount FROM deposit_link dl, order_details od WHERE od.mdn = 1146255650307 AND od.id = dl.order_details_idERROR 2013-07-18 14:48:43,251 [main] : org.postgresql.util.PSQLException: ERROR: column "mdn" does not existPosition: 39org.postgresql.util.PSQLException: ERROR: column "mdn" does not existPosition: 39
Not that I am aware of.
Is there some way I could check or some more information I could provide?
Thanks
--
Wayne
On Mon, Jul 22, 2013 at 8:39 PM, Wayne Oliver <wayn0.ml@gmail.com> wrote: > Not that I am aware of. > Is there some way I could check or some more information I could provide? Are you issuing your queries via JDBC, maybe building them into your program, or are you using something like dao.persist( myObject )? In the former case you are not using an ORM like Hibernate, in the second case you are. If you are using a third party application you have to check the application details or search manually the jars to see which libraries are used. Now, to exclude some driver issue, what happens if you change the type of the target column to a more standard type (integer for example), if that is possible, of course? Have you checked, as Tom suggested, if there is another table that is masquerading the one you think your application is querying? Luca