Fixing "FATAL: sorry, too many clients already" in PostgreSQL

Posted by Kyle Hankinson August 21, 2026

Your application cannot reach the database, and the logs show one of these:

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

Both mean the same underlying thing: every connection slot up to max_connections is taken, and Postgres is turning new clients away at the door. The second wording appears a little earlier because Postgres holds back a few slots (superuser_reserved_connections, default 3) so an administrator can still get in while ordinary users cannot. That exact message is what PostgreSQL 16 prints; on PostgreSQL 14 the same condition reads remaining connection slots are reserved for non-replication superuser connections. I reproduced both on Docker instances started with max_connections=5: two app-role connections plus the reserve filled the server, the third app connection got the "reserved" message, and once superuser sessions consumed the reserve too, the next attempt got "sorry, too many clients already".

The instinctive response is to raise max_connections. Sometimes that is right, but in most incidents the slots are being wasted rather than used, so start by finding out who is holding them.

Step 1: See how bad it is

Connect as a superuser (this is what the reserved slots are for) and run:

SHOW max_connections;

SELECT count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend';

The backend_type filter matters: pg_stat_activity also lists internal workers like the autovacuum launcher and WAL writer, and you only care about client sessions. If the count is at or near max_connections, you are in the right article.

Step 2: Find out who is hoarding slots

Group the sessions by who owns them and what they are doing:

SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY count(*) DESC;

The state column is the diagnostic gold. active sessions are running queries; a wall of idle sessions from one application usually means an oversized or leaking connection pool; idle in transaction sessions are the worst case, holding a transaction (and its locks) open while doing nothing. All of these columns are stable from PostgreSQL 10 onward; the full column list is in the monitoring documentation.

To see how long the squatters have been squatting:

SELECT pid, usename, state,
       now() - state_change AS in_state_for,
       left(query, 45) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
ORDER BY state_change
LIMIT 10;

On a test server where I parked a session mid-transaction, this surfaced it immediately: idle in transaction for 22 seconds with the last completed statement still attached. In production you will see the same shape with hours instead of seconds. These queries are plain SQL, so they run as-is from any client; in SQLPro Studio you can export the result grid to CSV or JSON, which is handy for keeping a snapshot of exactly who was connected when the incident hit.

Step 3: Reclaim slots to stop the bleeding

You can terminate sessions server-side:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';

Verified on PostgreSQL 16: the function returns t per session and the backend disappears from pg_stat_activity. Be careful with this. pg_terminate_backend kills the session and rolls back its open transaction; whatever that application had half-done is gone, and a client that does not handle disconnects will surface errors to its users. Target it narrowly (specific PIDs, long idle times, known-leaky app names) rather than sweeping everything. For a single stuck query, prefer pg_cancel_backend, which cancels the statement but keeps the session; the difference is covered in killing or cancelling a long-running Postgres query. The same technique clears connections ahead of maintenance, as in dropping a database with active connections.

Step 4: Keep it from happening again

Set a timeout for abandoned transactions. This is the single highest-value setting in this area:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

Caution: any session that sits idle inside a transaction longer than the limit is disconnected, so make sure nothing legitimate (interactive psql sessions during migrations, for example) needs to hold transactions open longer. PostgreSQL 14+ also offers idle_session_timeout for sessions idle outside a transaction, which is stricter medicine; both are described in the connection settings documentation.

Cap chatty roles. If one service must never starve the others:

ALTER ROLE app_user CONNECTION LIMIT 30;

Verified on PostgreSQL 16: connections beyond the limit fail with FATAL: too many connections for role "app_user", which pins the failure on the misbehaving service instead of taking down every consumer of the database.

Fix the pool, not the symptom. The most common root causes are boring: a pool created per request instead of per process, a serverless function that opens a raw connection per invocation, or twelve app instances each configured with a 20-connection pool against a 100-connection server. Postgres backends are processes, and each one costs real memory and coordination overhead, which is why max_connections = 1000 tends to make throughput worse, not better. The PostgreSQL wiki's Number Of Database Connections page makes the case that a small pool feeding queued requests usually outperforms a huge connection count. If you cannot shrink the client side, put PgBouncer in transaction mode in front of the server; thousands of client connections then share a few dozen real backends.

Raise max_connections only after the math says so. If every session is genuinely active and short-lived and you still hit the ceiling, then yes, raise it (it requires a server restart) and size memory accordingly, since settings like work_mem are per operation within each backend. On PostgreSQL 16 and later there is also reserved_connections, which holds back slots for roles granted pg_use_reserved_connections, so your monitoring or migration tooling can keep superuser-free guaranteed access.

One last note for managed Postgres: RDS, Supabase, Heroku, and similar services often cap connections well below the stock default of 100 on smaller plans, and some route you through a built-in pooler with its own limits. If the numbers from Step 1 look nothing like your configuration, check the plan's connection limit before blaming your code.

Tags: PostgreSQL

SQL NULL traps: = NULL, NOT IN, and sort order

Posted by Kyle Hankinson August 7, 2026

A query that returns zero rows with zero errors is harder to debug than one that fails loudly, and NULL is behind more of those silent empties than everything else combined. The root cause is always the same: SQL comparisons involving NULL do not evaluate to true or false but to a third value, UNKNOWN, and a WHERE clause keeps only rows where the condition is TRUE. UNKNOWN rows are dropped without comment.

That one rule produces three distinct traps. All of the MySQL, PostgreSQL, and SQLite behavior below was run against MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6; the SQL Server behavior is cited from Microsoft's documentation, linked where used.

Two tables are enough to demonstrate everything:

CREATE TABLE customers (id int, name varchar(20));
INSERT INTO customers VALUES (1, 'alice'), (2, 'bob'), (3, 'carol');

CREATE TABLE orders (customer_id int);
INSERT INTO orders VALUES (1), (NULL);   -- one order has no customer

Trap 1: WHERE col = NULL matches nothing

The natural way to find the orphaned order reads fine and returns nothing:

SELECT count(*) FROM orders WHERE customer_id = NULL;   -- 0
SELECT count(*) FROM orders WHERE customer_id IS NULL;  -- 1

Identical results on MySQL, PostgreSQL, and SQLite: the = version finds zero rows even though a NULL row is sitting right there. NULL means "unknown", and asking whether an unknown value equals another unknown value can only be answered "unknown", so the comparison never becomes TRUE for any row. SQL Server follows the same logic under its default settings; Microsoft's NULL and UNKNOWN page states that comparisons between two null values, or between a null value and any other value, return unknown, and directs you to IS NULL / IS NOT NULL.

When you genuinely want NULL-tolerant equality, where NULL equals NULL and nothing else, every engine has an operator for it, they just disagree on the spelling:

MySQL's comparison operators documentation notes that <=> is equivalent to the standard IS NOT DISTINCT FROM, so all four spellings mean the same thing.

Trap 2: one NULL turns NOT IN into an empty result

Now the trap with real teeth. Find the customers who have no orders:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

You would expect bob and carol. On MySQL 8.4, PostgreSQL 16, and SQLite 3.50.6 alike, this returns zero rows. Not a wrong list, an empty one, and the same three-valued logic explains it in SQL Server as well.

The subquery produces the list (1, NULL), and id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL. That second comparison is UNKNOWN for every row in the table, and TRUE AND UNKNOWN is UNKNOWN, so no row ever qualifies. One stray NULL in the subquery quietly vetoes the entire result. This is the nastiest variety of NULL bug because the query works perfectly in development and then returns nothing in production the day the first NULL shows up in that column.

Two fixes, both verified to return bob and carol on all three engines. The direct one is to keep NULLs out of the list:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders
                 WHERE customer_id IS NOT NULL);

The better one is to stop using NOT IN for this job entirely:

SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);

NOT EXISTS asks "is there a matching row?" instead of comparing values, so NULLs in the subquery cannot poison it. It states the anti-join intent directly (the same family of shapes covered in SQL joins explained), and it is safe to use as a habit even on columns that are NOT NULL today, because columns have a way of becoming nullable later.

Trap 3: every engine sorts NULLs somewhere different

The first two traps behave identically everywhere. The third is where the engines part ways. Sort three values, one of them NULL:

CREATE TABLE s (v int);
INSERT INTO s VALUES (2), (NULL), (1);
SELECT v FROM s ORDER BY v ASC;

MySQL and SQLite treat NULL as smaller than every value, so it leads an ascending sort. PostgreSQL treats NULL as larger, so it trails. SQL Server sides with MySQL: the ORDER BY clause documentation states that NULL values are treated as the lowest possible values. The practical consequence: port a "latest items first, blanks at the bottom" query from MySQL to Postgres and the blanks migrate from bottom to top with no error and no warning.

Where the standard syntax exists, pinning the position is trivial. Verified on PostgreSQL 16 and SQLite 3.50.6:

SELECT v FROM s ORDER BY v ASC NULLS LAST;   -- 1, 2, NULL

On MySQL 8.4 that syntax is a hard error (ERROR 1064), but a boolean sort key does the same job, verified to return 1, 2, NULL:

SELECT v FROM s ORDER BY (v IS NULL), v;

v IS NULL is 0 for values and 1 for NULLs, so NULLs sink to the end. SQL Server lacks the syntax too; the equivalent trick there is ORDER BY CASE WHEN v IS NULL THEN 1 ELSE 0 END, v, using the conditional ordering pattern shown in the same ORDER BY documentation.

Chasing this class of bug across engines is considerably less painful when you can run the identical script against MySQL, PostgreSQL, SQL Server, and SQLite connections in one place and compare the grids, which is precisely the sort of side-by-side work SQLPro Studio exists for.

The habits that make all three traps a non-issue: write IS NULL rather than = NULL always, reach for NOT EXISTS rather than NOT IN under a subquery, and never let a query's correctness depend on where the engine happens to put NULLs in a sort. Declare it with NULLS LAST or a boolean sort key, and the query means the same thing everywhere.

Tags: MySQL PostgreSQL Microsoft SQL Server SQLite

"Permission denied for schema public" in PostgreSQL 15 and later

Posted by Kyle Hankinson August 4, 2026

If a role that worked fine for years suddenly fails like this after a Postgres upgrade or a move to a new server:

CREATE TABLE widgets (id int);

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE widgets (id int);
                     ^

nothing is broken. You are on PostgreSQL 15 or later, and this is a deliberate change: ordinary roles can no longer create objects in the public schema by default. The same user, same statement, same everything succeeds on PostgreSQL 14 and fails on 15, 16, 17, and 18. I verified this directly by running an identical non-superuser CREATE TABLE against PostgreSQL 14.23 (succeeds) and 16.14 (fails with the error above).

The quick fix

If you just want the old behavior back for your application role, connect to the affected database as its owner (or a superuser) and run:

GRANT CREATE ON SCHEMA public TO app_user;

Verified on PostgreSQL 16: the CREATE TABLE that failed a second earlier succeeds immediately after the grant. USAGE on public is still granted to everyone by default, so GRANT CREATE alone is enough; add USAGE to the statement only if someone has revoked it.

A caution before you paste that into production: this reopens a shared schema. The restriction exists for a reason, so treat the grant as a conscious decision rather than a reflex, and consider the alternatives below first. Also note that GRANT ... TO PUBLIC (every role, present and future) restores the pre-15 world for the entire server one database at a time, which is almost never what you want.

What actually changed in PostgreSQL 15

Two related things, both visible in \dn+ public.

On PostgreSQL 14, the default looks like this:

  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |

The line =UC/postgres means every role (= is the PUBLIC pseudo-role) holds both USAGE and CREATE. Anyone who can connect can create tables.

On PostgreSQL 16, the same command shows:

  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |

Two differences. First, =U/...: everyone still has USAGE (you can reference objects in public), but CREATE is gone. Second, the schema is now owned by pg_database_owner, a pseudo-role that always resolves to whoever owns the current database, instead of being owned by the bootstrap superuser.

The motivation was security. With a world-writable default schema, any user could create objects (including operators and functions) that other users' queries might resolve first via search_path, the attack pattern behind CVE-2018-1058. The change is listed in the PostgreSQL 15 release notes, and the reasoning is laid out in the schemas documentation.

You can check what a role can actually do without reading privilege strings:

SELECT has_schema_privilege('app_user', 'public', 'CREATE') AS can_create,
       has_schema_privilege('app_user', 'public', 'USAGE')  AS can_usage;

On a default PostgreSQL 16 database this returns f and t for a plain role, which is the whole story of the error in one row.

Three fixes, and when to use each

The dedicated schema is usually the right call for anything shared. The role owns its schema outright and needs no grants on public at all:

CREATE SCHEMA app AUTHORIZATION app_user;

Unqualified table names will still resolve to public by default, so either qualify names (CREATE TABLE app.gadgets ...) or point the role's search path at its schema:

ALTER ROLE app_user IN DATABASE appdb SET search_path = app;

Verified on PostgreSQL 16: after that ALTER ROLE, an unqualified CREATE TABLE from app_user lands in the app schema.

The ownership route fits the common one-database-per-app layout. Because public now belongs to pg_database_owner, making your application role the database owner gives it full rights over public with no explicit grants (verified on 16: a fresh database, one ALTER DATABASE ... OWNER TO, and the role creates tables in public immediately). Cleaner still is creating it correctly from the start with CREATE DATABASE appdb OWNER app_user. If you decide to rebuild an existing database under the right owner and connections are in the way, see how to drop a database with active connections.

The gotchas that keep this error alive

Grants are per database. GRANT CREATE ON SCHEMA public fixes only the database you were connected to when you ran it. Every database has its own public schema with its own ACL. I verified this on PostgreSQL 16: after granting in appdb, the same role creating a table in appdb2 still fails with permission denied for schema public. If your app spans several databases, repeat the grant in each one. This is worth remembering in a GUI client too: SQLPro Studio shows each database's schemas and owners in its object browser, which makes it easy to confirm against which database a grant actually ran before you assume the problem is solved.

Old tutorials predate the change. Most "create a Postgres user for your app" guides written before late 2022 end with CREATE ROLE ... LOGIN and nothing else, because nothing else was needed. On 15+ that recipe produces a user who can connect and read but cannot create anything.

Restores trip it too. Dump a pre-15 database, restore into 15+, and objects owned by non-owner roles or scripts that recreate tables in public can hit the error mid-restore. Run the restore as the database owner, or apply one of the fixes above first.

Managed providers vary. On services like RDS or Cloud SQL you typically do not get a true superuser; use the admin account the provider gives you (which owns the databases it creates) to run the grants. Some providers pre-configure public more permissively, so check \dn+ public before assuming either behavior.

The GRANT syntax has more depth (granting to groups, WITH GRANT OPTION, default privileges for future objects) than one article can cover; the GRANT reference is the authoritative list. For most teams, though, the decision is simply the table above: reopen public, give the app its own schema, or make the app the owner. Any of the three turns the error into a one-time footnote of the PostgreSQL 15 upgrade.

Tags: PostgreSQL

Fixing "duplicate key value violates unique constraint" in Postgres

Posted by Kyle Hankinson July 17, 2026

You insert a row, let Postgres generate the ID, and get this back:

ERROR:  duplicate key value violates unique constraint "customers_pkey"
DETAIL:  Key (id)=(1) already exists.

The confusing part is that you never supplied a duplicate. Postgres did. The sequence that hands out values for your SERIAL or identity column is pointing at a number that already exists in the table, almost always because rows were inserted with explicit IDs at some point: a bulk import, a pg_restore, an ETL job, a fixture loader, or a well-meaning script that copied data between environments.

Sequences in Postgres only advance when something calls nextval on them. An INSERT that supplies its own id value writes the row but leaves the sequence untouched. The next insert that relies on the default calls nextval, gets a stale number, and collides with a row that is already there.

Reproducing it in twenty seconds

Here is the whole failure, run on PostgreSQL 16. It behaves identically for identity columns (PostgreSQL 10 and later) and old-style SERIAL columns:

CREATE TABLE customers (
    id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

-- an "import" that supplies its own IDs
INSERT INTO customers (id, name)
VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Edsger');

-- a normal application insert
INSERT INTO customers (name) VALUES ('Linus');

On PostgreSQL 16 that last statement fails with exactly the error above: the sequence hands out 1, and row 1 already exists. Run it two more times and it fails on 2, then 3, then finally succeeds with 4. That staircase pattern (a few failures, then it starts working) is a reliable sign the sequence fell behind rather than anything actually being duplicated.

Confirm the diagnosis

First find the sequence. Don't guess at the name; ask Postgres:

SELECT pg_get_serial_sequence('customers', 'id');

This works for both identity and SERIAL columns and returns the schema-qualified name, here public.customers_id_seq. Now compare where the sequence is against where the data is:

SELECT last_value, is_called FROM customers_id_seq;
SELECT MAX(id) FROM customers;

On the broken table above, this is what comes back:

If last_value is less than MAX(id), the sequence is behind and every default insert will keep colliding until it catches up. A client like SQLPro for Postgres makes this comparison quick since you can run both statements together and see the two result sets side by side, and the sequence itself is visible in the schema browser next to the table it feeds.

The fix

One statement resynchronizes the sequence with the table:

SELECT setval(
    pg_get_serial_sequence('customers', 'id'),
    COALESCE(MAX(id), 1),
    MAX(id) IS NOT NULL
) FROM customers;

After running this, INSERT INTO customers (name) VALUES ('Linus') succeeds and returns id 4. Verified on PostgreSQL 16.

The third argument deserves a closer look because most snippets floating around omit it. setval accepts an is_called flag: when true, the next nextval returns the stored value plus one; when false, it returns the stored value itself. MAX(id) IS NOT NULL evaluates to true for a table with rows (correct: the next ID should be max plus one) and false for an empty table (correct: the first ID should be 1, not 2).

This matters. Run the two-argument version on an empty table and you burn ID 1:

-- empty table, two-argument setval
SELECT setval(pg_get_serial_sequence('empty_tbl', 'id'), COALESCE(MAX(id), 1))
FROM empty_tbl;

INSERT INTO empty_tbl (v) VALUES ('a') RETURNING id;
-- returns 2, not 1

The three-argument version returns 1 for the same insert. Both behaviors verified on PostgreSQL 16. Skipping an ID is rarely harmful, but if you are resetting sequences you may as well reset them correctly. The full setval semantics are in the sequence functions documentation.

Fixing every sequence after a full import

After restoring a whole database from a dump that carried explicit IDs, you rarely want to fix tables one at a time. This block finds every column in public that is backed by a sequence and resynchronizes each one:

DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT quote_ident(t.relname) AS table_name,
               quote_ident(a.attname) AS column_name,
               pg_get_serial_sequence(quote_ident(n.nspname) || '.' || quote_ident(t.relname), a.attname) AS seq_name
        FROM pg_class t
        JOIN pg_namespace n ON n.oid = t.relnamespace
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum > 0 AND NOT a.attisdropped
        WHERE n.nspname = 'public'
          AND t.relkind = 'r'
          AND pg_get_serial_sequence(quote_ident(n.nspname) || '.' || quote_ident(t.relname), a.attname) IS NOT NULL
    LOOP
        EXECUTE format(
            'SELECT setval(%L, COALESCE((SELECT MAX(%I) FROM %I), 1), (SELECT MAX(%I) IS NOT NULL FROM %I))',
            rec.seq_name, rec.column_name, rec.table_name, rec.column_name, rec.table_name);
    END LOOP;
END $$;

Verified on PostgreSQL 16: it picks up identity and SERIAL sequences alike and handles empty tables through the same three-argument setval. Adjust the nspname filter if your tables live outside public. For a broader look at resetting counters across different databases, see how to reset auto-increment and identity columns in SQL.

Why ON CONFLICT is not the answer

It is tempting to slap ON CONFLICT (id) DO NOTHING on the failing insert and move on. Don't. The IDs genuinely collide, so Postgres treats your new row as a duplicate of an old, unrelated row and silently discards it:

INSERT INTO customers (name) VALUES ('lost')
ON CONFLICT (id) DO NOTHING;
-- INSERT 0 0: the row is gone, no error, no data

Verified on PostgreSQL 16: the table still has three rows and 'lost' is nowhere. You have converted a loud, harmless error into silent data loss. ON CONFLICT is the right tool for real upserts against natural keys, which is a different problem covered in PostgreSQL upsert with INSERT ON CONFLICT.

Preventing the next occurrence

The cleanest prevention is to stop supplying explicit IDs during imports. Omit the ID column and let the sequence assign values; nothing ever drifts.

When you must preserve IDs (foreign keys reference them, or systems need to agree on identifiers), prefer GENERATED ALWAYS AS IDENTITY over SERIAL or GENERATED BY DEFAULT. It refuses accidental explicit IDs at insert time:

CREATE TABLE invoices (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric
);

INSERT INTO invoices (id, amount) VALUES (100, 9.99);

On PostgreSQL 16 this fails immediately with:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

An intentional import states its intent explicitly with INSERT ... OVERRIDING SYSTEM VALUE, and that explicitness is exactly the reminder you need to run the setval fix afterward. Identity columns are available from PostgreSQL 10 onward and are the recommended style in the identity columns documentation. Casual scripts that would have quietly desynchronized a SERIAL column fail loudly instead, and loud failures are the cheap kind.

Tags: PostgreSQL

SQL Window Functions: SUM, AVG, LAG, and LEAD

Posted by Kyle Hankinson January 25, 2026

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into groups like GROUP BY does. They are one of the most powerful features in modern SQL.

The OVER() Clause

Every window function uses OVER() to define which rows to include:

SELECT name, department, salary,
    SUM(salary) OVER () AS total_salary
FROM employees;

This adds a total_salary column with the sum of all salaries — without grouping. Every row still appears individually.

PARTITION BY

PARTITION BY divides rows into groups (like GROUP BY, but without collapsing):

SELECT name, department, salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Each row shows the department total and average alongside the individual salary.

ORDER BY in OVER()

Adding ORDER BY creates a running calculation:

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

LAG and LEAD

Compare the current row to previous or next rows:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

LAG(col, n) looks back n rows (default 1). LEAD(col, n) looks forward n rows.

Default Values

Avoid NULLs for the first/last row:

LAG(revenue, 1, 0) OVER (ORDER BY month)  -- returns 0 instead of NULL

FIRST_VALUE and LAST_VALUE

SELECT name, department, salary,
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid
FROM employees;

Percent of Total

SELECT name, department, salary,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

Moving Average

Use a frame specification to average over a sliding window:

SELECT order_date, amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_avg
FROM daily_sales;

Frame Types

Combining Multiple Window Functions

SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS pct_of_company
FROM employees
ORDER BY department, salary DESC;

WINDOW Clause (Reusable Definitions)

Avoid repeating the same OVER clause:

SELECT
    order_date, amount,
    SUM(amount) OVER w AS running_total,
    AVG(amount) OVER w AS running_avg,
    COUNT(*) OVER w AS running_count
FROM orders
WINDOW w AS (ORDER BY order_date);

Supported in PostgreSQL, MySQL 8.0+, and SQLite 3.28+. Not supported in SQL Server or Oracle.

Database Compatibility

Tags: MySQL PostgreSQL Microsoft SQL Server