PostgreSQL - SELECT FROM Table

In this tutorial we will learn to select data from tables in PostgreSQL.

For this tutorial we will use the employee table and the comments table, the ones we created in the CREATE Table tutorial.

Select all columns of a table

To select all the columns of a table we use the SELECT * FROM tableName query.

In the following example we are going to select all the columns from the employee table.

SELECT * from employee;

We will get result like the following.

SELECT * FROM employee;

 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+---------------------
 e01        | Yusuf     | Shakeel  |     0 | 2021-01-01 01:01:01 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e02        | John      | Doe      |     8 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     6 | 2018-01-01 01:01:04 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(3 rows)

Similarly, we can select all the columns from the comments table by running the following query.

SELECT * FROM comments;

Result:

SELECT * FROM comments;

 commentId | commentBody |   lastModifiedAt    |      createdAt      | employeeId
-----------+-------------+---------------------+---------------------+------------
         3 | Awesome     | 2021-01-01 02:03:04 | 2021-01-01 02:03:04 | e03
(1 row)

Selecting specific columns of a table

To retrieve specific columns of a given table we specify the name of the columns.

In the following example we are going to fetch the following columns employeeId, firstName and lastName from the employee table.

SELECT "employeeId", "firstName", "lastName" FROM employee;

 employeeId | firstName | lastName
------------+-----------+----------
 e01        | Yusuf     | Shakeel
 e02        | John      | Doe
 e03        | Jane      | Doe
(3 rows)

Similarly, in the following example we are selecting commentId and commentBody from the comments table.

SELECT "commentId", "commentBody" FROM comments;

 commentId | commentBody
-----------+-------------
         3 | Awesome
(1 row)

Giving new name to the selected columns

We use AS in the select query to give a new name to the selected columns.

In the following example we are going to give the employeeId column a new name id.

SELECT "employeeId" AS id, "firstName", "lastName" FROM employee;

 id  | firstName | lastName
-----+-----------+----------
 e01 | Yusuf     | Shakeel
 e02 | John      | Doe
 e03 | Jane      | Doe
(3 rows)

Concat two columns in SELECT query

In the following example we are going to concatenate two columns firstName and lastName and name it employeeName.

SELECT "employeeId", CONCAT("firstName", "lastName") AS "employeeName" FROM employee;

 employeeId | employeeName
------------+--------------
 e01        | YusufShakeel
 e02        | JohnDoe
 e03        | JaneDoe
(3 rows)

We can see that in the above result there is no space between the firstName and lastName. So we will modify our select query and include the space between the firstName and lastName.

SELECT "employeeId", CONCAT("firstName", ' ', "lastName") AS "employeeName" FROM employee;

 employeeId | employeeName
------------+---------------
 e01        | Yusuf Shakeel
 e02        | John Doe
 e03        | Jane Doe
(3 rows)