Split column into multiple rows in Postgres
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:
- "invalid reference to FROM-clause entry for table" in Postgres query
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:
- What is the expected behaviour for multiple set-returning functions in SELECT clause?
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:
- What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
We could also use regexp_split_to_table()
, but that's typically slower because regular expressions cost a bit more. See:
- SQL select rows containing substring in text field
- PostgreSQL unnest() with element number
Postgres, split single row into multiple rows
In Postgres, you can efficiently unpivot the columns to rows with a lateral join:
select x.*
from mytable as t
cross join lateral (values
(t.age1, t.val1),
(t.age2, t.val2),
(t.age3, t.val3)
) as x(age, val)
Postgres: Split column values & transpose
You can use PostgreSQL's array manipulation functions:
SELECT category_id, unnest(string_to_array(product_id, ';')) FROM test;
string_to_array
does exactly what it says — splits a string into an array of parts using a supplied delimiter, then unnest
is used to separate an array value into multiple rows containing elements from the array.
Split value in single row to multiple rows with Postgresql
You could create an array and then use UNNEST():
SELECT UNNEST(ARRAY[1,2,3]);
Postgresql split column into rows
Try this:
SELECT
username,
regexp_split_to_table(towns, E',')
FROM yourTable
SQLFIDDLE DEMO
Postgres - Splitting a row into multiple rows based on two datetime fields to get logged minutes each hour
We can approach this by first generating a calendar table covering all minutes on all dates in your data set. Then, inner join to your data table and aggregate by the hour (and day) to generate the counts:
WITH dates AS (
SELECT '2021-04-05'::date AS dt UNION ALL
SELECT '2021-04-06'::date
),
minutes AS (
SELECT (n || ' minute')::INTERVAL AS min
FROM generate_series(0, 1439) n
)
SELECT
DATE_TRUNC('hour', d.dt + m.min),
COUNT(*) AS minutes_logged
FROM dates d
CROSS JOIN minutes m
INNER JOIN yourTable t
ON d.dt + m.min >= t.started_at AND d.dt + m.min < t.end_at
GROUP BY DATE_TRUNC('hour', d.dt + m.min)
ORDER BY DATE_TRUNC('hour', d.dt + m.min);
Demo
Note: To support wide range of start and end dates following can be implemented
with dates AS (
select * from generate_series(timestamp '2019-12-08'::date, '2021-04-09'::date, '1 day') as dt
),
Split column and values into multiple rows in Postgres
You can use generate_series()
to produce list of consecutive days between first_date
and last_date
:
with dates as (
select d::date, last_date- first_date+ 1 ct
from test, generate_series(first_date, last_date, '1d'::interval) d
)
select subject, flag/ ct flag, d date
from dates
cross join test;
subject | flag | date
----------------+------------------------+------------
this is a test | 0.50000000000000000000 | 2016-01-01
this is a test | 0.50000000000000000000 | 2016-01-02
this is a test | 0.50000000000000000000 | 2016-01-03
this is a test | 0.50000000000000000000 | 2016-01-04
(4 rows)
Related Topics
Error 1046 No Database Selected, How to Resolve
Concatenate Multiple Result Rows of One Column into One, Group by Another Column
SQL Case Sensitive String Compare
SQL Query: Delete All Records from the Table Except Latest N
How to Create a Sequence in MySQL
SQL to Json - Array of Objects to Array of Values in SQL 2016
How to Use on Delete Cascade in MySQL
How to Generate a Range of Dates in SQL Server
Microsoft Jet Wildcards: Asterisk or Percentage Sign
Key Value Pairs in Relational Database
How to Set Table Name in Dynamic SQL Query
Group by and Aggregate Sequential Numeric Values
How to Dump the Data of Some Sqlite3 Tables
SQL Server - How to Lock a Table Until a Stored Procedure Finishes