skip to content

In SQL, what do the PREPARE and EXECUTE statements do, and how do their forms differ between PostgreSQL and MySQL?

level: middleimportance: should knowfreq 38%

answer

  1. a named statement on one session
  2. AS statement versus FROM string
  3. $1 versus ?
  4. EXECUTE q(42) versus USING @id
  5. DEALLOCATE, or the session ends

basics

~10 s

PREPARE stores a named statement with placeholders on the current session, and EXECUTE runs it with values. PostgreSQL writes PREPARE name AS statement with $1; MySQL writes PREPARE name FROM a string with ?.

solid answer

~50 s

`PREPARE` gives a statement a name on the current session, with placeholders where values will go; `EXECUTE` runs it by name and supplies the values. In PostgreSQL the form is `PREPARE q (int) AS SELECT ... WHERE id = $1` and then `EXECUTE q(42)`. The body is written as a statement, not a string, and is parsed and analysed at `PREPARE`, so a missing table fails there. In MySQL it is `PREPARE q FROM 'SELECT ... WHERE id = ?'` and then `EXECUTE q USING @id`, with the values in user variables. Because MySQL's `FROM` also accepts a user variable, a statement built with `CONCAT` can be prepared from inside SQL; PostgreSQL's `PREPARE` takes no computed string, so the client builds the text first. In both, the statement belongs to one session, so another connection cannot run it, and `DEALLOCATE` removes it early.

code

sql · 7 lines
sql
PREPARE report (int) AS
  SELECT id, total
  FROM orders
  WHERE tenant_id = $1 AND created_at >= $2;

EXECUTE report(42, current_date - 7);
DEALLOCATE report;

go deeper

for a junior

Recall the sequence: PREPARE a named statement with placeholders, EXECUTE it with values, DEALLOCATE when done, and know it disappears when the session does.

for a middle

Write both spellings from memory: AS with $1 and EXECUTE q(42) in PostgreSQL, FROM a string with ? and EXECUTE q USING @id in MySQL, and say what each one checks at PREPARE.

for a senior

Explain the consequences of session scope for anything run across connections, and how a statement reaches PREPARE when its text is assembled, which differs sharply between the two engines.

for a principal

Weigh how much SQL-level PREPARE belongs in a codebase at all, given that drivers already prepare over the wire and these statements do not move between engines.

## What the pair does A **prepared statement** is a statement the server holds under a name, with placeholders where values will go. `PREPARE` creates it. `EXECUTE` runs it, supplying the values for that run. `DEALLOCATE` removes it. The same name can be executed many times with different values. This matters here because it separates two things: the statement's shape, which is settled when it is prepared, and its values, which arrive with each execution. Everything else about bind parameters follows from that split. In PostgreSQL, `PREPARE` parses, analyses and rewrites the statement; `EXECUTE` plans and runs it. So a misspelled column fails at `PREPARE`, not at the first execution. How the plan is chosen across executions is a separate subject, covered with plan caching. ## The two forms side by side These statements are not portable, and the spelling differences are what interviews probe. | | PostgreSQL | MySQL | |---|---|---| | Prepare | `PREPARE q (int) AS SELECT ...` | `PREPARE q FROM 'SELECT ...'` | | The body is | a statement, written directly | a string literal or a user variable | | Placeholder | `$1`, `$2` by position | `?` | | Parameter types | optional list, otherwise inferred from context | not declared | | Execute | `EXECUTE q(42)` | `EXECUTE q USING @id` | | Values are | expressions | user variables | | Remove | `DEALLOCATE q` | `DEALLOCATE PREPARE q` | | Same name again | an error | replaces the old statement | ## The same query in both ```sql -- PostgreSQL PREPARE orders_for (int, text) AS SELECT id, total FROM orders WHERE tenant_id = $1 AND status = $2; EXECUTE orders_for(42, 'open'); DEALLOCATE orders_for; -- MySQL PREPARE orders_for FROM 'SELECT id, total FROM orders WHERE tenant_id = ? AND status = ?'; SET @tenant = 42, @status = 'open'; EXECUTE orders_for USING @tenant, @status; DEALLOCATE PREPARE orders_for; ``` ## The lifetime rules - **One session.** A prepared statement lasts as long as the database session that created it and is then forgotten. Another connection cannot run it; it has to prepare its own. - **Names are unique per session.** PostgreSQL does not overload a prepared statement by parameter type or count the way it overloads functions, so the name alone identifies it. - **Values must fit.** In PostgreSQL, passing the wrong number of values, or a value not compatible with the parameter's type, raises an error. - **Limited statement types.** PostgreSQL accepts `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE` and `VALUES`, so `PREPARE p AS CREATE TABLE ...` is a syntax error. MySQL's permitted list is wider and includes some DDL. - **The result looks like the statement.** In PostgreSQL the command tag returned by `EXECUTE` is that of the prepared statement, for example `INSERT`, not `EXECUTE`. ## Where dynamic SQL meets PREPARE `PREPARE` does not make a statement dynamic. It fixes the shape and leaves only values open. Dynamic SQL happens earlier, when the text passed to `PREPARE` gets built, and here the engines really differ: - **MySQL** can do it from inside SQL, because `FROM` accepts a user variable: `SET @sql = CONCAT('SELECT id FROM ', @table_name)`, then `PREPARE s FROM @sql`. The name in `@table_name` must already be checked, since it lands in the text. - **PostgreSQL's** `PREPARE` takes the statement as written and no expression that computes one, so `PREPARE q AS 'SELECT 1'` is a syntax error. The client builds the whole `PREPARE` text, or just sends the built statement. ## Portability and the other EXECUTE The SQL standard does have `PREPARE` and `EXECUTE`, but PostgreSQL's documentation notes they are only for embedded SQL there, and that its own syntax differs. Elsewhere, SQLite exposes preparing through its C API with no SQL statement, and SQL Server's usual route is `sp_executesql`, which is its own subject. PostgreSQL holds one more trap. PL/pgSQL has an `EXECUTE` statement that takes a string to run. It is not related to the SQL `EXECUTE` described here, and that SQL statement cannot be used directly inside a PL/pgSQL function. ## What applications actually do Most applications never type `PREPARE`. Their database driver asks for a prepared statement over the wire protocol, and the placeholder style is often the driver's. Knowing the SQL form still helps, because it is what you use in a console to reproduce what a driver is doing, and the rules about shape, values and session lifetime carry over.

  • What happens when a session runs PREPARE with a name it has already used?
    It depends on the engine. PostgreSQL raises an error saying the prepared statement already exists, so you have to `DEALLOCATE` first. MySQL deallocates the old statement implicitly and prepares the new one. A script that relies on one behaviour can fail or silently replace a statement on the other.
  • Can you prepare a CREATE TABLE statement in PostgreSQL?
    No. PostgreSQL's `PREPARE` accepts only `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `MERGE` or `VALUES`, so `PREPARE p AS CREATE TABLE ...` is a syntax error. MySQL's list of preparable statements is wider and includes some DDL, which is why a statement that works there may not move across.
  • How does PL/pgSQL's EXECUTE relate to the SQL EXECUTE statement in PostgreSQL?
    It does not relate at all, despite the shared name. The SQL statement runs a named prepared statement. The PL/pgSQL one runs a command string inside a function, and PostgreSQL's documentation states that the SQL form cannot be used directly in PL/pgSQL. Confusing them leads people to look for one statement's options in the other.

  • PREPARE and EXECUTE are spelled the same on every engine.
  • PostgreSQL's PREPARE takes the statement as a quoted string, as MySQL's does.
  • A prepared statement is stored in the catalog, so any connection can run it.
  • PREPARE makes a statement dynamic because its values arrive later.
  • PL/pgSQL's EXECUTE is just the SQL EXECUTE statement called from a function.
  • PostgreSQL's PREPARE only checks syntax, so a missing table first fails at EXECUTE.