Postgresql: Cast row to type
And now I'm asking myself what this means and if I could store a locations object in there.


Yes, you can. (But there are not many great use cases for that.)

This does not do what you seem to think it does:

SELECT ROW(x)::locations FROM locations X LIMIT 1;

x is already a row type. By wrapping it into ROW(x) you create a record containing a column of type locations, which cannot be cast to the row type locations as it's something else. Use instead:

SELECT x::locations FROM locations x LIMIT 1;


... where the cast is redundant. So just:

SELECT x FROM locations x LIMIT 1;


However, if there is a column of the same name "x", this resolves to the column name. Pick a table alias that can never appear as column name or use this to be sure:

SELECT (x.*)::locations FROM locations x LIMIT 1;


Now, the cast is not redundant as Postgres would otherwise expand x.* or even (x.*) to the list of columns. Read the manual here and here.



Also just:

SELECT pg_typeof(x) FROM locations x LIMIT 1;


instead of:

SELECT pg_typeof(ROW(x)) FROM locations x LIMIT 1;

Aside: the ROW constructor does not preserve column names and always produces an anonymous record (as you found out the hard way).

SELECT ROW(x)::locations FROM locations X LIMIT 1;


Related:


Array of strings when updating a field