Postgres ships with a genuinely bountiful catalog of built-in functions. There are literally thousands of them, covering everything from trigonometry to JSON path queries to full-text ranking. It's a kitchen sink of staggering proportions. Somehow despite all of that, there are actually still a few gaps to fill.

Need a random date for some test data? That's going to be an entire expression, I'm afraid. How about the exact definition of a role, tablespace, or database? You better have a GUI database tool like pgAdmin or access to the pg_dumpall command with a little assistance from grep.

It's little annoyances like this that Postgres 19 aims to alleviate. Let's explore these new quality of life enhancements!

In the Near Future

Suppose we want a random date sometime in 2026. The canonical approach for this has been to begin with January 1st and add a random amount of days:

SELECT '2026-01-01'::DATE + (random(0, 364) * INTERVAL '1 day');

It's kind of a hack, but it works. Aside from not accounting for leap years, there's also something else to take note of:

SELECT pg_typeof('2026-01-01'::DATE + (random(0, 364) * INTERVAL '1 day'));

          pg_typeof          
-----------------------------
 timestamp without time zone

We asked for a date and got a TIMESTAMP WITHOUT TIME ZONE, because adding an interval to a date promotes the whole expression. If we actually wanted a DATE, we have to wrap the result in another cast. If we wanted a TIMESTAMP WITH TIME ZONE, that's a different cast. That's not exactly critical, but type sensitivity varies across platforms.

Either way, that's a lot of ceremony for "give me a random date."

So Postgres 19 adds a family of random() overloads that take an explicit lower and upper bound. The random() we all know and love is still around in the mathematical functions, but now there are three new temporal definitions in datetime functions.

There's one for each of the valid Postgres date and time types:

SELECT
  random(1, 100)                              AS an_integer,
  random(1000000000::bigint, 9999999999)      AS a_bigint,
  random(0.00, 1.00)                          AS a_numeric,
  random('2026-01-01'::date, '2026-12-31')    AS a_date,
  random('2026-01-01'::timestamp,
         now()::timestamp)                    AS a_timestamp,
  random('2026-01-01'::timestamptz,
         now())                               AS a_timestamptz;

-[ RECORD 1 ]-+------------------------------
an_integer    | 47
a_bigint      | 8240669905
a_numeric     | 0.58
a_date        | 2026-12-23
a_timestamp   | 2026-06-22 02:08:50.238273
a_timestamptz | 2026-04-13 11:57:41.820763+00

A random date is now just random(start, end), and the result is a real DATE. No interval multiplication, trailing cast, or surprise timestamp conversion. Ditto for TIMESTAMP and TIMESTAMPTZ. The inclusive bounds also address the issue we had with leap years, where adding 364 days could mean never reaching December 31st.

You may have also noticed the matching significant figures in the NUMERIC output. If you haven't ever used that before, it works universally. Here it is with four digits of precision:

SELECT random(0.0000, 1.0000);

 random      
--------
 0.4829

All of this takes the guesswork out of the function. Type goes in, type goes out. Everyone can explain that.

A Short Aside About Seeds

Random data is most useful when it's reproducible. A test that fails on one random dataset and passes on the next can be worse than no test at all. Sometimes, we want the same "random" values every time we run a script. For that, there’s the setseed() function:

SELECT setseed(0.42);
SELECT random(1, 100), random('2026-01-01'::date, '2026-12-31');

 random |   random   
--------+------------
     96 | 2026-07-05

Execute those two commands in a fresh session and the random output will always be the same. That allows us to deterministically generate sample data in bulk with the aid of generate_series(): 

SELECT setseed(0.42);
SELECT 
  g                           AS order_id,
  random(1, 500)              AS customer_id,
  random('2026-01-01', now()) AS placed_at
FROM
  generate_series(1, 5) g;

 order_id | customer_id |           placed_at           
----------+-------------+-------------------------------
        1 |         381 | 2026-03-15 19:17:31.344619+00
        2 |         480 | 2026-01-14 02:18:49.998104+00
        3 |          49 | 2026-02-13 13:05:42.398651+00
        4 |         252 | 2026-03-21 06:08:25.325942+00
        5 |         295 | 2026-06-03 12:22:22.426006+00

Five hundred customers, orders scattered across the year, all in one readable statement. Bumping the series to a million nets us a respectable load-testing fixture. Neat!

What else does Postgres 19 have for us?

The DDL Firehose

Quick! Without using some GUI, what is the exact definition of the postgres role on this server? Until now, the only way to do something like that was with the pg_dumpall cluster-wide dump utility. Why? Because roles, tablespaces, and databases are global objects living outside of any single database. Short of mining the system catalog, there's been no historical way of doing this.

Here's how it used to work:

# Every role and tablespace definition
pg_dumpall -g

# Roles only
pg_dumpall -r

# Tablespaces only
pg_dumpall -t

# Databases are worse: dump the whole schema and go fishing
pg_dumpall -s | grep DATABASE

It gets the job done, but consider how awful that interface is. We can't use standard SQL, so it's not possible to join the result against the catalogs, filter it, or feed it into another statement. We're relying on an external binary, which means either local or remote shell access. If it's local, our pg_dumpall must be the same version or newer than what's on the server. And the utility dumps every global object we request, meaning subsequent filtering. It's a mess!

Well, it was a mess.

On a Role

Postgres 19 introduces three new information functions designed to interrogate the database on the previously obfuscated global objects. The first of these is pg_get_role_ddl(), which shows exactly how to recreate a named role.

Let's build a role with enough going on to be interesting:

CREATE ROLE reporting NOLOGIN;

CREATE USER app_ro
  PASSWORD 'secret123'
  CONNECTION LIMIT 10
  VALID UNTIL '2027-01-01'
  IN ROLE reporting;

ALTER ROLE app_ro SET statement_timeout = '30s';
ALTER ROLE app_ro SET search_path = app, public;

Now, instead of dumping the entire cluster's worth of roles and hunting for this one, we can ask for it directly:

SELECT pg_get_role_ddl('app_ro');

CREATE ROLE app_ro NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 10 VALID UNTIL '2027-01-01 00:00:00+00';
ALTER ROLE app_ro SET statement_timeout TO '30s';
ALTER ROLE app_ro SET search_path TO 'app', 'public';
GRANT reporting TO app_ro WITH ADMIN FALSE, INHERIT TRUE, SET TRUE GRANTED BY postgres;

Everything is there: attributes, per-role settings, even the membership grant into reporting. If you’re wondering why it says CREATE ROLE rather than CREATE USER, remember that the latter is just an alias for CREATE ROLE … LOGIN. And notice what is conspicuously absent: not even a hash of the password. This is a deliberate choice to avoid leaking sensitive material. That restriction doesn't apply to pg_dumpall, so that information is still available, just not to a user-callable function.

The function currently accepts two optional settings as name and value pairs after the role. If the membership grants are unnecessary, disable them with memberships: 

SELECT pg_get_role_ddl('app_ro', 'memberships', 'false');

And for a human to read the output rather than a script, pretty breaks the attributes onto their own indented lines: 

SELECT pg_get_role_ddl('app_ro', 'pretty', 'true');

CREATE ROLE app_ro
    NOSUPERUSER
    INHERIT
    NOCREATEROLE
    NOCREATEDB
    LOGIN
    NOREPLICATION
    NOBYPASSRLS
    CONNECTION LIMIT 10
    VALID UNTIL '2027-01-01 00:00:00+00';
ALTER ROLE app_ro SET statement_timeout TO '30s';
ALTER ROLE app_ro SET search_path TO 'app', 'public';
GRANT reporting TO app_ro WITH ADMIN FALSE, INHERIT TRUE, SET TRUE GRANTED BY postgres;

One function, one query, no shell.

Roles, though, are only one of the three global object types that used to send us reaching for pg_dumpall. Let’s take a look at the other two.

Databases and Tablespaces

The other two new DDL information functions focus on databases and tablespaces. These are fairly low-level objects, and tablespaces in particular drive Postgres DBAs nuts. The pg_tablespaces view does not tell us where the tablespace lives. We need the pg_tablespace_location() function for that.

Consider this tablespace:

CREATE TABLESPACE fast_ts LOCATION '/var/lib/postgresql/fast_ts';
ALTER TABLESPACE fast_ts SET (random_page_cost = 1.1);

Say it's been a while and we forgot where the tablespace went, and it wasn't documented for whatever reason. Previously, we had to do this to get all of the tablespace information:

SELECT t.spcname, r.rolname AS owner, t.spcoptions,
       pg_tablespace_location(t.oid) AS location
  FROM pg_tablespace t
  JOIN pg_roles r ON (r.oid = t.spcowner);

  spcname   |  owner   |       spcoptions       |          location           
------------+----------+------------------------+-----------------------------
 pg_default | postgres |                        | 
 pg_global  | postgres |                        | 
 fast_ts    | postgres | {random_page_cost=1.1} | /var/lib/postgresql/fast_ts

Instead of that, why not just retrieve the tablespace definition itself? Now let's use the new pg_get_tablespace_ddl() function:

SELECT pg_get_tablespace_ddl('fast_ts');

CREATE TABLESPACE fast_ts OWNER postgres LOCATION '/var/lib/postgresql/fast_ts';
ALTER TABLESPACE fast_ts SET (random_page_cost='1.1');

Both the location and the storage settings arrive intact. The owner is also just a username rather than a system catalog oid that requires a join to resolve. Maybe I’m lazy, but sometimes I just want to see the DDL for an object without jumping through a bunch of hoops.

Databases work the same way. Consider this busy database definition that even uses the new tablespace:

CREATE DATABASE mydb
  WITH LOCALE_PROVIDER = builtin
       BUILTIN_LOCALE = 'C.UTF-8'
       TABLESPACE fast_ts
       CONNECTION LIMIT = 5
       TEMPLATE = template0;

The pg_get_database_ddl() function makes short work of it:

SELECT pg_get_database_ddl('mydb');

CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = builtin LOCALE = 'en_US.utf8' BUILTIN_LOCALE = 'C.UTF-8' TABLESPACE = fast_ts;
ALTER DATABASE mydb OWNER TO postgres;
ALTER DATABASE mydb CONNECTION LIMIT = 5;

That's the identical output we once needed pg_dumpall -s to obtain with the assistance of grep. Now it arrives as the result of a query with no external tooling or schema dump to wade through.

Each of these accepts the same style of name and value options, so we can suppress the OWNER line, drop a database's TABLESPACE clause, or request pretty formatting:

SELECT pg_get_database_ddl('mydb', 'owner', 'false', 'tablespace', 'false');

CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = builtin LOCALE = 'en_US.utf8' BUILTIN_LOCALE = 'C.UTF-8';
ALTER DATABASE mydb CONNECTION LIMIT = 5;

Imagine the possibilities! Check this out:

CREATE OR REPLACE VIEW v_db_definitions AS
SELECT d.datname, pg_get_database_ddl(d.datname::regdatabase, 
       'owner', 'false', 'pretty', 'true')
  FROM pg_database d
 WHERE d.datname NOT IN ('template0', 'template1');

SELECT * FROM v_db_definitions;

 datname  |            pg_get_database_ddl            
----------+-------------------------------------------
 postgres | CREATE DATABASE postgres                 +
          |     WITH TEMPLATE = template0            +
          |     ENCODING = 'UTF8'                    +
          |     LOCALE_PROVIDER = libc               +
          |     LOCALE = 'en_US.utf8';
 mydb     | CREATE DATABASE mydb                     +
          |     WITH TEMPLATE = template0            +
          |     ENCODING = 'UTF8'                    +
          |     LOCALE_PROVIDER = builtin            +
          |     LOCALE = 'en_US.utf8'                +
          |     BUILTIN_LOCALE = 'C.UTF-8'           +
          |     TABLESPACE = fast_ts;
 mydb     | ALTER DATABASE mydb CONNECTION LIMIT = 5;

Try doing that with pg_dumpall! Throw on a timestamp and we can audit database definitions over time. SQL does make everything better, after all.

Final Thoughts

Little tweaks like this improve QoL in a way users don't exactly expect, but may find pleasantly surprising. "Oh, Postgres does that? Nice!" Generating a random date is now much easier and less error-prone. Obtaining DDL for global objects no longer requires external tooling like a shell utility or a GUI admin client. Now it's all just a function call.

I won't pretend the DDL functions are a complete analog for pg_dump and pg_dumpall. They currently cover global objects and nothing more, so there's still room to grow. There have long been functions for retrieving function, trigger, index, and view definitions, so it would be nice if every object had a DDL extraction equivalent. We don't have a full onboard introspection library just yet, but it's getting closer.

In the end, Postgres 19 makes the engine just a tiny bit more appealing in ways that don't require a lot of fanfare. This isn't the headline catching query hint system, or temporal tables, or REPACK syntax, or any of the other more noteworthy topics I've covered in this series so far. They're just examples of the long and steady forward march of improvement each release tends to bring.

And Postgres 19 still has a few more tricks up its sleeve - more coming next week. Stay tuned!