gem-hunter
Language: PLpgSQL
Learn PostgreSQL, published by Packt
Build and manage high-performance database solutions using PostgreSQL 12 and 13
PostgreSQL is one of the fastest-growing open source object-relational database management systems (DBMS) in the world. As well as being easy to use, it’s scalable and highly efficient. In this book, you’ll explore PostgreSQL 12 and 13 and learn how to build database solutions using it. Complete with hands-on tutorials, this guide will teach you how to achieve the right database design required for a reliable environment.
This book covers the following exciting features:
If you feel this book is for you, get your copy today!
This Postgres book is for anyone interested in learning about the PostgreSQL database from scratch. Anyone looking to build robust data warehousing applications and scale the database for high-availability and performance using the latest features of PostgreSQL will also find this book useful. Although prior knowledge of PostgreSQL is not required, familiarity with databases is expected.
The book is divided into five main parts. The following is a list of the book chapters.
Every chapter will have the following main structure:
This repository contains ongoing stuff related to the book, including code examples.
Every chapter has its own folder named after the chapter number, for instance Chapter01 for the very first chapter.
In order to ease the execution of the code examples by readers, every chapter will have a set of source scripts that the reader can immediatly load into her database.
Every file is named after the its type, for example .sql for an SQL script or a collection of SQL statements.
Any picture will be named with the pattern Chapter<CC>_picture<PP>.<type> where:
CC is the chapter number;PP is the picture number as listed within the chapter;type is a suffix related to the picture file type (e.g., png for a Portable Network Graphic image).Images could appear differently from the printed book due to graphical needs.
We also provide a PDF file that has color images of the screenshots/diagrams used in this book.
In the book code listings and examples, the command prompts are one of the two that follows:
$ stands for an Unix shell prompt (like Bourne, Bash, Zsh);forumdb=> stands for the psql(1) command prompt when an active connection to the database is opened.As an example, the following is a command issued on the operating system:
$ sudo service postgresql restart
while the following is a query issued within an active database connection:
forumdb=> SELECT CURRENT_TIMESTAMP;
Whenere there is the need to execute a command or a statement with administrative privileges, the command prompt will reflect it using a # sign as the end part of the command prompt. For example, the following is an SQL statement issued as PostgreSQL administrator:
forumdb=# SELECT pg_terminate_backend( 987 );
ùPlease note the presence of the # in the forumdb=# prompt, as opposed to the > sign in the normal user forumdb=> prompt.
In the case a command on the operating system must be run with superuser (root) privileges, the command will be run via sudo(1), as in:
$ sudo initdb -D /postgres/12
and therefore in this case the command prompt will not change, rather the presence of the sudo(1) command indicates root privileges are required.
The book is built over an example database that implements an online forum storage. In order to be able to execute any example of any chapter, the reader has to initialize the forum database.
The scripts in the folder setup, executed in lexicographically order, implement the example database and setup the environment so that other examples can be run against the database.
In particular, in order to get the database structure as shown in Chapter 4 and the followings, you have to executed something has follows:
psql -U <your-username> -h <database-host> < setup/00-forum-database.sql
where
your-username is a PostgreSQL username of choice;database-host is the host the database is running on, if different from localhost.The end result will be to have the forumdb created and populated with tables.
You can also invoke the script interactively from within a psql connection, such as:
psql -U <your-username> -h <database-host> template1 template1=> \i setup/-00-forum-database.sql
The simplest form to get the example database up and running is the following one (assuming you are running PostgreSQL locally):
$ psql -U postgres template1 -c 'CREATE ROLE forumdb_user WITH LOGIN CREATEDB;' $ psql -U forum_user template1 < setup/00-forum-database.sql
| Chapter | Software required | OS required |
|---|---|---|
| 1 | PostgreSQL 12 - 13 | Linux OS / FreeBSD |
Luca Ferrari has been passionate about computer science since the Commodore 64 era, and today holds a master's degree (with honors) and a Ph.D. from the University of Modena and Reggio Emilia. He has written several research papers, technical articles, and book chapters. In 2011, he was named an Adjunct Professor by Nipissing University. An avid Unix user, he is a strong advocate of open source, and in his free time, he collaborates with a few projects. He met PostgreSQL back in release 7.3; he was a founder and former president of the Italian PostgreSQL Community (ITPUG). He also talks regularly at technical conferences and events and delivers professional training.
Enrico Pirozzi has been passionate about computer science since he was a 13-year-old, his first computer was a Commodore 64, and today he holds a master's degree from the University of Bologna. He has participated as a speaker at national and international conferences on PostgreSQL. He met PostgreSQL back in release 7.2, he was a co-founder of the first PostgreSQL Italian mailing list and the first Italian PostgreSQL website, and he talks regularly at technical conferences and events and delivers professional training. Right now, he is employed as a PostgreSQL database administrator at Nexteam (Zucchetti Group S.p.a.).
Click here if you have any feedback or suggestions.
psql -l. Unluckily, the first line of command output, that reports List of databases, has been placed on the right side of the very same line of the command. In order to reproduce the command, you need to copy and execute only the psql -l command (reported errata CA162683 on 2021-09-30);j_tags_posts should be j_posts_tagsinserted_post should be let's create a table named inserted_posts;row_number shown in the query output. The correct query to use is select category,count(*) over w , row_number() over w from posts WINDOW w as (partition by category) order by category;. that reports the output as shown in the section output.LAST_VALUE Window Function): the query select category, row_number() over w, title, last_value(title) over w from posts WINDOW w as (partition by category order by category) order by category; should be select category, row_number () over w, title, last_value (title) over w from posts WINDOW w as (partition by category order by title) order by category;. For more information about this error, see the issue #63 is missing the FROM clause, and should be select pk,tag,length(tag),octet_length(tag),char_length(tag) FROM new_tags;The installation example in the Chapter 1, with particular regard to the section Installing PostgreSQL 12 on GNU/Linux Debian, Ubuntu and derivatives refers to the adoption of the PostgreSQL Global Development Groupd (PGDG) repositories, as specified at the first step in the example.
Since PostgreSQL 10, both the GNU Debian and Ubuntu operating system families have renamed the PostgreSQL Contrib module removing the version number suffix, therefore the right package to install is postgresql-contrib instead of the one presented in the installation instruction in the first chapter, wrongly named postgresql-contrib-12.
The package postgresql-contrib-12 is a virtual package that refers to postgresql-12, threfore to the whole server and not to the contrib module.
If you have already purchased a print or Kindle version of this book, you can get a DRM-free PDF version at no cost.
Simply click on the link to claim your free PDF.