sql Archives - 2ndQuadrant

Posts

PostgreSQL: Regular expressions and pattern matching

July 31, 2019/1 Comment/in Haroon's PlanetPostgreSQL /by Muhammad Haroon

A regular expression is a special text string used to describe a search pattern. PostgreSQL’s regular expressions supports three separate approaches to pattern matching: POSIX-style regular expressions (BREs and EREs) SIMILAR TO operator added in SQL:1999 SQL LIKE operator There are some more advanced techniques for advanced pattern matching requirements but those will very likely […]

JOIN LATERAL

March 1, 2019/2 Comments/in Elein’s PlanetPostgreSQL, PostgreSQL /by Elein

LATERAL The primary feature of LATERAL JOIN is to enable access elements of a main query in a subquery which can be very powerful. Several common uses of LATERAL are to: denormalize arrays into parent child tables aggregation across several tables row or action generation. Note, however, that the subquery will execute for each main […]

Evolution of Fault Tolerance in PostgreSQL: Synchronous Commit

August 17, 2016/2 Comments/in 2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, PostgreSQL /by Gulcin Yildirim

PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the fourth post of the series and we’ll talk about synchronous commit and its effects on fault tolerance and dependability of PostgreSQL. […]

Evolution of Fault Tolerance in PostgreSQL: Time Travel

July 21, 2016/0 Comments/in 2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, PostgreSQL /by Gulcin Yildirim

PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the third post of the series and we’ll talk about timeline issues and their effects on fault tolerance and dependability of PostgreSQL. […]

Evolution of Fault Tolerance in PostgreSQL: Replication Phase

July 11, 2016/0 Comments/in 2ndQuadrant, Featured, Gulcin's PlanetPostgreSQL, PostgreSQL /by Gulcin Yildirim

PostgreSQL is an awesome project and it evolves at an amazing rate. We’ll focus on evolution of fault tolerance capabilities in PostgreSQL throughout its versions with a series of blog posts. This is the second post of the series and we’ll talk about replication and its importance on fault tolerance and dependability of PostgreSQL. If […]

What is SKIP LOCKED for in PostgreSQL 9.5?

April 13, 2016/26 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

PostgreSQL 9.5 introduces a new SKIP LOCKED option to SELECT … FOR [KEY] UPDATE|SHARE. It’s used in the same place as NOWAIT and, like NOWAIT, affects behaviour when the tuple is locked by another transaction. The main utility of SKIP LOCKED is for building simple, reliable and efficient concurrent work queues.

Tablesample In PostgreSQL 9.5

August 7, 2015/0 Comments/in Featured, Gulcin's PlanetPostgreSQL /by Gulcin Yildirim

PostgreSQL 9.5 introduces support for TABLESAMPLE, an SQL SELECT clause that returns a random sample from a table.

Why should you learn PostgreSQL?

June 9, 2015/1 Comment/in PostgreSQL /by 2ndQuadrant Press

A few weeks ago I asked one of my colleagues why I should learn PostgreSQL. Harald’s answer was: “To start any new journey of learning, asking ‘why’ is an excellent question. It gives us both a direction to send us on our way and an expected destination. Without a clear path to follow, our brains […]

The WITHIN GROUP and FILTER SQL clauses of PostgreSQL 9.4

May 25, 2015/0 Comments/in Featured, Giuseppe's PlanetPostgreSQL /by 2ndQuadrant Press

PostgreSQL 9.4 extends the SQL standard by inserting two new clauses that facilitate many operations required during the development of applications: the WITHIN GROUP and FILTER clauses.

PostgreSQL anti-patterns: read-modify-write cycles

July 26, 2014/25 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

Shaun Thomas’s recent post about client-side loops as an SQL anti-pattern is well worth a read if you’re relatively new to SQL-based application development. It’s reminded me of another SQL coding anti-pattern that I see quite a lot: the naïve read-modify-write cycle. Here I’ll explain what this common development mistake is, how to identify it, […]