How to return two values from PostgreSQL subquery?

I have a problem where I need to get the last item across various tables in PostgreSQL.

The following code works and returns me the type of the latest update and when it was last updated.

The problem is, this query needs to be used as a subquery, so I want to select both the type and the last updated value from this query and PostgreSQL does not seem to like this... (Subquery must return only one column)

Any suggestions?

SELECT last.type, last.max FROM (
    SELECT MAX(a.updated_at), 'a' AS type FROM table_a a WHERE a.ref = 5 UNION
    SELECT MAX(b.updated_at), 'b' AS type FROM table_b b WHERE b.ref = 5
) AS last ORDER BY max LIMIT 1

Query is used like this inside of a CTE;

WITH sql_query as (
    SELECT id, name, address, (...other columns),

    last.type, last.max FROM (
      SELECT MAX(a.updated_at), 'a' AS type FROM table_a a WHERE a.ref = 5 UNION
      SELECT MAX(b.updated_at), 'b' AS type FROM table_b b WHERE b.ref = 5
    ) AS last ORDER BY max LIMIT 1

    FROM table_c
    WHERE table_c.fk_id = 1      
  )

Solution

The inherent problem is that SQL (all SQL not just Postgres) requires that a subquery used within a select clause can only return a single value. If you think about that restriction for a while it does makes sense. The select clause is returning rows and a certain number of columns, each row.column location is a single position within a grid. You can bend that rule a bit by putting concatenations into a single position (or a single "complex type" like a JSON value) but it remains a single position in that grid regardless.

Here however you do want 2 separate columns AND you need to return both columns from the same row, so instead of LIMIT 1 I suggest using ROW_NUMBER() instead to facilitate this:

WITH LastVals as (
      SELECT type
           , max_date
           , row_number() over(order by max_date DESC) as rn
      FROM (
            SELECT MAX(a.updated_at) AS max_date, 'a' AS type FROM table_a a WHERE a.ref = 5
            UNION ALL
            SELECT MAX(b.updated_at) AS max_date, 'b' AS type FROM table_b b WHERE b.ref = 5
            )
     )
, sql_query as (
     SELECT id
          , name, address, (...other columns)

          , (select type from lastVals where rn = 1) as last_type
          , (select max_date from lastVals where rn = 1) as last_date 

     FROM table_c
     WHERE table_c.fk_id = 1      
    )

----

By the way in your subquery you should use UNION ALL with type being a constant like 'a' or 'b' then even if MAX(a.updated_at) was identical for 2 or more tables, the rows would still be unique because of the difference in type. UNION will attempt to remove duplicate rows but here it just isn't going to help, so avoid that wasted effort by using UNION ALL.

----

For another way to skin this cat, consider using a LEFT JOIN instead

  SELECT id
      , name, address, (...other columns)

      , lastVals.type
      , LastVals.last_date 

  FROM table_c
  WHERE table_c.fk_id = 1      
  LEFT JOIN (
        SELECT type
             , last_date
             , row_number() over(order by last_date DESC) as rn
        FROM (
              SELECT MAX(a.updated_at) AS last_date, 'a' AS type FROM table_a a WHERE a.ref = 5
              UNION ALL
              SELECT MAX(b.updated_at) AS last_date, 'b' AS type FROM table_b b WHERE b.ref = 5
              )
        ) LastVals ON LastVals.rn = 1