How to Flatten an Array in PostgreSQL

Assume that we are dealing with the following table called tbl

| name  | values  |
+-------+---------+
| hello | {1,2,3} |
| world | {4,5,6} |
| world |         |

and we can to flatten it as follows:

| name  | value |
+-------+-------+
| hello | 1     |
| hello | 2     |
| hello | 3     |
| world | 4     |
| world | 5     |
| world | 6     |
| world |       |

Notice the null value that we value for the values in the last row.

We can flatter it using the unnest function and the left join operation so that to include the NULL values.

SELECT t.name, v.value
FROM   tbl t
LEFT   JOIN unnest(t.values) v(value) ON true;

References

[1] Stack Overflow