Overview

I had an opportunity to use PostgreSQL, so I started learning it.

I'll limit my notes to things that seem important and things I'm likely to forget.

References

Constants

String Constants

Enclose in single quotation marks.

'string'

If a string contains a single quotation mark, write two consecutive single quotation marks. The \ mark is treated as a literal string.

'I''ll be back.'

In PostgreSQL 14, you can also escape with a backslash, but this is planned to be disallowed in future versions — be careful.

Escaping a single quotation mark with a backslash (\') is possible. However, this will no longer be possible in future PostgreSQL versions. Applications using backslash must be changed to comply with the standard described above.

Source

Escape String Syntax

Using escape string syntax allows entering special characters. In escape string syntax, enclose the string in single quotation marks and prefix with E or e.

E'\041'
or
e'\041'

Bit Strings and Hex Strings

Bit strings

B'01' or b'011110'

Hex strings

X'3F' or x'A37E'

Data Types

String Types

There are varchar, char, and text types.

varchar is a variable-length string type; char is a fixed-length string type.

text is a variable-length string type with no character limit. Convenient, but note that it is outside the ANSI SQL standard.

Floating-Point Types

There are numeric, real, and double precision types (where does the name "real" come from?...).

numeric(8, 2) can store values with 2 decimal places and 6 integer digits. Exceeding the integer digit count results in an error.

real is 4 bytes with 6 significant decimal digits; double precision is 8 bytes with 15 significant decimal digits.

serial Type

When a serial type column is created in a table and a record is inserted without specifying a value for that column, an auto-incrementing integer is automatically inserted.

Behind the scenes, a sequence is being used.

You can insert an arbitrary integer value into a serial column, but the serial type doesn't detect this, so duplicate integer values may be issued.

While serial is 4 bytes and handles values from 1 to 2147483647, bigserial is 8 bytes and handles values from 1 to 9223372036854775807.

Date/Time Data Types

There are date, timestamp, and time types.

date stores only the date. The time portion is truncated. Note that this differs from Oracle's date type.

Example: 2018-01-23

timestamp stores both date and time. Seconds can be stored up to 6 decimal places.

Example: 2018-01-23 12:34:56.526066

time stores only the time. It does not hold date data.

Example: 12:34:56.526066

Casting

You can use the CAST function or :: notation.

Both of the following result in the integer 123:

SELECT CAST('123' AS integer);
SELECT '123'::interger;

Creating and Initializing a Database Cluster

A database cluster = a directory (storing table files, index files, configuration files, management files, etc.)

A database cluster can be created with the initdb command.

initdb --no-locale --encoding=UTF8 --pgdata=/var/lib/pgsql/14/data

UTF8 encoding is fine to specify.

Locale is an OS mechanism that handles processing according to regional language and culture — for example, date and currency display formatting, string sort ordering, etc. In PostgreSQL, locale use is not recommended because there is no unified specification for locale functionality.

--pgdata specifies the directory in which to create the database cluster. It can also be set via the environment variable $PGDATA.

Configuration goes in $PGDATA/postgresql.conf.

A database cluster can be started with the pg_ctl command. Which database cluster to use is set with the --pgdata flag or the environment variable $PGDATA. The startup state of the database cluster can be checked with pg_ctl status.

pg_ctl start --pgdata=/var/lib/pgsql/14/data
pg_ctl status

Similarly, specify the database cluster when stopping it:

pg_ctl stop --pgdata=/var/lib/pgsql/14/data

Default Databases

PostgreSQL has postgres, template0, and template1 as default databases.

template0 and template1 are database templates.

When creating a database with a CREATE DATABASE statement, template1 is copied by default to create the database. Since template1 is writable, it's convenient to register settings in template1 in advance.

pgbench

A benchmarking tool bundled with PostgreSQL.

Uses a benchmark scenario called TPC-B that models bank account, branch, and teller operations.

The pgbench command can create benchmark tables in a database.

pgbench --initialize --scale=30 <database name>

--initialize means initializing the benchmark tables.

--scale represents the scale of the benchmark tables. --scale=1 results in a database size of about 15 MB.