PostgreSQL, unnest array into values

Question

Assuming I have text input:

Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ

Please quote us on prices and stock availability

К52-1Б - 68мкф/50в
К52-1 - 100мкф/63в

How can I modify this query, so I don't need to split lines manually?

with long_string (ls) as (values
    ('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ'),
    ('Please quote us on prices and stock availability'),
    ('К52-1Б - 68мкф/50в'),
    ('К52-1 - 100мкф/63в')
)
select products.*, matches, similarity
from products
cross join long_string
cross join lateral
(select
    (name % ls)::int as matches,
    similarity(name, ls) as similarity
) m
where matches > 0 and similarity > 0.7
order by matches desc, similarity desc;

I tried

with long_string (ls) as (
    values
        unnest(string_to_array('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ 
        Please quote us on prices and stock availability
        К52-1Б - 68мкф/50в
        К52-1 - 100мкф/63в', '\n'))
    )
select products.*, matches, similarity
....

But there is an error ERROR: syntax error at or near "unnest"

Gordon Linoff · Accepted Answer · 2019-03-01 13:18:17Z

3

If this is just about parsing the string, then:

with long_string (ls) as (
    select unnest(string_to_array(str, E'\n'))
    from (values ('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ 
Please quote us on prices and stock availability
К52-1Б - 68мкф/50в
К52-1 - 100мкф/63в')
         ) v(str)
    )
select ls
from long_string;

Here is a db<>fiddle.

answered Mar 1, 2019 at 13:18

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

JosMac · Accepted Answer · 2019-03-01 13:21:04Z

1

Or you can do this:

with long_string (ls) as (
    select
         trim(regexp_split_to_table('Buying on Saturday, delivery by XXX, address YYY, payment method ZZZ
        Please quote us on prices and stock availability
        К52-1Б - 68мкф/50в
        К52-1 - 100мкф/63в', '\n'))
    )
select *
from long_string

answered Mar 1, 2019 at 13:21

JosMac

2,3601 gold badge22 silver badges25 bronze badges

1 Comment

Ilya Cherevkov Over a year ago

Thank you, Jos, this also works. I gonna accept Gordon's answer, since he was first to reply

Collectives™ on Stack Overflow

PostgreSQL, unnest array into values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related