Postgresql: Cast row to type I read: Use composite type to create new table

I have a table called locations, e.g. representing objects with lat. and long. coordinates.

In another table, I declared a column of type locations (just for fun, not trying to be smart), i.e.

CREATE TABLE XXX (..., some_column locations, ...);


And now I'm asking myself what this means and if I could store a locations object in there.

And here's what I tried to do:

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


which returns record. I tried casting this to locations, i.e.

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


which yields 


  ERROR:  cannot cast type record to locations


Next I tried defining a composite type type_location based on the columns of the locations table, and created a typed table (CREATE TABLE ... OF ...) based on it. Still I am unable to do ROW(x)::locations.

Ultimately I'm trying to get a value to store into table XXX of type locations (or type_location) but I don't understand which part my reasoning is fallacious.

PS: I'm not trying to create a sound database design using this construction but really only just toying around with Postgresql and its type system.
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