Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given the string:

'I think that PostgreSQL is nifty'

I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and would like to join an unnested array of that string on this dictionary.

So far I have:

select word, meaning, partofspeech
from unnest(string_to_array('I think that PostgreSQL is nifty',' ')) as word
from table t
join dictionary d
on t.word = d.wordname;

This accomplishes the fundamentals of what I was hoping to do, but it does not preserve the original word order.

Related question:
PostgreSQL unnest() with element number

share|improve this question
    
Do you want to process one string or a whole table of strings? If so, does the table have a primary key? –  Erwin Brandstetter Oct 19 '12 at 21:35
    
@ErwinBrandstetter one string in a table (that does have a primary key) –  swasheck Oct 19 '12 at 22:14
add comment

1 Answer

up vote 9 down vote accepted

For a single string

You can apply the window function row_number() to remember a distinct order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the ordinal number of the original position in the string.

You could try and simply omit the ORDER BY to get the position "as is":

SELECT *, row_number() OVER () AS rn
FROM  (
   SELECT regexp_split_to_table('I think postgres is nifty', ' ') AS word
   ) x;

Performance of regexp_split_to_table() degrades with long strings. unnest(string_to_array(...)) scales better:

SELECT *, row_number() OVER () AS rn
FROM  (
   SELECT unnest(string_to_array('I think postgres is nifty', ' ')) AS word
   ) x;

However, while this works normally, and I have never seen it break in simple queries, PostgreSQL asserts nothing concerning the order of rows without explicit ORDER BY.

To guarantee row numbers matching the ordinal number of the element in the string:

SELECT arr[rn] AS word, rn
FROM   (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM  (
      SELECT string_to_array('I think postgres is nifty', ' ') AS arr
      ) x
   ) y;

http://www.postgresql.org/docs/current/interactive/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

For a table of strings

Add PARTITION BY id to the OVER clause ..

Demo table:

CREATE TEMP TABLE strings(string text);
INSERT INTO strings VALUES
  ('I think postgres is nifty')
 ,('And it keeps getting better');

I use ctid as ad-hoc pk substitute. If you have a primary key or any unique column available use that instead.

SELECT *, row_number() OVER (PARTITION BY ctid) AS rn
FROM  (
   SELECT ctid, unnest(string_to_array(string, ' ')) AS word
   FROM   strings
   ) x;

This works without any distinct ID:

SELECT arr[rn] AS word, rn
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM  (
      SELECT string_to_array(string, ' ') AS arr
      FROM   strings
      ) x
   ) y

-> sqlfiddle.

Answer to question

SELECT z.arr, z.rn, z.word, d.meaning   -- , partofspeech --?
FROM  (
   SELECT *, arr[rn] AS word
   FROM   (
       SELECT *, generate_subscripts(arr, 1) AS rn
       FROM  (
         SELECT string_to_array(string, ' ') AS arr
         FROM   strings
         ) x
      ) y
   ) z
JOIN   dictionary d ON d.wordname = z.word
ORDER  BY z.arr, z.rn;

WITH ORDINALITY in version 9.4

As detailed in the related answer on SO, the upcoming feature simplifies this class of problems. The above query can then be:

SELECT *
FROM   regexp_split_to_table('I think postgres is nifty', ' ')
       WITH ORDINALITY AS x(word, rn)
share|improve this answer
1  
You can also exploit Pg's quirky SRF-in-SELECT-list behaviour: SELECT generate_series(1,array_length(word_array,1)), unnest(word_array) FROM ..... 9.3's LATERAL might provide nicer solutions to this problem. –  Craig Ringer Oct 21 '12 at 8:46
1  
Wouldn't generate_subscripts(arr, 1) work instead of generate_series(1, array_upper(arr, 1))? I'd prefer the former for clarity. –  dezso Nov 8 '12 at 13:46
    
@dezso: Good point. Simplifies the query some more. I amended the answer accordingly. –  Erwin Brandstetter Nov 8 '12 at 14:30
1  
@Erwin have you seen this WITH ORDINALITY post from depesz? –  Jack Douglas Sep 28 '13 at 15:03
1  
@JackDouglas: As it happens, we had a discussion about a related topic on Friday, which lead me to a similar discovery. I added a bit to the answer. –  Erwin Brandstetter Sep 30 '13 at 13:00
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.