PostgreSQL SELECT AS SQL Clause

Introduction

When you’re querying for data in a PostgreSQL table, you may not want to reference certain column names or table names in your query. There can be multiple reasons for this– assigning a temporary name to certain columns can make a query easier to read, and using aliases can be particularly helpful when there’s a JOIN or an aggregate function in your query. In PostgreSQL, we can use the SELECT AS clause to assign an alias in a SQL query. This article will provide several examples of how to use the PostgreSQL SELECT AS SQL clause to query records in a Postgres table.

Prerequisites

There are a few key prerequisites that must be in place before we can proceed with this tutorial:

  • PostgreSQL must be installed and working properly. You can verify that PostgreSQL is installed on your machine by using the command systemctl status postgresql. This command will return the status of the PostgreSQL service on your device.

  • You’ll also need to have psql running on your machine. You can check the version of the interactive psql command-line interface by typing the psql -V.

Access psql

We’ll need to enter the psql interface in order to use the SELECT AS statement in a query, so our first step will be to connect and access the interactive PostgreSQL command-line interface. To do this, we use the following command:

1
sudo su - postgres

You’ll be prompted for a password. After you enter it, use the command:

1
psql

This will grant you access to the superuser postgres and create the database with the table.

Create a PostgreSQL database

If you’re just getting started with PostgreSQL, it’s important to understand exactly what certain key terms mean. For example, a database can be defined as a system that allows users to store data and manipulate it in various ways. You can maintain data structures, manage records and retrieve information from tables that exist inside the database.

To create a database in PostgreSQL, use the command shown below:

1
CREATE DATABASE database_name;

You can supply your own name in place of ‘database_name’.

The command \c followed by the database name will allow you to enter the database. You can then create a table, insert data and perform other operations.

Use the \l command to display a list of all the databases that exist in your PostgreSQL implementation.

Create a PostgreSQL table

Now that we’ve created a database, the next step is to set up a table. To create a table, use the command shown below:

1
2
CREATE TABLE table1
(col_id SERIAL PRIMARY KEY, col_string VARCHAR (100));

Insert value in PostgreSQL table

Let’s insert some values into the table1 table. We’ll use the following command:

1
INSERT INTO table1(col_string) VALUES('Lily'), ('Ash'), ('Levy'), ('Gajeel'), ('Carla');

This command should return INSERT 0 5 as a response.

PostgreSQL ‘SELECT AS’

The PostgreSQL SELECT AS clause allows you to assign an alias, or temporary name, to either a column or a table in a query.

Select alias from column

The following example creates an alias for a column name using AS. The alias is displayed when the query returns the table’s records:

1
2
3
4
5
6
7
8
9
orkb=# SELECT col_string AS name FROM table1;
  name  
--------
 Lily
 Ash
 Levy
 Gajeel
 Carla
(5 ROWS)

In this query, we assign the alias name to the column col_string in table1. This alias is shown instead of the column’s actual name in the results.

Select alias from tables

We can also assign an alias to a table in PostgreSQL. Let’s look at an example:

1
SELECT col_id, col_string FROM table1 AS users;

NOTE: Note that this query will return the same results as the query in the previous example. The difference is that we assigned the alias to the table instead of the column.

The next example will show why it can be helpful to use the PostgreSQL SELECT AS clause to assign aliases. Let’s imagine that we have another table with some of the same column names as our first table. Using aliases can make the results a lot easier to understand:

1
SELECT users.col_id AS user_id, users.col_string AS name, info.col_string AS address FROM table1 AS users, table2 AS info WHERE users.col_id = info.col_id;
1
2
3
4
5
6
7
8
 user_id |  name  |    address    
---------+--------+---------------
       1 | Lily   | united states
       2 | Ash    | australia
       3 | Levy   | japan
       4 | Gajeel | philippines
       5 | Carla  | thailand
(5 ROWS)

NOTE: We use the WHERE clause in our query to match col_id values from table1 and table2.

Screenshot of PostgreSQL SELECT AS clause example in the psql command line interface

Conclusion

When you need to assign a temporary name to either a column or a table in your SQL query, the PostgreSQL SELECT AS clause can help you get the job done. In this article, we learned how to use the SELECT AS SQL clause to utilize aliases in our queries. With the instructions and examples provided in this tutorial, you’ll be ready to incorporate this helpful clause in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.