PostgreSQL SELECT Query Basics – Tutorial

In this post we will discuss about the usage of SELECT query in PostgreSQL. SELECT Query form the very basic of the Structured Query Language(SQL) standard. Over the years PostgreSQL has added a handful of advanced features to the SELECT Query. Though, SELECT in the purest form works perfectly in PostgreSQL. If you want to learn the advanced versions of SELECT Query, have a look at the links at the bottom.

 “Be not afraid of growing slowly, be afraid only of standing still.”
-Chinese Proverb

Consider the following table:

CREATE TABLE products
(
id integer NOT NULL,
name character varying,
cost double precision,
manufacturer character varying,
warranty character varying,
category character varying,
CONSTRAINT products_pkey PRIMARY KEY (id)
)

Quick Navigation

1. The use of *
2. AND Conditions
3. ANDs mixed with ORs
4. Use of DISTINCT Keyword

Following query will return all records with all columns:

SELECT * FROM products ;

The same result can be achieved by:

SELECT id, name, cost, manufacturer, warranty, category
FROM products;

SELECT * is not desired, unless you want to return all columns. Using * will degrade performance, both in terms of DBMS and network usage.
People often use * to count rows, which is considered a bad practice.
So always do this:
SELECT count(1) FROM products;
Instead of this:
SELECT count(*) FROM products;

Now, lets write a query to return all “Men’s Clothing” priced less than 35 with their name, id and cost . 

SELECT id, name, cost FROM products
WHERE category = 'Men''s Clothing' AND cost<35;

Notice the redundant single quote in Men‘‘s Clothing! It is one of the way to escape a single quote in PostgreSQL.

The result look little ambiguous as it does not mention the denomination currency.

Let’s add a $ sign to the currency using the concatenation operator.

SELECT id, name, '$' || cost AS price FROM products
WHERE category = 'Men''s Clothing' AND cost<35;

Notice that we have used an alias name “price” for the third column. Though not mandatory, it is a good practice.

This time, the buyer has a little tricky condition

1. Return all products with 1 or 2 Years warranty.
2. OR
3. Any Puma product with warranty of any period of time.

SELECT id, name, cost, manufacturer, warranty, category FROM products
WHERE (warranty = '1 Year' OR warranty = '2 Years') OR
(manufacturer = 'Puma' AND warranty IS NOT NULL) ORDER BY id;

Omitting the parenthesis in where condition will yield an incorrect result.

Notice that we have used IS NOT NULL to filter NULL values. This is because of the “three valued logic” used in SQL.
Natural tendency to use  warranty != NULL is a guaranteed way to get a wrong result set!

Notice the usage of ORDER BY clause.

To do: Rewrite this query with IN clause instead of redundant ORs.

Query to return Unique Names

This time we need to get the set of all available warranty periods from the database, may be it can be used to build the faceted search.

We use the DISTINCT keyword to return the unique(non repeating) rows from a column or a combination of columns.

SELECT DISTINCT warranty FROM products ;

Beware of the NULL value, to avoid NULL value use the IS NOT NULL keyword in the WHERE part.

If multiple columns are referenced in the SELECT part, the DISTINCT is applied to the entire row rather than any individual cells.

For example this query will return 6 rows.

SELECT DISTINCT warranty, name FROM products ;