PostgreSQL Notes for Professionals

9

9

9

12

12

12

12

17

18

18

18

18

21

21

21

Chapter 10: JSON Support

.....................................................................................................................................

22

Section 10.1: Using JSONb operators

Section 10.2: Querying complex JSON documents

Section 10.3: Creating a pure JSON table

........................................................................................................................

22

..................................................................................................

26

.................................................................................................................

27

Chapter 11: Aggregate Functions

........................................................................................................................

28

Section 11.1: Simple statistics: min(), max(), avg()

Section 11.2: regr_slope(Y, X) : slope of the least-squares-fit linear equation determined by the (X, Y) pairs

.....................................................................................................

28

................................................................................................................................................................................

28

Section 11.3: string_agg(expression, delimiter)

........................................................................................................

29

Chapter 12: Common Table Expressions (WITH)

.........................................................................................

31

Section 12.1: Common Table Expressions in SELECT Queries

Section 12.2: Traversing tree using WITH RECURSIVE

.................................................................................

31

............................................................................................

Chapter 13: Window Functions

Section 13.1: generic example

Section 13.2: column values vs dense_rank vs rank vs row_number

.............................................................................................................................

.....................................................................................................................................

...................................................................

33

Chapter 14: Recursive queries

Section 14.1: Sum of Integers

.............................................................................................................................

34

......................................................................................................................................

34

Chapter 15: Programming with PL/pgSQL

....................................................................................................

35

Section 15.1: Basic PL/pgSQL Function

Section 15.2: custom exceptions

Section 15.3: PL/pgSQL Syntax

Section 15.4: RETURNS Block

......................................................................................................................

35

................................................................................................................................

..................................................................................................................................

.....................................................................................................................................

Chapter 16: Inheritance

............................................................................................................................................

37

Section 16.1: Creating children tables

........................................................................................................................

37

Chapter 17: Export PostgreSQL database table header and data to CSV file

...........................

38

Section 17.1: copy from query

Section 17.2: Export PostgreSQL table to csv with header for some column(s)

Section 17.3: Full table backup to csv with header

Chapter 18: Triggers and Trigger Functions

...................................................................................................

.................................................................................................

....................................................................................................................................

38

...................................................

38

Section 18.1: Type of triggers

Section 18.2: Basic PL/pgSQL Trigger Function

......................................................................................................................................

.......................................................................................................

40

Chapter 19: Event Triggers

....................................................................................................................................

42

Section 19.1: Logging DDL Command Start Events

..................................................................................................

42

Chapter 20: Role Management

...........................................................................................................................

43

Section 20.1: Create a user with a password

Section 20.2: Grant and Revoke Privileges

Section 20.3: Create Role and matching database

Section 20.4: Alter default search_path of user

Section 20.5: Create Read Only User

Section 20.6: Grant access privileges on objects created in the future

............................................................................................................

...............................................................................................................

.................................................................................................

......................................................................................................

........................................................................................................................

................................................................

45

Chapter 21: Postgres cryptographic functions

............................................................................................

46

Section 21.1: digest

.......................................................................................................................................................

46

Chapter 22: Comments in PostgreSQL

............................................................................................................

47

Section 22.1: COMMENT on Table

Section 22.2: Remove Comment

Chapter 23: Backup and Restore

Section 23.1: Backing up one database

Section 23.2: Restoring backups

..............................................................................................................................

...............................................................................................................................

........................................................................................................................

48

....................................................................................................................

48

................................................................................................................................

48

31

32

32

35

36

36

38

39

39

43

43

44

44

45

47

47

49

49

50

52

52

55

56

58

58

58

Section 23.3: Backing up the whole cluster

Section 23.4: Using psql to export data

Section 23.5: Using Copy to import

Section 23.6: Using Copy to export

..............................................................................................................

48

....................................................................................................................

...........................................................................................................................

...........................................................................................................................

Chapter 24: Backup script for a production DB

.........................................................................................

51

Section 24.1: saveProdDb.sh

.......................................................................................................................................

51

Chapter 25: Accessing Data Programmatically

Section 25.1: Accessing PostgreSQL with the C-API

Section 25.2: Accessing PostgreSQL from python using psycopg2

Section 25.3: Accessing PostgreSQL from .NET using the Npgsql provider

Section 25.4: Accessing PostgreSQL from PHP using Pomm2

......................................................................

.........................................................

55

...............................................................................

..........................................................................................

.................................................................................................

Chapter 26: Connect to PostgreSQL from Java

Section 26.1: Connecting with java.sql.DriverManager

Section 26.2: Connecting with java.sql.DriverManager and Properties

Section 26.3: Connecting with javax.sql.DataSource using a connection pool

.........................................................................................

............................................................................................

.................................................................

.....................................................

59

Chapter 27: PostgreSQL High Availability

.....................................................................................................

61

Section 27.1: Replication in PostgreSQL

....................................................................................................................

61

Chapter 28: EXTENSION dblink and postgres_fdw

...................................................................................

64

Section 28.1: Extention FDW

Section 28.2: Foreign Data Wrapper

Section 28.3: Extention dblink

.......................................................................................................................................

64

........................................................................................................................

64

....................................................................................................................................

65

Chapter 29: Postgres Tip and Tricks

.................................................................................................................

66

Section 29.1: DATEADD alternative in Postgres

Section 29.2: Comma separated values of a column

Section 29.3: Delete duplicate records from postgres table

Section 29.4: Update query with join between two tables alternative since Postresql does not support join

.......................................................................................................

.............................................................................................

..................................................................................

66

66

66

in update query

...................................................................................................................................................

66

Section 29.5: Dierence between two date timestamps month wise and year wise

Section 29.6: Query to Copy/Move/Transafer table data from one database to other database table with

..........................................

66

same schema

......................................................................................................................................................

67

Credits

..............................................................................................................................................................................

68

You may also like

........................................................................................................................................................

70

About

Please feel free to share this PDF with anyone for free,

latest version of this book can be downloaded from:

https://goalkicker.com/PostgreSQLBook

This PostgreSQL® Notes for Professionals book is compiled from Stack Overflow

Documentation, the content is written by the beautiful people at Stack Overflow.

Text content is released under Creative Commons BY-SA, see credits at the end

of this book whom contributed to the various chapters. Images may be copyright

of their respective owners unless otherwise specified

This is an unofficial free book created for educational purposes and is not

affiliated with official PostgreSQL® group(s) or company(s) nor Stack Overflow.

All trademarks and registered trademarks are the property of their respective

company owners

The information presented in this book is not guaranteed to be correct nor

accurate, use at your own risk

Please send feedback and corrections to [email protected]

GoalKicker.com – PostgreSQL® Notes for Professionals

1

Chapter 1: Getting started with PostgreSQL

Version Release date EOL date

2022-10-01

10.0

2017-10-05

9.6

9.5

9.4

9.3

9.2

9.1

9.0

8.4

2016-09-29

2021-09-01

2016-01-07

2021-01-01

2014-12-18

2019-12-01

2013-09-09

2018-09-01

2012-09-10

2017-09-01

2011-09-12

2016-09-01

2010-09-20

2015-09-01

2009-07-01

2014-07-01

Section 1.1: Installing PostgreSQL on Windows

While it's good practice to use a Unix based operating system (ex. Linux or BSD) as a production server you can

easily install PostgreSQL on Windows (hopefully only as a development server).

Download the Windows installation binaries from EnterpriseDB:

http://www.enterprisedb.com/products-services-training/pgdownload This is a third-party company started by core

contributors to the PostgreSQL project who have optimized the binaries for Windows.

Select the latest stable (non-Beta) version (9.5.3 at the time of writing). You will most likely want the Win x86-64

package, but if you are running a 32 bit version of Windows, which is common on older computers, select Win

x86-32 instead.

Note: Switching between Beta and Stable versions will involve complex tasks like dump and restore. Upgrading

within beta or stable version only needs a service restart.

You can check if your version of Windows is 32 or 64 bit by going to Control Panel -> System and Security -> System

-> System type, which will say "##-bit Operating System". This is the path for Windows 7, it may be slightly different

on other versions of Windows.

In the installer select the packages you would like to use. For example:

pgAdmin ( https://www.pgadmin.org ) is a free GUI for managing your database and I highly recommend it. In

9.6 this will be installed by default .

PostGIS ( http://postgis.net ) provides geospatial analysis features on GPS coordinates, distances etc. very

popular among GIS developers.

The Language Package provides required libraries for officially supported procedural language PL/Python,

PL/Perl and PL/Tcl.

Other packages like pgAgent, pgBouncer and Slony are useful for larger production servers, only checked as

needed.

All those optional packages can be later installed through "Application Stack Builder".

Note: There are also other non-officially supported language such as PL/V8, PL/Lua PL/Java available.

Open pgAdmin and connect to your server by double clicking on its name, ex. "PostgreSQL 9.5 (localhost:5432).

From this point you can follow guides such as the excellent book PostgreSQL: Up and Running, 2nd Edition (

http://shop.oreilly.com/product/0636920032144.do ).

GoalKicker.com – PostgreSQL® Notes for Professionals

2

Optional: Manual Service Startup Type

PostgreSQL runs as a service in the background which is slightly different than most programs. This is common for

databases and web servers. Its default Startup Type is Automatic which means it will always run without any input

from you.

Why would you want to manually control the PostgreSQL service? If you're using your PC as a development server

some of the time and but also use it to play video games for example, PostegreSQL could slow down your system a

bit while its running.

Why wouldn't you want manual control? Starting and stopping the service can be a hassle if you do it often.

If you don't notice any difference in speed and prefer avoiding the hassle then leave its Startup Type as Automatic

and ignore the rest of this guide. Otherwise...

Go to Control Panel -> System and Security -> Administrative Tools.

Select "Services" from the list, right click on its icon, and select Send To -> Desktop to create a desktop icon for more

convenient access.

Close the Administrative Tools window then launch Services from the desktop icon you just created.

Scroll down until you see a service with a name like postgresql-x##-9.# (ex. "postgresql-x64-9.5").

Right click on the postgres service, select Properties -> Startup type -> Manual -> Apply -> OK. You can change it

back to automatic just as easily.

If you see other PostgreSQL related services in the list such "pgbouncer" or "PostgreSQL Scheduling Agent -

pgAgent" you can also change their Startup Type to Manual because they're not much use if PostgreSQL isn't

running. Although this will mean more hassle each time you start and stop so it's up to you. They don't use as many

resources as PostgreSQL itself and may not have any noticeable impact on your systems performance.

If the service is running its Status will say Started, otherwise it isn't running.

To start it right click and select Start. A loading prompt will be displayed and should disappear on its own soon

after. If it gives you an error try a second time. If that doesn't work then there was some problem with the

installation, possibly because you changed some setting in Windows most people don't change, so finding the

problem might require some sleuthing.

To stop postgres right click on the service and select Stop.

If you ever get an error while attempting to connect to your database check Services to make sure its running.

For other very specific details about the EDB PostgreSQL installation, e.g. the python runtime version in the official

language pack of a specific PostgreSQL version, always refer to the official EBD installation guide , change the

version in link to your installer's major version.

Section 1.2: Install PostgreSQL from Source on Linux

Dependencies:

GNU Make Version > 3.80

an ISO/ ANSI C-Compiler (e.g. gcc)

an extractor like tar or gzip

zlib-devel

GoalKicker.com – PostgreSQL® Notes for Professionals

3

readline-devel oder libedit-devel

Sources: Link to the latest source (9.6.3)

Now you can extract the source files:

tar -xzvf postgresql-9.6.3.tar.gz

There are a large number of different options for the configuration of PostgreSQL:

Full Link to the full installation procedure

Small list of available options:

--prefix=PATH path for all files

--exec-prefix=PATH path for architectur-dependet file

--bindir=PATH path for executable programs

--sysconfdir=PATH path for configuration files

--with-pgport=NUMBER specify a port for your server

--with-perl add perl support

--with-python add python support

--with-openssl add openssl support

--with-ldap add ldap support

--with-blocksize=BLOCKSIZE set pagesize in KB

BLOCKSIZE must a power of 2 and between 1 and 32

--with-wal-segsize=SEGSIZE set size of WAL-Segment size in MB

SEGSIZE must be a power of 2 between 1 and 64

Go into the new created folder and run the cofigure script with the desired options:

./configure --exec=/usr/local/pgsql

Run make to create the objectfiles

Run make install to install PostgreSQL from the built files

Run make clean to tidy up

For the extension switch the directory cd contrib, run make and make install

Section 1.3: Installation on GNU+Linux

On most GNU+Linux operating systems, PostgreSQL can easily be installed using the operating system package

manager.

Red Hat family

Respositories can be found here: https://yum.postgresql.org/repopackages.php

Download the repository to local machine with the command

yum -y install https://download.postgresql.org/pub/repos/yum/X.X/redhat/rhel-7-x86_64/pgdg-

redhatXX-X.X-X.noarch.rpm

View available packages:

GoalKicker.com – PostgreSQL® Notes for Professionals

4

yum list available | grep postgres*

Neccesary packages are: postgresqlXX postgresqlXX-server postgresqlXX-libs postgresqlXX-contrib

These are installed with the following command: yum -y install postgresqlXX postgresqlXX-server postgresqlXX-libs

postgresqlXX-contrib

Once installed you will need to start the database service as the service owner (Default is postgres). This is done

with the pg_ctl command.

sudo -su postgres

./usr/pgsql-X.X/bin/pg_ctl -D /var/lib/pgsql/X.X/data start

To access the DB in CLI enter psql

Debian family

On Debian and derived operating systems, type:

sudo apt-get install postgresql

This will install the PostgreSQL server package, at the default version offered by the operating system's package

repositories.

If the version that's installed by default is not the one that you want, you can use the package manager to search

for specific versions which may simultaneously be offered.

You can also use the Yum repository provided by the PostgreSQL project (known as PGDG) to get a different

version. This may allow versions not yet offered by operating system package repositories.

Section 1.4: How to install PostgreSQL via MacPorts on OSX

In order to install PostgreSQL on OSX, you need to know which versions are currently supported.

Use this command to see what versions you have available.

sudo port list | grep "^postgresql[[:digit:]]\{2\}[[:space:]]"

You should get a list that looks something like the following:

postgresql80 @8.0.26 databases/postgresql80

postgresql81 @8.1.23 databases/postgresql81

postgresql82 @8.2.23 databases/postgresql82

postgresql83 @8.3.23 databases/postgresql83

postgresql84 @8.4.22 databases/postgresql84

postgresql90 @9.0.23 databases/postgresql90

postgresql91 @9.1.22 databases/postgresql91

postgresql92 @9.2.17 databases/postgresql92

postgresql93 @9.3.13 databases/postgresql93

postgresql94 @9.4.8 databases/postgresql94

postgresql95 @9.5.3 databases/postgresql95

postgresql96 @9.6beta2 databases/postgresql96

In this example, the most recent version of PostgreSQL that is supported in 9.6, so we will install that.

GoalKicker.com – PostgreSQL® Notes for Professionals

5

sudo port install postgresql96-server postgresql96

You will see an installation log like this:

---> Computing dependencies for postgresql96-server

---> Dependencies to be installed: postgresql96

---> Fetching archive for postgresql96

---> Attempting to fetch postgresql96-9.6beta2_0.darwin_15.x86_64.tbz2 from

https://packages.macports.org/postgresql96

---> Attempting to fetch postgresql96-9.6beta2_0.darwin_15.x86_64.tbz2.rmd160 from

https://packages.macports.org/postgresql96

---> Installing postgresql96 @9.6beta2_0

---> Activating postgresql96 @9.6beta2_0

To use the postgresql server, install the postgresql96-server port

---> Cleaning postgresql96

---> Fetching archive for postgresql96-server

---> Attempting to fetch postgresql96-server-9.6beta2_0.darwin_15.x86_64.tbz2 from

https://packages.macports.org/postgresql96-server

---> Attempting to fetch postgresql96-server-9.6beta2_0.darwin_15.x86_64.tbz2.rmd160 from

https://packages.macports.org/postgresql96-server

---> Installing postgresql96-server @9.6beta2_0

---> Activating postgresql96-server @9.6beta2_0

To create a database instance, after install do

sudo mkdir -p /opt/local/var/db/postgresql96/defaultdb

sudo chown postgres:postgres /opt/local/var/db/postgresql96/defaultdb

sudo su postgres -c '/opt/local/lib/postgresql96/bin/initdb -D

/opt/local/var/db/postgresql96/defaultdb'

---> Cleaning postgresql96-server

---> Computing dependencies for postgresql96

---> Cleaning postgresql96

---> Updating database of binaries

---> Scanning binaries for linking errors

---> No broken files found.

The log provides instructions on the rest of the steps for installation, so we do that next.

sudo mkdir -p /opt/local/var/db/postgresql96/defaultdb

sudo chown postgres:postgres /opt/local/var/db/postgresql96/defaultdb

sudo su postgres -c '/opt/local/lib/postgresql96/bin/initdb -D

/opt/local/var/db/postgresql96/defaultdb'

Now we start the server:

sudo port load -w postgresql96-server

Verify that we can connect to the server:

su postgres -c psql

You will see a prompt from postgres:

psql (9.6.1)

Type "help" for help.

GoalKicker.com – PostgreSQL® Notes for Professionals

6

postgres=#

Here you can type a query to see that the server is running.

postgres=#SELECT setting FROM pg_settings WHERE NAME='data_directory';

And see the response:

setting

------------------------------------------

/opt/local/var/db/postgresql96/defaultdb

(1 row)

postgres=#

Type \q to quit:

postgres=#\q

And you will be back at your shell prompt.

Congratulations! You now have a running PostgreSQL instance on OS/X.

Section 1.5: Install postgresql with brew on Mac

Homebrew calls itself 'the missing package manager for macOS'. It can be used to build and install applications and

libraries. Once installed, you can use the brew command to install PostgreSQL and it's dependencies as follows:

brew UPDATE

brew install postgresql

Homebrew generally installs the latest stable version. If you need a different one then brew SEARCH postgresql will

list the versions available. If you need PostgreSQL built with particular options then brew info postgresql will list

which options are supported. If you require an unsupported build option, you may have to do the build yourself,

but can still use Homebrew to install the common dependencies.

Start the server:

brew services START postgresql

Open the PostgreSQL prompt

psql

If psql complains that there's no corresponding database for your user, run CREATEDB.

Section 1.6: Postgres.app for Mac OSX

An extremely simple tool for installing PostgreSQL on a Mac is available by downloading Postgres.app.

You can change preferences to have PostgreSQL run in the background or only when the application is running.

GoalKicker.com – PostgreSQL® Notes for Professionals

7

Chapter 2: Data Types

PostgreSQL has a rich set of native data types available to users. Users can add new types to PostgreSQL using the

CREATE TYPE command.

https://www.postgresql.org/docs/9.6/static/datatype.html

Section 2.1: Numeric Types

Name

SMALLINT

INTEGER

Storage Size

2 bytes

Description

Range

small-range integer

-32768 to +32767

4 bytes

ypical choice for integer

-2147483648 to +2147483647

BIGINT

8 bytes

large-range integer

DECIMAL

variable

user-specified precision, exact

NUMERIC

variable

user-specified precision, exact

-9223372036854775808 to

+9223372036854775807

up to 131072 digits before the decimal point; up

to 16383 digits after the decimal point

up to 131072 digits before the decimal point...