Neues vom PostgreSQL Planet
Laurenz Albe: A unique constraint where NULL conflicts with everything
© 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 constraintWe 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
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?
Andreas 'ads' Scherbaum: Jim Chanco Jr
Ryan Lambert: Relational and Non-relational Data: PGSQL Phriday #005
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
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!
A simple way to make more attractive the PostgreSQL command line interface!
Ryan Booz: PostgreSQL Basics: Roles and Privileges
Authentication
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)
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
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
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
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
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
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
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?
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
How to correctly detect an SQL NULL value in PL/Perl.
Henrietta Dombrovskaya: One of many dumb things I did recently…
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ý
Christophe Pettus: OK, sometimes you can lock tables.
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.