-3

I want to select rows based on a column value. I know for a fact the column value exists. The first query returns 100 rows from the listing table. The second query, which looks for listings.OriginatingSystemName = 'mfrmls` returns nothing. Why?

(Removing the quotes or using double quotes does not work).

I am using pgAdmin4 to run these queries.

first query:

select * from listing limit 100;

second query:

select * from listing where 'listing.OriginatingSystemName' = 'mfrmls'

This produces a 'column does not exist' error:

select * from listing where OriginatingSystemName = 'mfrmls'
Igor Tatarinov
  • 75
  • 6
  • Please avoid posting [images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) of data or code, sample data should be *consumable text* in your question, ideally as *create* and *insert* statements, or alternatively a [DB<>Fiddle](https://dbfiddle.uk/). See the [question guide](https://stackoverflow.com/help/how-to-ask). – Stu Feb 16 '22 at 23:15
  • Have you tried the where clause "OriginatingSystemName" like '%mfrmls%' to see if there may be spaces in column values? – Shiping Feb 16 '22 at 23:24
  • I tried running SELECT * FROM listings WHERE 'OriginatingSystemName' = '%mfrmls%'; but no luck – Igor Tatarinov Feb 16 '22 at 23:28
  • I updated the question to remove the code images per Stu comment – Igor Tatarinov Feb 16 '22 at 23:29
  • Single quotes are for string constants, not for column (or table) names. –  Feb 17 '22 at 06:30

1 Answers1

4

The correct syntax is to just write the column name in your WHERE statement:

SELECT * FROM listings WHERE "OriginatingSystemName" = 'mfrmls';

To elaborate further:

What your original query is doing is selecting every row in the listings table where the text string 'listings.OriginatingSystemName' is equal to this other text string 'mfrmls'. It is not actually grabbing the value from the column you want. No row in the table satisfies your where statement because your where statement is always false. Therefore, no rows are returned but the query was a success.

We need to implement the double quotes when dealing with case-sensitive identifiers. Here is some helpful documentation.

Aretle
  • 265
  • 1
  • 7
  • Your solution produces the following: `ERROR: column "originatingsystemname" does not exist` Putting quote marks around the column also does not work. – Igor Tatarinov Feb 16 '22 at 23:14
  • Sorry about that. Try wrapping your column name in double quotes. "OriginatingSystemName". Double quotes are used for case senstive identifiers. I'll correct the answer for you – Aretle Feb 16 '22 at 23:47