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?
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):
So even if there aren't any result rows, we already know the structure they would've had.