Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to rename default f1, f2, f3... names when using row_to_json function for only some columns?

I can do




But if I want only names without id_customer, I have to use

row_to_json(row(first_name, last_name))

and then I get


And I would like to get this result with either default column names or my own. I know I can create my own composite type and use

row_to_json(row(first_name, last_name))::my_custom_type

but isn't it possible to do it right in the query without creating that type?

share|improve this question
Also, see: reference 1 and reference 2 for similar –  M.Mugge May 27 '14 at 21:57

3 Answers 3

up vote 7 down vote accepted

A common table expression allows you to specify aliases explicitly, not only for the CTE but for its columns.

WITH data(col1,col2,cola,colb) AS (
  VALUES (1,2,'fred','bob')
SELECT row_to_json(data) FROM data;

This is different to @dezso's example in that it doesn't use col AS alias for each col in a SELECT list; it aliases the column names in the CTE table alias.

I've used a VALUES expression as a subquery but you can use a SELECT whatever you like; the point is that whatever column-aliases are provided or assumed in the subquery can be overridden in the CTE definition by specifying a column-name-list.

You can do the same thing in a subquery, again instead of using AS alias:

SELECT row_to_json(data) 
FROM (VALUES (1,2,'fred','bob')) data(col1,col2,cola,colb);

This doesn't work with a ROW expression directly; you can only cast a ROW to a concrete type, you cannot alias it.

regress=> SELECT ROW(1,2,'fred','bob') AS x(a,b,c,d);
ERROR:  syntax error at or near "("
LINE 1: SELECT ROW(1,2,'fred','bob') AS x(a,b,c,d);
share|improve this answer
Is there any difference (apart from style and/or readability) between our solutions (usage, performance, etc.)? –  dezso Oct 27 '12 at 17:43
@dezso No, and I probably should've just posted a comment. Sorry. –  Craig Ringer Oct 28 '12 at 0:28
I think this is OK. I even upvoted your answer b/c it contains useful information which mine does not. –  dezso Oct 28 '12 at 8:08
   (select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last,
   customers as c

will do what you want without any performance impact (and is not too verbose):

  id  |   first_last                                |   age
  1   | {"fisrt_name": "John", "last_name": "Smit"} |   34
share|improve this answer

You can do something like this:

  SELECT 'bla' AS name1, 'otherbla' AS name2
SELECT row_to_json(r.*)

(Of course, the same can be achieved with

SELECT row_to_json(r.*)
FROM (SELECT 'bla' AS name1, 'otherbla' AS name2) r

but I found the former more readable.)

In the WITH part you can construct rows of any structure on the fly.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.