Bruce Momjian
VP, Postgres Evangelist, EDB
Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.
Read Blogs
Postgres supports both traditional join syntax, which uses the where clause to specify joined columns, and ansi join syntax, that uses the word join in the from clause. While both syntaxes can be used for inner joins, only the ansi join syntax supports outer joins in Postgres. Enterprise-ready Postgres tools for high availability, monitoring, and disaster recovery. Download Now. Because column...
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 single null in such cases. Users migrating from other database systems sometimes want to emulate this behavior in Postgres. Fortunately, this can be done. First, let me show the default Postgres behavior: CREATE TABLE nulltest (x INTEGER UNIQUE)...
EDB Labs
You might be aware that the sql standard reserves certain identifiers that cannot be used for naming user objects. Postgres follows that standard, with slight modifications. For example, you cannot create a table called all: CREATE TABLE all (x INTEGER); ERROR: syntax error at or near "all" LINE 1: CREATE TABLE all (x INTEGER); ^ It is actually very easy to find what identifiers are reserved...
EDB Labs
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 local variables, as outlined in this presentation.
EDB Labs
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 default, so there is no need to use it. Desc actually does have a use, but it isn't obvious. Postgres can access indexes forward or backward, so there is no need to specify ascending/descending if all columns are ascending or descending. The use-case...
EDB Labs
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 columns are all at the front of the index, e.g. a and b in the previous index, but it can also use the index if some of the indexed column values are not supplied, e.g. columns a and c in the previous index. It does this by looking up a in the index, then...
In my previous blog post, I showed how statistics generated on expression indexes can be used to produce more accurate row counts, and potentially better plans. While I did show more accurate row counts via explain, I did not show changed query plans. I plan to do so in this blog post. First, the setup: CREATE TABLE test1 AS SELECT * FROM generate_series(1, 100000) AS f(x); CREATE TABLE test2 AS...
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 "'Oracle-style' optimizer hints" is listed in the "Features We Do Not Want" section of the Postgres todo list. A wiki page outlines the reasons for this. While this seems perfectly logical to people who have used Postgres for years, it strikes new users...
EDB Labs
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 clauses.
EDB Labs
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 setting application_name doesn't change the behavior of Postgres (with one small exception), but it is very useful for monitoring.