PostgreSQL® Notes for Professionals book (GoalKicker Books) (Z-Library)

PostgreSQL Notes for ProfessionalsPostgreSQL® Notes for Professionals GoalKicker.com Free Programming Books Disclaimer This is an unocial free book created for educational purposes and is not aliated with ocial PostgreSQL® group(s) or company(s). All trademarks and registered trademarks are the property of their respective owners 60+ pages of professional hints and tricks

Contents About 1 ................................................................................................................................................................................... Chapter 1: Getting started with PostgreSQL 2 .................................................................................................. Section 1.1: Installing PostgreSQL on Windows 2 ........................................................................................................... Section 1.2: Install PostgreSQL from Source on Linux 3 ............................................................................................... Section 1.3: Installation on GNU+Linux 4 ......................................................................................................................... Section 1.4: How to install PostgreSQL via MacPorts on OSX 5 ................................................................................... Section 1.5: Install postgresql with brew on Mac 7 ........................................................................................................ Section 1.6: Postgres.app for Mac OSX 7 ........................................................................................................................ Chapter 2: Data Types 8 ............................................................................................................................................... Section 2.1: Numeric Types 8 ........................................................................................................................................... Section 2.2: Date/ Time Types 8 .................................................................................................................................... Section 2.3: Geometric Types 9 ....................................................................................................................................... Section 2.4: Network Adress Types 9 ............................................................................................................................. Section 2.5: Character Types 9 ....................................................................................................................................... Section 2.6: Arrays 9 ......................................................................................................................................................... Chapter 3: Dates, Timestamps, and Intervals 11 ............................................................................................. Section 3.1: SELECT the last day of month 11 ............................................................................................................... Section 3.2: Cast a timestamp or interval to a string 11 .............................................................................................. Section 3.3: Count the number of records per week 11 ............................................................................................... Chapter 4: Table Creation 12 ..................................................................................................................................... Section 4.1: Show table definition 12 ............................................................................................................................... Section 4.2: Create table from select 12 ........................................................................................................................ Section 4.3: Create unlogged table 12 ........................................................................................................................... Section 4.4: Table creation with Primary Key 12 .......................................................................................................... Section 4.5: Create a table that references other table 13 .......................................................................................... Chapter 5: SELECT 14 ...................................................................................................................................................... Section 5.1: SELECT using WHERE 14 ............................................................................................................................. Chapter 6: Find String Length / Character Length 15 ................................................................................... Section 6.1: Example to get length of a character varying field 15 ............................................................................. Chapter 7: COALESCE 16 ............................................................................................................................................... Section 7.1: Single non null argument 16 ........................................................................................................................ Section 7.2: Multiple non null arguments 16 .................................................................................................................. Section 7.3: All null arguments 16 ................................................................................................................................... Chapter 8: INSERT 17 ...................................................................................................................................................... Section 8.1: Insert data using COPY 17 ........................................................................................................................... Section 8.2: Inserting multiple rows 18 ........................................................................................................................... Section 8.3: INSERT data and RETURING values 18 ..................................................................................................... Section 8.4: Basic INSERT 18 ........................................................................................................................................... Section 8.5: Insert from select 18 .................................................................................................................................... Section 8.6: UPSERT - INSERT ... ON CONFLICT DO UPDATE.. 19 ................................................................................ Section 8.7: SELECT data into file 19 .............................................................................................................................. Chapter 9: UPDATE 21 .................................................................................................................................................... Section 9.1: Updating a table based on joining another table 21 ................................................................................ Section 9.2: Update all rows in a table 21 ...................................................................................................................... Section 9.3: Update all rows meeting a condition 21 ................................................................................................... Section 9.4: Updating multiple columns in table 21 ......................................................................................................

Chapter 10: JSON Support 22 ..................................................................................................................................... Section 10.1: Using JSONb operators 22 ........................................................................................................................ Section 10.2: Querying complex JSON documents 26 .................................................................................................. Section 10.3: Creating a pure JSON table 27 ................................................................................................................. Chapter 11: Aggregate Functions 28 ........................................................................................................................ Section 11.1: Simple statistics: min(), max(), avg() 28 ..................................................................................................... Section 11.2: regr_slope(Y, X) : slope of the least-squares-fit linear equation determined by the (X, Y) pairs 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 31 ................................................................................. Section 12.2: Traversing tree using WITH RECURSIVE 31 ............................................................................................ Chapter 13: Window Functions 32 ............................................................................................................................. Section 13.1: generic example 32 ..................................................................................................................................... Section 13.2: column values vs dense_rank vs rank vs row_number 33 ................................................................... Chapter 14: Recursive queries 34 ............................................................................................................................. Section 14.1: Sum of Integers 34 ...................................................................................................................................... Chapter 15: Programming with PL/pgSQL 35 .................................................................................................... Section 15.1: Basic PL/pgSQL Function 35 ...................................................................................................................... Section 15.2: custom exceptions 35 ................................................................................................................................ Section 15.3: PL/pgSQL Syntax 36 .................................................................................................................................. Section 15.4: RETURNS Block 36 ..................................................................................................................................... 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 38 .................................................................................................................................... Section 17.2: Export PostgreSQL table to csv with header for some column(s) 38 ................................................... Section 17.3: Full table backup to csv with header 38 ................................................................................................... Chapter 18: Triggers and Trigger Functions 39 ................................................................................................. Section 18.1: Type of triggers 39 ...................................................................................................................................... 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 43 ............................................................................................................ Section 20.2: Grant and Revoke Privileges 43 ............................................................................................................... Section 20.3: Create Role and matching database 44 ................................................................................................. Section 20.4: Alter default search_path of user 44 ...................................................................................................... Section 20.5: Create Read Only User 45 ........................................................................................................................ 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 47 .............................................................................................................................. Section 22.2: Remove Comment 47 ............................................................................................................................... Chapter 23: Backup and Restore 48 ........................................................................................................................ Section 23.1: Backing up one database 48 .................................................................................................................... Section 23.2: Restoring backups 48 ................................................................................................................................

Section 23.3: Backing up the whole cluster 48 .............................................................................................................. Section 23.4: Using psql to export data 49 .................................................................................................................... Section 23.5: Using Copy to import 49 ........................................................................................................................... Section 23.6: Using Copy to export 50 ........................................................................................................................... Chapter 24: Backup script for a production DB 51 ......................................................................................... Section 24.1: saveProdDb.sh 51 ....................................................................................................................................... Chapter 25: Accessing Data Programmatically 52 .......................................................................................... Section 25.1: Accessing PostgreSQL with the C-API 52 ................................................................................................. Section 25.2: Accessing PostgreSQL from python using psycopg2 55 ...................................................................... Section 25.3: Accessing PostgreSQL from .NET using the Npgsql provider 55 ......................................................... Section 25.4: Accessing PostgreSQL from PHP using Pomm2 56 ............................................................................... Chapter 26: Connect to PostgreSQL from Java 58 ......................................................................................... Section 26.1: Connecting with java.sql.DriverManager 58 ............................................................................................ Section 26.2: Connecting with java.sql.DriverManager and Properties 58 ................................................................. 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 64 ....................................................................................................................................... Section 28.2: Foreign Data Wrapper 64 ........................................................................................................................ Section 28.3: Extention dblink 65 .................................................................................................................................... Chapter 29: Postgres Tip and Tricks 66 ................................................................................................................. Section 29.1: DATEADD alternative in Postgres 66 ....................................................................................................... Section 29.2: Comma separated values of a column 66 ............................................................................................. Section 29.3: Delete duplicate records from postgres table 66 .................................................................................. Section 29.4: Update query with join between two tables alternative since Postresql does not support join in update query 66 ................................................................................................................................................... Section 29.5: Dierence between two date timestamps month wise and year wise 66 .......................................... Section 29.6: Query to Copy/Move/Transafer table data from one database to other database table with same schema 67 ...................................................................................................................................................... Credits 68 .............................................................................................................................................................................. You may also like 70 ........................................................................................................................................................

GoalKicker.com – PostgreSQL® Notes for Professionals 1 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 web@petercv.com

GoalKicker.com – PostgreSQL® Notes for Professionals 2 Chapter 1: Getting started with PostgreSQL Version Release date EOL date 10.0 2017-10-05 2022-10-01 9.6 2016-09-29 2021-09-01 9.5 2016-01-07 2021-01-01 9.4 2014-12-18 2019-12-01 9.3 2013-09-09 2018-09-01 9.2 2012-09-10 2017-09-01 9.1 2011-09-12 2016-09-01 9.0 2010-09-20 2015-09-01 8.4 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 3 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 4 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 5 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 6 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 7 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 8 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 Storage Size Description Range SMALLINT 2 bytes small-range integer -32768 to +32767 INTEGER 4 bytes ypical choice for integer -2147483648 to +2147483647 BIGINT 8 bytes large-range integer -9223372036854775808 to +9223372036854775807 DECIMAL variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point NUMERIC variable user-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point REAL 4 bytes variable-precision, inexact 6 decimal digits precision DOUBLE PRECISION 8 bytes variable-precision, inexact 15 decimal digits precision smallserial 2 bytes small autoincrementing integer 1 to 32767 serial 4 bytes autoincrementing integer 1 to 2147483647 BIGSERIAL 8 bytes large autoincrementing integer 1 to 9223372036854775807 int4range Range of integer int8range Range of bigint numrange Range of numeric Section 2.2: Date/ Time Types Name Storage Size Description Low Value High Value Resolution TIMESTAMP (without time zone) 8 bytes both date and time (no time zone) 4713 BC 294276 AD 1 microsecond / 14 digits TIMESTAMP (with time zone) 8 bytes both date and time, with time zone 4713 BC 294276 AD 1 microsecond / 14 digits DATE 4 bytes date (no time of day) 4713 BC 5874897 AD 1 day TIME (without time zone) 8 bytes time of day (no date) 00:00:00 24:00:00 1 microsecond / 14 digits TIME (with time zone) 12 bytes times of day only, with time zone 00:00:00+1459 24:00:00-1459 1 microsecond / 14 digits INTERVAL 16 bytes time interval -178000000 years 178000000 years 1 microsecond / 14 digits tsrange range of timestamp without time zone tstzrange range of timestamp with time zone daterange range of date

GoalKicker.com – PostgreSQL® Notes for Professionals 9 Section 2.3: Geometric Types Name Storage Size Description Representation point 16 bytes Point on a plane (x,y) line 32 bytes Infinite line {A,B,C} lseg 32 bytes Finite line segment ((x1,y1),(x2,y2)) BOX 32 bytes Rectangular box ((x1,y1),(x2,y2)) path 16+16n bytes Closed path (similar to polygon) ((x1,y1),...) path 16+16n bytes Open path [(x1,y1),...] polygon 40+16n bytes Polygon (similar to closed path) ((x1,y1),...) CIRCLE 24 bytes Circle <(x,y),r> (center point and radius) Section 2.4: Network Adress Types Name Storage Size Description CIDR 7 or 19 bytes IPv4 and IPv6 networks INET 7 or 19 bytes IPv4 and IPv6 hosts and networks macaddr 6 bytes MAC addresses Section 2.5: Character Types Name Description CHARACTER varying(n), varchar(n) variable-length with limit character(n), char(n) fixed-length, blank padded TEXT variable unlimited length Section 2.6: Arrays In PostgreSQL you can create Arrays of any built-in, user-defined or enum type. In default there is no limit to an Array, but you can specify it. Declaring an Array SELECT INTEGER[]; SELECT INTEGER[3]; SELECT INTEGER[][]; SELECT INTEGER[3][3]; SELECT INTEGER ARRAY; SELECT INTEGER ARRAY[3]; Creating an Array SELECT '{0,1,2}'; SELECT '{{0,1},{1,2}}'; SELECT ARRAY[0,1,2]; SELECT ARRAY[ARRAY[0,1],ARRAY[1,2]]; Accessing an Array By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with ARRAY[1] and ends with ARRAY[n]. --accesing a spefific element

GoalKicker.com – PostgreSQL® Notes for Professionals 10 WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT int_arr[1] FROM arr; int_arr --------- 0 (1 ROW) --sclicing an array WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT int_arr[1:2] FROM arr; int_arr --------- {0,1} (1 ROW) Getting information about an array --array dimensions (as text) WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT ARRAY_DIMS(int_arr) FROM arr; array_dims ------------ [1:3] (1 ROW) --length of an array dimension WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT ARRAY_LENGTH(int_arr,1) FROM arr; array_length -------------- 3 (1 ROW) --total number of elements across all dimensions WITH arr AS (SELECT ARRAY[0,1,2] int_arr) SELECT cardinality(int_arr) FROM arr; cardinality ------------- 3 (1 ROW) Array functions will be added

GoalKicker.com – PostgreSQL® Notes for Professionals 11 Chapter 3: Dates, Timestamps, and Intervals Section 3.1: SELECT the last day of month You can select the last day of month. SELECT (DATE_TRUNC('MONTH', ('201608'||'01')::DATE) + INTERVAL '1 MONTH - 1 day')::DATE; 201608 is replaceable with a variable. Section 3.2: Cast a timestamp or interval to a string You can convert a TIMESTAMP or INTERVAL value to a string with the TO_CHAR() function: SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'DD Mon YYYY HH:MI:SSPM'); This statement will produce the string "12 Aug 2016 04:40:32PM". The formatting string can be modified in many different ways; the full list of template patterns can be found here. Note that you can also insert plain text into the formatting string and you can use the template patterns in any order: SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, '"Today is "FMDay", the "DDth" day of the month of "FMMonth" of "YYYY'); This will produce the string "Today is Saturday, the 12th day of the month of August of 2016". You should keep in mind, though, that any template patterns - even the single letter ones like "I", "D", "W" - are converted, unless the plain text is in double quotes. As a safety measure, you should put all plain text in double quotes, as done above. You can localize the string to your language of choice (day and month names) by using the TM (translation mode) modifier. This option uses the localization setting of the server running PostgreSQL or the client connecting to it. SELECT TO_CHAR('2016-08-12 16:40:32'::TIMESTAMP, 'TMDay, DD" de "TMMonth" del año "YYYY'); With a Spanish locale setting this produces "Sábado, 12 de Agosto del año 2016". Section 3.3: Count the number of records per week SELECT DATE_TRUNC('week', <>) AS "Week" , COUNT(*) FROM <> GROUP BY 1 ORDER BY 1;

GoalKicker.com – PostgreSQL® Notes for Professionals 12 Chapter 4: Table Creation Section 4.1: Show table definition Open the psql command line tool connected to the database where your table is. Then type the following command: \d tablename To get extended information type \d+ tablename If you have forgotten the name of the table, just type \d into psql to obtain a list of tables and views in the current database. Section 4.2: Create table from select Let's say you have a table called person: CREATE TABLE person ( person_id BIGINT NOT NULL, last_name VARCHAR(255) NOT NULL, first_name VARCHAR(255), age INT NOT NULL, PRIMARY KEY (person_id) ); You can create a new table of people over 30 like this: CREATE TABLE people_over_30 AS SELECT * FROM person WHERE age > 30; Section 4.3: Create unlogged table You can create unlogged tables so that you can make the tables considerably faster. Unlogged table skips writing WRITE-ahead log which means it's not crash-safe and unable to replicate. CREATE UNLOGGED TABLE person ( person_id BIGINT NOT NULL PRIMARY KEY, last_name VARCHAR(255) NOT NULL, first_name VARCHAR(255), address VARCHAR(255), city VARCHAR(255) ); Section 4.4: Table creation with Primary Key CREATE TABLE person ( person_id BIGINT NOT NULL, last_name VARCHAR(255) NOT NULL, first_name VARCHAR(255), address VARCHAR(255), city VARCHAR(255), PRIMARY KEY (person_id)

GoalKicker.com – PostgreSQL® Notes for Professionals 13 ); Alternatively, you can place the PRIMARY KEY constraint directly in the column definition: CREATE TABLE person ( person_id BIGINT NOT NULL PRIMARY KEY, last_name VARCHAR(255) NOT NULL, first_name VARCHAR(255), address VARCHAR(255), city VARCHAR(255) ); It is recommended that you use lower case names for the table and as well as all the columns. If you use upper case names such as Person you would have to wrap that name in double quotes ("Person") in each and every query because PostgreSQL enforces case folding. Section 4.5: Create a table that references other table In this example, User Table will have a column that references the Agency table. CREATE TABLE agencies ( -- first create the agency table id SERIAL PRIMARY KEY, NAME TEXT NOT NULL ) CREATE TABLE users ( id SERIAL PRIMARY KEY, agency_id NOT NULL INTEGER REFERENCES agencies(id) DEFERRABLE INITIALLY DEFERRED -- this is going to references your agency table. )

GoalKicker.com – PostgreSQL® Notes for Professionals 14 Chapter 5: SELECT Section 5.1: SELECT using WHERE In this topic we will base on this table of users : CREATE TABLE sch_test.user_table ( id serial NOT NULL, username CHARACTER VARYING, pass CHARACTER VARYING, first_name CHARACTER varying(30), last_name CHARACTER varying(30), CONSTRAINT user_table_pkey PRIMARY KEY (id) ) +----+------------+-----------+----------+------+ | id | first_name | last_name | username | pass | +----+------------+-----------+----------+------+ | 1 | hello | world | hello | word | +----+------------+-----------+----------+------+ | 2 | root | me | root | toor | +----+------------+-----------+----------+------+ Syntax Select every thing: SELECT * FROM schema_name.table_name WHERE <condition>; Select some fields : SELECT field1, field2 FROM schema_name.table_name WHERE <condition>; Examples -- SELECT every thing where id = 1 SELECT * FROM schema_name.table_name WHERE id = 1; -- SELECT id where username = ? and pass = ? SELECT id FROM schema_name.table_name WHERE username = 'root' AND pass = 'toor'; -- SELECT first_name where id not equal 1 SELECT first_name FROM schema_name.table_name WHERE id != 1;

GoalKicker.com – PostgreSQL® Notes for Professionals 15 Chapter 6: Find String Length / Character Length To get length of "character varying", "text" fields, Use char_length() or character_length(). Section 6.1: Example to get length of a character varying field Example 1, Query: SELECT CHAR_LENGTH('ABCDE') Result: 5 Example 2, Query: SELECT CHARACTER_LENGTH('ABCDE') Result: 5

GoalKicker.com – PostgreSQL® Notes for Professionals 16 Chapter 7: COALESCE Coalesce returns the first none null argument from a set of arguments. Only the first non null argument is return, all subsequent arguments are ignored. The function will evaluate to null if all arguments are null. Section 7.1: Single non null argument PGSQL> SELECT COALESCE(NULL, NULL, 'HELLO WORLD'); COALESCE -------- 'HELLO WORLD' Section 7.2: Multiple non null arguments PGSQL> SELECT COALESCE(NULL, NULL, 'first non null', NULL, NULL, 'second non null'); coalesce -------- 'first non null' Section 7.3: All null arguments PGSQL> SELECT COALESCE(NULL, NULL, NULL); COALESCE --------