user3044041 user3044041 - 1 year ago 87
SQL Question

Postgres character(50) compare to 'some string'

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

FROM customer c
WHERE = '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

Answer Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download