DataBrighten

    Invoices sample database for PostgreSQL

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    pgAdmin or DBeaver

    Create a database, open the Query Tool, open the .sql file and run it.

    Tables and columns

    invoices 1,000 rows

    ColumnPostgreSQL type
    invoice_idVARCHAR(20)
    clientVARCHAR(30)
    issue_dateDATE
    due_dateDATE
    paid_dateDATE
    subtotalDECIMAL(9,2)
    tax_rateDECIMAL(8,4)
    taxDECIMAL(8,2)
    totalDECIMAL(10,2)
    statusVARCHAR(20)
    days_lateINTEGER

    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

    statusinvoicestotal
    Paid6392,429,763.23
    Paid late215799,346.67
    Overdue146506,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

    unpaidoutstanding
    146506,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_lateavg_days_late
    21522.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

    clientinvoicesinvoiced
    Granite Dental Group71289,367.35
    Maple Design Studio63286,534.26
    Cedar Design Studio66246,002.47
    Delta Logistics LLC59243,071.27
    Redwood Print Shop58237,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

    monthinvoiced
    2024-01297,105.81
    2024-02298,363.89
    2024-03292,532.10
    2024-04288,950.21
    2024-05309,437.91
    2024-06366,142.27
    2024-07313,354.37
    2024-08414,329.34
    2024-09304,277.59
    2024-10291,716.19
    2024-11309,157.08
    2024-12250,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.