Simple Postgresql Statement - column name does not exists

Question

I've been pulling my hair out. I have a very simple postgre database, one specific table has a column named lName (uppercase N). Now I know with postgre I must quote lName since it contains an uppercase N.

I am trying to query the database with the following statement:

SELECT * 
FROM employee 
WHERE "lName" LIKE "Smith"

But I am receive this error:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "Smith" does not exist in .....

What is the issue here? Why is it saying the column is "Smith"?

Marc Gravell · Accepted Answer · 2011-04-27 06:25:01Z

34

I would guess:

 SELECT * FROM employee WHERE "lName" LIKE 'Smith'

(note the different quotes; "foo" is a quoted identifier; 'foo' is a string literal)

Also, in most SQL dialects, a LIKE without a wildcard is equivalent to =; did you mean to include a wildcard?

answered Apr 27, 2011 at 6:25

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Eisentraut · Accepted Answer · 2011-04-27 07:37:40Z

5

Because "Smith" is an identifier, and in that position, an identifier is expected to be a column. What you probably meant is a string literal, which uses single quotes: 'Smith'. So

SELECT * FROM employee WHERE "lName" LIKE 'Smith'

You probably also want a wildcard in the string to search for ('Smith%'?). LIKE matching is anchored to the beginning and end of a string, unlike typical regular expression matching.

answered Apr 27, 2011 at 7:37

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Collectives™ on Stack Overflow

Simple Postgresql Statement - column name does not exists

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related