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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 – Mike Mügge May 27 '14 at 21:57
up vote 13 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
Is there a syntax for getting the column aliases dynamically? I am pulling from an EAV (entity attribute value) schema where the desired key names are also selected from the column. – Chris Mar 1 '15 at 18:42
@Chris You'll need the more sophisticated json functions in 9.4. – Craig Ringer Mar 1 '15 at 21:39
   (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
This answer is a gem. – tiffon Mar 22 at 3:34
Thanks a lot you saved my afternoon, too bad this is not a cited example in PostgreSQL API. I knew it was possible – jlandercy Apr 28 at 13:31

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
To concatenate non-jsonb with jsonb :: SELECT row_to_json(r.*) FROM (SELECT c1,c2 ::jsonb FROM us_ca_monterey_aoc.test) as r – Andrew Scott Evans May 25 at 17:27

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.