When we use the VALUES statement to create a constant table, PostgreSQL automatically names the columns column1, column2, etc. This saves us from having to provide names for the columns or from getting a blank column header.

But we also have the option of naming the columns ourselves.

The Default Names

First, here’s an example that shows the default column names produced by VALUES:

VALUES 
  (1, 'Cat'), 
  (2, 'Dog'), 
  (3, 'Bird');

Result:

 column1 | column2 
---------+---------
       1 | Cat
       2 | Dog
       3 | Bird

By default, the column headers show column1 and column2. If there was a third column it would be named column3, a fourth column4, and so on.

Renaming Columns – Example 1

Here’s one technique we can use to provide our own names for the columns:

SELECT * FROM (
  VALUES 
    (1, 'Cat'), 
    (2, 'Dog'), 
    (3, 'Bird')
  ) AS t1 (id, animal);

Result:

 id | animal 
----+--------
  1 | Cat
  2 | Dog
  3 | Bird

Here, I used column aliases to provide my own names for the columns. I did this by including the column names in parentheses at the end of the query.

Note that when we put VALUES in a subquery like this, we must provide an alias for the table. In this case I named the table t1.

Renaming Columns – Example 2

Here’s another way to do it:

SELECT 
  column1 AS id,
  column2 AS animal
FROM (
  VALUES 
    (1, 'Cat'), 
    (2, 'Dog'), 
    (3, 'Bird')
  ) AS t1;

Result:

 id | animal 
----+--------
  1 | Cat
  2 | Dog
  3 | Bird

In this example I still provided column aliases, but this time I defined them in the SELECT list. We got the same result as when we defined them at the end of the query.