An SQL command for creating a prepared statement

PREPARE is a utility command for creating a prepared statement.

PREPARE was added in PostgreSQL 7.3.


PREPARE is used to create a named prepared statement, optionally with a list of parameters specified by position as $1, $2 etc..

The prepared statement created by this command is valid for the current session, and cannot be accessed from other sessions. It is removed automatically when the session ends, or can be manually reoved with the DEALLOCATE command. Only one prepared statement with the same name can exist in the same session; it is not possible to "overload" prepared statements by creating variants with different parameters.

The prepared statement can be executed with EXECUTE.

Change history


Basic usage example for PREPARE:

postgres=# PREPARE foo_ins (INT, TEXT) AS
             INSERT INTO foo VALUES ($1, $2);

postgres=# SELECT * FROM pg_prepared_statements;
-[ RECORD 1 ]---+-----------------------------------
name            | foo_ins
statement       | PREPARE foo_ins (INT, TEXT) AS    +
                |   INSERT INTO foo VALUES ($1, $2);
prepare_time    | 2022-08-04 23:17:04.706349+09
parameter_types | {integer,text}
result_types    | 
from_sql        | t
generic_plans   | 0
custom_plans    | 0

The provided statement must be syntatically valid and reference visible objects:

postgres=# PREPARE bar_ins (INT, TEXT) AS
                        INSERT INTO bar VALUES ($1, $2);
ERROR:  relation "bar" does not exist
LINE 2:              INSERT INTO bar VALUES ($1, $2);

Attempting to create a prepared statement when one with the same name already exists:

postgres=# PREPARE foo_ins (INT) AS
             INSERT INTO foo VALUES ($1);
ERROR:  prepared statement "foo_ins" already exists
  • PostgreSQL documentation: PREPARE


SQL command, Utility command

See also

EXECUTE, DEALLOCATE, plan_cache_mode, pg_prepared_statements