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
– sticky bit, Commented Feb 8, 2021 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 — spicyhotnoodles
– spicyhotnoodles, Commented Feb 8, 2021 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
– sticky bit, Commented Feb 8, 2021 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 — spicyhotnoodles
– spicyhotnoodles, Commented Feb 8, 2021 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
– sticky bit, Commented Feb 8, 2021 at 11:42

score 2 · Accepted Answer · 2021-02-08 11:20:57Z

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 8, 2021 at 11:20

answered Feb 8, 2021 at 11:14

user330315

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

4 Comments

spicyhotnoodles Over a year ago

I see, thank you. I just wonder what the trim() function does

user330315 Over a year ago

trim() removes spaces at the beginning and the end of a string

spicyhotnoodles Over a year ago

Wonderful. I didn't know that. Thank you so much, it's the second time you answer me, I'm so glad

Frank Heikens Over a year ago

@stuckwithmrblonde: Check the manual, you can find the description of all functions, like TRIM(): postgresql.org/docs/13/functions-string.html

Collectives™ on Stack Overflow

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

Problem Description:

What I experience:

What I expect:

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem Description:

What I experience:

What I expect:

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related