Database Administration
postgresql datatypes composite-types
Updated Tue, 26 Jul 2022 03:50:24 GMT

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.

Solution

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).

Related:

Comments (4)

  • +0 – Much obliged. The ROW() was indeed unnecessary. Just a follow-up question: Assuming you stored a locations object in this table XXX, why can't you do this: SELECT x.location.latitude FROM XXX X? The error I get is: ERROR: missing FROM-clause entry for table "location". Wait a minute, I thought location was of type locations which has a latitude field. Could this work if locations was a typed table? (disclaimer: again, not trying to do anything smart) — Sep 09, 2019 at 00:50  
  • +1 – This does work SELECT (x.location).latitude FROM XXX x. The parentheses probably alleviate the confusion between selecting from schema.table.field and the intended use here. — Sep 09, 2019 at 00:59  
  • +1 – @VH-NZZ: You can (as you found out yourself): SELECT (x.location).latitude FROM XXX x. And yes, the parentheses are needed to disambiguate like you state. See: stackoverflow.com/a/17245358/939860, stackoverflow.com/a/10774585/939860 — Sep 09, 2019 at 01:37  
  • +0 – @VH-NZZ: Note the added bits to address a corner case. — Sep 16, 2019 at 13:01  

External Links

External links referenced by this document:

References