API

LibPQ API

Public

Connections

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

source
LibPQ.executeFunction.
execute(
    {jl_conn::Connection, query::AbstractString | stmt::Statement},
    [parameters::Union{AbstractVector, Tuple},]
    throw_error::Bool=true,
    column_types::AbstractDict=ColumnTypeMap(),
    type_map::AbstractDict=LibPQ.PQTypeMap(),
    conversions::AbstractDict=LibPQ.PQConversions(),
) -> 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. For information on the column_types, type_map, and conversions arguments, see Type Conversions.

source
LibPQ.prepareFunction.
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.

Note

Currently the statement is not explicitly deallocated, but it is deallocated at the end of session per the PostgreSQL documentation on DEALLOCATE.

source
LibPQ.statusMethod.
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

source
Base.closeMethod.
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.

source
Base.isopenMethod.
isopen(jl_conn::Connection) -> Bool

Check whether a connection is open.

source
LibPQ.reset!Method.
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.

Note

This function can be called on a connection with status CONNECTION_BAD, for example, but cannot be called on a connection that has been closed.

source
Base.showMethod.
show(io::IO, jl_conn::Connection)

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

source

Results

LibPQ.ResultType.
mutable struct Result

A result from a PostgreSQL database query

Fields:

  • result::Ptr{Nothing}

    A pointer to a libpq PGresult object (C_NULL if cleared)

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

    True if the PG object has been cleaned up

  • column_oids::Array{UInt32,1}

    PostgreSQL Oids for each column in the result

  • column_types::Array{Type,1}

    Julia types for each column in the result

  • not_null::Array{Bool,1}

    Whether to expect NULL for each column (whether output data can have missing)

  • column_funcs::Array{Union{Function, Type},1}

    Conversions from PostgreSQL data to Julia types for each column in the result

  • column_names::Array{String,1}

    Name of each column in the result

source
LibPQ.statusMethod.
status(jl_result::Result) -> libpq_c.ExecStatusType

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

See also: error_message

source
Base.closeMethod.
close(jl_result::Result)

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

source
Base.isopenMethod.
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.

source
LibPQ.num_rowsMethod.
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.

source
LibPQ.num_columnsMethod.
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.

source
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.

source
Base.showMethod.
show(io::IO, jl_result::Result)

Show a PostgreSQL result and whether it has been cleared.

source

Statements

LibPQ.StatementType.
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 neame 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

source
LibPQ.num_columnsMethod.
num_columns(stmt::Statement) -> Int

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

source
LibPQ.num_paramsMethod.
num_params(stmt::Statement) -> Int

Return the number of parameters in the prepared statement.

source
Base.showMethod.
show(io::IO, jl_result::Statement)

Show a PostgreSQL prepared statement and its query.

source
LibPQ.load!Function.
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;");
source

Copy

LibPQ.CopyInType.
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

source
LibPQ.executeMethod.
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.

source

Asynchronous

LibPQ.async_executeFunction.
async_execute(
    jl_conn::Connection,
    query::AbstractString,
    [parameters::Union{AbstractVector, Tuple},]
    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. 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.

source
mutable struct AsyncResult

An asynchronous PostgreSQL query

Fields:

  • jl_conn::LibPQ.Connection

    The LibPQ.jl Connection used for the query

  • result_kwargs::Ref

    Keyword arguments to pass to Result on creation

  • should_cancel::Bool

    Whether or not the query should be cancelled, if running

  • result_task::Task

    Task which errors or returns a LibPQ.jl Result which is created once available

source
LibPQ.cancelFunction.
cancel(async_result::AsyncResult)

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

source

Internals

Connections

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.

source
LibPQ.server_versionFunction.
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.

source
LibPQ.encodingFunction.
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 straighforward.

See also: set_encoding!, reset_encoding!

source
LibPQ.set_encoding!Function.
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!

source
LibPQ.reset_encoding!Function.
reset_encoding!(jl_conn::Connection, encoding::String)

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

See also: encoding, set_encoding!

source
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.

source
LibPQ.unique_idFunction.
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.

source
error_message(jl_conn::Connection) -> String

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

source

Connection Info

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)

source
LibPQ.conninfoFunction.
conninfo(jl_conn::Connection) -> Vector{ConnectionOption}

Get all connection options for a connection.

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

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

source

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
source
Base.parseMethod.
parse(::Type{ConninfoDisplay}, str::AbstractString) -> ConninfoDisplay

Parse a ConninfoDisplay from a string. See ConninfoDisplay.

source

Results and Statements

LibPQ.handle_resultFunction.
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.

source
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.

source
LibPQ.column_nameFunction.
column_name(jl_result::Result, column_number::Integer) -> String

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

source
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.

source
LibPQ.column_namesFunction.
column_names(jl_result::Result) -> Vector{String}

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

source
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.

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

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

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

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

source
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.

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

Return the PostgreSQL oids for each column in the result.

source
LibPQ.column_typesFunction.
column_types(jl_result::Result) -> Vector{Type}

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

source
LibPQ.num_paramsMethod.
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.

source
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.

source

Errors

Exception Type Hierarchy
LibPQ Exception Type Hierarchy

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

source

An exception generated by LibPQ.jl

source

An exception with an error message generated by PostgreSQL

source

An error regarding a connection generated by LibPQ.jl

source

An error regarding a query result generated by LibPQ.jl

source

An error from parsing connection parameter strings reported by PostgreSQL

source

An error regarding a connection reported by PostgreSQL

source

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}
source

Type Conversions

LibPQ.oidFunction.
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.

source
LibPQ.PQCharType.
primitive type PQChar 8

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

Fields:

source
LibPQ.PQ_SYSTEM_TYPESConstant.
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.

source
LibPQ.PQTypeMapType.
struct PQTypeMap <: AbstractDict{UInt32,Type}

A mapping from PostgreSQL Oid to Julia type.

Fields:

  • type_map::Dict{UInt32,Type}
source
Base.getindexMethod.
Base.getindex(tmap::PQTypeMap, typ) -> Type

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

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

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

source
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.

source
LibPQ.LIBPQ_TYPE_MAPConstant.
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.

source
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}}
source
Base.getindexMethod.
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.

source
Base.setindex!Method.
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.

source
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.

source
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.

source

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.

source

Parsing

LibPQ.PQValueType.
struct PQValue{OID}

A wrapper for one value in a PostgreSQL result.

Fields:

  • jl_result::LibPQ.Result

    PostgreSQL result

  • row::Int32

    Row index of the result (0-indexed)

  • col::Int32

    Column index of the result (0-indexed)

source
LibPQ.data_pointerFunction.
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.

source
LibPQ.num_bytesFunction.
num_bytes(pqv::PQValue) -> Cint

The length in bytes of the PQValue's corresponding data. LibPQ.jl currently always uses text format, so this is equivalent to C's strlen.

See also: data_pointer

source
Base.unsafe_stringMethod.
unsafe_string(pqv::PQValue) -> String

Construct a String from a PQValue by copying the data.

source
LibPQ.string_viewFunction.
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.

Note

The underlying data will be freed by libpq when the result is cleared, and should only be used temporarily.

See also: bytes_view

source
LibPQ.bytes_viewFunction.
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.

Note

The underlying data will be freed by libpq when the result is cleared, and should only be used temporarily.

source
Base.parseMethod.
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.

source

Miscellaneous

LibPQ.@pqv_strMacro.
@pqv_str -> VersionNumber

Parse a PostgreSQL version.

Note

As of version 10.0, PostgreSQL moved from a three-part version number (where the first two parts represent the major version and the third represents the minor version) to a two-part major-minor version number. In LibPQ.jl, we represent this using the first two VersionNumber components as the major version and the third as the minor version.

Examples

julia> using LibPQ: @pqv_str

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

julia> pqv"9.2.5" == v"9.2.5"
true
source
string_parameters(parameters::AbstractVector) -> Vector{Union{String, Missing}}

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

source
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.

source
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.

source