...

PostgreSQL Protocol: Server-Side Prepared Statements

Learn to write the server-side prepared statements using PostgreSQL and the application code.

We'll cover the following...

Extended query
Example
Try it yourself

It’s possible to send the query string and its arguments separately on the wire by using server-side prepared statements. This is a pretty common way to do it, mostly because PQexecParams isn’t well-known, though it made its debut in PostgreSQL 7.4 in 2003. To this day, a lot of PostgreSQL drivers still don’t expose the PQexecParams facility, which is unfortunate.

Server-side prepared statements can be used in SQL thanks to the prepare and execute commands syntax, as in the following example:

Press + to interact

factbook.sql

factbook.csv

begin;
create table factbook
 (
   year    int,
   date    date,
   shares  text,
   trades  text,
   dollars text
 );
-- datestyle of the database to ISO, MDY
\copy factbook from 'factbook.csv' with delimiter E'\t' null ''
alter table factbook
   alter shares
    type bigint
   using replace(shares, ',', '')::bigint,
   alter trades
    type bigint
   using replace(trades, ',', '')::bigint,
   
   alter dollars
    type bigint
   using substring(replace(dollars, ',', '') from 2)::numeric;
commit;
prepare foo as
 select date, shares, trades, dollars
   from factbook
  where date >= $1::date
    and date  < $1::date + interval '1 month'
  order by date;
execute foo('2010-02-01');