Month: October 2025| www.depesz.com
On 20th of October 2025, Masahiko Sawada committed patch: Support COPY TO for partitioned tables. Previously, COPY TO command didn't support directly specifying partitioned tables so users had to use COPY (SELECT ...) TO variant. This commit adds direct COPY TO support for partitioned tables, improving both usability and performance. Performance tests show … Continue reading "Waiting for PostgreSQL 19 – Support COPY TO for partitioned tables."| select * from depesz;
On 3rd of October 2025, Tatsuo Ishii committed patch:| select * from depesz;
On 29th of September 2025, Tom Lane committed patch: Add GROUP BY ALL. GROUP BY ALL is a form of GROUP BY that adds any TargetExpr that does not contain an aggregate or window function into the groupClause of the query, making it exactly equivalent to specifying those same expressions in an explicit GROUP … Continue reading "Waiting for PostgreSQL 19 – Add GROUP BY ALL."| select * from depesz;
Lately I've been running quite a lot of commands that take long time, and was wondering how long they took. Of course I can use the standard time program, but it doesn't show me how long it already took.| select * from depesz;
On 9th of September 2025, Dean Rasheed committed patch:| select * from depesz;
Yesterday someone posted a set of queries for interviews, all centered on answering business-like questions from database.| select * from depesz;
Recently I've seen case like:| select * from depesz;
In previous post in the series I wrote about how to interpret single line in explain analyze output, it's structure, and later on described all basic data-getting operations (nodes in explain tree).| select * from depesz;
On 29th of July 2025, David Rowley committed patch:| select * from depesz;
Got interested recently in speed of pg_dump. Specifically, if, over the years, it has became faster, and if yes, how much.| select * from depesz;
On 8th of April 2025, Daniel Gustafsson committed patch:| select * from depesz;
On 7th of April 2025, Álvaro Herrera committed patch: Allow NOT NULL constraints to be added as NOT VALID This allows them to be added without scanning the table, and validating them afterwards without holding access exclusive lock on the table after any violating rows have been deleted or fixed. Doing ALTER TABLE … Continue reading "Waiting for PostgreSQL 18 – Allow NOT NULL constraints to be added as NOT VALID"| select * from depesz;
On 5th of April 2025, Álvaro Herrera committed patch: Add modern SHA-2 based password hashes to pgcrypto. This adapts the publicly available reference implementation on https://www.akkadia.org/drepper/SHA-crypt.txt and adds the new hash algorithms sha256crypt and sha512crypt to crypt() and gen_salt() respectively. Author: Bernd Helmle <mailings@oopsware.de> Reviewed-by: Japin Li <japinli@hotmail.com> Discussion: https://postgr.es/m/c763235a2757e2f5f9e3e27268b9028349cef659.camel@oopswar...| select * from depesz;
On 4th of April 2025, Andrew Dunstan committed patch: Non text modes for pg_dumpall, correspondingly change pg_restore pg_dumpall acquires a new -F/--format option, with the same meanings as pg_dump. The default is p, meaning plain text. For any other value, a directory is created containing two files, globals.data and map.dat. The first contains SQL … Continue reading "Waiting for PostgreSQL 18 – Non text modes for pg_dumpall, correspondingly change pg_restore"| select * from depesz;
On 5th of March 2025, Andrew Dunstan committed patch: Allow json{b}_strip_nulls to remove null array elements An additional paramater ("strip_in_arrays") is added to these functions. It defaults to false. If true, then null array elements are removed as well as null valued object fields. JSON that just consists of a single null is not … Continue reading "Waiting for PostgreSQL 18 – Allow json{b}_strip_nulls to remove null array elements"| select * from depesz;
On 11st of December 2024, Masahiko Sawada committed patch: Add UUID version 7 generation function. This commit introduces the uuidv7() SQL function, which generates UUID version 7 as specified in RFC 9652. UUIDv7 combines a Unix timestamp in milliseconds and random bits, offering both uniqueness and sortability. In our implementation, the 12-bit sub-millisecond … Continue reading "Waiting for PostgreSQL 18 – Add UUID version 7 generation function."| select * from depesz;
I once wrote about this problem, but given that we now have DO blocks and procedures I can make nicer, easy to use example. Over the years there have been many improvements to how long ALTER TABLE can take. You can now (in some cases) change datatype without rewrite or add default value. Regardless how … Continue reading "How to ALTER tables without breaking application?"| select * from depesz;
Every now and then I see something like this: select u.* from users u where 0 = (select count(*) from addresses a where a.user_id = u.id); and it kinda pains me. So figured, I'll write about what is the problem with it, and how to avoid such constructs. Lets consider what went through someones mind … Continue reading "SQL best practices – don’t compare count(*) with 0"| select * from depesz;
Based on checking logs, and my own personal needs I added more categories of keywords to pgdoc.link: environment variables, like: PGHOST config file names, like: pgpass functions from some contrib modules, like: akeys, or cube_ll_coord various keyword-like functions (?), like: current_user. This happened thanks to bug report by Benjie Gillam This brought the total number … Continue reading "Changes on pgdoc.link"| select * from depesz;
Was asked recently about optimization of interesting case. There was table like: =$ CREATE TABLE input_data ( category_id INT8, object_id INT8, interaction_ts timestamptz, interaction_type TEXT, interaction_count INT4 ); And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and timestamp truncated to hour. Basically, storing somewhere result of: … Continue reading "Grouping data into array of sums – fun with custom aggregates"| select * from depesz;
On 3rd of March 2025, Tomas Vondra committed patch:| select * from depesz;
On 21st of February 2025, Robert Haas committed patch:| select * from depesz;
On 7th of February 2025, Peter Eisentraut committed patch:| select * from depesz;
On 14th of February 2025, Nathan Bossart committed patch:| select * from depesz;
On 11st of February 2025, Nathan Bossart committed patch:| select * from depesz;
On 22nd of January 2025, Tom Lane committed patch:| select * from depesz;
This question happens every now and then in one of PostgreSQL support places.| select * from depesz;
On 16th of January 2025, Dean Rasheed committed patch:| select * from depesz;
On 6th of January 2025, Nathan Bossart committed patch:| select * from depesz;
On 11st of December 2024, David Rowley committed patch:| select * from depesz;
On 27th of November 2024, Peter Eisentraut committed patch:| select * from depesz;
On 18th of December 2024, Michael Paquier committed patch:| select * from depesz;
On 16th of January 2024, Peter Eisentraut committed patch:| select * from depesz;
Ever been in situation where you had to sort data that is partially text, and partially numerical?| select * from depesz;
On 14th of December 2022, Peter Eisentraut committed patch:| select * from depesz;