Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 3 Stunden 4 Minuten

Laurenz Albe: A unique constraint where NULL conflicts with everything

24. Januar 2023 - 10:00

© Laurenz Albe 2022

I have been faced with a request for an unusual unique constraint that puzzled me for a while. Since the solution I came up with is a nice show-case for range data types, I’ll share it with you. Also, it allows me to rant some about NULL, which is a temptation I find hard to resist.

The problem: an unusual unique constraint

We have a table like this:

CREATE TABLE tab ( a integer NOT NULL, b integer );

Now we want to make sure that the combination of a and b is unique in the following way:

Akhil Reddy Banappagari: Online rebuild of Indexes – Oracle vs PostgreSQL

23. Januar 2023 - 23:10

Sometimes, we see Customers curious about the features of PostgreSQL and see if the features similar to Oracle exist in PostgreSQL. One of such features is the capabilities of Rebuilding an Index ONLINE.

Hubert 'depesz' Lubaczewski: Can you use fzf for psql history?

23. Januar 2023 - 17:00
FZF is quite popular tool for fuzzy string finder. Very helpful for checking history of commands. But, can I use it in psql? Initially, the tests seemed promising. Within psql, I can: =$ \set x `fzf < :HISTFILE` And then I can: $ :x age ────────────────────────────────────────── 14 years 11 mons 28 days 14:16:01.493027 (1 row) … Continue reading "Can you use fzf for psql history?"

Andreas 'ads' Scherbaum: Jim Chanco Jr

23. Januar 2023 - 15:00
PostgreSQL Person of the Week Interview with Jim Chanco Jr: My name is Jim Chanco Jr, I’m 35 years old, and I’m a father of 2 (Malachi 13, Isabelle 3). I live in Clayton, North Carolina, but I’m originally from Charleston, South Carolina.

Ryan Lambert: Relational and Non-relational Data: PGSQL Phriday #005

23. Januar 2023 - 6:01

Welcome to the 5th installment of the #PGSQLPhriday blogging series. I am thrilled to be this month's host! The topic posts should be published by Friday February 3rd.

Henrietta Dombrovskaya: Chicago PostgreSQL User Group in 2023

23. Januar 2023 - 4:35

On January 17, Chicago PUG started the new year by welcoming Ryan Booz. Ryan’s presentation Successful Database DevOps with PostgreSQL. Although I already thanked Ryan and want to thank him again – the presentation was phenomenal! We had an extended discussion after his talk, and even the next day, people in my office kept talking about it! I should mention that there was a poker tournament in the room adjacent to the one where we had our meetup. A couple of people migrated to our room to listen to the presentation!

Luca Ferrari: PostgreSQL command line colors!

23. Januar 2023 - 1:00

A simple way to make more attractive the PostgreSQL command line interface!

Ryan Booz: PostgreSQL Basics: Roles and Privileges

19. Januar 2023 - 22:21


Authorization Verify that the user is who they claim to be. This can be through password, trust, or some other federated login (like Kerberos) As an authenticated user, what am I permitted to do within the system?

Before we get started, lets establish a few terms:

Kirk Roybal: The PostgreSQL Job Scheduler You Always Wanted (But Be Careful What You Ask For)

19. Januar 2023 - 17:28

What is a database job scheduler? Essentially, a job scheduler is a process that kicks off in-database functions and procedures at specified times and runs them independently of user sessions. The benefits of having a scheduler built into the database are obvious: no dependencies, no inherent security leaks, fits in your existing high availability plan, and takes part in your data recovery plan, too.

Nazir Bilal Yavuz: Debugging PostgreSQL CI failures faster: 4 tips

18. Januar 2023 - 18:09

Postgres is one of the most widely used databases and supports a number of operating systems. When you are writing code for PostgreSQL, it’s easy to test your changes locally, but it can be cumbersome to test it on all operating systems. A lot of times, you may encounter failures across platforms and it can get confusing to move forward while debugging. To make the dev/test process easier for you, you can use the Postgres CI.

Frits Hoogland: Watching a YugabyteDB table: tablets and replicas

18. Januar 2023 - 17:04

After looking at the implications of the replication factor for a YugabyteDB cluster, this blogpost looks at how a table "materialises" in a YugabyteDB cluster.

The creation of a table in YugabyteDB YSQL first of all adds the table metadata to the PostgreSQL catalog, exactly like it does in PostgreSQL. This data (the metadata) is stored on the master.

Greg Sabino Mullane: How to Solve Advent of Code 2022 Using Postgres - Day 13

18. Januar 2023 - 11:00

This article will contain spoilers both on how I solved 2022 Day 13's challenge "Distress Signal" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up.

Christophe Pettus: A foreign key pathology to avoid

18. Januar 2023 - 8:00

There’s a particular anti-pattern in database design that PostgreSQL handles… not very well.

For example, let’s say you are building something like Twitch. (The real Twitch doesn’t work this way! At least, not as far as I know!) So, you have streams, and you have users, and users watch streams. So, let’s do a schema!

CREATE TABLE stream (stream_id bigint PRIMARY KEY);

CREATE TABLE "user" (user_id bigint PRIMARY KEY);

Henrietta Dombrovskaya: PG Day Chicago Volunteer Sign-Up

17. Januar 2023 - 21:16

Hi, friends and colleagues in Chicago, and those who plan to attend the PG Day Chicago on April 20! We are looking for volunteers, and there are many options available. I know that the volunteer sign-up was not working before, but now it is fixed, and you can sign up here: the link to the volunteer form.

Frits Hoogland: Watching a YugabyteDB table: replication factor

17. Januar 2023 - 16:48

One of the most frequently reoccurring questions that we get at YugabyteDB how to interpret the information that YugabyteDB provides when people are testing (high) availability. This is a summary of that.

Pavlo Golub: usql: universal psql?

17. Januar 2023 - 11:00
usql? But why?

usql is a universal command-line interface for many database(repositories). But why are we still using CLI (command line utilities) in the 21st century? And what is wrong with psql?

Luca Ferrari: Handling NULLs and Empty values in PL/Perl

17. Januar 2023 - 1:00

How to correctly detect an SQL NULL value in PL/Perl.

Henrietta Dombrovskaya: One of many dumb things I did recently…

16. Januar 2023 - 16:36

I know that many people are reluctant to admit they made some stupid mistakes (like accidentally dropping a table in production). Some of these mistakes are very visible to the rest of the world, like the infamous Amazon S3 rm *, but raise the hands those who never ever did anything like this?! That’s why I always try to share my “oops,” in hope that it will make many people feel better.

Now, here is my recent story.

Andreas 'ads' Scherbaum: Miroslav Šedivý

16. Januar 2023 - 15:00
PostgreSQL Person of the Week Interview with Miroslav Šedivý: My name is Miroslav Šedivý, but most people call me Miro, which allows them to avoid typing some letters they do not have on their keyboard. I was born in Czechoslovakia (yes, I am over 30 now), studied computer sciences in France and Germany, and now I am living in Austria.

Christophe Pettus: OK, sometimes you can lock tables.

16. Januar 2023 - 8:00

Previously, I wrote that you should never lock tables. And you usually shouldn’t! But sometimes, there’s a good reason to. Here’s one.

When you are doing a schema-modifying operation, like adding a column to a table, PostgreSQL needs to take an ACCESS EXCLUSIVE lock on the table while it is modifying the system catalogs. Unless it needs to rewrite the table, this lock isn’t held for very long.