Sammlung von Newsfeeds

David Z: Large Object in PostgreSQL

Neues vom PostgreSQL Planet - 27. Januar 2023 - 22:21
1. Overview

Sometimes, you may need to manage large objects, i.e. CLOB, BLOB and BFILE, using PostgreSQL. There are two ways to deal with large objects in PostgreSQL: one is to use existing data type, i.e. bytea for binary large object, and text for character-based large object; another is to use pg_largeobject; This blog will explain how to use pg_largeobject.

Ryan Booz: PGSQL Phriday #005

Neues vom PostgreSQL Planet - 27. Januar 2023 - 14:00

Invitation from Ryan Lambert

Chris Travers: How TimescaleDB Solves Common PostgreSQL Problems in Database Operations With Data Retention Management

Neues vom PostgreSQL Planet - 26. Januar 2023 - 15:00

In my career, I have frequently worked for companies with large amounts of time-partitioned data, where I was a software engineer focusing on our PostgreSQL databases. So it was my job to deal with outages in the database. One source of repeated outages has been partition management to handle data retention.

Jagadeesh Panuganti: Migration of Synonyms from Oracle to PostgreSQL

Neues vom PostgreSQL Planet - 24. Januar 2023 - 21:22

You might have already seen our previous articles supporting Oracle to PostgreSQL and SQL server to PostgreSQL migrations. Our previous articles have provided solutions to some of the complexities during Oracle to PostgreSQL migrations. In this article, we are going to discuss about migration of synonyms from Oracle to PostgreSQL.

Robert Haas: Surviving Without A Superuser - Coming to v16

Neues vom PostgreSQL Planet - 24. Januar 2023 - 19:27
As I've written about before, a PostgreSQL superuser always has the ability to take over the operating system account in which PostgreSQL is running, but sometimes you'd like to have a role that can administer the database but not break out of it.

Jobin Augustine: WAL Compression in PostgreSQL and Recent Improvements in Version 15

Neues vom PostgreSQL Planet - 24. Januar 2023 - 15:03

Attempts to compress PostgreSQL WAL at different levels have always been around since the beginning. Some of the built-in features (wal_compression) have been there since 2016, and almost all backup tools do the WAL compression before taking it to the backup repository. But it is time to take another look at the built-in wal_compression because PostgreSQL 15 has much more to offer.

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

Neues vom PostgreSQL Planet - 24. Januar 2023 - 11:00
Disclaimer

This article will contain spoilers both on how I solved 2022 Day 14's challenge "Regolith Reservoir" 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.

Laurenz Albe: A unique constraint where NULL conflicts with everything

Neues vom PostgreSQL Planet - 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

Neues vom PostgreSQL Planet - 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?

Neues vom PostgreSQL Planet - 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

Neues vom PostgreSQL Planet - 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

Neues vom PostgreSQL Planet - 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

Neues vom PostgreSQL Planet - 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!

Neues vom PostgreSQL Planet - 23. Januar 2023 - 1:00

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

Ryan Booz: PostgreSQL Basics: Roles and Privileges

Neues vom PostgreSQL Planet - 19. Januar 2023 - 22:21

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)

Neues vom PostgreSQL Planet - 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

Neues vom PostgreSQL Planet - 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

Neues vom PostgreSQL Planet - 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

Neues vom PostgreSQL Planet - 18. Januar 2023 - 11:00
Disclaimer

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

Neues vom PostgreSQL Planet - 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);

Seiten