Invoices sample database for PostgreSQL
A ready-to-run PostgreSQL script with invoices with due dates, payment dates, tax, status and days late. Tested on PostgreSQL 15.18. Updated 19 November 2026.
Invoices database for PostgreSQL
Each row is one invoice issued in 2024 to a fictional client: issue and due dates, paid date, subtotal, tax rate and amount, total, status as of 31 January 2025 and days late.
This page has it as a PostgreSQL script: one table and 1,000 rows, with column types chosen for PostgreSQL, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the invoices dataset page, where it is also available as CSV and Excel.
We loaded this exact file into PostgreSQL 15.18 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
psql
createdb practice
psql -d practice -f invoices-postgresql.sqlpgAdmin or DBeaver
Create a database, open the Query Tool, open the .sql file and run it.
Tables and columns
invoices 1,000 rows
| Column | PostgreSQL type |
|---|---|
| invoice_id | VARCHAR(20) |
| client | VARCHAR(30) |
| issue_date | DATE |
| due_date | DATE |
| paid_date | DATE |
| subtotal | DECIMAL(9,2) |
| tax_rate | DECIMAL(8,4) |
| tax | DECIMAL(8,2) |
| total | DECIMAL(10,2) |
| status | VARCHAR(20) |
| days_late | INTEGER |
Practice questions
answers from PostgreSQL 15.18
How many invoices are in each status, and what is their total value?
SQL
SELECT status, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS total
FROM invoices
GROUP BY status
ORDER BY invoices DESC, status;Result
| status | invoices | total |
|---|---|---|
| Paid | 639 | 2,429,763.23 |
| Paid late | 215 | 799,346.67 |
| Overdue | 146 | 506,974.73 |
How many invoices have no paid date yet, and how much is outstanding?
SQL
SELECT COUNT(*) AS unpaid, ROUND(SUM(total), 2) AS outstanding
FROM invoices
WHERE paid_date IS NULL;Result
| unpaid | outstanding |
|---|---|
| 146 | 506,974.73 |
On average, how many days late are invoices that were paid after the due date? (computed from the dates)
SQL
SELECT COUNT(*) AS paid_late, ROUND(AVG((paid_date - due_date)), 2) AS avg_days_late
FROM invoices
WHERE paid_date > due_date;Subtracting one DATE from another returns the number of days as an integer.
Result
| paid_late | avg_days_late |
|---|---|
| 215 | 22.50 |
Which 5 clients were invoiced the most?
SQL
SELECT client, COUNT(*) AS invoices, ROUND(SUM(total), 2) AS invoiced
FROM invoices
GROUP BY client
ORDER BY invoiced DESC, client
LIMIT 5;LIMIT n at the end returns the first n rows.
Result
| client | invoices | invoiced |
|---|---|---|
| Granite Dental Group | 71 | 289,367.35 |
| Maple Design Studio | 63 | 286,534.26 |
| Cedar Design Studio | 66 | 246,002.47 |
| Delta Logistics LLC | 59 | 243,071.27 |
| Redwood Print Shop | 58 | 237,442.06 |
What was the total invoiced per month of issue?
SQL
SELECT to_char(issue_date, 'YYYY-MM') AS month, ROUND(SUM(total), 2) AS invoiced
FROM invoices
GROUP BY to_char(issue_date, 'YYYY-MM')
ORDER BY month;to_char(date, 'YYYY-MM') formats a date as year-month text.
Result
| month | invoiced |
|---|---|
| 2024-01 | 297,105.81 |
| 2024-02 | 298,363.89 |
| 2024-03 | 292,532.10 |
| 2024-04 | 288,950.21 |
| 2024-05 | 309,437.91 |
| 2024-06 | 366,142.27 |
| 2024-07 | 313,354.37 |
| 2024-08 | 414,329.34 |
| 2024-09 | 304,277.59 |
| 2024-10 | 291,716.19 |
| 2024-11 | 309,157.08 |
| 2024-12 | 250,717.87 |
Check the data: how many rows have a total that differs from subtotal + tax by more than one cent?
SQL
SELECT COUNT(*) AS mismatches
FROM invoices
WHERE ABS(total - (subtotal + tax)) > 0.01;Result
| mismatches |
|---|
| 0 |
PostgreSQL differences used here
- Subtracting one DATE from another returns the number of days as an integer.
- LIMIT n at the end returns the first n rows.
- to_char(date, 'YYYY-MM') formats a date as year-month text.