Split column into multiple rows in Postgres

by

In Postgres 9.3+ use a LATERAL join. Minimal form:

SELECT token, flag
FROM   tbl, unnest(string_to_array(subject, ' ')) token
WHERE  flag = 2;

The comma in the FROM list is (almost) equivalent to CROSS JOIN, LATERAL is automatically assumed for set-returning functions (SRF) in the FROM list. Why “almost”? See:

The alias “token” for the derived table is also assumed as column alias for a single anonymous column, and we assumed distinct column names across the query. Equivalent, more verbose and less error-prone:

SELECT s.token, t.flag
FROM   tbl t
CROSS  JOIN LATERAL unnest(string_to_array(subject, ' ')) AS s(token)
WHERE  t.flag = 2;

Or move the SRF to the SELECT list, which is allowed in Postgres (but not in standard SQL), to the same effect:

SELECT unnest(string_to_array(subject, ' ')) AS token, flag
FROM   tbl
WHERE  flag = 2;

The last one seems acceptable since SRF in the SELECT list have been sanitized in Postgres 10. See:

If unnest() does not return any rows (empty or NULL subject), the (implicit) join eliminates the row from the result. Use LEFT JOIN ... ON true to keep qualifying rows from tbl. See:

We could also use regexp_split_to_table(), but that’s typically slower because regular expressions cost a bit more. See:

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Split comma separated column data into additional columns
  3. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  4. Call a set-returning function with an array argument multiple times
  5. Split given string and prepare case statement
  6. What’s the proper index for querying structures in arrays in Postgres jsonb?
  7. Record returned from function has columns concatenated
  8. How to return two columns with function
  9. Parallel unnest() and sort order in PostgreSQL
  10. PostgreSQL Crosstab Query
  11. Insert text with single quotes in PostgreSQL
  12. How do you use script variables in psql?
  13. Best way to check for “empty or null value”
  14. How to avoid multiple function evals with the (func()).* syntax in a query?
  15. Time zone storage in data type “timestamp with time zone”
  16. PostgreSQL: Which Datatype should be used for Currency?
  17. Finding similar strings with PostgreSQL quickly
  18. Sort NULL values to the end of a table
  19. Window Functions or Common Table Expressions: count previous rows within range
  20. Select (retrieve) all records from multiple schemas using Postgres
  21. Optimize groupwise maximum query
  22. Use email address as primary key?
  23. Creating temporary tables in SQL
  24. SQL query to find record with ID not in another table
  25. Get execution time of PostgreSQL query
  26. Postgresql tables exists, but getting “relation does not exist” when querying
  27. Function with SQL query has no destination for result data
  28. Crosstab with a large or undefined number of categories
  29. Truncate timestamp to arbitrary intervals
  30. Conditional lead/lag function PostgreSQL?

Leave a Comment