Postgres Playground

How to use the playground

1. Press Run (or Ctrl+Enter). The first time, the engine downloads once and a progress bar shows while it loads.
2. Edit the SQL in the open tab, or press + to add another script file. Run all runs every file in the order of the tabs.
3. Each statement gets its own result table with the row count and time. Select text before pressing Run to run only that part.
4. Click a table in Schema to add a quick SELECT for it. Explain shows the plan of the last statement with real timings.
5. In the ⋯ menu, download a .sql file of what you changed, reset the database, or open a .sql script from your computer.

Good to know

The database lives in memory and starts fresh each visit. Table and column names are lower case unless you wrap them in double quotes. To tidy a long query, use the SQL Formatter; to try the same ideas on a single file database, open the SQLite Playground.

This query adds up the paid orders for each country:

SELECT c.country, count(*) AS orders, sum(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC;

The result has six rows. The first three are:

country  | orders | revenue
---------+--------+---------
UK       | 15     | 4198.60
Spain    | 16     | 3928.00
Portugal | 11     | 3025.70

• Typing \d or \dt. Those are psql commands; use the Schema list or information_schema.
• Using a capital letter in a table name without quotes. CREATE TABLE Users makes a table called users.
• Comparing text and numbers without a cast. Write '5'::int when a function complains about its inputs.
• Hoping the tables are still there next visit. The database is only in memory; download the session SQL to keep it.