You can retrieve data from the table using a SELECT statement.

Syntax:

SELECT [column names] FROM [table_name]

Here,

  • column names: Name of the columns whose value you want to retrieve
  • FROM: The FROM clause defines one or more source tables for the SELECT.
  • table_name: The name of an existing table that you want to query

PostgreSQL Select Statement in SQL Shell

Step 1) We have a table "tutorials" with 2 columns "id" and "tutorial_name". Lets query it. Use the following query to list data in the table

SELECT id,tutorial_name FROM tutorials;

NOTE: Use the command \c to connect to the database that contains the table you want to query. In our case, we are connected to database guru99.

Step 2) If you want to view all the columns in a particular table, we can use the asterisk (*) wildcard character. This means it checks every possibility and, as a result, It will return every column.

SELECT *  FROM tutorials;

It displays all the records of the tutorials table.

Step 3) You can use the ORDER clause to sort data in a table based on a particular column. The ORDER clause organizes data in A to Z order.

SELECT *  FROM tutorials ORDER BY id;

You can sort from Z to A using "DESC" after the "ORDER BY" statement.

SELECT *  FROM tutorials ORDER BY id DESC; 

Step 4) The DISTINCT clause can be used to remove duplicate rows from the result. It keeps one row for each group of duplicates.

Syntax: 
SELECT DISTINCT column_1 FROM table_name;

Lets query Distinct id values from our table tutorials

SELECT DISTINCT(id) FROM tutorials; 

Step 5) You can use the LIMIT clause to restrict the number of records returned by the SELECT query

SELECT *  FROM tutorials LIMIT 4; 

PostgreSQL Select Statement in PgAdmin

Step 1) In the Object Tree

  1. Right Click on the Table
  2. Select Scripts
  3. Click on SELECT SCRIPT

Step 2) In the Panel on the right,

  1. Edit the SELECT query if required
  2. Click the Lightning Icon
  3. Observe the Output

Cheat Sheet

SELECT [column names] FROM [table_name] [clause] 

Here, are the various parameters

  • column names: Name of the columns whose value you want to retrieve
  • FROM: The FROM clause defines one or more source tables for the SELECT.
  • table_name: The name of an existing table that you want to query

Various clauses are:

*

Fetches records for all the rows in the table

DISTINCT

This option helps you to removes duplicates from the result.

ORDER BY

Sort Rows based on a column

Default Sort Order is Ascending.

Use Keyword DESC to sort in Descending order

LIMIT

It restricts the number of records returned by the query.

 

Guru99 is Sponsored by AQUAFOLD
Aquafold

Aqua Data Studio is proud to sponsor Guru99's informative website for database professionals. Take your productivity to new heights with query tools, visual analytics, and ER diagramming for multiple platforms.