PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why does postgres query return 0?

This query returns the desired result:

Select * from media

This query returns 0:

select id from media where conc_title = 'origin of life'

conc_title has the type varchar and the entry is in the db.

these modifications don’t work either:

SELECT id FROM media WHERE TRIM(conc_title) = 'origin of life'

SELECT id FROM media WHERE conc_title LIKE '%origin of life%'

Doesn’t work for other conc_titles either. The result is the datatype without any rows.

Tags:

3

Answers


  1. 0

    Postgres does never return NULL in case no result was found.
    In case no result for a given condition was found 0 is returned.

    You can use coalesce in case you want to have values OR really NULL. This would make it more transparent as a human reader.

    Login or Signup to reply.
  2. 0

    I suspect a case issue, try lowercasing the title before comparing it:

    where lower(conc_title) like '%origin%'

    Note that this won’t use the index on conc_title if any, so to be used carefully depending on your dataset. Another approach could be to store the title lowercased already, perhaps in a separated column so that you keep the original case available. The like operator also may do a fulltable scan depending on how it’s used, check with the dbms you’re using.

    Login or Signup to reply.
  3. 0

    I suggest

    SELECT id FROM media WHERE REPLACE(conc_title, ' ', '') = REPLACE('origin of life', ' ', '');

    This approach helps if spaces are causing the mismatch. If this doesn’t resolve the issue, verify the data for hidden characters or mismatches in the conc_title values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search