LibPQ API

Public

Connections

LibPQ.ConnectionType
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,
    binary_format::Bool=false,
    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.

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.

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

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},]
    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.

source
LibPQ.cancelFunction
cancel(async_result::AsyncResult)

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

source

Internals

Connections

LibPQ.handle_new_connectionFunction
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.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.transaction_statusFunction
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
LibPQ.error_messageMethod
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

LibPQ.ConnectionOptionType
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
LibPQ.ConninfoDisplayType
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:

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
LibPQ.error_messageMethod
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
LibPQ.Errors.PQResultErrorType

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
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
LibPQ.PQConversionsType
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
LibPQ.LIBPQ_CONVERSIONSConstant
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

Parsing

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. When a query uses LibPQ.TEXT format, 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
LibPQ.string_parametersFunction
string_parameters(parameters::AbstractVector) -> Vector{Union{String, Missing}}

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

source
LibPQ.parameter_pointersFunction
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
LibPQ.unsafe_string_or_nullFunction
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