PostgreSQL Tutorial

PostgreSQL Tutorial

PostgreSQL, also known as Postgres, is one of the most commonly used open-source Relational Database Management System (RDBMS). Postgres aims at SQL Standard complicity. It is an open-source successor to the UC Berkeley Ingres research project that started in the early 1970s. Postgres strives to be ACID (stands for Atomicity, Consistency, Isolation, Durability) compliant. In this PostgreSQL Tutorial, you will learn about the most important parts (from the practical standpoint) of Postgres such as databases, tables, data types, commands & queries, roles, schemas, views, and indexing.

1.

Intro

1.1.

Inception

Currently Postgres is an open-source software developed at the University of California at Berkeley. The original POSTGRES implementation begun in 1986 and was headed by Professor Michael Stonebraker and sponsored by several military and non-military organizations. The project pivoted to an open-source software in 1994 under the name Postgres95. Then the name was changed to the current one i.e. PostgreSQL with the starting version of 6.0. In present times, PostgreSQL is often referred to by its nickname Postgres.

1.2.

RDBMS

PostgreSQL (aka Postgres) is a relational database management system (RDBMS).

1.3.

ACID

Postgres transactions observe ACID properties aimed at ensuring data validity despite adverse circumstances such as errors or power failures. Atomicity ensures that a sequence of DB operations constituting a single logical operation i.e. a transaction succeeds or fails completely. Consistency ensures that a transaction can only transform the DB from one valid state to another. Isolation ensures that the DB state is the same after some transactions irrespective of whether they were executed concurrently or sequentially. Durability means that the effects of successful transactions are preserved even in cases of system failures.

1.4.

Referential Integrity

Referential integrity ensures that all references built within a DB in Postgres with primary and foreign keys are valid at all times. In other words Postgres does not allow for a non-NULL value to exist in a foreign key column that has no actual reference in the DB.

1.5.

Client / Server Model

Postgres uses the client / server model. The model consists of two parts:

  • the server process called postgres - the core PostgreSQL program that accepts one or many simultaneous connections from a client process or processes and directly manages and retrieves databases objects, and

  • one or many client processes - a program or programs that make connections to the server process with the purpose of indirect management and retrieval of database objects - such a client app can be for example a Python / Rails / NodeJS application or a command line iterface such as psql.

1.6.

psql: Command Line Interface

psql is a primary command-line interface for Postgres allowing for users and databases management and entering SQL commands and queries.

In addition to executing SQL commands psql features many its own internal commands which start with \.

For example to quit psql use the \q command, to get help the \h command and to list other internal commands use the \? command.

1.7.

Postgres Version

To verify the Postgres version currently installed in your system use the postgres --version shell command or the SELECT version(); SQL command.

2.

Tokens

2.1.

SQL Command

A Postgres SQL command is a programmer's means to communicate to Postgres what they want the Postgres database to effect.

An SQL command is built using tokens terminated with a semicolon ;.

SELECT first_name FROM users WHERE id = 42;

A token can be:

  • a key word (SELECT, FROM, WHERE),

  • an identifier (first_name, users, id),

  • a constant, aka literal (42),

  • an operator (=), or

  • a special character (;).

2.2.

Key Words

In SQL a key word is a word with fixed meaning used for denoting fixed command parts such as SELECT, INSERT, ANALYZE, DISTINCT , INTO, JOIN, SET, IS, NULL, NOT, AND.

Key words cannot be quoted and are case insensitive however a convention is to use uppercase.

2.3.

Identifiers

Identifiers are names dedicated to denoting database objects such as:

  • roles,

  • databases,

  • tables, and

  • columns.

Generally, an identifier should not coincide with an SQL key word such as SELECT or UPDATE but Postgres does not enforce it.

There are two types of identifiers in Postgres:

  • unquoted identifiers, and

  • quoted identifiers.

Unquoted identifiers must begin with:

  • a letter, or

  • an underscore (_).

Subsequent characters of an unquoted identifier can include letters, digits and underscores.

In Postgres unquoted identifiers are case insensitive and are always folded to lower case. This is not congruent with the SQL Standard which directs to fold to uppercase.

Quoted identifiers (aka delimited identifiers) are identifiers enclosed in double quotes ("...") and are case sensitive. A quoted word in an SQL command is always an identifier not a key word. Quoted identifiers can include almost any characters even spaces.

2.4.

Constants

In Postgres there are the following types of implicitly typed constants:

  • strings - a sequence of characters most often enclosed in single quotes or using other manners like E (escape string constants), U& (unicode escape string constants), $$ (non SQL standard dollar quoted escape string constants) when "escape" capabilities are needed,

  • bit strings - binary strings denoted with binary notation (e.g. B'101010') or hexadecimal notation (e.g. X'2A'),

  • numbers - e.g. 42, -42, 4.2, 4., .2, 4e2 (numeric constants are often coerced to the context applicable type).

In addition, Postgres allows for user specifying explicitly typed constants.

2.5.

Operators

An operator is a set of specific non-alphanumeric characters which often denotes an action to be performed between some constants.

The notable operators are:

  • =, >, <, >=, <= (comparisons), and

  • +, -, *, /, %, ^ (mathematical operations).

2.6.

Special Characters

Special characters are non-alphanumeric characters that denote a special meaning within a Postgres SQL command, such as for example:

  • . - specifying database objects within other database objects (e.g. a column within a table - users.first_name),

  • ; - terminating an SQL command,

  • () - grouping and precedence, and

  • * - all columns.

3.

Roles

3.1.

Definition

A role in Postgres is one of the Postgres identifiers and a concept dedicated to group and manage database object privileges. Alternatively, it can be stated that role is used to denote:

  • a user,

  • a group of users, or

  • a container for privileges to be used or inherited by other roles.

Roles and role names (like some other objects) are global across a database cluster.

Postgres diverges from the SQL standard when it comes to roles. In the SQL standard the concept of a user is distinct from the concept of a role.

3.2.

Predefined Role

An initialized Postgres installation always comes with a one predefined role with a superuser attribute. Most probably the name of the role will be the same as the operating initializing system user. This role can be used to connect to a database cluster with a psql -U [role name] command and create additional roles.

3.3.

Database Connection

A connection to a database is always associated with a role and is governed by the scope of privileges of that role.

Many Postgres command line commands, including psql, when no role is expressly indicated use the name of the current operating system user. This works similarly for the default database name. Therefore, when the current operating system user is johndoe simply entering psql on the command line will try to establish a connection to the database johndoe using the role johndoe. If no such role or database exist a fatal error is thrown.

$ psql

To expressly indicate the role to be used when establishing the connection use -U option.

$ psql -U otherjohndoe

To expressly indicate the database to which the connection should be established use -d option.

$ psql -d otherdatabase

-U and -d options can be combined.

$ psql -U otherjohndoe -d otherdatabase

The above command line command will try to establish a connection using the role otherjohndoe to the database otherdatabase.

Once connected it is possible to change the current's connection role using the SET ROLE command.

SET ROLE role_name;
3.4.

Listing Roles

To list roles existing in the current Postgres database cluster use \du meta-command:

\du

The \du returns the list of all database cluster roles with indication of their names, attributes and memberships.

3.5.

Creating & Dropping Roles

To create a role use CREATE ROLE command.

CREATE ROLE admin;

To drop a role use DROP ROLE command.

DROP ROLE admin;

A role cannot be dropped when there are database objects owned by it. To reassign or drop database objects globally within a given database use REASSIGN OWNED or DROP OWNED respectively.

3.6.

Role Attributes

Each role can have different attributes - granted to it at its creation or later - defining the scope of privileges the role has and/or its login behavior. The attributes include among others:

  • superuser - roles with SUPERUSER attribute ignore all permission checks,

  • login - only roles with LOGIN attribute can establish initial connections,

  • password - only roles with PASSWORD attribute require passwords for establishing connections,

  • database creation - only roles with CREATEDB attribute can create/drop databases,

  • role creation - only roles with CREATEROLE attribute can create/drop roles and grant/revoke role memberships.

To create a superuser role use the following command:

CREATE ROLE admin SUPERUSER;

To create a role with login and password attributes that can create databases and roles use the following command:

CREATE ROLE admin LOGIN PASSWORD 'foobar' CREATEDB CREATEROLE;

To alter an existing role use ALTER ROLE command. For example, to a take database creation privilege from a role use the command NOCREATEDB.

ALTER ROLE admin NOCREATEDB;
3.7.

Ownership & Privileges

A role that creates an object becomes its owner.

CREATE DATABASE bike_shop;

An ownership can be transferred to another role.

ALTER DATABASE bike_shop OWNER TO jimmy;

Only a role owning an object can take effective actions on it unless it inherits respective privileges.

There are many kind of privileges. Among them are:

  • INSERT,

  • SELECT,

  • UPDATE,

  • DELETE, and

  • CONNECT.

To grant privileges on a database object use GRANT ... ON ... TO command.

GRANT INSERT ON users TO jimmy;
GRANT ALL ON users TO jimmy;

To revoke privileges use REVOKE ... ON ... FROM command.

3.8.

Memberships

Each role can be granted a membership in another role which allows it to use privileges of that another role.

Some roles can be created with the purpose of grouping privileges. Such roles can sometimes be referred to as group roles. Being granted a membership in a group role allows for sharing its privileges.

A role can use privileges of the role to which it is a member in two ways:

  • it can set (using the SET ROLE command) the current's connection role to the role to which it is a member and use the privileges directly, or

  • use the privileges itself provided it has the INHERIT attribute.

Member roles can be set to INHERIT or NOINHERIT. Default in Postgres is INHERIT.

SUPERUSER, LOGIN, CREATEDB and CREATEROLE attributes are not inherited.

To grant a membership in a given role use GRANT [group role] TO [role];.

GRANT admins TO joe;

To revoke use REVOKE [group role] FROM [role];.

REVOKE admins FROM joe;

To restore an initial connection role use SET ROLE or RESET ROLE.

SET ROLE NONE;
RESET ROLE;

All roles are members of the PUBLIC role. Therefore granting privileges to PUBLIC means granting them to all roles.

4.

Databases

4.1.

What is a Database?

A Postgres database - and a computer database in general - is a collection of data stored in computer memory organized specifically for rapid write and read operations.

As already noted Postgres itself is not a database but a database management system (DBMS) or more specifically an relational database management system (RDBMS).

A given Postgres server instance features exactly one database cluster (a collection of databases) which in turn includes at least one database.

Postgres databases data is stored in tables - aka relations ("relation" is a mathematical term for "table").

Postgres database objects are organized by schemas.

4.2.

Connecting to a Database

Before trying to connect to a Postgres database ensure that a Postgres server is running. The Postgres server process is called postgres. The postgres process manages database objects, performs actions on those objects and accepts connections to databases from client processes.

The postgres process can accept multiple database connections at the same time.

4.2.1.

Connecting to a Database with psql from within the Shell

There are several ways of establishing a connection to a Postgres database. One of them is using the psql client that comes with the Postgres server installation.

In addition to executing commands from the shell psql allows - once connected to a database - to execute SQL commands.

Executing simply psql in the shell without any options will attempt to connect with a role name and the database name of the current system user. If no such role or database exists the attempt will fail.

To attempt to establish a connection with explicit role name use -U option.

$ psql -U foo

The above command will attempt to connect as the role with the name foo to the database with the name foo.

To attempt to establish a connection with the explicit database name use -d option.

$ psql -d bar

The above command will attempt to connect to the database name bar with the implicit role name of the system user.

The -U and -d options can be combined.

$ psql -U user -d bar

A shorthand for psql -U user -d bar is psql -U user bar.

To check the name of the currently connected role use the SELECT current_user; command.

To check the name of the currently connected database use the SELECT current_database(); command.

4.2.2.

Connecting to a Database with psql from within the psql Prompt

Once connected to a database and being within the psql prompt it is possible to establish the connection to another database (which terminates the connection to the current database) using the \c command.

$ psql bar 

bar=> \c foo;
You are now connected to database "foo" as user "bar".
4.2.3.

Connecting to a Database with Non-psql Clients

psql is not the only way to connect to a Postgres database nor is it the primary one.

The primary purpose of any database is to be used as a rapid write and read data storage by any application.

An application (such as a Django, Next.js or Ruby on Rails app) to establish a direct connection to a Postgres database needs to provide:

  • host / socket e.g. 127.0.0.1 or foo.bar.compute.amazonaws.com,

  • port e.g. 5432,

  • role (user) name,

  • role password (if any),

  • database name,

  • other - if necessary (e.g. SSL mode).

In addition it is possible to establish a connection to a remote Postgres database using SSH tunnel.

When the client and the server processes are located on separate hosts the communication is effected over TCP/IP protocol.

4.3.

Creating a Database

4.3.1.

Creating a Database with the createdb Shell Command

To create a database from the shell use the createdb foo command where foo is the database name to be created.

Executing simply createdb will attempt to create a database with the name of the current system user and using the role with the name of the current system user.

4.3.2.

Creating a Database with an SQL Command

To create a database with an SQL command use the CREATE DATABASE command.

CREATE DATABASE foo;
4.4.

Dropping a Database

4.4.1.

Dropping a Database with the dropdb Shell Command

To drop a database from the shell use the dropdb foo command.

4.4.2.

Dropping a Database with an SQL Command

To drop a database with an SQL command use the DROP DATABASE command.

DROP DATABASE foo;
4.5.

Listing Databases from within psql

To list all databases from within psql from the current database cluster use the \l internal command.

\l
5.

Schemas

5.1.

Schema Definition

A given Postgres database is organized with at least one schema.

A schema logically groups database objects such as tables for manageability and access constraining.

Two or more tables with the same name can exist within the same database as long as they are grouped in different schemas.

At any given time a client can be connected to only one database but access objects in many different schemas within that database as long as it has the respective role privileges to that objects.

To create a schema within a currently connected database use CREATE SCHEMA commands. To drop use DROP SCHEMA command.

CREATE SCHEMA schema_name;

An object contained within a given schema is indicated using the dot notation: schema_name.schema_object_name.

5.2.

Public Schema

Each Postgres database has a schema called public. When an object is specified without schema indication the public schema is indicated by default. By default the command CREATE TABLE users (); and the command CREATE TABLE public.users (); are tantamount. However, the default schema can be changed.

5.3.

SQL Standard & Other SQL Standard Implementations

Schema rules vary between SQL standard, Postgres implementation and other SQL standard implementations. If portability is an issue use Postgres schema system with caution.

6.

Tables

6.1.

What is a Table?

A table is a named collection of data organized in rows with named columns of specific data types.

A table can be stored in a database but also calculated at query time (e.g. subquery, view).

A table is sometimes being referred to as a "relation" (however the extent of equivalence of both terms is being disputed). Therefore the word "relational" in "relational database" refers rather to tables than to relations between rows of tables.

The order of rows in a table is not guaranteed in any way however the rows can be ordered explicitly in an SQL query.

6.2.

Creating Tables

To create a new table use the CREATE TABLE command appended with the table name and optional column data in parenthesis. Each row of the optional column data should include the required column name, the required column type and the optional column constraint.

CREATE TABLE clients ();

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR (42) NOT NULL,
  last_name VARCHAR (42),
  email VARCHAR (128) UNIQUE NOT NULL
);
6.3.

Listing Tables

6.3.1.

Listing Tables Using SQL Queries

To list all tables in the currently connected database use the SELECT * FROM information_schema.tables; SQL query.

To list all tables in the currently connected database in a particular schema use the SELECT * FROM information_schema.tables WHERE table_schema = 'foo'; SQL query.

6.3.2.

Listing Tables Using psql

To list all tables in the currently connected database in psql use the \dt (\dt+ for extended information) internal command.

To list all tables in the currently connected database in a particular schema in psql use the same \dt (\dt+ for extended information) internal command but appended with the schema name and the select all operator (*).

\dt+ foo.*
6.4.

Renaming Tables

To rename a table use the ALTER TABLE ... RENAME TO ... SQL command.

ALTER TABLE users RENAME TO clients;
6.5.

Dropping tables

To drop (aka delete) a table use the DROP TABLE SQL command.

DROP TABLE users;
7.

Columns

7.1.

What is a Column?

A column in a relational database is a named and typed collection of data intersecting with unnamed and untyped row or rows.

An intersection of a column and a row is called a cell. The cell stores a piece of data with the type imposed by the column type.

7.2.

Column Data Types

7.2.1.

What is a Column Data Type?

A column data type is a limitation of manner in which a data piece can be stored in a given column.

For example a column with integer data type can store whole numbers (e.g. 42) but not floating point numbers (e.g. 42.42) nor words (e.g. forty two).

7.2.2.

Built-In Data Types

There are tens of built-in data types in Postgres.

The most widely used data types are:

  • integer - a 16-bit signed integer,

  • double precision float,

  • varchar (aka text) - variable-length strings,

  • boolean,

  • date,

  • time,

  • timestamp,

  • uuid.

Other commonly used data types are

  • smallint - a 8-bit signed integer,

  • bigint - a 64-bit signed integer,

  • numeric - a selectable precision decimal,

  • real - a single precision float,

  • char - a fixed-length string,

  • json,

  • jsonb - decomposed JSON

  • xml.

There are also datatypes dedicated to storing byte data.

7.2.3.

Custom Data Types

Postgres allows for creating new custom data types.

7.2.4.

serial & bigserial

serial and bigserial are not actual Postgres data types but a shorthand for creating a column with auto-incrementing integer (serial) or bigint (bigserial) data types with NOT NULL constraint applied.

serial & bigserial do not apply UNIQUE, nor PRIMARY KEY constraints automatically.

Using serial & bigserial is not the only way of applying auto-incrementation. Auto-incrementation can also be applied manually using DEFAULT nextval('tablename_colname_seq').

7.3.

Column Constraints

7.3.1.

What is a Column Constraint?

A column constraint is a limitation of acceptable data values that can be stored in the column cells.

A column can be constrained with more than one constraint at the same time.

7.3.2.

Built-in Column Constrains

PostgreSQL features the following built-in column constraints:

  • check,

  • primary key,

  • foreign key,

  • not null, and

  • unique.

7.3.3.

Check Constraint

The check constraint is a limitation of acceptable data values that can be stored in the column cells through verifying whether a value - before it is stored - satisfies a provided boolean expression.

For example, to ensure that a user age is always positive the CHECK (age > 0) constraint can be used.

CREATE TABLE users (
  age integer CHECK (age > 0)
);
7.3.4.

Primary Key Constraint

The primary key constraint is a limitation of acceptable data values that can be stored in column cells through allowing:

  • only unique values, and

  • only not null values.

A given table can have only one primary key.

CREATE TABLE users (
  id serial PRIMARY KEY
);

A primary key constraint can constrain multiple columns at the same time creating a multi‑column primary key.

7.3.5.

Foreign Key Constraint

The foreign key constraint is a limitation of acceptable data values that can be stored in column cells through allowing only values that have their counterparts as primary keys of other tables to which they reference. This ensures a so called referential integrity.

CREATE TABLE orders (
  user_id integer REFERENCES users (id)
);

The orders table is the referencing table and the users table is the referenced table.

The referenced column name (in the above case (id)) can be omitted if the id column is the primary key of the referenced table.

A given table can have many foreign keys.

A foreign key constraint can constrain multiple columns at the same time if it references a multi‑column primary key.

It is allowed for a table to reference itself.

7.3.6.

Not Null Constraint

The not null constraint is a limitation of acceptable data values that can be stored in column cells through allowing only not null values.

CREATE TABLE users (
  email VARCHAR (256) NOT NULL
);
7.3.7.

Unique Constraint

The unique constraint is a limitation of acceptable data values that can be stored in column cells through allowing only unique values.

CREATE TABLE users (
  token VARCHAR (256) UNIQUE
);

A unique constraint automatically applies a B-tree index on the relevant column or columns.

7.3.8.

Table Constraints

It is possible to apply a constraint to more than one column using a table constraint.

A table constraint is a constraint applied on a table as a whole instead of a specific one column.

For example, it is possible apply a unique constraint to two columns to ensure that no two same combinations of values are present in the table. However, such a two-column unique constraint does not disallow from duplicated values in each column separately as only the combinations have to be unique. Further, combinations with null values in any of the columns count as unique.

CREATE TABLE users (
  first_name VARCHAR (256),
  last_name VARCHAR (256),
  UNIQUE (first_name, last_name)
);

A table constraint can compare values from two columns before allowing them to be saved.

CREATE TABLE users (
  price numeric,
  discount numeric,
  CHECK (price > discount)
);

A multi-column check constraint is satisfied if its boolean expression evaluates to true or null. Therefore, caution is advised as some expressions evaluate to null when any of their operands are null.

7.3.9.

Naming Constraints

For easier referencing it is possible to name constraints.

Both, column and table, constraints can be named.

If no constraint name is provided Postgres chooses one automatically.

7.3.10.

Adding Constraints to Existing Tables & Columns

It is possible add both table and column constraints associated with already existing columns using the ALTER TABLE and ALTER COLUMN commands.

ALTER TABLE users ADD UNIQUE (first_name, last_name);
ALTER TABLE users ALTER COLUMN first_name SET NOT NULL;
7.3.11.

Dropping Constraints

To drop a constraint - other than a not null constraint which does not have a name - it is required to know its name. The constraint name could have been granted to it explicitly or automatically.

ALTER TABLE users DROP CONSTRAINT users_email_key;
ALTER TABLE users ALTER COLUMN first_name DROP NOT NULL;

To get constraint names for a given table use the \d internal command appended with the table name.

\d users
7.4.

Column Defaults

A column default value is a piece of data that is stored in the column cell when creating a new row when:

  • no value is provided for that cell, or

  • the command creating the row explicitly calls for the default column value for that cell.

When no default value is explicitly set for a given column the default value is NULL.

To create a column with a default value use the DEFAULT key word appended with the default value.

CREATE TABLE users (
  occupation VARCHAR(256) DEFAULT 'Database Engineer'
);

ALTER TABLE users ALTER COLUMN occupation SET DEFAULT 'Full-Stack Engineer';

To drop an explicit column default value and restore the implicit NULL default value use the DROP DEFAULT command..

ALTER TABLE users ALTER COLUMN occupation DROP DEFAULT;
7.5.

Adding Columns

To add a column to an existing table use the ALTER TABLE ... ADD COLUMN command appended with the column name, type and optional constraints.

ALTER TABLE users
ADD COLUMN last_name VARCHAR(256) NOT NULL;
7.6.

Renaming Columns

To rename a column use the ALTER TABLE ... RENAME COLUMN command.

ALTER TABLE users
RENAME COLUMN email TO email_address;
7.7.

Listing Columns

7.7.1.

Listing Columns with psql

To list columns in a given table with psql use the \d+ internal command appended with the table name.

=> \d+ users
7.7.2.

Listing Columns with SQL

To list columns in a given table with SQL select all rows from information_schema.columns where table_name equals the contemplated table name.

SELECT *
FROM information_schema.columns
WHERE table_name = 'users';
7.8.

Dropping Columns

To drop a column use the ALTER TABLE ... DROP COLUMN command appended with the column name.

ALTER TABLE users DROP COLUMN last_name;
8.

Statements, Commands & Queries

8.1.

SQL Community View

When talking about SQL language instructions communicating the requested behavior to a DBMS (such as Postgres) three terms are often used:

  • a statement,

  • a command, and

  • a query.

Unfortunately, the terms are not clearly defined nor delimited by the SQL community (vide Difference between a statement and a query in SQL).

Some deem a statement (a command) to be any SQL instruction to a DBMS that can potentially 1) persist manipulated data and/or 2) retrieve unmanipulated data, and a query an SQL instruction that can only retrieve unmanipulated data. In this view the query term is a semantic subset of the statement (the command) term.

Other deem a statement (a command) to be an SQL instruction to a DBMS that can potentially persists manipulated data, and a query an SQL instruction to a DBMS that only retrieves unmanipulated data. In this view the statement (the command) term is semantically disjointed from the query term.

In both views the term statement and command is equalized.

8.2.

Object-Oriented Programming Command Query Separation

The term "Command Query Separation" was coined by Bertrand Meyer in the book "Object-Oriented Software Construction". The term does not refer to SQL commands or queries but to object methods and is presented here for reference purposes only.

In the Command Query Separation view a command method changes the state of a system but does not return a value, and a query method does not change the state of the system but does return a value.

9.

Creating (Inserting) Data

9.1.

What is Creating (Inserting) Data?

Creating (inserting) data means adding a new row or rows to a table providing literal (constant) or scalar expression values for cells of the row or rows and/or instructing Postgres to supplement the unprovided values with the default column values.

In turn, adding a new column to a table is not denoted as creating (inserting) data but as altering that table.

9.2.

The Insert Command

To create a new row (to insert a new row) use the SQL INSERT INTO command appended with the:

  • required table name,

  • optional column order (if no such order is provided values are inserted in the table column order),

  • required values prepended with the VALUES key word.

INSERT INTO users VALUES ('test@example.net', 'John', 'Doe');

INSERT INTO users (email, first_name, last_name) VALUES ('test@example.net', 'John', 'Doe');

INSERT INTO users (last_name, email) VALUES 'test@example.net', 'Doe';
9.3.

Default Values

It is possible to instruct Postgres to use default values for unprovided row cell values:

  • implicitly without using the DEFAULT key word and simply not provided a value for a given cell,

  • explicitly with using the DEFAULT key word.

For example, for a table users with the first_name, last_name columns and a column role with the default 'client' value we can create a new row with the said default in the following manners.

INSERT INTO users (first_name, last_name, role) VALUES ('John', 'Doe');
INSERT INTO users (first_name, last_name, role) VALUES ('John', 'Doe', DEFAULT);
INSERT INTO users DEFAULT VALUES;
9.4.

Inserting Multiple Rows

It is possible to insert multiple rows simultaneously.

INSERT INTO users (first_name, last_name)
VALUES ('John', 'Doe'), ('Geralt', 'of Rivia'), ('Jean-Luc', 'Picard');
9.5.

Inserting Subquery Result

As a SELECT query result consists of literal values it can be used as values for the INSERT INTO command.

10.

Reading (Querying) Data

10.1.

Simple Querying

To select all rows and columns from a given table:

SELECT * FROM table_name;

To select all rows but selected columns only:

SELECT column_name, column_name FROM table_name;
10.2.

Conditional Querying

To select all columns but only rows meeting a specific condition:

SELECT * FROM table_name WHERE condition;

To select only selected columns only from rows meeting a specific condition:

SELECT column_name, column_name FROM table_name WHERE condition;
10.3.

Distinctive Querying

To get distinct values from a given column:

SELECT DISTINCT column_name FROM table_name;
10.4.

Aliasing

To temporarily rename a column name in querying results:

SELECT column_name AS new_temporary_column_name FROM table_name;
10.5.

Pattern Matching

To select only columns meeting a specific pattern:

SELECT * table_name WHERE column_name LIKE pattern;

In a pattern _ stands for any single character and % stands for zero or more characters.

LIKE pattern always regards an entire string. Therefore to look for a match within an entire string the pattern should start and end with %.

11.

Updating Data

11.1.

What is Updating Data?

Updating data in an SQL database table is changing (aka modifying) values in all, some or one of the the already existing table cells of a given column or given columns.

11.2.

Updating All Cells of a Given Column

It is possible to update all cells of a given column simultaneously.

UPDATE users
SET role = 'client';

The above command updates the role column in all rows of the table users to the string 'client'.

11.3.

Updating Only Specific Cells of a Given Column

It is possible to update only cells belonging to rows meeting specific criteria. The criteria (conditions) are provided using the key word WHERE appended with a boolean expression which can reference the updated table columns.

UPDATE horses
SET free_roaming = TRUE
WHERE status = 'mustang';

The above command updates the column free_roaming to TRUE for all rows in the table horse where status equals to mustang. The remainig rows remain unaffected.

UPDATE users
SET role = 'admin'
WHERE id = 42;

The above command updates the column role in only the row with the id of 42.

12.

Deleting Data

12.1.

What is Deleting Data?

Deleting data from an SQL database table is removing entire rows or row from that table.

12.2.

Removing All Rows from a Given Table

To delete all rows from a given table use the DELETE command appended with the table name and without providing any conditions regarding the rows to delete.

DELETE FROM users;

The above command removes all rows from the table users.

12.3.

Removing Only Specific Rows from a Given Table

It is possible to delete only rows meeting specific criteria. The criteria (conditions) are provided using the key word WHERE appended with a boolean expression which can reference cell values of the table rows.

DELETE FROM products
WHERE fit_for_consumption = FALSE;

The above command removes only those rows from products which have the column fit_for_consumption value set to FALSE.

We use cookies and similar technologies to enhance the quality of services, maintain statistics and adjust marketing content. You will find more information in the Cookies Policy.

By clicking OK you grant consent to processing of your personal data by us and our Trusted Partners with the purpose of maintain statistics and adjustment of the marketing content pursuant to the Privacy Policy. If you wish to not grant that consent and/or limit its extent click Settings.