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

postgresql query does not return anything even if data is there

i have a postgres db here where i can get the data from like so:

SELECT * FROM berlinBuildingFootprints

it returns all data from that table. when i want to receive on single datapoint like this:

SELECT * FROM berlinBuildingFootprints WHERE id = 'DEBE00YYH600008C'

it does not return anything, even if i know that this is in the database!

enter image description here

waht is wrong here? is the query wrong?

thanks a lot for help!!

Tags:

2

Answers


  1. 0

    You’re using the wrong quotes. 'id' = 'DEBC...' compares a literal text value of 'id' to your target, so you’re just comparing two different text constants. You probably wanted "id"='DEBC...' to compare the column named id to the target.

    Double quotes are for identifiers, single quotes for constants/literals. Doc warns about this:

    A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character (").

    If it still doesn’t work with the right quotes, you can inspect the value a bit closer: demo

    select quote_literal(id),
       (select array_agg(x) from string_to_table(id,null)_(x)) as characters,
       (select array_agg(ascii(x)) from string_to_table(id,null)_(x)) as codes,
       md5(id),
       sha256(id::bytea)
from (select id from berlinBuildingFootprints where ogc_fid=2260957 
      union all select 'DEBE00YYH600008C') a;
    quote_literal characters codes md5 sha256
    ‘DEBEO0YYН600OO8С ‘ {D,E,B,E,O,0,Y,Y,Н,6,0,0,O,O,8,С," "," "} {68,69,66,69,79,48,89,89,1053,54,48,48,79,79,56,1057,32,9} b73a00d70a9cb9648562f439c405ba84 xb71e3118fab4f0e6c56e889ee3b1e6bbc2c3bf6559344af91043d343b40df5b5
    ‘DEBE00YYH600008C’ {D,E,B,E,0,0,Y,Y,H,6,0,0,0,0,8,C} {68,69,66,69,48,48,89,89,72,54,48,48,48,48,56,67} 71d7878fa23f6d64cd9d18f09ead1b5e x35be25e5f87b7149d82c304e42a2707c08ce8363bcc14dbc952a792201980851 
    select a.n, 
       a.a, ascii(a.a), 
       b.b, ascii(b.b), 
       a.a is not distinct from b.b AS is_equal
from berlinBuildingFootprints x,
     string_to_table(id,null) with ordinality as a(a,n) full outer join 
     string_to_table('DEBE00YYH600008C',null) with ordinality as b(b,n) on a.n=b.n
where x.ogc_fid=2260957;
    n a ascii b ascii is_equal
    1 D 68 D 68 t
    2 E 69 E 69 t
    3 B 66 B 66 t
    4 E 69 E 69 t
    5 O 79 0 48 F
    6 0 48 0 48 t
    7 Y 89 Y 89 t
    8 Y 89 Y 89 t
    9 Н 1053 H 72 F
    10 6 54 6 54 t
    11 0 48 0 48 t
    12 0 48 0 48 t
    13 O 79 0 48 F
    14 O 79 0 48 F
    15 8 56 8 56 t
    16 С 1057 C 67 F
    17   32 null null F
    18 9 null null F
    Login or Signup to reply.
  2. 0

    In the spirit of Mandy and Laurenz’s points, you could also try ..

    SELECT * FROM berlinBuildingFootprints WHERE 'DEBE00YYH600008C' in id

    … or …

    SELECT * FROM berlinBuildingFootprints WHERE id LIKE 'DEBE00YYH600008C%'

    Both queries allow for trailing whitespace. The former query also allows for leading whitespace. The latter query allows for any string that starts with the characters before the % sign.

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

Back To Top
Search