PostgreSQL Select Statement

The SELECT statement is one of the most popular and useful clauses in relational databases. It allows you to fetch specific information from the SQL language while preserving a simple syntax.

However, there is more to SELECT than meets the eye. In this tutorial, we will walk you through the fundamentals of working with the SELECT statement in PostgrSQL.

SELECT Statement

By itself, the SELECT statement itself is not very useful. However, it allows you to combine it with other clauses to perform complex tasks.

The following are some of the clauses you can use with the SELECT statement.

1. DISTINCT - The distinct operator allows you to select distinct rows from a specific table.
2. ORDER BY - The order by clause allows you to sort the rows of a specific result.
3. WHERE - The where clause allows you to filter rows based on specific boolean conditions.
4. LIMIT - The limit clause allows you to select a subset of rows from a resulting set.
5. GROUP BY - This clause allows you to group rows into various groups using specific conditions.
6. HAVING - The having clause allows you to filter groups that match a specific condition.
7. INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN' - allows to perform join operations between tables of the same database.
8. UNION - Performs set operations.

For this article however, we will focus on the fundamentals of the SELECT statement. For more information, you can search our site on other statements such as LIMIT, GROUP BY, WHERE, etc.

SELECT Statement Syntax

The SELECT statement is relatively simple. The syntax for select values from a table using the SELECT statement is as shown below:

SELECT column_list FROM table_name;

We can break down the statement syntax as shown:

• The first is the SELECT keyword followed by the list of columns we wish to retrieve from the table. Here, you can pass a list of columns (seperated by commas) or use wildcard characters such as asterisk (*) to select all columns from a table.
• The second is the FROM keyword which allows us to specify the name of the table from which we wish to select the requested values.

Keep in mind that you do not have to select data from a table. You pass other sources such as a function, views, result sets, etc.

Select Statement - Practical Example

To better understand how to use the SELECT statement, let us use a practical example to illustrate.

Let us start by creating a sample database and table as shown in the code below:

-- CREATE DATABASE
CREATE DATABASE "inventory";
-- SWITCH DATABASE
\c "inventory"
-- CREATE TABLE SCHEMA
CREATE TABLE products(
	product_id SERIAL PRIMARY KEY,
	product_name VARCHAR NOT NULL,
	price INT,
	quantity INT,
	instock BOOL
);

Once we have the sample database and table schema created, we can insert sample data as shown in the code below:

INSERT INTO products(product_name, price, quantity, instock) VALUES
('Apple AirTag', 30, 100, TRUE),
('Fitbit Smart Watch', 170, 30, TRUE),
('Laptop Stand', 60, 50, TRUE),
('Bluetooth Speaker', 99, 40, FALSE),
('Smart Glasses', 400, 30, TRUE)

The statement above adds some sample products into the table.

To select all the product names from the product table, we can run the command:

SELECT product_name FROM products;

The statement above should return an output as shown:

We can see from the output above, that the SELECT statement returns all the rows from the product_name column. You can have multiple select statements by seperating each statement with a semi-colon.

Select Statement - Multiple Columns

We can also select multiple columns by seperating them with commas. For example, to get the product_id, product_name and the price of the products, we can run:

SELECT
	product_id,
	product_name,
	price
FROM
	products;

The code above should return a result as shown:

Select Statement - All Columns

To select all the columns from a specific table, we can use an asterisk which denotes all columns. An exmaple is as shown:

SELECT
	*
FROM
	products;

This should include all the columns in the specified table as shown:

Congratulations, you have successfully learned the basics of working with SELECT statement in PostgreSQL.

Stay tuned as we upload more tutorials.

The full source code used in this article is as provided below:

CREATE TABLE products (
	product_id SERIAL PRIMARY KEY,
	product_name VARCHAR NOT NULL,
	price INT,
	quantity INT,
	instock BOOL
);
INSERT INTO products(product_name, price, quantity, instock) VALUES
('Apple AirTag', 30, 100, TRUE),
('Fitbit Smart Watch', 170, 30, TRUE),
('Laptop Stand', 60, 50, TRUE),
('Bluetooth Speaker', 99, 40, FALSE),
('Smart Glasses', 400, 30, TRUE);

SELECT product_name FROM products;
SELECT
	product_id,
	product_name,
	price
FROM
	products;
SELECT
	*
FROM
	products;

See you in the next one!!

If you enjoy our content, please consider buying us a coffee to support our work: