pgdebug
is a tool which helps you to debug complicated queries. It takes a select
statement as an input and outputs a series of partial queries corresponding to each CTE and each subquery in the from
clause.
Supported commands: select
.
Unsupported commands: insert
, update
, delete
.
Supported subqueries:
with
from
clausevalues
pgdebug
is a Node.js-based tool, so you can install it from npm
:
npm install --save pgdebug
--ast Output source query AST in JSON format
instead of SQL statements
-c, --command string SQL command to process
-f, --file string File name to read SQL command from
-i, --interactive Read SQL from stdin
Here is an example of applying pgdebug
to a query with CTEs (the original query is included in the output):
~/t/pgdebug ❯❯❯ npx pgdebug -c "with a as ( ⏎
select * from customers
)
, b as (
select * from projects
)
select *
from b
join a using (customer_id)"
WITH a AS (SELECT * FROM "customers") SELECT * FROM "a";
WITH a AS (SELECT * FROM "customers"), b AS (SELECT * FROM "projects") SELECT * FROM "b";
with a as (
select * from customers
)
, b as (
select * from projects
)
select *
from b
join a using (customer_id)
Here is an example of a recursive CTE (note that the recursive
qualifier is preserved in the output):
~/t/pgdebug ❯❯❯ npx pgdebug -c "with recursive t(n) as (
values (1)
union all
select n+1 from t where n < 100
)
select sum(n) from t"
WITH RECURSIVE t ("n") AS ((VALUES (1)) UNION ALL (SELECT (("n") + (1)) FROM "t" WHERE
(("n") < (100)))) SELECT * FROM "t";
with recursive t(n) as (
values (1)
union all
select n+1 from t where n < 100
)
select sum(n) from t
And here is an example with subqueries in the from
clause:
~/t/pgdebug ❯❯❯ npx pgdebug -c "select * from (select * from customers) c
join (select * from projects) p using (customer_id)"
SELECT * FROM "customers";
SELECT * FROM "projects";
select * from (select * from customers) c join (select * from projects) p
using (customer_id)
Producing these queries isn’t all that useful in itself as you still have to copy-paste and execute them, so I use pgdebug
in combination with psql
, first piping the queries into psql
and then piping the output into less
so I can scroll through it:
echo "select * from (select zone_id, project_id, name from zones) z
join (select project_id, name from projects) p using (project_id)"
| npx pgdebug -i
| psql -q -e -d pgtime
| less
-q
supresses psql
messages, and -e
makes it output the query SQL, which is handy to understand which result set I’m looking at. The result looks like this:
SELECT "zone_id",
"project_id",
"name" FROM "zones";
zone_id | project_id | name
---------+------------+--------
14 | 1 |
1 | 1 | Zone 1
2 | 1 | Zone 2
2 | 1 |
(4 rows)
Time: 1.648 ms
SELECT "project_id",
"name" FROM "projects";
project_id | name
------------+-----------
1 | Highway 1
2 | Highway 2
2 | Highway 2
1 | Highway 1
(4 rows)
Time: 0.290 ms
select * from (select zone_id, project_id, name from zones) z join
(select project_id, name from projects) p using (project_id)
project_id | zone_id | name | name
------------+---------+--------+-----------
1 | 14 | | Highway 1
1 | 14 | | Highway 1
1 | 1 | Zone 1 | Highway 1
1 | 1 | Zone 1 | Highway 1
1 | 2 | Zone 2 | Highway 1
1 | 2 | Zone 2 | Highway 1
1 | 2 | | Highway 1
1 | 2 | | Highway 1
(8 rows)
Time: 0.732 ms
“PostgreSQL: Time and Temporal Data” is a short book that will help you get it under control. Master Postgres time tools, get a handle on time zones, learn how to represent and query complex temporal data.
Pop in your email to receive a sample chapter.
You will also get notifications of new posts along with other mailing list only freebies.