Essential Postgres Database Development using PostgreSQL (Rick Silva) (Z-Library)

Essential Postgres: Database Development using PostgreSQL Copyright 2020, 2021 © Rick Silva All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted, in any form without prior written permission of the author. Every effort has been made to ensure the accuracy of the information presented. However, the information contained in the book is sold without warranty, either express or implied. The author will not be held liable for any damages caused directly or indirectly by this book. The image of the elephant on the front cover by Ksyu Deniska/Shutterstock.com ISBN: 9798687854184 (paperback)

To Patti, Jacki, and Rj - the best family in the history of families.

TABLE OF CONTENTS Introduction Audience Conventions Ellipses Used in Examples Parens 1 Selecting Data from a Postgres Database Introducing Database Tables Introducing SQL Ordering Rows Null Values Formatting SQL Adding Comments to SQL Calling Postgres Functions The upper( ) Function The coalesce( ) Function Calling Aggregate Functions Using “Group By” with Aggregate Functions A Rule of Thumb for Using “Group By” Using “Having” in SQL Don’t Repeat Yourself Joining Database Tables Table Aliasing Types of Joins Inner Joins Outer Joins Cross Joins Self Joins Column Aliasing

Alternate Join Syntax Using Parens Joining with “Using” Instead of “On” Old-School SQL Complex Joins with Multiple Tables. Complex SQL Statements with Inner and Outer Joins String Concatenation Comparing Values in SQL Greater Than Greater Than or Equal to Less Than Less Than or Equal to Not Equal In Between Like Is Null Not Subqueries Correlated Subqueries Subqueries that Return One Row Distinct Limit 2 Creating and Changing Database Tables Data Types Commonly-Used Data Types Text Integer Numeric Date Time Without Time Zone Time With Time Zone Timestamp Without Time Zone Timestamp With Time Zone Boolean Character

Less Commonly-Used Data Types Numbers Formatted Data Serial Bits and Bytes Geometry-Related Specialty Addresses Miscellaneous Constraints Primary Keys Single Column Primary Keys Multi-Column Primary Keys Foreign Keys Not Null Columns Unique Check Indexes Dropping Tables Altering Tables Adding and Modifying Data Inserting Data The Number of Columns Must Match the Number of Values Always List your Columns Inserting Data from Another Table Updating Data Deleting Data The Delete Statement The Truncate Statement 3 More Database Objects Views Using Views to Hide Sensitive Data Materialized Views Sequences Creating Functions Parameters and Arguments SQL vs. PL/pgSQL

Writing Functions with SQL Writing Functions with PL/PgSQL Return Types Dollar Quoting with Tags Parameters by Position vs Name Parameter Modes: In, Out, Inout, and Variadic Procedures Triggers Audit Triggers Creating the Audit Table Creating the Trigger Function Creating the Trigger Watching it Work Auditing Updates and Deletes Special Variables for Triggers Altering Triggers Dropping Triggers Disabling Triggers Enabling Triggers 4 Built-In Functions Aggregate Functions abs( ) upper( ) lower( ) initcap( ) round( ) trunc( ) ceil( ) floor( ) length( ) substr( ) trim( ) ltrim( ) rtrim( ) left( ) right( )

lpad( ) replace( ) format( ) extract( ) exp( ) pi( ) power( ) random( ) position( ) version( ) 5 Tips and Tricks PgAdmin Connecting PgAdmin to a Postgres Server Production Servers and Development Servers Creating the Server Connections The Tree Control Pane The Query Tool Changing a Table’s Column Order Debugging with Raise All Balls Casting Data Types Functions for Casting to_date( ) to_timestamp( ) to_char( ) to_number( ) Using Parens for Math Transferring Data To and From Files Loading Unstructured Data Common Table Expressions Calling Postgres from Programming Languages Final Thoughts

INTRODUCTION PostgreSQL is a popular free and open source relational database management system. It is also widely known as “Postgres”. I will refer to it by its more informal alias, “Postgres”, in this book rather than as “PostgreSQL” because that’s the way I hear it said most often in the real world. A relational database management system (RDMS) or “relational database”, at its heart, is a system to store and retrieve data from a database. The term “relation” describes a structure that stores data in a grid of rows and columns. A relation is essentially a database table, so a relational database is a database system that stores data in tables that have rows and columns. Structured Query Language (SQL) is the language we use to interact with relational databases, including Postgres. SQL allows us to store, modify, and delete data from our database. It also allows us to create and remove tables, query our data, and much more. Postgres is a great technology to learn. It is a powerful, secure, and fast relational database system that is growing in popularity. It is a mature, stable database that has been around for over 30 years. Postgres runs on a wide variety of operating systems, including Linux, Windows, macOS, and it even runs in the cloud. Postgres helps you to maintain the integrity of your data. It has a robust set of features, and is extensible if you ever need to do something unconventional that it doesn’t already do. This book, Essential Postgres, will give you exposure to the most used – and useful – parts of Postgres development, as well as tips and insights that I have picked up over my years of working with Postgres. Writing SQL statements, creating tables, functions, triggers, views, and data integrity are explained.

AUDIENCE This book is suitable for anybody who wants to learn to use Postgres. That includes folks who are new to Postgres and databases, intermediate-level developers who would like a refresher, and even seasoned software developers who are transitioning from another database to Postgres. This book is for anybody who is interested in learning about the essentials of using the Postgres database. The aim of this book is to show you how to do things with the Postgres database. The book is short on theory and long on examples. If you are the type of person who likes that sort of thing, read on. Since this book focuses on Postgres development rather than administration, if you are a Postgres Database Administrator (DBA) you may want to choose another book. This book does not discuss backup, recovery, security or other DBA-related issues.

CONVENTIONS In this book, I have made some stylistic choices that you might want to be aware of. ELLIPSES USED IN EXAMPLES There are several examples in this book that show data being returned by the Postgres database. In cases where many rows are returned, I have shown a few of the rows and added an ellipsis (“…”) rather than showing all the results and making the book 900 pages long. When you see “…” be aware that I am not showing all of the results in order to save space. For example: president_id president_name president_party 1 George Washington null 2 John Adams Federalist 3 Thomas Jefferson Democratic-Republican … … … 44 Barack Obama Democrat 45 Donald Trump Republican 46 Joe Biden Democrat In this case, a full set of all 46 presidents was returned from the database, but I omitted presidents 4 – 43 for the sake of brevity. PARENS I often refer to parentheses as “parens” because that’s the way I most often hear them referred to among developers. Also, I add parens to the end of function names. For example, Postgres has a function called “upper” that I write as “upper( )”. You would say this aloud as “the upper function”, but in this book I add the “parens” to the end of function name to make it clear that “upper( )” is a function. Functions are explained later in this book, and we’ll see what the “parens” are for then.

1 SELECTING DATA FROM A POSTGRES DATABASE INTRODUCING DATABASE TABLES In Postgres, data is stored in database tables. Think of a table as a grid of rows and columns where data can be saved. You can create your own tables and choose the names of the columns that you want, and specify what type of data they should contain. Each record is saved as a row in the table. For example, here is a table called us_president that has been defined with three columns: president_id, president_name, and president_party. president_id president_name president_party 1 George Washington null 2 John Adams Federalist 3 Thomas Jefferson Democratic-Republican 4 James Madison Democratic-Republican 5 James Monroe Democratic-Republican 6 John Quincy Adams Democratic-Republican 7 Andrew Jackson Democrat 8 Martin Van Buren Democrat 9 William Harrison Whig 10 John Tyler Whig 11 James Polk Democrat 12 Zachary Taylor Whig 13 Millard Fillmore Whig 14 Franklin Pierce Democrat 15 James Buchanan Democrat 16 Abraham Lincoln Republican 17 Andrew Johnson Democrat

18 Ulysses Grant Republican 19 Rutherford Hayes Republican 20 James Abram Garfield Republican 21 Chester Arthur Republican 22 Grover Cleveland Democrat 23 Benjamin Harrison Republican 24 Grover Cleveland Democrat 25 William McKinley Republican 26 Theodore Roosevelt Republican 27 William Taft Republican 28 Woodrow Wilson Democrat 29 Warren Harding Republican 30 Calvin Coolidge Republican 31 Herbert Hoover Republican 32 Franklin Roosevelt Democrat 33 Harry Truman Democrat 34 Dwight Eisenhower Republican 35 John Kennedy Democrat 36 Lyndon Johnson Democrat 37 Richard Nixon Republican 38 Gerald Ford Republican 39 Jimmy Carter Democrat 40 Ronald Reagan Republican 41 George Herbert Walker Bush Republican 42 Bill Clinton Democrat 43 George W. Bush Republican 44 Barack Obama Democrat 45 Donald Trump Republican 46 Joe Biden Democrat This table contains 46 rows of data, one row for each president. When naming database tables, it’s a good idea to stick with singular names like us_president rather than plural names like us_presidents. Although Postgres will allow you to name tables in the plural form, table names should be singular by convention. INTRODUCING SQL In order to interact with Postgres, you will use Structured Query Language (SQL). You will use SQL often for interacting with Postgres. Using SQL, you

can create tables, show the contents of tables, create new rows, update values, and much more. For example, you could see the data in our us_president table by using this SQL select statement. select president_id, president_name, president_party from us_president; This select statement returns data from the us_president database table and displays it for you. Here we chose to select the president_id, president_name, and president_party columns. Note that you should type a semi-colon at the end of your SQL statements. The results are: president_id president_name president_party 1 George Washington null 2 John Adams Federalist 3 Thomas Jefferson Democratic-Republican … … … 44 Barack Obama Democrat 45 Donald Trump Republican 46 Joe Biden Democrat The query returns all 46 US presidents (although I have not shown presidents 4 – 43 for brevity). Now let’s say you want to show only the rows for Republicans in the table. You could make a change to the SQL and add a “where” clause. select president_id, president_name, president_party from us_president where president_party = 'Republican'; president_id president_name president_party 16 Abraham Lincoln Republican

18 Ulysses Grant Republican 19 Rutherford Hayes Republican 20 James Garfield Republican 21 Chester Arthur Republican 23 Benjamin Harrison Republican 25 William McKinley Republican 26 Theodore Roosevelt Republican 27 William Taft Republican 29 Warren Harding Republican 30 Calvin Coolidge Republican 31 Herbert Hoover Republican 34 Dwight Eisenhower Republican 37 Richard Nixon Republican 38 Gerald Ford Republican 40 Ronald Reagan Republican 41 George Herbert Walker Bush Republican 43 George W. Bush Republican 45 Donald Trump Republican The SQL query now returns 19 rows because there are 19 Republicans in the table. Since we are now retrieving a list of Republicans only, let’s change the SQL to not select the president_party column. Let’s select just the president_id and president_name columns. select president_id, president_name from us_president where president_party = 'Republican'; president_id president_name 16 Abraham Lincoln 18 Ulysses Grant 19 Rutherford Hayes 20 James Garfield 21 Chester Arthur 23 Benjamin Harrison 25 William McKinley 26 Theodore Roosevelt 27 William Taft

29 Warren Harding 30 Calvin Coolidge 31 Herbert Hoover 34 Dwight Eisenhower 37 Richard Nixon 38 Gerald Ford 40 Ronald Reagan 41 George Herbert Walker Bush 43 George W. Bush 45 Donald Trump The SQL query didn’t show the party_name column because we didn’t select it, and again, the query returned 19 rows. ORDERING ROWS Now let’s display all the rows and columns in the table ordered by political party and then by the president’s ID. To do that, we can add an “order by” clause to our SQL statement. select president_id, president_name, president_party from us_president order by president_party, president_id; president_id president_name president_party 7 Andrew Jackson Democrat 8 Martin Van Buren Democrat 11 James Polk Democrat 14 Franklin Pierce Democrat 15 James Buchanan Democrat 17 Andrew Johnson Democrat 22 Grover Cleveland Democrat 24 Grover Cleveland Democrat 28 Woodrow Wilson Democrat 32 Franklin Roosevelt Democrat 33 Harry Truman Democrat 35 John Kennedy Democrat 36 Lyndon Johnson Democrat 39 Jimmy Carter Democrat

42 Bill Clinton Democrat 44 Barack Obama Democrat 46 Joe Biden Democrat 3 Thomas Jefferson Democratic-Republican 4 James Madison Democratic-Republican 5 James Monroe Democratic-Republican 6 John Quincy Adams Democratic-Republican 2 John Adams Federalist 16 Abraham Lincoln Republican 18 Ulysses Grant Republican 19 Rutherford Hayes Republican 20 James Garfield Republican 21 Chester Arthur Republican 23 Benjamin Harrison Republican 25 William McKinley Republican 26 Theodore Roosevelt Republican 27 William Taft Republican 29 Warren Harding Republican 30 Calvin Coolidge Republican 31 Herbert Hoover Republican 34 Dwight Eisenhower Republican 37 Richard Nixon Republican 38 Gerald Ford Republican 40 Ronald Reagan Republican 41 George Herbert Walker Bush Republican 43 George W. Bush Republican 45 Donald Trump Republican 9 William Harrison Whig 10 John Tyler Whig 12 Zachary Taylor Whig 13 Millard Fillmore Whig 1 George Washington null Adding the “order by” clause had the effect of showing us the rows ordered alphabetically by the president_party column and then ordered by the president_id column in lowest to highest order numerically. Democrats were shown first because the string “Democrat” comes alphabetically before the other political parties (“Democratic-Republican”, “Federalist”, “Republican”, and “Whig”). Of the Democrat presidents, Andrew Jackson appeared before Martin Van Buren because Jackson’s president_id value was lower.

You can also specify whether you want to order in ascending or descending order. That is, in low-to-high order or in high-to-low order. select president_id, president_name, president_party from us_president order by president_party desc, president_id asc; president_id president_name president_party 1 George Washington null 9 William Harrison Whig 10 John Tyler Whig 12 Zachary Taylor Whig 13 Millard Fillmore Whig 16 Abraham Lincoln Republican 18 Ulysses Grant Republican … … … 2 John Adams Federalist 3 Thomas Jefferson Democratic-Republican 4 James Madison Democratic-Republican 5 James Monroe Democratic-Republican 6 John Quincy Adams Democratic-Republican 7 Andrew Jackson Democrat 8 Martin Van Buren Democrat 11 James Polk Democrat 14 Franklin Pierce Democrat 15 James Buchanan Democrat 17 Andrew Johnson Democrat … … … The query returned all 46 rows (Again, I haven’t shown all the rows for brevity). The data is displayed in descending (reverse) order for the president_party column and then displayed in ascending (low-to-high) order for the president_id column. If you don’t specify descending or ascending (which can be abbreviated “desc” or “asc”), Postgres defaults to ascending order and you will see your results in low-to-high order. NULL VALUES

What’s going on with George Washington? Why is his political party listed as null? Null is a special value in Postgres that represents an empty value. George Washington was never a member of a political party so his political_party value is set to null in our database table. To show only the rows for presidents that have a null political_party, you could run this query: select president_id, president_name, president_party from us_president where president_party is null; That SQL query would return results for only George. president_id president_name president_party 1 George Washington null On the other hand, if you wanted to exclude rows that have a null president_party, you would run this query: select president_id, president_name, president_party from us_president where president_party is not null; All presidents except George Washington would be returned by this query because we specified that we want to see rows where the president_party column is not null. Null is a special value in Postgres. Null is not the same as zero, and it is not the same as an empty string or a space character. It is its own value, and Postgres has special syntax to help you deal with it. The word “syntax”, in software development speak, means the words and symbols you can use that are part of the language. Some of the special syntax