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!

You got spaces around the dashes in the original data. The result isn't `'Florence'` but `' Florence '` for instance. Why don't you just normalize the original table though? — sticky bit, Feb 08 '21 at 11:08
@stickybit Oh, I see. So if I just would insert the string as `'Via dei Banchi 45-Florence-50123-Florence'` the problem could be solved. But what do you mean by 'normalize the original table'? I'm sorry, I am a newbie with DBMS systems — noodles, Feb 08 '21 at 11:26
By "normalize the original table" I mean you shouldn't store the address as a string with dashes in the first place but make several columns and store the individual parts. Like a column for the city, the post code, etc. Than you don't even need that awkward view. — sticky bit, Feb 08 '21 at 11:29
Now I got it. You know, I used the view and that split function just to make the professor know I can use it. That's the reason why — noodles, Feb 08 '21 at 11:40
Hmm, I don't know your Prof, but maybe showing them you know the first normal form (research that term if you don't know it) impresses them more... — sticky bit, Feb 08 '21 at 11:42

score 2 · Accepted Answer · 2021-02-08T11:20:57.927

2

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"

edited Feb 08 '21 at 11:20

answered Feb 08 '21 at 11:14

I see, thank you. I just wonder what the `trim()` function does – noodles Feb 08 '21 at 11:27
`trim()` removes spaces at the beginning and the end of a string – Feb 08 '21 at 11:28
Wonderful. I didn't know that. Thank you so much, it's the second time you answer me, I'm so glad – noodles Feb 08 '21 at 11:41
@stuckwithmrblonde: Check the manual, you can find the description of all functions, like TRIM(): https://www.postgresql.org/docs/13/functions-string.html – Frank Heikens Feb 08 '21 at 12:32

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

Problem Description:

What I experience:

What I expect:

1 Answers1