PostgreSQL SELECT AS SQL Clause
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.
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
psqlcommand-line interface by typing the
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:
sudo su - postgres
You’ll be prompted for a password. After you enter it, use the command:
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:
CREATE DATABASE database_name;
You can supply your own name in place of ‘database_name’.
\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.
\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:
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:
INSERT INTO table1(col_string) VALUES('Lily'), ('Ash'), ('Levy'), ('Gajeel'), ('Carla');
This command should return
INSERT 0 5 as a response.
PostgreSQL ‘SELECT AS’
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:
orkb=# SELECT col_string AS name FROM table1;
In this query, we assign the alias
name to the column
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:
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:
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;
user_id | name | address
1 | Lily | united states
2 | Ash | australia
3 | Levy | japan
4 | Gajeel | philippines
5 | Carla | thailand
NOTE: We use the
WHERE clause in our query to match
col_id values from
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