PostgreSQL: Indexes

This PostgreSQL tutorial explains how to create, drop, and rename indexes in PostgreSQL with syntax and examples.

What is an Index in PostgreSQL?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

Create an Index

You can create an index in PostgreSQL using the CREATE INDEX statement.

Syntax

The syntax to create an index using the CREATE INDEX statement in PostgreSQL is:

CREATE [UNIQUE] INDEX [CONCURRENTLY] index_name
  [ USING BTREE | HASH | GIST | SPGIST | GIN ]
  ON table_name
    (index_col1 [ASC | DESC], 
     index_col2 [ASC | DESC],
     ...
     index_col_n [ASC | DESC]);

UNIQUE

Optional. The UNIQUE modifier indicates that the combination of values in the indexed columns must be unique.

CONCURRENTLY

Optional. When the index is created, it will not lock the table. By default, the table is locked while the index is being created.

index_name

The name to assign to the index.

table_name

The name of the table in which to create the index.

index_col1, index_col2, ... index_col_n

The columns to use in the index.

ASC

Optional. The index is sorted in ascending order for that column.

DESC

Optional. The index is sorted in descending order for that column.

Example

Let's look at an example of how to create an index in PostgreSQL.

For example:

CREATE INDEX order_details_idx
  ON order_details (order_date);

In this example, the CREATE INDEX statement would create an index called order_details_idx that consists of the order_date field.

Unique Index

To create a unique index on a table, you need to specify the UNIQUE keyword when creating the index.

For example:

CREATE UNIQUE INDEX order_details_idx
  ON order_details (order_date, note);

In this example, we would create a unique index on order_details table that consists of the order_date and note fields so that the combination of these fields must always contain a unique value with no duplicates. This is a great way to enforce integrity within your database if you require unique values in columns that are not part of your primary key.

Drop an Index

You can drop an index in PostgreSQL using the DROP INDEX statement.

Syntax

The syntax to drop an index using the DROP INDEX statement in PostgreSQL is:

DROP INDEX [CONCURRENTLY] [IF EXISTS] index_name
  [ CASCADE | RESTRICT ];

CONCURRENTLY

Optional. When the index is dropped, it will not lock the table. By default, the table is locked while the index is being removed from the table.

IF EXISTS

Optional. If specified, the DROP INDEX statement will not raise an error if the index does not exist.

index_name

The name of the index to drop.

CASCADE

Optional. All objects that depend on this index are also dropped.

RESTRICT

Optional. Index will not be dropped if there are objects that depend on the index.

Example

Let's look at an example of how to drop an index in PostgreSQL.

For example:

DROP INDEX order_details_idx;

In this example, we've dropped an index called websites_idx from the websites table.

Rename an Index

You can rename an index in PostgreSQL using the ALTER INDEX statement.

Syntax

The syntax to rename an index using the ALTER INDEX statement is:

ALTER INDEX [IF EXISTS] index_name,
  RENAME TO new_index_name;

IF EXISTS

Optional. If specified, the ALTER INDEX statement will not raise an error if the index does not exist.

index_name

The name of the index that you wish to rename.

new_index_name

The new name for the index.

Example

Let's look at an example of how to rename an index in PostgreSQL.

For example:

ALTER INDEX order_details_idx
  RENAME TO od_new_index;

In this example, we've renamed the index called order_details_idx to od_new_index.

 

PostgreSQL: Unique Constraints

This PostgreSQL tutorial explains how to create, add, and drop unique constraints in PostgreSQL with syntax and examples.

What is a unique constraint in PostgreSQL?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Create unique Contraint - Using a CREATE TABLE statement

The syntax for creating a unique constraint using a CREATE TABLE statement in PostgreSQL is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the unique constraint.

uc_col1, uc_col2, ... uc_col_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in PostgreSQL using the CREATE TABLE statement.

CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_unique UNIQUE (order_id)
);

In this example, we've created a unique constraint on the order_details table called order_unique. It consists of only one field - the order_id field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_date_unique UNIQUE (order_id, order_date)
);

Create unique contraint - Using an ALTER TABLE statement

The syntax for creating a unique constraint using an ALTER TABLE statement in PostgreSQL is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

table_name

The name of the table to modify. This is the table that you wish to add a unique constraint to.

constraint_name

The name of the unique constraint.

column1, column2, ... column_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in PostgreSQL using the ALTER TABLE statement.

ALTER TABLE order_details
ADD CONSTRAINT order_unique UNIQUE (order_id);

In this example, we've created a unique constraint on the existing order_details table called order_unique. It consists of the field called order_id.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE order_details
ADD CONSTRAINT order_date_unique UNIQUE (order_id, order_date);

Drop Unique Constraint

The syntax for dropping a unique constraint in PostgreSQL is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

table_name

The name of the table to modify. This is the table that you wish to remove the unique constraint from.

constraint_name

The name of the unique constraint to remove.

Example

Let's look at an example of how to remove a unique constraint from a table in PostgreSQL.

ALTER TABLE order_details
DROP CONSTRAINT order_unique;

In this example, we're dropping a unique constraint on the order_details table called order_unique.

 

PostgreSQL: Primary Keys

This PostgreSQL tutorial explains how to create, drop, disable, and enable a primary key in PostgreSQL with syntax and examples.

What is a primary key in PostgreSQL?

In PostgreSQL, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a NULL value. A table can have only one primary key.

Note

• In PostgreSQL, a primary key is created using either a CREATE TABLE statement or an ALTER TABLE statement.
• You use the ALTER TABLE statement in PostgreSQL to add or drop a primary key.

Create Primary Key - Using CREATE TABLE statement

You can create a primary key in PostgreSQL with the CREATE TABLE statement.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in PostgreSQL is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name
   PRIMARY KEY (index_col1, index_col2, ... index_col_n)
);

OR

CREATE TABLE table_name
(
  column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
  column2 datatype [ NULL | NOT NULL ],
  ...
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table. See the PostgreSQL CREATE TABLE statement for more detailed CREATE TABLE syntax as this is an over-simplification to demonstrate how to create a Primary Key.

constraint_name

The name of the primary key.

index_col1, index_col2, ... index_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in PostgreSQL.

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

Or you could also create the primary key on the order_details table using the following syntax:

CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200)
);

In these two examples, we've created a primary key on the order_details table called order_details_pk. It consists of only one column - the order_detail_id column.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE order_details
( order_date date NOT NULL,
  customer_id integer NOT NULL,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_details_pk PRIMARY KEY (order_date, customer_id)
);

This example creates a primary key called order_details_pk that is made up of a combination of the order_date and customer_id columns. So each combination of order_date and customer_id must be unique in the order_details table.

Create Primary Key - Using ALTER TABLE statement

You can create a primary key in PostgreSQL with the ALTER TABLE statement.

Syntax

The syntax to create a primary key using the ALTER TABLE statement in PostgreSQL is:

ALTER TABLE table_name
  ADD CONSTRAINT [ constraint_name ]
    PRIMARY KEY (index_col1, index_col2, ... index_col_n)

table_name

The name of the table to modify.

constraint_name

The name of the primary key.

index_col1, index_col2, ... index_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the ALTER TABLE statement in PostgreSQL.

ALTER TABLE order_details
  ADD CONSTRAINT order_details_pk 
    PRIMARY KEY (order_detail_id);

In this example, we've created a primary key on the existing order_details table called order_details_pk. It consists of the order_detail_id column.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE order_details
  ADD CONSTRAINT order_details_pk
    PRIMARY KEY (order_date, customer_id);

This example we've created a primary key called contacts_pk that is made up of a combination of the last_name and first_name columns.

Drop Primary Key

You can drop a primary key in PostgreSQL using the ALTER TABLE statement.

Syntax

The syntax to drop a primary key in PostgreSQL is:

ALTER TABLE table_name
  DROP CONSTRAINT constraint_name;

table_name

The name of the table to modify.

constraint_name

The name of the primary key that you wish to drop.

Example

Let's look at an example of how to drop a primary key using the ALTER TABLE statement in PostgreSQL.

ALTER TABLE order_details
  DROP CONSTRAINT order_details_pk;

In this example, we've dropped the primary key on the contacts table. We do not need to specify the name of the primary key as there can only be one on a table.

 

PostgreSQL: Functions - Listed Alphabetically

For easy reference, we have provided a list of all PostgreSQL functions. The list of PostgreSQL functions is sorted alphabetically based on the function name.

These functions can be used in SQL statements or queries in PostgreSQL. Or, they can be used within the programming environment provided by the PostgreSQL database, such as stored procedures, functions, triggers, etc.

Below is the list of PostgreSQL functions, sorted by alphabetically by function name.

absageavgbtrimceilceilingchar_lengthcharacter_lengthconcat with ||countcurrent_datecurrent_timecurrent_timestampdate_partdivexpextract

floorinitcaplengthlocaltimelocaltimestamplowerlpadltrimmaxminmodnowpositionpowerrandomrepeatreplace

roundrpadrtrimsetseedsignsqrtstrpossubstringsumto_charto_dateto_numberto_timestamptranslatetrimtruncupper

 

PostgreSQL: SELECT Statement

This PostgreSQL tutorial explains how to use the PostgreSQL SELECT statement with syntax and examples.

Description

The PostgreSQL SELECT statement is used to retrieve records from one or more tables in PostgreSQL.

Syntax

In its simplest form, the syntax for the SELECT statement in PostgreSQL is:

SELECT expressions
FROM tables
[WHERE conditions];

However, the full syntax for the PostgreSQL SELECT statement is:

SELECT [ ALL | DISTINCT | DISTINCT ON (distinct_expressions) ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS FIRST | NULLS LAST ]]
[LIMIT [ number_rows | ALL]
[OFFSET offset_value [ ROW | ROWS ]]
[FETCH { FIRST | NEXT } [ fetch_rows ] { ROW | ROWS } ONLY]
[FOR { UPDATE | SHARE } OF table [ NOWAIT ]];

Parameters or Arguments

ALL

Optional. Returns all matching rows.

DISTINCT

Optional. Removes duplicates from the result set. Learn more about DISTINCT clause

DISTINCT ON

Optional. Removes duplicates based on the distinct_expressions. Learn more about the DISTINCT ON clause.

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

GROUP BY expressions

Optional. It collects data across multiple records and groups the results by one or more columns.

HAVING condtion

Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE.

ORDER BY expression

Optional. It is used to sort the records in your result set.

LIMIT

Optional. If LIMIT is provided, it controls the maximum number of records to retrieve. At most, the number of records specified by number_rows will be returned in the result set. The first row returned by LIMIT will be determined by offset_value.

FETCH

Optional. If FETCH is provided, it controls the maximum number of records to retrieve. At most, the number of records specified by fetch_rows will be returned in the result set. The first row returned by FETCH will be determined by offset_value.

FOR UPDATE

Optional. Records affected by the query are write-locked until the transaction has completed

FOR SHARE

Optional. Records affected by the query can be used by other transactions but can not be updated or deleted by those other transactions

Example - Select all fields from one table

Let's look at how to use a PostgreSQL SELECT query to select all fields from a table.

SELECT *
FROM categories
WHERE category_id >= 2500
ORDER BY category_id ASC;

In this PostgreSQL SELECT statement example, we've used * to signify that we wish to select all fields from the categories table where the category_id is greater than or equal to 2500. The result set is sorted by category_id in ascending order.

Example - Select individual fields from one table

You can also use the PostgreSQL SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT category_id, category_name, comments
FROM categories
WHERE category_name = 'Hardware'
ORDER BY category_name ASC, comments DESC;

This PostgreSQL SELECT example would return only the category_id, category_name, and comments fields from the categories table where the category_name is 'Hardware'. The results are sorted by category_name in ascending order and then comments in descending order.

Example - Select fields from multiple tables

You can also use the PostgreSQL SELECT statement to retrieve fields from multiple tables.

SELECT products.product_name, categories.category_name
FROM categories
INNER JOIN products
ON categories.category_id = products.category_id
ORDER BY product_name;

This PostgreSQL SELECT example joins two tables together to gives us a result set that displays the product_name and category_name fields where the category_id value matches in both the categories and products table. The results are sorted by product_name in ascending order.