Use Postgres JSON Type and Aggregate Functions to Map Relational Data to JSON

Posted on April 22 2015 05:11 AM by jatten in Postgres, Database   ||   Comments (0)

name-that-tile-cropped

Postgres is just too cool. Because Postgres can work with both JSON and arrays as first-class data types, it is possible to perform some very handy mappings on the server end which would become potential performance bottlenecks on the client side.

When working in Node.JS, it might be handy to do some heavy lifting via Postgres to reduce some n+1 / lazy loading issues, especially when pulling data for display. Using some of Postgres’ in-built JSON  functions in conjunction with the JSON data type, we can compose ready-to-use JSON objects before returning the result.

In this post, we’ll take a quick look at the row_to_json() function, the array_to_json() function, and the json_agg() function, and see how between these three we can shape relational data on the database side, possibly in a much more performant manner than we might within our application.

First off, lets’ aggregate some row data into a JSON object.

Example Data from Chinook (modified)

For the examples which follow, I will be using a slightly modified version of the Chinook database, which I tweaked to be a little more Postgres-friendly.

Consider the following tables from Chinook (with Sample Data added):

The Chinook Artists and Albums Table:
CREATE TABLE albums
(
    id int DEFAULT nextval('albums_id_seq'::regclass) NOT NULL,
    title VARCHAR(160) NOT NULL,
    artist_id INT NOT NULL,
    CONSTRAINT pk_albums PRIMARY KEY  (id)
);
CREATE TABLE artists
(
    id int DEFAULT nextval('artists_id_seq'::regclass) NOT NULL,
    name VARCHAR(120),
    CONSTRAINT pk_artists PRIMARY KEY  (id)
);

 

For our purposes the artists and albums tables, populated as they are, will do admirably.

Transform Row Data to a JSON Object Using row_to_json()

We can use the Postgres row_to_json() function in a straightforward manner to take a full row of data, and squeeze it into a JSON object before the result set is returned.

For example, let’s grab all the artist records, and return rows of JSON:

Transform Artist Records to JSON:
select row_to_json(artists)
from (
  select * from artists
) as artists

 

Running the query above gives output like so:

Result Set from row_to_json on the Artists Table:
{"id":1,"name":"AC/DC"}
{"id":2,"name":"Accept"}
{"id":3,"name":"Aerosmith"}
// ... a bunch more artist records ...

 

We could modify the above, and add some criteria so that we are limiting the records to to a specific artist like so:

Transform a Specific Artist Record to JSON:
select row_to_json(artists)
from (
  select * from artists where id = 12
) as artists

 

Running the above with criteria set to artist id 12 (which happens to be Black Sabbath) returns the following predictable result:

Result Set from row_to_json() with Criteria:
{"id":12,"name":"Black Sabbath"}

 

Now, each artist can have any number of albums in the albums table. Wouldn’t it be nice if we could grab the albums for a specific artist, and transform them into JSON array?

Aggregate Rows into a JSON Array Using the json_agg() Function

Postgres offers us the json_agg() function, which takes an input values and aggregates them as a JSON array:

For example, we might want to aggregate the album records for a specific artist into a JSON array:

Aggregate Album Records into JSON Array using json_agg():
select json_agg(albums)
from (
  select * from albums where artist_id = 12
) as albums;

 

Running this query returns the following:

Result Set from json_agg() on the Albums Table:
[{"id":16,"title":"Black Sabbath","artist_id":12}, 
 {"id":17,"title":"Black Sabbath Vol. 4 (Remaster)","artist_id":12}]

 

There we go… we now have an array of albums for a specific artist. Now, can we expand on this, and return the artist record as a JSON object, including an albums property represented by such an array, containing all the albums for each artist?

Why, yes, yes we can!

Aggregate Parent and Child Records into a singe JSON Object in the Result Set

Let’s return each row as a complete JSON object, such that each artist record is represented as JSON, and contains an albums property which itself contains an array of album objects.

We can aggregate the album records for each artist using json_agg() in a correlated subquery with the artists table, and then we can pass each row to the row_to_json() function to transform the result into complete JSON object, ready for use in our application.

Per usual with correlated subqueries, keep your eye on the numerous database object aliases here. Naming can get a little funky…

Transform and Aggregate Artist and Album Records into JSON Objects:
select row_to_json(art) as artists
from(
  select a.id, a.name, 
  (select json_agg(alb)
  from (
    select * from albums where artist_id = a.id
  ) alb
) as albums
from artists as a) art;

 

The result of the query above:

Result Set from Correlated Transform/Aggregate Query:
  {"id":1,"name":"AC/DC","albums":[
    {"id":1,"title":"For Those About To Rock We Salute You","artist_id":1}, +
    {"id":4,"title":"Let There Be Rock","artist_id":1}
  ]}
  {"id":2,"name":"Accept","albums":[
    {"id":2,"title":"Balls to the Wall","artist_id":2},                    +
    {"id":3,"title":"Restless and Wild","artist_id":2}
  ]}
  {"id":3,"name":"Aerosmith","albums":[
    {"id":5,"title":"Big Ones","artist_id":3}
  ]}
  // ... a bunch more records ...

 

Voila – we have ready-to-use JSON to return to our application. Most importantly, we have completely assembled artist objects, with albums available as an array via the albums property on each artist

The Tip of the Iceberg

Postgres offers a wealth of innovative features such as the JSON / JSONB data type, the array data type, HStore, and plenty of functionality built around each. What we have seen here is a simple solution to one of the common challenges presented by data access and managing parent/child relationships – the lazy loading / n+1 problem.

By flexing the power of Postgres’ JSON type and associated functions, we have blurred the line between document and relational storage.

If you haven’t taken Postgres for a spin yet, I strongly suggest you do. There’s more where that came from.

Additional Resources and Items of Interest

 

 

Posted on April 22 2015 05:11 AM by jatten     

Comments (0)

Installing and Configuring PostgreSQL 9.4 on Linux Mint/Ubuntu

Posted on April 19 2015 07:41 AM by jatten in Postgres, Database, Linux, CodeProject   ||   Comments (0)

psql-header

Installing and configuring PostgreSQL on a Linux box is either simple, because you are a Linux and/or Postgres expert, or not so simple, because you are new(er) to Linux, Postgres, or both. Over the past year, I have resided firmly in the latter camp. I am a huge fan of the Postgres database platform, and I have been slowly but steadily trying to improve my Linux chops.

If you are an experienced Linux user, or a PostgreSQL DBA, this is not the post for you, although your feedback and constructive criticism are most welcome – if you see something amiss, please do let m eknow in the comments, or via email.

I should note here that, where practical, I do as much as I can on Linux from the terminal. While the desktop/GUI is handy for some tasks, I am doing my level best to become proficient with the terminal in Linux. I strongly recommend doing the same. Which is how we’re going to do things here.

The installation process for Postgres on Ubuntu or Linux Mint is, like many things in the Linux world, less than intuitive for new users. As much for my own sake as anyone else’s, I’m going to walk through the steps to getting Postgres installed and configured on a Linux box.

Why Postgres?

PostgreSQL is a fantastic database platform. Postgres is open source, cross-platform, free, and offers an amazing feature set which, in my mind, exceeds those of its principle peers in the relational database space.

Postgres offers all of the (mostly) standards-compliant SQL/relational database feature you would expect, plus a host of exciting and innovative features. Highlights include a JSON datatype (and also JSONB!), an array datatype, and the new HStore type, which essentially allows the specification of a column as containing a list of key/value pairs. We’ll take a tour of PostgreSQL in another post, but first, let’s get the thing installed and running.

Installation Steps for PostgreSQL on Linux Mint or Ubuntu

At the moment, my preferred Linux distro is Linux Mint 17 (“Quiana”) with the Cinnamon desktop. This is a long-term support release of the Linux Mint distro, very stable, and an excellent place to start. If you do not have a dedicated Linux machine, it is simple enough to spin up a VM using Virtual Box

As of this writing, the most recent version of PostgreSQL is version 9.4, which brought with it some very cool features such as full JSONB support. However, the 9.4 release is not available directly using the Advanced Packaging Tool (APT) or the Linux Mint/Ubuntu Software Manager.

Fortunately, the PostgreSQL Global Development Group (PGDB) maintain an APT repository of PostgreSQL packages for Debian and Ubuntu-derived Linux distros.

Before we can install Postgres, we need to add the package source for the distro we are using. In my case, I am using Linux Mint 17, which is derived from, and compatible with, the Ubuntu 14.04 (“Trusty Tahar”) release. We’ll see why this matters in a moment.

Add the Postgres Package Source for Your Linux Release

We need to create a sources file reflecting the proper Postgres source for our particular distro. In my case, as noted above, we need the source compatible with the “Trusty” release of Ubuntu. So we can do this from the terminal to add the file (make sure you use sudo in all of the following steps):

Add the PGDB APT Source file From the Terminal:
~ $ sudo touch /etc/apt/sources.list.d/pgdg.list

 

Now that the file exists, open in your editor of choice (we’ll use gedit here):

Open the pgdg.list File in gedit (use sudo):
~ $ sudo gedit /etc/apt/sources.list.d/pgdg.list

 

then add the following line in gedit and save (where I used “trusty” below, use the name of your release for Ubuntu, or the corresponding Ubuntu release if you you are using Linux Mint):

Add the Postgres Package Repository and Specify Your Distro Release:
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

 

Save, and close gedit.

Alternately, we can achieve all of the above in one shot from the terminal like so (take note of the placement of single and double quotes here…):

Add the Package Source in one multi-line Terminal Command:
~ $ sudo sh -c \
'echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > \
/etc/apt/sources.list.d/pgdg.list'

 

Add the Postgres Package Repository Key

Next, add the package repository key:

Add the Postgres Package Repository Key:
~ $ sudo apt-get install wget ca-certificates
~ $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

 

Update, Upgrade, and Install Postgres

Then, we need to update our package sources:

Update Package Sources:
~ $ sudo apt-get update

 

Once that’s done, we need to upgrade packages to the latest versions:

Upgrade Packages:
~ $ sudo apt-get upgrade

 

Note, this can be a long process. Also, you may be prompted at several points to make some choices about configuration items. Specifically, you may informed that this that or the other configuration file has been changed, and asked if you want to keep your original version, or replace with the package maintainer’s version. Select “Y” to accept the package maintainer’s version in these cases.

When the upgrade process finishes, we are ready to install Postgres (we’ll also include pgadmin3):

Install Postgres:
~ $ sudo apt-get install postgresql-9.4 pgadmin3

 

This shouldn’t take long (especially compared to the installation times for certain other GUI – installer-based database platforms. I’m looking at you, SQL Server). Once the installation completes, we’re ready to configure our new database.

Configuring Postgres for Use

We’ve now installed both PostgreSQL and the database management utility Pg Admin 3. Next, we should understand a few things about how PostgreSQL works, out of the box.

The Postgres User

When PostgreSQL was installed, a system user account named postgres was created. with a matching user account in Postgres. By default, the postgres user account is not configured with a password, so it is not possible to log into the server using the postgres user account without first creating a password for it. This postgres account has an all-access pass on your postgres database server, permission-wise. The postgres user account is analogous to the sa account in SQL Server. For security reasons, it is recommended that a password not be created for the postgres account.

The Postgres Database

PostgresSql is installed with a default database named…wait for it… postgres. From the PostgreSQL documentation:

Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the "template1" and "postgres" databases . . .

. . . The postgres database is a default database meant for use by users, utilities and third party applications.

For the most part, we use the postgres database for admin purposes, and create new databases on the PostgreSQL server to suit our needs.

The psql Command Line Utility

PostgreSQL includes psql, a command line utility for managing your databases and server. While a GUI-based utility such as pgadmin3 is often easier to use in the day-to-day, the command line utilty psql is also handy. Psql offers complete control of your Postgres system from the terminal, including the ability to execute SQL queries.

Also, we need to use psql to perform our initial configuration, and to create an initial database super user.

Create a Super User Account

Since we will not be creating a password for the postgres user account, we need a super-user account in order to work with our database in the day-to-day.

To do this, we will gain access to the postgres account through your system root user, and then use that postgres account to create a new super-user account on your Postgres installation which can be regulated more effectively. As an example, from my own machine (comments denoted by ##):

Access the Postgres User Through Root:
## switch user to root:
xivsolutions@mint-vm ~ $ su -
Password: 
## switch user to postgres:
mint-vm ~ # su - postgres
postgres@mint-vm ~ $ 

 

As we can see, we now have a prompt for the postgres user. We can now log in to the default postgres database and, using psql, create a super user account for ourselves:

To get into the psql command line utility, we type the following:

Enter the psql Command Line Utility:
postgres@mint-vm ~ $ psql

 

Now, from the psql prompt, enter the following. Note the name you specify for your super-user account should match the system user account you plan to use to manage your Postgres Installation (use your own user account name in place of youruseraccount here):

Create a New Super User from the psql Prompt:
postgres=# CREATE USER youruseraccount
postgres-# WITH SUPERUSER CREATEDB CREATEROLE
postgres-# PASSWORD 'userAccountPassword';

 

Notice in the above we can enter multiple lines of SQL. The SQL is not executed until we enter a semi-colon followed by enter. Which means, the semi-colon matters!

Now, we can exit psql, exit the postgres user, and exit root like so:

Return to your normal user account:
postgres=# \q
postgres@mint-vm ~ $ exit
logout
mint-vm ~ # exit
logout
xivsolutions@mint-vm ~ $ 

 

With that, we should now be able to log in using psql and make sure everything is wired up correctly.

Log In Using Psql and Create a Test Database

Now, just to make sure everything is working correctly, let’s log in with psql using our new super user account and create a quick test database:

Log-In Using the New Super-User Account:
xivsolutions@mint-vm ~ $ psql postgres
psql (9.4.1)
Type "help" for help.
postgres=# 

 

Note in the above, we specified the postgres default database when we logged in, since there aren’ tyet any other databases to connect to. We’ll use the default postgres as our admin platform, create a new database, and then connect to the new database to test things out.

So, let’s create a database to play with (once again, make sure to end the SQL statement with a semi-colon!):

Create a Test Database Using Psql:
postgres=# CREATE DATABASE test_db WITH OWNER xivsolutions;

 

Now that we have our own database to mess with, use the \connect command to switch psql to that:

Switch the Active Psql Connection to the new Test Database:
postgres=# \connect test_db;
You are now connected to database "test_db" as user "xivsolutions".
test_db=# 

 

Now, let’s whip up a quick and dirty table, insert some data, and query:

Create Table, Insert Data, and Select Query Using Psql:
test_db=# CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT);
test_db=# INSERT INTO products (name) VALUES ('Brass Widgets');
INSERT 0 1
test_db=# SELECT * FROM products;
 id |     name      
----+---------------
  1 | Brass Widgets
(1 row)

 

This is a quick example of what can be done from the psql command line. psql is a powerful tool, and is very much worth exploring. While GUI-based tools like PG Admin 3 and others certainly can be helpful, gaining a certain level of comfort working with Postgres from the terminal will save you time in many cases.

That said, let’s configure PG Admin 3, so we can have our cake and eat it too.

Configure PG Admin 3 for Local Connections

PG Admin 3 is a free, open source GUI database management tool for Postgres. While the GUI itself is not as polished as some, all the essentials are there. We already installed PG Admin 3 when we installed Postgres itself, so let’s take a look.

First off, open PG Admin 3from the terminal:

Open PG Admini 3:
xivsolutions@mint-17 ~ $ pgadmin3

 

You will probably see something like this:

The PG Admin 3 GUI After Opening:

pgadmin-gui-default

Before we can do much of anything here, we need to add our new database server. Since we are working on our local machine, we’ll go ahead and add a local server, which points to localhost. Use File –> Add Server… to open the Server Registration Dialog, and you should see something like the following:

Register your Postgres Server with PG Admin 3:

pgadmin3-new-server-dialog

Note the default values you see in the above may differ slightly on your machine. We’re going to provide a name by which our local server will be known, specify the host, and make sure that the User Name matches our new Super User name (which in most cases should match our system user name, although this is not a requirement).

As an example, on my machine, I set things up like so:

Enter PG Admin Server Registration Items for Local Postgres Database:

pgadmin3-register-server-completed

In the above note that I deselected the “Store Password” option. In general, storing of Postgres user passwords is not recommended, although you may consider your circumstances and proceed accordingly. For example, if you are setting up a local development installation on your laptop, you may choose to store the password anyway. Next, hit “OK” and voila – our existing Postgres database server is available in PG Admin 3:

Postgres Database Server Registered in PG Admin 3:

pgadmin3-logged-in

Now you can use PG Admin 3 to create new databases, tables, query, and all the other tasks you might expect. While the GUI experience is not as polished as some commercially available alternatives, you will find that PG Admin 3 is highly functional, and can handle most of your database administration needs quite well.

Postgres Configuration Options – Database Files and Directory Location

When we performed our installation and configuration above, we basically allowed Postgres to use its own sensible defaults for most things. Most of the time, if you are setting up Postgres on your own machine, for development or just to mess around, these are sufficient. However, understanding some of the basic configuration options, how and where these are set, can be most helpful.

When Apt installs Postgres, it creates a few directories by default:

/etc/postgresql/9.4/main – Primary PostgreSQL configuration files are located here. This directory is owned by root, so you will need to elevate your privileges to edit files in here. Of particular interest to us are two files:

  • pg_hba.conf – Configuration parameters for client authentication and access is specified in this file.
  • postgresql.conf – Configuration parameters for important file locations, logging, resource usage, and a host of other database configuration items are set here. For our purposes, most of the defaults will do for now. However, the file locations may be of interest, as we’ll see in a moment.

/var/lib/postgresql/9.4/main – Default location for the database cluster and associated files. Above, when we initialized our database, we were working with database files in this location.

Specify a Different Directory for the Database Cluster Files

For a variety of reasons we may wish to specify a different directory for our database files. We may want to keep them more easily accessible, or we might have set up our system with a separate partition for data files. In any case, if we want to use a location other than the default for our database cluster, we can.

The Postgres cluster directory must be owned by the postgres user, so we need to create the directory, and then assign ownership to postgres.

For our example, we’ll create a /database directory in our file system root, and within that a pg subdirectory:

Create a New Directory for Postgres Data:
$ sudo mkdir –p /database/pg
$ sudo chown -R postgres /database

 

In the above, the –p flag tells bash to make sure any leading directories are created on the way to the final directory. The –R flag used with chown tells chown to work recursively, and apply the ownership change to any nested directories or files.

Now we need to initialize the new database cluster in the new directory. Note, if you are already running an existing postgres instance, it is best to stop the server before moving the data directory:

Stop Existing Postgres Instance:
$ sudo service postgresql stop

 

Once again, we will need to get to our postgres user through root, and then initialize the new cluster (note – I’m showing the full terminal prompt here for clarity, so we can see which user privileges are used):

Initialize the New Postgres Cluster Using initdb:
xivsolutions@mint-vm / $ su -
mint-vm ~ # su - postgres
postgres@mint-vm ~ $ /usr/lib/postgresql/9.4/bin/initdb -D /database/pg
postgres@mint-vm ~ $ exit
mint-vm ~ # exit
xivsolutions@mint-vm / $

 

Don’t forget to exit from the postgres user, and then also exit root as shown.

Next, we need to edit the postgresql.conf file and point the data_directory variable to the new cluster location. We can open the file with elevated privileges in gedit like so:

Open the postgresql.conf File with Elevated Privileges in Gedit:
$ sudo gedit /etc/postgresql/9.4/main/postgresql.conf

 

Scroll down in the file to the file locations section, comment out the existing entry for data_directory (using the ‘#’ character), and add a new entry pointing to our new directory like so:

Replace the data_directory Parameter to Point at the New Directory Location:
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
# Add a '#' to the beginning of the line below:
# data_directory = '/var/lib/postgresql/9.4/main'    # use data in another directory
# THIS IS OUR NEW DIRECTORY PARAMETER:
data_directory = '/database/pg'          
# Blah blah more configuration stuff . . . 

 

Save and exit Gedit, then re-start the postgres server:

Restart the Postgres Server:
$ sudo service postgresql restart


NOTE: If you forgot to stop an existing instance of Postgres before moving the data directory (or for other potential reasons), you may receive an error indicating that “the pid file is invalid, and to manually kill the stale server process.” If this happens, you can manually kill the process with the following:

Manually Kill a Stale Postgresql Server Process:
sudo pkill -u postgres

 

Before restarting the server, it is important to make certain all Postgres processes have, in fact, stopped. Make sure that the shell command ps returns no results when used as follows:

Ensure All Postgres Processes have been Killed:
ps -u postgres

 

Then go ahead and restart postgres as per previous.

You should now be running Postgres in the new location. Note that any existing database files did not move as part of this process, so you will need to follow the previous steps for creating a new super user, etc.

In upcoming posts, we’ll take a look at some of the stand-out features of this database.

Additional Resources and Items of Interest

If you are just digging in to PostgreSQL, Linux, or both, see some of these excellent resources for additional information:

Some OSS projects I’m privileged to be involved with using PostgreSQL:

 

Posted on April 19 2015 07:41 AM by jatten     

Comments (0)

A More Useful Port of the Chinook Database to Postgresql

Posted on April 5 2015 12:26 PM by jatten in Database, Postgres, CodeProject   ||   Comments (0)

chinookI use Chinook Database as the sample database for a lot of things, primarily because the same data set is available for most of the major database platforms, so it's easy to port/compare etc. Chinook Database offers up a set of tables with enough sample data to satisfy most testing/demo needs, in a general enough schema that it is easy to get something up and running quickly.

However, the Chinook database as made available at the Chinook site maintains the same object naming conventions (proper-cased table and column names) across database platforms, and this I DON'T like. Also, the Postgres version of Chinook available as part of the Chinook package uses integer primary keys, but not auto-incrementing integers (this corresponds to the serial data type in Postgres).

Image by Ingrid Talar  |  Some Rights Reserved

Because I use Chinook fairly often with Postgres, I decided to make a few mods to the Chinook Postgres script to fix these things, and provide a more native PG experience when using this otherwise handy sample database.

Use Postgres-Idiomatic SQL

Postgresql ("Postgres" or "pg") has its roots in the Unix world. Database object names are case-sensitive, and in fact the convention is to use lower-case names, and where needed, separate with underscores. It is possible to use proper-cased object names in Postges by escaping them with double-quotes. However, this makes for some atrocious-looking SQL.

A sample excerpted from the Chinook script Postgres perfectly illustrates this:

Excerpt from Standard Postgres Chinook Script:
CREATE TABLE "Album"
(
    "AlbumId" INT NOT NULL,
    "Title" VARCHAR(160) NOT NULL,
    "ArtistId" INT NOT NULL,
    CONSTRAINT "PK_Album" PRIMARY KEY  ("AlbumId")
);

 

The above forces the Album table to be created with proper-cased object names (the table name itself, as well as the columns). However, from this point forward you will need to use the double-quote escapes in any SQL you push in which requires the use of object names explicitly.

At a minimum, you would need to do THIS to pull all the records from the Album table:

Select all the Records from the Chinook DB Album Table:
SELECT * FROM "Album"

 

Note the quotes around the table name. If you don’t use those, you’ll get an error indicating that the table album doesn’t exist. This is because Postgres will automatically down-case incoming SQL before processing it.

Now let’s imagine you wanted to set some criteria on that SELECT statement:

Select some specific Records from the Chinook DB Album Table:
SELECT * FROM "Album" WHERE "ArtistId" = 2

 

Again with the double-quote escape sequence. Things continue to go downhill from there. Imagine a simple JOIN between two tables with a minimal set of columns returned:

Select Records from the Chinook DB Album Table using a JOIN to Artists:
SELECT 
  ar."Name", 
  al."Title"
FROM 
  "Album" AS al, 
  "Artist" AS ar
WHERE 
  al."ArtistId" = ar."ArtistId";

 

Yeah. Now things start to look ugly.

Composing SQL against this (and even worse, code which incorporates said SQL) gets painful fast.

Use Serial Integer Primary Keys

The maintainers of Chinook Database for some reason chose not to implement auto-incrementing integers for table primary keys in the Postgres version. Whatever the reason, I want auto-incrementing integer keys on the tables in my Chinook database.

Making this happen took a little doing, since the sample data itself establishes table relationships based on existing primary key data. How to solve this?

The Postgres serial type utilizes sequences to maintain the current value of a particular serial column. We can specify the starting value of a sequence, and the amount by which it is incremented, using the Postgres CREATE SEQUENCE command:

Create Sequence Example:
CREATE SEQUENCE "albums_id_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 2147483647
 START 347
 CACHE 1;
SELECT setval('"public"."albums_id_seq"', 347, true);

 

But wait, John – you pluralized the name of the albums table there. Also, what’s with that setting the START value at 347?

Good question. We’ll get to the pluralized naming convention – I changed that for the entire Chinook script. As for the initial value, the Chinook sample data set contains an initial set of 347 album records. Therefore, we want to set an initial value for our table PK sequence at 347, so that the next ID provided will be 348. 

Down-Casing Object Names, and Simplifying

I went through the Chinook script for Postgres, and modified the object names for all of the tables and columns, down-casing everything, and adding underscores as needed. I also pluralized the table names themselves. With that in mind, as well as the fact that we want to use a serial data type for our primary key columns, the create statement for our albums table might now look more like this:

Modified Create SQL for Chinook Album Table:
CREATE TABLE albums
(
    id int DEFAULT nextval('albums_id_seq'::regclass) NOT NULL,
    title VARCHAR(160) NOT NULL,
    artist_id INT NOT NULL,
    CONSTRAINT pk_albums PRIMARY KEY  (id)
);

 

Notice we have pluralized the table name, and also used a straight-forward id as the name for the primary key column, doing away with the problematic AlbumId (we could have used album_id, but why?). We set the default value for the id using the Postgres nextval() function, which will pull the next value based on the sequence we created unless a value is provided.

Get the Modified Chinook Script from Github

You can find the full, modified version of the Chinook database script at my Github repo. There are two versions, each on a separate branch.

Branch Master contains the full Chinook script, with down-cased/underscore-separated object names. However, table names have not been pluralized, and table primary key columns maintain the underscored version of the original column name. For example, AlbumID becomes simply album_id.

Branch pg_names contains the same database, but with table names pluralized, and table PK column names reduced to simply id. In other words, the table Album in the original Chinook DB is now albums, and the PK for albums is simply id.

See Something Amiss?

I am not a DBA, and it is possible I did something stupid in here. I like to think I know enough database to do those things I need to do as a developer. However, if you see something I did that might be accomplished better another way, please do sound off in the comments, shoot me a PR on Github, or shoot me an email at the address in the “About the Author” section.

 

Posted on April 5 2015 12:26 PM by jatten     

Comments (0)

About the author

My name is John Atten, and my "handle" on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, JavaScript/Node, and databases of many flavors. Actively learning always. I dig web development. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

johnatten at typecastexception dot com

Web Hosting by