Search code examples
sqlpostgresql

Why does filtering on column return nothing in postgresql?

I am able to select all rows in postgresql:
SELECT * FROM public.qTimesProduction ORDER BY id ASC (works)
but filter selecting on a column:
SELECT * FROM public.qtimesproduction WHERE queryname = 'testQuery'; (!work)
returns nothing.
I have checked the privileges on the user and also the security policy on the table.
User has all privileges granted and there is no security policy on the table.

My table description: psql table description

Using the latest pgAdmin and postgres version 16.2

Solution

  • One possibility could be that the value in column has some non visible/printable character(s).
    A small sample:

    Create Table tbl ( id Int,  col Varchar(32) );
Insert Into tbl VALUES(1, 'DEF'), (2, 'GHI');
Select * From tbl;
    id col
    1 DEF
    2 GHI 
    -- adding two more rows with COL starts with 'ABC' but one with a blank and another with a blank and CR character
Insert Into tbl VALUES(101, 'ABC '), (102, 'ABC ' || Chr(10));
Select tbl.* From tbl;
    id col
    1 DEF
    2 GHI
    101 ABC
    102 ABC

    ... COL column looks the same - ABC

    -- uusing Where for what you see
Select * From tbl Where COL = 'ABC';
    id col

    No rows fetched ...

    ... there are various ways to check it - here are two of them...

    Select * From tbl Where SubStr(COL, 1, 3) = 'ABC';
    id col
    101 ABC
    102 ABC 
    Select tbl.*, Length(col) as len From tbl;
    id col len
    1 DEF 3
    2 GHI 3
    101 ABC 4
    102 ABC
    		5

    fiddle

    NOTE: Have in mind that nonprintables could be anywhere in the string and adjust your testings accordingly. In this answer I put them at the end of string.