PostgreSQL: `VIEW` returns no rows with `SPLIT_PART()` columns

Question

Problem Description:

Hi everyone, I'm having some troubles querying on a VIEW whose columns are, in part, the result of SPLIT_PART() function on og table's column; I created the VIEW as it follows:

CREATE VIEW ClientsAddressList(Client_ID, FirstName, LastName, ResidenceAddress, City, PostalCode, Province) AS
SELECT Client_ID, 
       FirstName, 
       LastName, 
       SPLIT_PART(Address, '-', 1) AS ResidenceAddress, 
       SPLIT_PART(Address, '-', 2) AS City, 
       SPLIT_PART(Address, '-', 3) AS PostalCode,     
       SPLIT_PART(Address, '-', 4) AS Province
FROM Clients;

My intention was to divide the structured attribute (Clients.Address defined as a string VARCHAR(255)) which contains all the informations releated to client's domicile in several columns to separately query (e.g. SELECT FirstName, LastName FROM ClientAddressList WHERE City LIKE 'N%'; or SELECT Client_ID FROM ClientAddressList WHERE PostalCode = '82305';).

What I experience:

The Clients table contains one test row:

Client_ID	FirstName	LastName	ResidenceAddress	City	PostalCode	Province
00451	Ezio	Auditore	Via dei Banchi 45 - Florence - 50123 - Florence	Florence	50123	Florence

So my VIEW has this row:

Client_ID	FirstName	LastName	ResidenceAddress	City	PostalCode	Province
00451	Ezio	Auditore	Via dei Banchi 45	Florence	50123	Florence

I've tried:

SELECT Client_ID, FirstName, LastName
FROM ClientsAddressList
WHERE City = 'Florence'

And it returns no result:

Client_ID	FirstName	LastName	ResidenceAddress	City	PostalCode	Province

But if I query on columns that are not the result of SPLIT_PART() it works:

SELECT Client_ID, FirstName, LastName, City
FROM ClientsAddressList
WHERE Client_ID = '00451'

Client_ID	FirstName	LastName	City
00451	Ezio	Auditore	Florence

What I expect:

I would WHERE clause to work and returns values even on SPLIT_PART() result columns:

SELECT Client_ID
FROM ClientAddressList
WHERE PostalCode LIKE = '%123'

Client_ID
00451

Can someone explain me what could be the problem, please? Thank you so much!

Answer 1

As sticky bit wrote: there are spaces around the values. There are two ways to deal with this. One way is to just slap a trim() around the expressions in the view:

trim(SPLIT_PART(Address, '-', 2)) AS City, 

The other option is to use an appropriate regex to split the information to remove the whitespace during splitting:

select client_id, 
       firstname, 
       lastname,
       address[1] as residenceaddress,
       address[2] as city,
       address[3] as postalcode,
       address[4] as province
from (
  select client_id, firstname, lastname,
         regexp_split_to_array(residenceaddress, '\s*-\s*') as address      
  from clients
) t  

Online example

---

In the long run you should fix your data model by properly normalizing it and storing those values in separate columns. I don't know how many city names contain dashes in Italy, but in Germany, this pattern would break quickly with city names like "Garmisch-Partenkirchen" or "Leinfelden-Echterdingen"