Tag: Postgresql

Support for New Versions of PostgreSQL and MS SQL Server in Vertabelo

With the latest release of Vertabelo in 2021, we have overhauled the UI for a better experience in database modeling. We’ve also added a lot of new features requested by our customers. We’re happy to announce we now support the latest versions of PostgreSQL and MS SQL Server. You can now define partitions, specify data types for sequences, and add identity columns in PostgreSQL. Similarly, you can create DDLs for the latest versions of MS SQL Server.

Converting an Analytics System from Postgres to Redshift

Online systems tend to track user’s actions. Gathering information about users’ behavior can increase the quality of their experience, which can lead to increased business income. In this article, we will show how to reimplement an existing Postgres database to a more complex analytics database like Amazon Redshift. The solution we want to reengineer is a tracking system for an online SQL learning platform like LearnSQL.com. The source implementation is built on a PostgreSQL database and contains two main tables:

What’s the Best ER Diagram Tool for PostgreSQL?

Data modeling for PostgreSQL is no longer a challenge. Read on and learn about the best tool for PostgreSQL ER diagrams. PostgreSQL, a.k.a. Postgres, is a free and open-source relational database management system (RDBMS). It has become popular for database development among major organizations such as Instagram, Skype, and Netflix. Since the ERD (Entity Relationship Diagram) is an essential tool in data modeling, it’s worth the effort to find the perfect ERD tools that support Postgres.

Constraints in PostgreSQL and How to Model Them in Vertabelo

Database constraints allow you to give a certain shape to your data, tables, and columns. Are you planning to use PostgreSQL as your database? Check out what PostgreSQL constraints can do! In this article, we will go over PostgreSQL’s database constraints and see how to model them in Vertabelo. For each of the constraints, I’ll provide: A definition of the constraint. A usage example. Instructions on how to model the constraint in Vertabelo.

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. The composite primary key is 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.

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 practically becomes the 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 the statistics 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 Tool The 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.

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 a fixed amount of 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.

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.

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.

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 Hans-Jürgen Schönig, the recognized authority in the PostgreSQL community.

PostgreSQL Database Replication

Motivation The 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.

Setting up a Local Development Environment

How This Tutorial Is Organized The 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 Step 4: Install PostgreSQL 9.

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

How This Tutorial Is Organized The 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 Step 4: Install PostgreSQL 9.

Flask Web-App Development. Part II: Application Development

How This Tutorial Is Organized The 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 Step 4: Install PostgreSQL 9.

Flask Web Application Development. Introduction

How This Tutorial Is Organized The 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 Step 4: Install PostgreSQL 9.

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 defines what should be done but does not specify how the queries should be executed.

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?

The Turing Award is 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.

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.

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.

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 our website and in our GitHub repository.

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.

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.

Time Zones in Databases

Anyone who had to schedule an intercontinental phone call knows that there is no such thing as a simple time called now. What you should rather think about is a time comprised of here and now. The Earth rotates around its own axis. When it’s solar 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.

PostgreSQL Collations

List Collations Collations in PostgreSQL are available depending on operating system support. For example, in Ubuntu type the following to list the names of the available collations: locale -a The same locales are available in PostgreSQL in the pg_collation catalog (mappings from an SQL name to operating system locale categories). select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype -----------+---------------+-----------+--------------+-------------+------------ default | 11 | 10 | -1 | | C | 11 | 10 | -1 | C | C POSIX | 11 | 10 | -1 | POSIX | POSIX C.

Database engine usage by Vertabelo users

The Vertabelo journey continues … We now have almost 10,000 users and the number of Vertabelo advocates keeps growing strong. Vertabelo users come from over 100 countries 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.

Can Python Help You Win Scrabble?

It is hard to find someone who doesn’t know how to play Scrabble. 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.

The Concept of Materialized Views (MVs)

The concept of materialized 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 table backup_data which 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.

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 a breath of fresh air. Interval Arithmetic You can represent a time interval: select interval '2 days'; '2 days' select interval '3 hours'; 03:00:00

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 target log 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.

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 design To design a database I will use Vertabelo. Since this is a Vertabelo product blog, the choice is obvious :)

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 away conference tickets to three of our lucky users! When and where The conference takes place on 22-23 May (Thu-Fri) at the University 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. Example Let’s take a look at the 2014 Sochi Olympics Men’s Normal Hill Individual ski jumping results in the skijump_results table. There is no index on the skijump_results table.


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 NULL ON DELETE CASCADE ON DELETE NO ACTION ON DELETE RESTRICT Today we’ll investigate the subtle difference between the last two options. In Some Databases There Is No Difference at All In Oracle, there is no RESTRICT keyword. The only option is NO ACTION.

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: