PostgreSQL: cast row to type

Question

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.

• Evan Carroll: This doesn't make sense. I don't understand what you're trying to do. What is the question? The chosen answer is just clarifying a misconception about the row constructor you've got. Even on the chosen answer "Yes, you can." But, "you can" what? He doesn't show what you can do, why you'd want to do it, or anything related to a question. It's just basically "this how the ROW() constructor works." Which isn't the question.
• VH-NZZ: @EvanCarroll Actually this does make perfect sense. Try reading the question and the answer again. If you still need help, try posting a question (and not a comment).
• Evan Carroll: I don't have a question. I don't understand fully your question. And I don't think the answer provided is very clear either.
• Evan Carroll: see if you find that answer any cleaner in explaining this.

Answer 1

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:

• Array of strings when updating a field

• VH-NZZ: 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)
• VH-NZZ: 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.
• Erwin Brandstetter: @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
• Erwin Brandstetter: @VH-NZZ: Note the added bits to address a corner case.

Answer 2

The record constructor ROW() and a Composite Type

In PostgreSQL, every TABLE has a corresponding composite TYPE that represents a row. That is to say, when you create a table,

CREATE TABLE loc (x float, y float);
INSERT INTO loc VALUES (1.0,0.0);

You have a corresponding composite type created (ignore what this returns),

SELECT * FROM pg_catalog.pg_type WHERE typname = 'loc';

Likewise, you can NOT create a type over it, because it's as if you already created the type (which you did implicitly by creating the table),

CREATE TYPE loc AS (x float, y float, z float);
ERROR: type "loc" already exists

You can select all the columns, by asking for a lookup in the metadata,

SELECT * FROM loc;

Or you can select the composite type representing the row, either implicitly typed or explicitly typed,

SELECT loc FROM loc;
SELECT loc::loc FROM loc;

You can index into the row too,

SELECT loc.x FROM loc; -- One column, explicit
SELECT loc.* FROM loc; -- All columns, metadata lookup

The row-constructor ROW() does not create a strongly typed composite type, it creates a record, but you CAN covert from a record to a composite type,

SELECT pg_typeof(ROW(1.0,2.0)); -- returns 'record'
SELECT ROW(1.0,2.0)::loc; -- works
SELECT (ROW(1.0,2.0)::loc).x; -- works

Your question

Now this is the problem in your question: you got this error,

ERROR: cannot cast type record to locations

But that is NOT true, you CAN cast a type record to locations. The problem is record is effectively untyped and anything can be a record, and yours is incompatible with implicit casting,

-- Create a record by wrapping the composite type loc
-- then convert that to type loc (ERROR)
SELECT ROW(loc)::loc FROM loc;
-- Create a record by first unwrapping the composite type
-- wrapping the constituents of it into a record, then
-- convert the record to a composite type loc
SELECT ROW(loc.*)::loc FROM loc;

PostgreSQL only supports the coercion on the bottom, which is still a record. But alas, why would you want deconstruct and unwrap the composite type and recreate it? And that's the problem full-circle, just don't involve the abstract record constructor at all and always use the stricter composite type.

SELECT loc FROM loc;

• VH-NZZ: Great answer. Thank you for contributing.
• Evan Carroll: @VH-NZZ you can mark it as chosen if you feel like it better answers the question. You're the only judge of that.

Answer 3

I've been messing around with types and casts this past week too and love your question. Here's a bit of a trick (?)

create table state_crunched
 (data state);
-- table_name::table_name or table_name::text cast a row/compound type to a (csv,ish,"format like this") insert into state_crunched 
 select state::state from state; 

In my case, the sample table is state:

CREATE TABLE IF NOT EXISTS api.state (
 id uuid DEFAULT gen_random_uuid(),
 "name" citext,
 abbreviation citext,
 population bigint,
 total_sq_miles real,
 percent_land text
);

In our case, we're implementing one+ multiple custom types per table, and it might be helpful to be able to archive rows according to a type/format:

create table state_crunched
 (format_name text,
 data state);

I just tried, and I don't see a way of creating an anonymous record as a column type. (Other than storing them as text, etc. after the table::table serialization trick.) I'm thinking of storing records in rows for archiving. It's probably better to use replication and a history table for each source archive. But, like you, I'm just getting my head around the range of features available. So, in the sketch table above, the type name would then let you know how to unpack the data back into a record structure. You could have a ::casting defined with CREATE CAST and a function to handle the expansion. Or at least that's what I was thinking, I don't know how to make that work.

If you're aksing "why multiple compound types for a table, which is itself already a compound type?", then fair question.

CREATE TYPE api.state_v1 AS
(
 "name" citext,
 population bigint,
 total_sq_miles real,
 percent_land text,
 statehood_year integer,
);
CREATE TYPE api.state_v2 AS
(
 id uuid,
 "name" citext,
 abbr citext,
 population bigint,
 total_sq_miles real,
 percent_land real,
 statehood_year smallint,
 capital citext
);

In our case, we've got a distributed system where CRUD work is done in something other than Postgres. Then data is pushed up to Postgres for aggregation and analysis. The "client" applications in the field may lag significantly between updates. It's entirely possible for a site to be months behind the latest release. This means that operations like INSERT are based on the structure in Postgres from months ago. I think that in this situation, many people have an ORM or some centralized layer for translating input formats of different types into the current structure. Well, Postgres is our centralized system, so the code goes there. The idea is to have an INESRT handling function that accepts arrays of rows in a particular format, like state_v1[] or state_v2[]. The server-side function(s) then unnest the incoming array data, massages it as needed, and inserts it into the table. If the underlying table has had column names added, dropped, renamed, or retyped, then the function can deal with getting the old format into the new shape.

I'll be watching with interest if you come up with any ideas, info, or tricks.