Have you ever ran a SQL query and wished you could change the column names in the result? It’s not hard to do and in this short article I’m going to show you how.
This is a great technique to polish up query results before showing them to someone else, especially people who aren’t familiar with your database. You can turn obscure column names into ones that are easy to understand. This is helpful for other people as well as yourself, reducing the mental gymnastics that’s often required to understand databases.
If you are sharing the results with someone else, consider saving the query result as a CSV file. I have an article that covers how to do that: Saving A PostgreSQL Query Result As A CSV.
SQL’s AS Statement
SQL has a statement called AS, or an alias. This is a very useful command where you can alias long, obscure column names into short memorable ones. You might have seen this in your application’s query log before, it looks like this:
1
2
SELECT t1.user_id AS id
...
And in that query, you can now just type id instead of t1.user_id every time you need to. This is really handy for making queries easier to type and understand. But SQL also uses the alias as the column name when printing the result:
1
2
3
4
5
6
7
8
9
example=# SELECT first_name AS name FROM users;
name
---------
Frodo
Samwise
Meriadoc
Peregrin
(4 rows)
Getting Fancy
And this is close to what we want but we can go one step further. You can use quoted strings in the value for AS and rename a column to whatever you want. For instance:
1
2
3
4
5
6
7
8
9
example=# SELECT first_name AS "First Name" FROM users;
First Name
------------
Frodo
Samwise
Meriadoc
Peregrin
(4 rows)
Now this is looking better. When referencing this column in other parts of your query, you can still use the original column name, first_name in this case. Let’s take it one step further and use it for every column.
Full Example
Here we’re going to be selecting the entire table and giving each column a fancy name. You can mix and match though, not every column needs an alias.
Here is the normal query and result:
1
2
3
4
5
6
7
8
9
example=# SELECT * FROM users;
id | first_name | last_name | email
----+------------+------------+--------------------
1 | Frodo | Baggins | frodo@example.com
2 | Samwise | Gamgee | sam@example.com
3 | Meriadoc | Brandybuck | merry@example.com
4 | Peregrin | Took | pippin@example.com
(4 rows)
And now we’ll make them fancy:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
example=# SELECT
example-# id as "ID",
example-# first_name as "First Name",
example-# last_name as "Last Name",
example-# email as "Email"
example-# from users;
ID | First Name | Last Name | Email
----+------------+------------+--------------------
1 | Frodo | Baggins | frodo@example.com
2 | Samwise | Gamgee | sam@example.com
3 | Meriadoc | Brandybuck | merry@example.com
4 | Peregrin | Took | pippin@example.com
(4 rows)
And there it is! This database had column names that were already easy to understand, but I know you’ve seen obscure ones before. And I know you will again, so when you do, remember this little trick.
This works well in combination with exporting the result to a CSV file. It makes the file instantly ready to share with your boss or co-workers. My article, Saving A PostgreSQL Query Result As A CSV, explains how to do that.
Wrap Up
I hope this short guide was useful in showing you how to rename the columns in your SQL query results exactly how you want them. It may seem small and inconsequential, but I guarantee your non-technical boss won’t think so if you take the time to rename each column in plain English.