postgresql: extract multiple values from Char Varying field

Question

I have a field in PostgreSQL of type character varying [] stored here is sector, some customers have no data, others have 1 to many sectors which ends up looking like this

**Customer**  ABC        
**Sector** ,Exh: Food Trade,Exh: Beverage Trade,Evt: Sporting

does any one know how I could query this out per value? to give me the unique values per customer

**Customer** ABC **sector** Exh:Food Trade,
**Customer** ABC **sector** Exh:Beverage Trade
**Customer** ABC **Sector** Evt:Sporting

I don't understand how exactly the data is stored in that column. Sample data is better presented as a "table like" display (e.g. copy & paste from a psql output` See here for some tips on how to create nice looking tables. Or even better: create a little fiddle that sets up the table and some sample data, e.g. here: dbfiddle.uk/?rdbms=postgres_12 — user330315
– user330315, Commented Dec 6, 2019 at 8:47

Jim Jones · Accepted Answer · 2019-12-06 17:05:35Z

2

Try using unnest.

Test Data

CREATE TABLE t (customer text, sector text[]);
INSERT INTO t VALUES 
('ABC',string_to_array(',Exh: Food Trade,Exh: Beverage Trade,Evt: Sporting',','));

Query

SELECT customer, unnest(sector) FROM t;
 customer |       unnest        
----------+---------------------
 ABC      | 
 ABC      | Exh: Food Trade
 ABC      | Exh: Beverage Trade
 ABC      | Evt: Sporting
(4 Zeilen)

Edit: Getting rid of empty elements using a CTE (see comments)

WITH j AS (
SELECT customer, unnest(sector) as sector FROM t 
) SELECT * FROM j WHERE sector <> '';

 customer |       sector        
----------+---------------------
 ABC      | Exh: Food Trade
 ABC      | Exh: Beverage Trade
 ABC      | Evt: Sporting
(3 Zeilen)

edited Dec 6, 2019 at 17:05

answered Dec 6, 2019 at 8:51

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

2 Comments

user7679705 Over a year ago

aha I think that is it :), an example field has ",Exh: Food Trade,Exh: Beverage Trade," which un nested gives me a blank value for the first result, but then I do get exh: food trade and exh beverage trade in separate rows! why would I get the first row blank? is it the leading comma?

Jim Jones Over a year ago

it's because the first element of your array is empty. You string begins with a ,

Collectives™ on Stack Overflow

postgresql: extract multiple values from Char Varying field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related