Thread: Debugging PostgreSql queries
Long story short, I have a huge query in the application that throws the errror: "CASE types bigint and character varying cannot be matched ". I ran this query in pgAdmin, in phpPgAdmin, I only get the error but have no clue where the error actually is thrown. In mysql most of the time you get a "on line X" message in the error that can help you determine where the problem is. However in my case, since I didn't create the query( so I don't know the tables, fields, etc) and the query is very big, is there a way to find out where exactly where is this query thrown or do I have to manually start searching? 10x a lot |
On Thu, Nov 18, 2010 at 7:25 AM, Vaduvoiu Tiberiu <vaduvoiutibi@yahoo.com> wrote:
If you can run the query in psql you could try the following -
\set FETCH_COUNT 1
\pset pager off
<your query here>
It should cycle through row by row and then error on the line causing you problems.
Long story short, I have a huge query in the application that throws the errror:
"CASE types bigint and character varying cannot be matched ". I ran this query in pgAdmin, in phpPgAdmin, I only get the error but have no clue where the error actually is thrown. In mysql most of the time you get a "on line X" message in the error that can help you determine where the problem is. However in my case, since I didn't create the query( so I don't know the tables, fields, etc) and the query is very big, is there a way to find out where exactly where is this query thrown or do I have to manually start searching?
10x a lot
If you can run the query in psql you could try the following -
\set FETCH_COUNT 1
\pset pager off
<your query here>
It should cycle through row by row and then error on the line causing you problems.
Vaduvoiu Tiberiu <vaduvoiutibi@yahoo.com> writes: > Long story short, I have a huge query in the application that throws the errror: > "CASE types bigint and character varying cannot be matched ". I ran > this query in pgAdmin, in phpPgAdmin, I only get the error but have no > clue where the error actually is thrown. This error should include a cursor position if you're working with server version 8.4 or later. I infer that you're not. It'd be worth trying to install the application on a newer Postgres server. We frequently improve the error detection capabilities from one release to the next, so what was a hard problem might get easier. regards, tom lane