Tag: PostgreSQL

JSON and PostgreSQL: A Powerful Combination

JSON is a data interchange format that is designed to be lightweight and easy to work with. It’s quite popular in web applications, and it can be considerably more flexible than a traditional relational data model. PostgreSQL 9.3 and later versions support JSON, so you can store JSON data and use native Postgres functions to operate on it. This includes decomposing, transforming, or even creating JSON data from regular relational data.

How to Set Up a Composite Primary Key in jOOQ and Hibernate

In today’s article, we will take a look at composite primary key support in two top Java ORMs: jOOQ and Hibernate . We’ll look at a couple examples, learn how column configuration looks in Hibernate, and how support is organized in jOOQ.Thecomposite primary keyis a key that consists of more than one column, and its column combination guarantees its uniqueness.To handle the connection to the database, the application uses libraries known as object-relational mappers, or ORMs. Let’s see how jOOQ and Hibernate support this functionality.

The History of Slonik, the PostgreSQL Elephant Logo

Logos are powerful. What better way to remind people of a product than an eye-catching, memorable symbol? With that in mind, today we’ll answer the question ‘Why did PostgreSQL choose an elephant for its logo?’Every product or company has its logo – something that identifies and encapsulates the essence of their brand. In time, it practicallybecomesthe brand: can you imagine McDonald’s without its golden arches? What if the Coca-Cola logo was suddenly done in purple block print?

Postresql Monitoring with SQL

PostgreSQL provides an activity-tracking module called thestatistics collector, which tracks table access and other internal events. If your database is experiencing long wait times, you may be able to use this tool and some simple SQL to find and fix the problem.Tracking Postgres Activity With the Statistics Collector ToolThe statistics collector is designed to keep records about internal activity in a Postgres database manager. It can:count table and index access in both disk-block and individual-row terms.

Database Modeling Tips

When you were learning database concepts, data modeling looked pretty easy, didn’t it? You knew all the rules, and modeling seemed like a game: get a challenge, do your best, and eventually solve it. Job well done! Moving up to the next level – and so on.As you continue, though, you’ll see that database modeling is also an art. Many cases require a totally new approach. Everything can be done ‘by the book’, but sometimes you get better results when you go less orthodox.

Your First Steps With the Geography Data Type

Geographical applications are everywhere: GPS and sat nav systems, maps, get-a-taxi apps, real estate portals, etc. Behind each of them is a spatial database storing geographical data, and supporting spatial queries. In this article, we will introduce PostGIS, the main open-source spatial database manager.PostGIS is a spatial database extension for the PostgreSQL relational database. It adds support for geographic objects, allowing location queries to be run in SQL.PostGIS adds two main data types to PostgreSQL: geography and geometry. Both allow the storage of points in a table, as well as other more complex shapes like lines (a line is defined by two points), multipoint lines (defined by N points), polygons (defined by a closed multipoint line), and points with a specific altitude (defined by a third coordinate). This extender also offers a set of spatial functions for distance calculation, area calculation, intersection, and inclusion, among many others. All these new data types and functions can be used in combination with regular relational data in SQL, increasing the power of queries.

CHAR and VARCHAR Data Types in Different Database Engines

Storage engines can surprise you. For example, take the CHAR data type. It expects an exact number of characters and by definition stores afixed amountof information. However, you don’t have to fill all the available CHAR space – a shorter value will work. This is so similar to VARCHAR that I decided to explore the differences between these two types.Before diving into the details, let’s start with some basic information. CHAR and VARCHAR are SQL data types dedicated to storing character values. They are available in almost every database engine. Due to database and encoding particulars, the storage of character values in CHAR and VARCHAR columns differs.

Pivot Tables in PostgreSQL Using the Crosstab Function

Some years ago, when PostgreSQL version 8.3 was released, a new extension calledtablefuncwas introduced. This extension provides a really interesting set of functions. One of them is thecrosstabfunction, which is used for pivot table creation. That’s what we’ll cover in this article.The simplest way to explain how this function works is using an example with a pivot table. First, we will explain our initial point from a practical perspective, then we’ll define the pivot table we want.

How to Create a Spark REST API With jOOQ

I’ve been testing a ton of frameworks lately – good and otherwise. One of them, the Java micro framework Spark , really amazed me with its simplicity and rapid development capabilities. In this article, we’ll examine an example of Spark’s usefulness by creating a REST API.So, without further ado, let’s see how to store, retrieve, update and delete some information in a PostgreSQL database using jOOQ ORM over a RESTful API in a simple To-do app.

7 Common Database Design Errors

Why Talk About Errors? Model Setup 1 – Using Invalid Names 2 – Insufficient Column Width 3 – Not Indexing Properly 4 – Not Considering Possible Volume or Traffic 5 – Ignoring Time Zones 6 – Missing Audit Trail 7 – Ignoring Collation Why Talk About Errors?The art of designing a good database is like swimming. It is relatively easy to start and difficult to master. If you want to learn to design databases, you should for sure have some theoretic background, like knowledge about database normal forms and transaction isolation levels. But you should also practice as much as possible, because the sad truth is that we learn most… by making errors.

Playing Around With Python in PostgreSQL

I’m kind of guy who likes to make jokes or take some ideas ad absurdum. It may looks useless, but examination of extremes helps me appreciate the middle way.Some time ago I was heavily infected by an idea: “let’s ditch the server side” (see my article: Do we still need server side programming? ). The “PostgreSQL as an Application Server” idea was born then. My Vertabelo colleagues insisted that I should write an article about it. I didn’t like it at first: it would require some real work :) This week I said why not? I’ll learn something new about PostgreSQL at least. So here we go.

A Handy Guide to Solving PostgreSQL Problems

Written in the frequently-asked-questions style, this book provides the best troubleshooting techniques for PostgreSQL database administrators. It covers all major aspects of managing a PostgreSQL database; from database installation through optimizing performance, to dealing with transaction locks or fixing replication, to handling hardware and software disasters. So if you’re getting an error message when working with PostgreSQL, let this book help you solve your problem. “Troubleshooting PostgreSQL” is the latest book by

PostgreSQL Database Replication

MotivationThe main idea behind any type of database replication is to be able to tell your database to report to someone (usually to another database) via any of the mechanisms described below whenever changes to your main database have been made. Those changes will then be processed and applied to this second database, and if everything goes well, you will end up having an up to date replica of your main database running somewhere else. This can be useful for many reasons.

Setting up a Local Development Environment

How This Tutorial Is OrganizedThe tutorial is divided into four articles: Introduction – Dive into web development with Flask. This article explains the basics of web development with Flask Part I – Setting up a local development environment (You are here) Step 1: Create virtual environment for all required dependencies Step 2: required packages: Flask, psycopg2, SQLAlchemy Step 3: Create requirements.txt file with all dependencies listed

Flask Web-App Development. Part III: Deployment to Heroku

How This Tutorial Is OrganizedThe tutorial is divided into four articles: Introduction – Dive into web development with Flask. This article explains the basics of web development with Flask Part I – Setting up a Local Development Environment Step 1: Create virtual environment for all required dependencies Step 2: required packages: Flask, psycopg2, SQLAlchemy Step 3: Create requirements.txt file with all dependencies listed

Flask Web-App Development. Part II: Application Development

How This Tutorial Is OrganizedThe tutorial is divided into four articles: Introduction – Dive into web development with Flask. This article explains the basics of web development with Flask Part I – Setting up a local development environment Step 1: Create virtual environment for all required dependencies Step 2: required packages: Flask, psycopg2, SQLAlchemy Step 3: Create requirements.txt file with all dependencies listed

Flask Web Application Development. Introduction

How This Tutorial Is OrganizedThe tutorial is divided into four articles: Introduction – Dive into web development with Flask. This article explains the basics of web development with Flask (You are here) Part I – Setting up a local development environment Step 1: Create virtual environment for all required dependencies Step 2: required packages: Flask, psycopg2, SQLAlchemy Step 3: Create requirements.txt file with all dependencies listed

Understanding Execution Plans in PostgreSQL

Execution plans can become a very useful tool for every database developer. They provide a deeper understanding of the mechanisms applied in the processing of queries. This article will take a closer look at how execution plans are retrieved and how to read them.What Are Execution Plans?SQL is, to a great extent, a declarative language. The user defineswhatshould be done but does not specifyhowthe queries should be executed. There are many ways in which certain parts of an SQL statement can be processed. For example, predicates can be computed in any sequence and subqueries can be turned into joins if necessary.

Slony-I Replication – Your Initial Setup

Slony-I is a popular PostgreSQL replication system. Basically, it allows you to automatically copy data from one database to other databases. It can also do a couple of other things, but in this article we will concentrate on replication and provide you with a step by step guide on how to configure your first Slony-I cluster in Windows. It will involve a master node and two slave nodes. The master node will be the database that will be replicated and the slaves will get updated as the master gets modified.

Who Will Get the Turing Award in 2030?

TheTuring Awardis an annual prize given by the Association for Computing Machinery (ACM) “for contributions of a technical nature made to the computing community.”  It is sometimes referred to as the “Nobel Prize” in Computing. Currently, the prize is 1 million dollars with funding provided by Google. The 2014 Turing Award – announced just three weeks ago on March 25, 2015 – was given to Michael Stonebraker , who earned the award for “fundamental contributions to database systems, which are one of the critical applications of computers today.” So I decided to take a look at what other Turing Awards were given for databases in the award’s 50 year history.

Sequences in Database Systems

Generating unique integers is a very common task in database systems. Many applications require each row in a given table to hold a unique value. One way to tackle this problem is to use sequences.What are Sequences?A sequence is a database object which allows users to generate unique integer values. The sequence is incremented every time a sequence number is generated. The incrementation occurs even if the transaction rolls back, which may result in gaps between numbers. Similarly, gaps may arise when two users increment the same sequence concurrently.

Dirty PostgreSQL Database? Clean It up With a VACUUM!

Just like your house gets dirty and requires cleaning from time to time, your PostgreSQL database may accumulate “dust” too. Unwanted pieces of data make the DB grow bigger and bigger. However, there is a mechanism to get rid of junk data and the DB can do it automatically for you.How Does a Database Get “Dirty”?Let’s talk a little bit about concurrency in databases. Generally, when multiple users (or more concretely – database transactions) read or modify the data stored in a database at the same time, they may conflict with each other. One user wants to read the row while another one may want to modify it. They do these operations within different transactions so they should be able to do their jobs without worrying about someone else doing something with the same data. It’s the DBMS’s job to handle it. So let’s look at this problem from a DBMS’s perspective. There are a few possible strategies to do this.

Designing & Deploying a PostgreSQL Database in the Cloud

Our users sometimes say to us: “Vertabelo is awesome but… how can I add the data?”  Oh, come on! Vertabelo is not a database engine or a database administration tool. At least not at the moment. For now, Vertabelo is intended mainly to design database models. This doesn’t mean that we don’t care about what you can do next with models created in our application. Of course, we do! That’s why we provide our users with some useful tools on

Foreign Data Wrappers for PostgreSQL

Foreign Data Wrapper (FDW) is one of the big features that came with PostgreSQL 9.1. Simply, it allows you to access external non-Postgres data as if it were a regular Postgres table. Postgres FDW is an implementation of a decade-old SQL/MED (Management of External Data) standard in PostgreSQL that contains the information on how to allow databases to make external data sources (text files, web services, etc.) look like tables and have easy access to them using SQL commands.

Formatting Ad-Hoc Reports in PostgreSQL

OR/M and noSQL guys see SQL as an awkward way to store and retrieve objects from the database. Here at Vertabelo, we see SQL as a useful language to query data. Making reports in pure SQL is a pure pleasure. Running an interactive query is almost the same experience as asking a human to provide information.At some point you’ll have a need to write a report in a few seconds. Running IDE and coding is not a viable option. In this article I’ll show you how to perform and format ad-hoc reports using a PostgreSQL command line client. I’ll focus on formatting options so examples will be simplified.

How to Trigger a Crude CRUD Database Prank

Learning new things should be fun. I’ve read about database triggers multiple times but I didn’t have a chance to use them. Triggers are not popular these days especially in web development. Let’s mix learning and making fun (of others).What is a trigger for? It’s a kind of aspect of programming, an extra business logic executed on a given action. With triggers you can do complex validation, update balance on summary tables, record the who, what and when of changes to data and so on. That’s triggers in a nutshell.

Time Zones in Databases

Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simpletimecallednow. What you should rather think about is a time comprised ofhere and now.The Earth rotates around its own axis. When it’ssolar noon(the sun is at its highest position) in one place, it’s already past noon in places to the east and it’s still before noon in places to the west.To make communication easier, at the end of the 19th century, the Earth was divided into 24 hour-wide

PostgreSQL Collations

List CollationsCollations in PostgreSQL are available depending on operating system support. For example, in Ubuntu type the following to list the names of the available collations:The same locales are available in PostgreSQL in the pg_collation catalog (mappings from an SQL name to operating system locale categories).Locale support is initialized when the database is created. After that, you can no longer change the locale, because the default collation selects LC_COLLATE and LC_CTYPE values are specified at database creation time. Appropriate language settings are defined in the below subcategories.

S.Q.L or Sequel: How to Pronounce SQL?

SQL has been around for decades and supports a many billion dollar market. However, many people still struggle with just how to pronounce the term SQL. Is it “S.Q.L” [ˈɛs kjuː ˈɛl] or is it “sequel” [ˈsiːkwəl]?SQL… Where it all startedLet’s start at the beginning.Relational databases came into existence with E.F. Codd’s 1970 publication “A Relational Model of Data for Large Shared Data Banks.” While Codd’s ideas were remarkable for the time, in San Jose, California, two colleagues named

Database engine usage by Vertabelo users

The Vertabelo journey continues … We now have almost10,000users and the number of Vertabelo advocates keeps growing strong.Vertabelo users come from over100countries and speak various languages. What unites them?The relational database.Let’s see what relational databases they use:We wanted to determine the most popular database engine among Vertabelo users based on one of three widely-used operating systems:Windows,Linux,Mac OS.Guess what we find out?Among all supported popular databases (PostgreSQL, MySQL, Microsoft SQL Server, SQlite, Oracle, IBM DB2, HSQLDB),

Can Python Help You Win Scrabble?

It is hard to find someone who doesn’t know how to playScrabble. In case someone isn’t familiar with the game, here’s a quick explanation:Scrabble is played by two to four players on a square board with a 15×15 grid of cells, each of which accommodates a single letter tile. The board is marked with “premium” squares, which multiply the number of points awarded; each letter tile has its own assigned numeric score. At the start of a Scrabble game, every player chooses 7 letter tiles.

The Concept of Materialized Views (MVs)

The concept ofmaterialized views (MVs)is almost 15 years old; Oracle first introduced these views in the 8i version of its DBMS. However, some well known DB vendors (like MySQL) still don’t support MVs or have added this functionality only quite recently (it’s available in PostgreSQL since version 9.3, which was released just a year ago). In this article I’ll try to give you some tips about when you should use MVs in OLTP systems.

How to delete old records from a database while retaining some history

If the amount of data in your temporary table is growing very fast, you may want to expunge old unnecessary data from your database.The scenario is like this: there is a tablebackup_datawhich contains backup data for various resources. The amount of data in the table is growing very fast, so you want to delete old unnecessary data. However, you still want to keep a little bit of historical data. The business rules for keeping the backups for every resource are a variation of the grandfather-father-son algorithm:


The standard DELETE statement in SQL returns the number of deleted rows.In PostgreSQL you can make DELETE statement return something else. You can return all rows that have been deleted.You can return the columns of your choice.In your code you can process the returned rows in the same way as you would process the results of an SQL query. For example, you may log the data that have been deleted.

How to Select the First Row in a Group?

Often you want to select a single row from each GROUP BY group. PostgreSQL has a statement especially for that: SELECT DISTINCT ON.Let’s say I want to select one weather report for each location.The query retrieves one weather report for each location.You can use several expressions in SELECT DISTINCT ON statement.For each resource the query retrieves a single backup data for each week.SELECT DISTINCT ON with ORDER BYThe undecorated SELECT DISTINCT ON selects one row for each group but

Date Arithmetic in PostgreSQL

If your default programming language, like mine, is Java, you most likely wince at the very thought of date arithmetic. (It’s changed for the better with Java 8 but Vertabelo is not there yet.)The date arithmetic API in PostgreSQL is like abreath of fresh air.Interval ArithmeticYou can represent a time interval:Add an interval to a date or subtract an interval from a date:Truncating DatesYou can truncate dates to the specified precision.

Relational databases are not rocket science ready

As usual I’ve tried to make the title of my article something catchy. This article will not be about rocket science per se, but instead it will be about a specific usage of relational databases. So, keep reading!Suppose you’re working at some top secret corporation and you’re developing a rocket controller. The rocket controller must:explode when it is near a targetlog time and distance (in case it doesn’t explode)The Java code to achieve this may looks like the following:

SQL Performance Explained – the must-read book

Some time ago, the Vertabelo Team participated in the PostgreSQL Conference Europe 2013 . Some of the talks were really nice. One of them stuck in my head for quite a long time. It was Markus Winand’s lecture titled“Indexes: The neglected performance all-rounder.”Although I had had a solid background in databases, this 50 minutes long talk showed me that not everything concerning indexes was as clear to me as I had thought. This was the kind of lecture I like the most – when you sit and say to yourself “damn, I didn’t know that!”

Online tools for trying out SQL design and queries

In this article I’m going to show you how to design a database, pour in some data, and finally execute queries. Everything will be done using only a … web browser. That’s right, no installation, no license keys, no de-installation. Just switch to Google Chrome and follow along.Database designTo design a database I will use  Vertabelo . Since this is a Vertabelo product blog, the choice is obvious :)You will need to

Win PGCon 2014 Tickets!

PGCon is an annual conference for users and developers of PostgreSQL. This year Vertabelo is one of the conference sponsors and we’re giving awayconference ticketstothreeof our lucky users!When and whereThe conference takes place on22-23 May (Thu-Fri)at theUniversity of Ottawa (Canada). The tickets enable you to attend all talks on Thursday and Friday (so no tutorials).The giveaway is open to users worldwide, but you will have to provide your own transportation to and from the conference as well as your own accommodations in Ottawa.

Top-N and Pagination Queries

Generally, we don’t limit query results. However, when we only care about the first few rows or to implement table pagination, limiting query results is just what we need. Database vendors provide us with such functionality; most of them in their own distinct way.ExampleLet’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in theskijump_resultstable. There is no index on theskijump_resultstable. The following queries are examples of a Top-N and pagination query, which I will use in the following parts of the article.


When you create a foreign key in your database, you can specify what happens upon delete of the parent row. There are usually four possibilities:ON DELETE SET NULLON DELETE CASCADEON DELETE NO ACTIONON DELETE RESTRICTToday we’ll investigate the subtle difference between the last two options.In Some Databases There Is No Difference at AllIn Oracle, there is no RESTRICT keyword . The only option is NO ACTION. In MySQL, there is

Designing a Database: Should a Primary Key Be Natural or Surrogate?

Suppose we design a database. We’ve created some tables, each one has a few columns. Now we need to choose columns to be primary keys (PK) and make references between tables. And here some inexperienced designers face the dilemma – should a primary key be natural or surrogate?There’s one and only one answer to that question:it depends. If anyone ever tried to convince you that you should have only natural keys or only surrogate keys, just smile :)

How to Count XML Nodes in PostgreSQL 9.1

Recently I was given a task which involved counting, with a single SQL query, all tables in each model version of Vertabelo. Vertabelo internally stores each model version as an XML file (download a sample XML file ). So my task was to count all XML nodes satisfying a certain XPATH expression. Vertabelo runs on PostgreSQL 9.1, so PostgreSQL 9.1 tools were all I could use.To begin at the end, the final query looks like this:

MySQL’s group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

group_concat in MySQLMySQL has a very handy function which concatenates strings from a group into one string. For example, let’s take a look at thechildrentable with data about parents’ and children’s names.To get the names of children of each person as a comma-separated string, you use thegroup_concatfunctions as follows:The result:To make sure the names of the children in each string arealphabetically orderedand to use semicolon „;” as aseparator, use this query: