0

I am accessing the PostgreSQL server with libpq. My query looks like this ('Africa' is any string, column_2 and column_3 are both valid names and both queries return fine when executed from psql prompt):

SELECT column_2 
    FROM mytable 
    WHERE column_1='Africa' AND column_2 IS NOT NULL 
    LIMIT 1;

I get a result of 1 row and 1 column. Then some time later I change column_2 to column_3 and issue:

SELECT column_3 
    FROM mytable 
    WHERE column_1='Africa' AND column_3 IS NOT NULL 
    LIMIT 1;

Now PQnfields() returns 1 as expected, But PQntuples returns 0! So now for some reason there are 0 rows, which of course breaks a call to data = PQgetvalue(resuls, 0, 0)

What is a possible source of error and why would it even return 1 column if there are no rows?

user10607
  • 3,011
  • 4
  • 24
  • 31
  • What is the problem with a result that has 1 (or 2, 3, 10 ...) columns and no rows? It is perfectly valid on postgres side. – Ihor Romanchenko Oct 02 '14 at 15:05
  • 1
    If there is no data that matches `column_1='Africa' AND column_3 IS NOT NULL` then there are 0 rows to return. The structure (number and types of the columns) of returned rows is defined by the query itself. This structure does not depend on number of rows returned. – Ihor Romanchenko Oct 02 '14 at 15:07
  • @IgorRomanchenko yes, I basically had a typo. I guess that its according to some standard that PostgreSQL decides to return 1 column for a failed query instead of 0 ? – user10607 Oct 03 '14 at 06:12

1 Answers1

3

This query has one column but zero rows:

SELECT a as col1
FROM generate_series(1,2) a
WHERE false;

Nonetheless, it still has the same result type as if it did not have the WHERE FALSE. It just has zero rows.

This is why I prefer to think of fields and records, not "columns" and "rows". The latter leads you to think about it like a spreadsheet, but a relation is not a spreadsheet table. It still has a well-defined structure even if it is empty.

So, if PQntuples returns zero, don't try to access results that don't exist. It's normal and acceptable for a query to return zero rows. It can be quite handy; for example, you can still use PQftype to determine the data type that any results that were returned would be, if there were any.

The underlying reason it works this way is that PostgreSQL answers a query in a few stages (simplifying by assuming v3 protocol parse/bind/describe/execute execution, and skipping irrelevant steps):

  • "Query parsed OK"
  • "Parameters received OK"
  • "Query result structure will be (colname coltype, colname coltype, ...)"
  • "Query executed OK"
  • "Query result rows are ..."

So even if there aren't any result rows, we already know the structure they would've had.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Good to know! This is why it returned 1 column instead of 0, the result has the right format (number of fields) but just does not contain any rows (records). – user10607 Oct 03 '14 at 06:15