Blog Posts by

Odd Month Arithmetic
Oct 10, 2017
You might be aware of the interval data type, which allows mathematical operations on date, time, and timestamp values. This can lead to odd behavior...
Use with Time Zone
Oct 3, 2017
If you often use the timestamp data type, you might not be making full use of it. In these queries: CREATE TABLE tztest (x TIMESTAMP ); INSERT INTO...
PL/Java Adoption
Sep 19, 2017
PL/Java has been around since 2005, but it has regularly struggled to gain users. Unfortunately, a lot of these problems are specific to the Java...
When to Use Server-Side Logic
Sep 12, 2017
The use of server-side logic, particularly stored procedures, has been a highly contentious topic among database professionals for decades. The...
Which NoSQL Database for New Project?
Aug 31, 2017
Oh, how I love the title of this 2014 Slashdot request, "Which NoSQL Database For New Project?" The user already knows the type of clients (iPhones...
You Don't Need Every Feature of Your Previous Database
Aug 8, 2017
In a March blog post I talked about the mismatch between what people expect from Postgres in terms of hints, and what exists. In this blog post I...
Outer Joins and Where Clauses
Jul 19, 2017
*/ Postgres supports both traditional join syntax , which uses the where clause to specify joined columns, and ansi join syntax, that uses the word...
Allowing Only One Null
Jun 14, 2017
While the sql standard allows multiple nulls in a unique column, and that is how Postgres behaves, some database systems (e.g. ms sql ) allow only a...
Postgres Keywords
May 17, 2017
You might be aware that the sql standard reserves certain identifiers that cannot be used for naming user objects. Postgres follows that standard,...
Column Storage Intervals
Apr 25, 2017
Postgres uses native cpu alignment to store values in a row. This allows blocks to be copied unchanged from disk into shared buffers and accessed as...
Creating Descending Indexes
Apr 18, 2017
You might have noticed that create index allows you to specify asc or desc for each column being indexed, and you might have wondered why. Asc is the...
Index Order Does Matter
Apr 12, 2017
Postgres has supported multi-column indexes since 1997, e.g. create index i_test on test (a, b, c). It can easily use an index if the supplied...
Expression Index Statistics and Joins
Apr 5, 2017
In my previous blog post, I showed how statistics generated on expression indexes can be used to produce more accurate row counts, and potentially...
Still No Query Hints?
Mar 28, 2017
So, it is 2017 and Postgres still doesn't support query hints like other relational databases? Yep, it's true, and probably will be forever since "'...
Statistics on Expression Indexes
Mar 22, 2017
Most people know that Postgres allows the creation of indexes on expressions. This is helpful if you need index lookups of expressions used in where...
Getting the Most of out Application_Name
Mar 15, 2017
Application_name might be one of those Postgres settings that you have seen in various places but never understood its purpose. It is true that...

Get Postgres Tips and Tricks

Subscribe to our newsletter to get advanced Postgres how-tos.