LibPQ API

mutable struct Connection

A connection to a PostgreSQL database.

Fields:

• conn::Ptr{Nothing}

  A pointer to a libpq PGconn object (C_NULL if closed)

• encoding::String

  libpq client encoding (string encoding of returned data)

• uid_counter::UInt64

  Integer counter for generating connection-level unique identifiers

• type_map::LibPQ.PQTypeMap

  Connection-level type correspondence map

• func_map::LibPQ.PQConversions

  Connection-level conversion functions

• closed::Base.Threads.Atomic{Bool}

  True if the connection is closed and the PGconn object has been cleaned up

• semaphore::Base.Semaphore

  Semaphore for thread-safety (not thread-safe until Julia 1.2)

• async_result::Any

  Current AsyncResult, if active

execute(
    {jl_conn::Connection, query::AbstractString | stmt::Statement},
    [parameters::Union{AbstractVector, Tuple},]
    throw_error::Bool=true,
    binary_format::Bool=false,
    column_types::AbstractDict=ColumnTypeMap(),
    type_map::AbstractDict=LibPQ.PQTypeMap(),
    conversions::AbstractDict=LibPQ.PQConversions(),
    not_null::Union{Bool, AbstractVector}=false,
) -> Result

Run a query on the PostgreSQL database and return a Result. If throw_error is true, throw an error and clear the result if the query results in a fatal error or unreadable response.

The query may be passed as Connection and AbstractString (SQL) arguments, or as a Statement.

execute optionally takes a parameters vector which passes query parameters as strings to PostgreSQL.

column_types accepts type overrides for columns in the result which take priority over those in type_map.

not_null indicates whether parsed columns should be able to contain null values, parsed as missing. The argument can be a single Bool for all columns, a list of Bool, or a list of column names.

For information on the column_types, type_map, and conversions arguments, see Type Conversions.

binary_format, when set to true, will transfer the data in binary format. Support for binary transfer is currently limited to a subset of basic data types.

prepare(jl_conn::Connection, query::AbstractString) -> Statement

Create a prepared statement on the PostgreSQL server using libpq. The statement is given an generated unique name using unique_id.

status(jl_conn::Connection) -> libpq_c.ConnStatusType

Return the status of the PostgreSQL database connection according to libpq. Only CONNECTION_OK and CONNECTION_BAD are valid for blocking connections, and only blocking connections are supported right now.

See also: error_message

close(jl_conn::Connection)

Close the PostgreSQL database connection and free the memory used by the PGconn object. This function calls PQfinish, but only if jl_conn.closed is false, to avoid a double-free.

isopen(jl_conn::Connection) -> Bool

Check whether a connection is open.

reset!(jl_conn::Connection; throw_error=true)

Reset the communication to the PostgreSQL server. The PGconn object will be recreated using identical connection parameters.

See handle_new_connection for information on the throw_error argument.

show(io::IO, jl_conn::Connection)

Display a Connection by showing the connection status and each connection option.

A result from a PostgreSQL database query

status(jl_result::Result) -> libpq_c.ExecStatusType

Return the status of a result's corresponding database query according to libpq.

See also: error_message

close(jl_result::Result)

Clean up the memory used by the PGresult object. The Result will no longer be usable.

isopen(jl_result::Result)

Determine whether the given Result has been closed, i.e. whether the memory associated with the underlying PGresult object has been cleared.

num_rows(jl_result::Result) -> Int

Return the number of rows in the query result. This will be 0 if the query would never return data.

num_columns(jl_result::Result) -> Int

Return the number of columns in the query result. This will be 0 if the query would never return data.

num_affected_rows(jl_result::Result) -> Int

Return the number of rows affected by the command returning the result. This is useful for counting the rows affected by operations such as INSERT, UPDATE and DELETE that do not return rows but affect them. This will be 0 if the query does not affect any row.

show(io::IO, jl_result::Result)

Show a PostgreSQL result and whether it has been cleared.

struct Statement

A PostgreSQL prepared statement

Fields:

• jl_conn::LibPQ.Connection

  A Connection for which this statement is valid. It may become invalid if the connection is reset.

• name::String

  An autogenerated name for the prepared statement (using unique_id

• query::String

  The query string of the prepared statement

• description::LibPQ.Result

  A Result containing a description of the prepared statement

• num_params::Int64

  The number of parameters accepted by this statement according to description

num_columns(stmt::Statement) -> Int

Return the number of columns that would be returned by executing the prepared statement.

num_params(stmt::Statement) -> Int

Return the number of parameters in the prepared statement.

show(io::IO, jl_result::Statement)

Show a PostgreSQL prepared statement and its query.

LibPQ.load!(table, connection::LibPQ.Connection, query) -> LibPQ.Statement

Insert the data from table using query. query will be prepared as a LibPQ.Statement and then execute is run on every row of table.

For best performance, wrap the call to this function in a PostgreSQL transaction:

julia> execute(conn, "BEGIN;");

julia> LibPQ.load!(
           (no_nulls = ["foo", "baz"], yes_nulls = ["bar", missing]),
           conn,
           "INSERT INTO libpqjl_test (no_nulls, yes_nulls) VALUES (\$1, \$2);",
       );

julia> execute(conn, "COMMIT;");

struct CopyIn

CopyIn(query, data_itr) -> CopyIn

Create a CopyIn query instance which can be executed to send data to PostgreSQL via a COPY <table_name> FROM STDIN query.

query must be a COPY FROM STDIN query as described in the PostgreSQL documentation. COPY FROM queries which use a file or PROGRAM source can instead use the standard execute query interface.

data_itr is an iterable containing chunks of data to send to PostgreSQL. The data can be divided up into arbitrary buffers as it will be reconstituted on the server. The iterated items must be AbstractStrings or Array{UInt8}s.

Fields:

• query::String

• data_itr::Any

execute(jl_conn::Connection, copyin::CopyIn, args...;
    throw_error::Bool=true, kwargs...
) -> Result

Runs execute on copyin's query, then sends copyin's data to the server.

All other arguments are passed through to the execute call for the initial query.

async_execute(
    jl_conn::Connection,
    query::AbstractString,
    [parameters::Union{AbstractVector, Tuple},]
    binary_format::Bool=false,
    kwargs...
) -> AsyncResult

Run a query on the PostgreSQL database and return an AsyncResult.

The AsyncResult contains a Task which processes a query asynchronously. Calling fetch on the AsyncResult will return a Result.

All keyword arguments are the same as execute and are passed to the created Result.

Only one AsyncResult can be active on a Connection at once. If multiple AsyncResults use the same Connection, they will execute serially.

async_execute does not yet support Statements.

async_execute optionally takes a parameters vector which passes query parameters as strings to PostgreSQL.

binary_format, when set to true, will transfer the data in binary format. Support for binary transfer is currently limited to a subset of basic data types.

Queries without parameters can contain multiple SQL statements, and the result of the final statement is returned. Any errors which occur during executed statements will be bundled together in a CompositeException and thrown.

As is normal for Tasks, any exceptions will be thrown when calling wait or fetch.

An asynchronous PostgreSQL query

cancel(async_result::AsyncResult)

If this AsyncResult represents a currently-executing query, attempt to cancel it.

handle_new_connection(jl_conn::Connection; throw_error=true) -> Connection

Check status and handle errors for newly-created connections. Also set the client encoding (23.3. Character Set Support) to jl_conn.encoding.

If throw_error is true, an error will be thrown if the connection's status is CONNECTION_BAD and the PGconn object will be cleaned up. Otherwise, a warning will be shown and the user should call close or reset! on the returned Connection.

server_version(jl_conn::Connection) -> VersionNumber

Get the PostgreSQL version of the server.

See 33.2. Connection Status Functions for information on the integer returned by PQserverVersion that is parsed by this function.

See @pqv_str for information on how this packages represents PostgreSQL version numbers.

encoding(jl_conn::Connection) -> String

Return the client encoding name for the current connection (see Table 23.1. PostgreSQL Character Sets for possible values).

Currently all Julia connections are set to use UTF8 as this makes conversion to and from String straightforward.

See also: set_encoding!, reset_encoding!

set_encoding!(jl_conn::Connection, encoding::String)

Set the client encoding for the current connection (see Table 23.1. PostgreSQL Character Sets for possible values).

Currently all Julia connections are set to use UTF8 as this makes conversion to and from String straighforward. Other encodings are not explicitly handled by this package and will probably be very buggy.

See also: encoding, reset_encoding!

reset_encoding!(jl_conn::Connection, encoding::String)

Reset the client encoding for the current connection to jl_conn.encoding.

See also: encoding, set_encoding!

transaction_status(jl_conn::Connection) -> libpq_c.PGTransactionStatusType

Return the PostgreSQL database server's current in-transaction status for the connection. See for information on the meaning of the possible return values.

unique_id(jl_conn::Connection, prefix::AbstractString="") -> String

Return a valid PostgreSQL identifier that is unique for the current connection. This is mostly used to create names for prepared statements.

error_message(jl_conn::Connection) -> String

Return the error message most recently generated by an operation on the connection. Includes a trailing newline.

struct ConnectionOption

A Julia representation of a PostgreSQL connection option (PQconninfoOption).

Fields:

• keyword::String

  The name of the option

• envvar::Union{Missing, String}

  The name of the fallback environment variable for this option

• compiled::Union{Missing, String}

  The PostgreSQL compiled-in default for this option

• val::Union{Missing, String}

  The value of the option if set

• label::String

  The label of the option for display

• disptype::LibPQ.ConninfoDisplay

  Indicator for how to display the option (see ConninfoDisplay)

• dispsize::Int64

  The size of field to provide for entry of the option value (not used here)

conninfo(jl_conn::Connection) -> Vector{ConnectionOption}

Get all connection options for a connection.

conninfo(str::AbstractString) -> Vector{ConnectionOption}

Parse connection options from a connection string (either a URI or key-value pairs).

primitive type ConninfoDisplay <: Enum{Int32} 32

Indicator for how to display a PostgreSQL connection option (PQconninfoOption).

Possible values are:

• Normal (libpq: ""): display as is
• Password (libpq: "*"): hide the value of this field
• Debug (libpq: "D"): don't show by default

Fields:

parse(::Type{ConninfoDisplay}, str::AbstractString) -> ConninfoDisplay

Parse a ConninfoDisplay from a string. See ConninfoDisplay.

handle_result(jl_result::Result; throw_error::Bool=true) -> Result

Check status and handle errors for newly-created result objects.

If throw_error is true, throw an error and clear the result if the query results in a fatal error or unreadable response. Otherwise a warning is shown.

Also print an info message about the result.

handle_result(async_result::AsyncResult; throw_error=true) -> Result

Executes the query in async_result and waits for results.

This implements the loop described in the PostgreSQL documentation for Asynchronous Command Processing.

The throw_error option only determines whether to throw errors when handling the new Results; the Task may error for other reasons related to processing the asynchronous loop.

The result returned will be the Result of the last query run (the only query if using parameters). Any errors produced by the queries will be thrown together in a CompositeException.

column_name(jl_result::Result, column_number::Integer) -> String

Return the name of the column at index column_number (1-based).

column_name(stmt::Statement, column_number::Integer) -> String

Return the name of the column at index column_number (1-based) that would be returned by executing the prepared statement.

column_names(jl_result::Result) -> Vector{String}

Return the names of all the columns in the query result.

column_names(stmt::Statement) -> Vector{String}

Return the names of all the columns in the query result that would be returned by executing the prepared statement.

column_number(jl_result::Result, column_name::Union{AbstractString, Symbol}) -> Int

Return the index (1-based) of the column named column_name.

column_number(jl_result::Result, column_idx::Integer) -> Int

Return the index of the column if it is valid, or error.

column_number(stmt::Statement, column_name::AbstractString) -> Int

Return the index (1-based) of the column named column_name that would be returned by executing the prepared statement.

column_oids(jl_result::Result) -> Vector{LibPQ.Oid}

Return the PostgreSQL oids for each column in the result.

column_types(jl_result::Result) -> Vector{Type}

Return the corresponding Julia types for each column in the result.

num_params(jl_result::Result) -> Int

Return the number of parameters in a prepared statement. If this result did not come from the description of a prepared statement, return 0.

error_message(jl_result::Result; verbose=false) -> String

Return the error message associated with the result, or an empty string if there was no error. If verbose, have libpq generate a more verbose version of the error message if possible. Includes a trailing newline.

Base abstract type for all custom exceptions thrown by LibPQ.jl

An exception generated by LibPQ.jl

An exception with an error message generated by PostgreSQL

An error regarding a connection generated by LibPQ.jl

An error regarding a query result generated by LibPQ.jl

An error from parsing connection parameter strings reported by PostgreSQL

An error regarding a connection reported by PostgreSQL

An error regarding a query result generated by PostgreSQL

The Code parameter represents the PostgreSQL error code as defined in Appendix A. PostgreSQL Error Codes. The Class parameter is the first two characters of that code, also listed on that page.

For a list of all error aliases, see src/error_codes.jl, which was generated using the PostgreSQL documentation linked above.

julia> try execute(conn, "SELORCT NUUL;") catch err println(err) end
LibPQ.Errors.SyntaxError("ERROR:  syntax error at or near \"SELORCT\"\nLINE 1: SELORCT NUUL;\n        ^\n")

julia> LibPQ.Errors.SyntaxError
LibPQ.Errors.PQResultError{LibPQ.Errors.C42, LibPQ.Errors.E42601}

oid(typ::Union{Symbol, String, Integer}) -> LibPQ.Oid

Convert a PostgreSQL type from an AbstractString or Symbol representation to its oid representation. Integers are converted directly to LibPQ.Oids.

primitive type PQChar 8

A one-byte character type for correspondence with PostgreSQL's one-byte "char" type.

Fields:

const PQ_SYSTEM_TYPES::Dict{Symbol, Oid}

Internal mapping of PostgreSQL's default types from PostgreSQL internal name to Oid. The names may not correspond well to the common names, e.g., "char(n)" is :bpchar. This dictionary is generated with the deps/system_type_map.jl script and contains only PostgreSQL's system-defined types. It is expected (but might not be guaranteed) that these are the same across versions and installations.

struct PQTypeMap <: AbstractDict{UInt32, Type}

A mapping from PostgreSQL Oid to Julia type.

Fields:

• type_map::Dict{UInt32, Type}

Base.getindex(tmap::PQTypeMap, typ) -> Type

Get the Julia type corresponding to the given PostgreSQL type (any type accepted by oid) according to tmap.

Base.setindex!(tmap::PQTypeMap, val::Type, typ)

Set the Julia type corresponding to the given PostgreSQL type (any type accepted by oid) in tmap.

const _DEFAULT_TYPE_MAP::PQTypeMap

The PQTypeMap containing the default type mappings for LibPQ.jl. This should not be mutated; LibPQ-level type mappings can be added to LIBPQ_TYPE_MAP.

const LIBPQ_TYPE_MAP::PQTypeMap

The PQTypeMap containing LibPQ-level type mappings for LibPQ.jl. Adding type mappings to this constant will override the default type mappings for all code using LibPQ.jl.

struct PQConversions <: AbstractDict{Tuple{UInt32, Type}, Union{Function, Type}}

A mapping from Oid and Julia type pairs to the function for converting a PostgreSQL value with said Oid to said Julia type.

Fields:

• func_map::Dict{Tuple{UInt32, Type}, Union{Function, Type}}

Base.getindex(cmap::PQConversions, oid_typ::Tuple{Any, Type}) -> Base.Callable

Get the function according to cmap for converting a PostgreSQL value of some PostgreSQL type (any type accepted by oid) to some Julia type.

Base.setindex!(cmap::PQConversions, val::Base.Callable, oid_typ::Tuple{Any, Type})

Set the function in cmap for converting a PostgreSQL value of some PostgreSQL type (any type accepted by oid) to some Julia type.

const _DEFAULT_CONVERSIONS::PQConversions

The PQConversions containing the default conversion functions for LibPQ.jl. This should not be mutated; LibPQ-level conversion functions can be added to LIBPQ_CONVERSIONS.

const LIBPQ_CONVERSIONS::PQConversions

The PQConversions containing LibPQ-level conversion functions for LibPQ.jl. Adding conversions to this constant will override the default conversions for all code using LibPQ.jl.

A fallback conversion mapping (like PQConversions which holds a single function for converting PostgreSQL data of a given Oid to a given Julia type, using the parse function.

A wrapper for one value in a PostgreSQL result.

data_pointer(pqv::PQValue) -> Ptr{UInt8}

Get a raw pointer to the data for one value in a PostgreSQL result. This data will be freed by libpq when the result is cleared, and should only be used temporarily.

num_bytes(pqv::PQValue) -> Cint

The length in bytes of the PQValue's corresponding data. When a query uses LibPQ.TEXT format, this is equivalent to C's strlen.

See also: data_pointer

unsafe_string(pqv::PQValue) -> String

Construct a String from a PQValue by copying the data.

string_view(pqv::PQValue) -> String

Wrap a PQValue's underlying data in a String. This function uses data_pointer and num_bytes and does not copy.

See also: bytes_view

bytes_view(pqv::PQValue) -> Vector{UInt8}

Wrap a PQValue's underlying data in a vector of bytes. This function uses data_pointer and num_bytes and does not copy.

This function differs from string_view as it keeps the byte at the end. PQValue parsing functions should use bytes_view when the data returned by PostgreSQL is not in UTF-8.

parse(::Type{T}, pqv::PQValue) -> T

Parse a value of type T from a PQValue. By default, this uses any existing parse method for parsing a value of type T from a String.

You can implement default PostgreSQL-specific parsing for a given type by overriding pqparse.

@pqv_str -> VersionNumber

Parse a PostgreSQL version.

julia> using LibPQ: @pqv_str

julia> pqv"10.1" == v"10.0.1"
true

julia> pqv"9.2.5" == v"9.2.5"
true

string_parameters(parameters::AbstractVector) -> Vector{Union{String, Missing}}

Convert parameters to strings which can be passed to libpq, propagating missing.

parameter_pointers(parameters::AbstractVector{<:Parameter}) -> Vector{Ptr{UInt8}}

Given a vector of parameters, returns a vector of pointers to either the string bytes in the original or C_NULL if the element is missing.

unsafe_string_or_null(ptr::Cstring) -> Union{String, Missing}

Convert a Cstring to a Union{String, Missing}, returning missing if the pointer is C_NULL.