How PostgreSQL processes a query (parser, planner, executor) - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to parse a SQL query string in PostgreSQL.

PostgreSQL

SELECT * FROM pg_parse_query('[1]');

Drag options to blanks, or click blank then click option'

ASELECT * FROM users

BSELECT 1

CINSERT INTO table VALUES (1)

DUPDATE table SET col=2

Attempts:

3 left

💡 Hint

Common Mistakes

Using incomplete or invalid SQL statements.

Confusing parser input with planner or executor commands.

The parser takes the SQL query string and converts it into a parse tree. Here, 'SELECT * FROM users' is a valid query string to parse.

2fill in blank

medium

Complete the code to plan a parsed query in PostgreSQL.

PostgreSQL

EXPLAIN [1] * FROM users WHERE id = 1;

Drag options to blanks, or click blank then click option'

ASELECT

BINSERT

CUPDATE

DDELETE

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXPLAIN with non-SELECT commands incorrectly.

Confusing query types.

The planner creates an execution plan for a SELECT query. EXPLAIN shows the plan.

3fill in blank

hard

Fix the error in the executor command to run a query in PostgreSQL.

PostgreSQL

EXECUTE [1];

Drag options to blanks, or click blank then click option'

AEXECUTE 'my_query'

B'my_query'

CEXECUTE my_query

Dmy_query

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the prepared statement name in quotes.

Repeating the EXECUTE keyword inside the command.

The EXECUTE command runs a prepared statement by its name without quotes.

4fill in blank

hard

Fill both blanks to create a prepared statement and execute it in PostgreSQL.

PostgreSQL

PREPARE [1] AS SELECT * FROM users WHERE id = [2]; EXECUTE [1];

Drag options to blanks, or click blank then click option'

Amy_query

B1

Cuser_query

D42

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names for PREPARE and EXECUTE.

Using invalid values for the condition.

We prepare a statement named 'my_query' and then execute it. Here, '1' is used in the WHERE clause.

5fill in blank

hard

Fill the blanks to parse, plan, and execute a simple SELECT query in PostgreSQL.

PostgreSQL

WITH parsed AS (SELECT pg_parse_query('[1]') AS tree), planned AS (SELECT pg_plan_query(tree) AS plan FROM parsed) SELECT pg_execute_plan(plan, [2]) AS result FROM planned;

Drag options to blanks, or click blank then click option'

ASELECT * FROM users

BARRAY[]::text[]

CARRAY['1']::text[]

DSELECT id FROM users

Attempts:

3 left

💡 Hint

Common Mistakes

Passing parameters in wrong format.

Using invalid SQL strings.

The query string is 'SELECT * FROM users'. Since there are no parameters, pass an empty text array 'ARRAY[]::text[]' to pg_execute_plan.