Skip to content


Subversion checkout URL

You can clone with
Download ZIP
Erlang PostgreSQL client
Branch: master
Failed to load latest commit information.
ebin generate at compile time
include initial import of epgsql
test_data add support for multibyte character(N)
test_ebin initial import of epgsql
test_src fix encoding of array lower bound
.gitignore generate at compile time
LICENSE initial import of epgsql
Makefile generate app version from latest git tag
README add support for float4 and float8 arrays
rebar add rebar script and config
rebar.config add rebar script and config


Erlang PostgreSQL Database Client

* Connect

  {ok, C} = pgsql:connect(Host, [Username], [Password], Opts).

  Host      - host to connect to.
  Username  - username to connect as, defaults to $USER.
  Password  - optional password to authenticate with.
  Opts      - property list of extra options. Supported properties:

    + {database, String}
    + {port,     Integer}
    + {ssl,      Atom}       true | false | required
    + {ssl_opts, List}       see ssl application docs in OTP
    + {timeout,  Integer}    milliseconds, defaults to 5000
    + {async,    Pid}        see Asynchronous Messages section

  {ok, C} = pgsql:connect("localhost", "username", [{database, "test_db"}]).
  ok = pgsql:close(C).

  The timeout parameter will trigger an {error, timeout} result when the
  server fails to respond within Timeout milliseconds. This timeout applies
  to the initial connection attempt and any subsequent queries.

* Simple Query

  {ok, Columns, Rows}        = pgsql:squery(C, "select ...").
  {ok, Count}                = pgsql:squery(C, "update ...").
  {ok, Count, Columns, Rows} = pgsql:squery(C, "insert ... returning ...").

  {error, Error}             = pgsql:squery(C, "invalid SQL").

  Columns       - list of column records, see pgsql.hrl for definition.
  Rows          - list of tuples, one for each row.
  Count         - integer count of rows inserted/updated/etc

  The simple query protocol returns all columns as text (Erlang binaries)
  and does not support binding parameters.

* Extended Query

  {ok, Columns, Rows}        = pgsql:equery(C, "select ...", [Parameters]).
  {ok, Count}                = pgsql:equery(C, "update ...", [Parameters]).
  {ok, Count, Columns, Rows} = pgsql:equery(C, "insert ... returning ...", [Parameters]).

  {error, Error}             = pgsql:equery(C, "invalid SQL", [Parameters]).

  Parameters    - optional list of values to be bound to $1, $2, $3, etc.

  The extended query protocol combines parse, bind, and execute using
  the unnamed prepared statement and portal. A "select" statement returns
  {ok, Columns, Rows}, "insert/update/delete" returns {ok, Count} or
  {ok, Count, Columns, Rows} when a "returning" clause is present. When
  an error occurs, all statements result in {error, #error{}}.

  PostgreSQL's binary format is used to return integers as Erlang
  integers, floats as floats, bytea/text/varchar columns as binaries,
  bools as true/false, etc. For details see pgsql_binary.erl and the
  Data Representation section below.

* Parse/Bind/Execute

  {ok, Statement} = pgsql:parse(C, [StatementName], Sql, [ParameterTypes]).

  StatementName   - optional, reusable, name for the prepared statement.
  ParameterTypes  - optional list of PostgreSQL types for each parameter.

  For valid type names see pgsql_types.erl.

  ok = pgsql:bind(C, Statement, [PortalName], ParameterValues).

  PortalName      - optional name for the result portal.

  {ok | partial, Rows} = pgsql:execute(C, Statement, [PortalName], [MaxRows]).
  {ok, Count}          = pgsql:execute(C, Statement, [PortalName]).
  {ok, Count, Rows}    = pgsql:execute(C, Statement, [PortalName]).

  PortalName      - optional portal name used in bind/4.
  MaxRows         - maximum number of rows to return (0 for all rows).

  execute returns {partial, Rows} when more rows are available.

  ok = pgsql:close(C, Statement).
  ok = pgsql:close(C, statement | portal, Name).
  ok = pgsql:sync(C).

  All functions return {error, Error} when an error occurs.

* Data Representation

  null        = null
  bool        = true | false
  char        = $A | binary
  intX        = 1
  floatX      = 1.0
  date        = {Year, Month, Day}
  time        = {Hour, Minute, Second.Microsecond}
  timetz      = {time, Timezone}
  timestamp   = {date, time}
  timestamptz = {date, time}
  interval    = {time, Days, Months}
  text        = <<"a">>
  varchar     = <<"a">>
  bytea       = <<1, 2>>
  array       = [1, 2, 3]

  record      = {int2, time, text, ...} (decode only)

* Errors

  Errors originating from the PostgreSQL backend are returned as {error, #error{}},
  see pgsql.hrl for the record definition. epgsql functions may also return
  {error, What} where What is one of the following:

  {unsupported_auth_method, Method}     - required auth method is unsupported
  timeout                               - request timed out
  closed                                - connection was closed
  sync_required                         - error occured and pgsql:sync must be called

* Asynchronous Messages

  PostgreSQL may deliver two types of asynchronous message: "notices" in response
  to notice and warning messages generated by the server, and "notifications" which
  are generated by the LISTEN/NOTIFY mechanism.

  Passing the {async, Pid} option to pgsql:connect will result in these async
  messages being sent to the specified process, otherwise they will be dropped.

  Message formats:

    {pgsql, Connection, {notification, Channel, Pid, Payload}}

      Connection  - connection the notification occured on

      Channel     - channel the notification occured on
      Pid         - database session pid that sent notification
      Payload     - optional payload, only available from PostgreSQL >= 9.0

    {pgsql, Connection, {notice, Error}}

      Connection  - connection the notice occured on
      Error       - an #error{} record, see pgsql.hrl

Something went wrong with that request. Please try again.