Postgres character(50) compare to 'some string'

Question

Can some explain to me why this comparison in postgresql is not working the way I want it to?

So I have a database called customer and it has the columns of customerid, firstname, lastname, phone, and city.. Each one of these columns is a Character(50). I want to find only customers from a certain city and ran multiple queries trying to figure out how to select a certain city.. and I feel like this is going to be a simple answer but I am pretty new to sql querying.

The first query I ran is this

SELECT * 
FROM customer c 
WHERE c.city = 'Denver'

This query returned no results and I also tried LIKE, ILIKE nothing seems to work I've even changed it from character(50) to character varying and still no results.. Not sure what I am doing wrong here

"If the string to be stored is shorter than the declared length, values of type character will be space-padded [...]" (source). Is there any reason why you chose that particular type? — Vincent Savard
– Vincent Savard, Commented Sep 29, 2016 at 18:41

user330315 · Accepted Answer · 2016-09-29 18:41:06Z

2

Because character pads all values to the defined length. So 'Denver' is stored as 'Denver '

Quote from the manual

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way

This is how this data type is defined by the SQL standard. It is an ancient left over.

In a nutshell: never use character. It is always better to use varchar (this is true for essentially every SQL database - and even more so for Postgres).

answered Sep 29, 2016 at 18:41

user330315

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

Comments

Vic Colborn · Accepted Answer · 2016-09-29 19:15:44Z

You may be able to answer that yourself with a query similar to this:

SELECT FORMAT('''%s''',city) FROM customer WHERE city ILIKE 'D%';

You're looking for the value 'Denver' so I can think of three scenarios:

CHAR(50) has exactly 50 characters all the time every time. Your search for a string with only six characters will properly produce no records. I suggest VARCHAR or VARCHAR(50) for your definition. CHAR is more useful for fixed-length tokens like 2-letter state abbreviations. But you tried a LIKE query so...
Did the wildcard character make it into the LIKE/ILIKE query? Properly, it would look like this: SELECT * FROM customer WHERE city LIKE 'D%';
You didn't mention whether your city column is case-sensitve. If it is, then 'DENVER' is different from 'Denver'. And since you also tried an ILIKE query...
Maybe an obvious first step, and the reason I suggest the query above, is to check that the value actually made it into the table.

Feels like (2) or (4) is the issue.

Collectives™ on Stack Overflow

Postgres character(50) compare to 'some string'

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related