LibPQ.jl

LibPQ.jl allows for hierarchical type conversion overrides:

Connection-level

When creating a LibPQ.Connection, you can provide type_map and conversions arguments. These settings apply to all queries executed using that specific connection.

Global-level

To change behavior for every query in the entire session, you can modify the global constants:

• LibPQ.LIBPQ_TYPE_MAP
• LibPQ.LIBPQ_CONVERSIONS

Priority Order

Overrides are applied in the following order of precedence (highest to lowest):

1. Query-level (execute arguments)
2. Connection-level (LibPQ.Connection arguments)
3. Global-level (LibPQ.LIBPQ_TYPE_MAP / LibPQ.LIBPQ_CONVERSIONS)
4. Default LibPQ mappings

PostgreSQL NULL values are represented as missing in Julia.

By default, data streamed via the Tables interface uses Union{T, Missing} (e.g., Vector{Union{Int32, Missing}}).

To avoid missing and retrieve data as pure types (e.g., Vector{Int32}), use the not_null keyword argument in execute. This asserts that the specified columns do not contain NULL values.

not_null accepts:

• A Bool: If true, asserts all columns are not null. If false, asserts all columns can be null.
• A list of column names (as Symbols or Strings).
• A list of column positions (as Integers).

# Example: Asserting columns are not null to get non-missing types
# (Assuming 'id' is a non-null column)
execute(conn, "SELECT id FROM users", not_null=[:id])

LibPQ.jl is a low-level wrapper. To use it effectively, understand what it does and does not provide:

What it does NOT do (Non-Goals)

• Does not parse or emit SQL: You must provide valid SQL strings.
• No transaction/cursor interface: It does not provide high-level abstractions for managing transactions or cursors.
• No SQL pattern abstractions: It does not provide helpers for common SQL patterns.
• No standard database interface: It does not aim to conform to a generic database interface (though it can be used as a base for a higher-level PostgreSQL.jl package).

Design Philosophy

• The package aims to wrap libpq as documented in the official PostgreSQL documentation, including non-deprecated functionality and error handling.

To test LibPQ.jl locally, you need access to a PostgreSQL server with a database named postgres. You can use Docker to quickly spin up a test instance.

When connecting, you may need to set the PGHOST=localhost environment variable to ensure the connection uses a TCP socket instead of a UNIX domain socket.

To specify the database user for testing, set the LIBPQJL_DATABASE_USER environment variable.

docker run --detach --name test-libpqjl -e POSTGRES_HOST_AUTH_METHOD=trust -p 5432:5432 postgres

For high-performance bulk data loading, use the PostgreSQL COPY command via LibPQ.CopyIn. This allows you to stream data directly to the server, which is often faster than repeated INSERT statements.

To use this, create a LibPQ.CopyIn object with your COPY ... FROM STDIN command and the data to be streamed (e.g., an iterator of CSV-formatted strings), then pass that object to execute(conn, copyin).

using LibPQ, DataFrames

conn = LibPQ.Connection("dbname=postgres user=$DATABASE_USER")

# Prepare data as CSV strings
row_strings = imap(eachrow(df)) do row
    if ismissing(row[:yes_nulls])
        "$(row[:no_nulls]),\n"
    else
        "$(row[:no_nulls]),$(row[:yes_nulls])\n"
    end
end

# Stream data using COPY
copyin = LibPQ.CopyIn("COPY libpqjl_test FROM STDIN (FORMAT CSV);", row_strings)
execute(conn, copyin)

close(conn)

LibPQ.jl can be used with Amazon Redshift. By default, LibPQ.jl sets certain client options to improve reliability, but these unsupported options may prevent Redshift connections. To connect to Redshift, you must disable these default options by passing an empty Dict{String, String} to the options keyword argument in the LibPQ.Connection constructor.

conn = LibPQ.Connection("dbname=myredshift"; options=Dict{String, String}())

LibPQ is compatible with the DBInterface.jl ecosystem. You can use DBInterface.connect to establish a connection using LibPQ.Connection and use generic DBInterface methods to execute queries and manage connections.

using LibPQ, DBInterface

conn = DBInterface.connect(LibPQ.Connection, "dbname=postgres")
res = DBInterface.execute(conn, "SELECT * FROM table")
DBInterface.close!(conn)

Currently, LibPQ.jl converts all Julia types to strings before passing them to libpq. There is no special treatment for specific Julia types. If you need to pass complex data types (like arrays) where the default Julia string representation is not valid in PostgreSQL, you must manually convert the data to a valid PostgreSQL string format before passing it to execute.

Example of manually formatting an array for a PostgreSQL ANY check:

# Converting a Julia array to a PostgreSQL array string format
A = collect(12:15)
# Resulting string: "{12,13,14,15}"
execute(conn, "SELECT $1 = ANY($2) AS result", Any[13, string("{", join(A, ","), "}")])

# Example: Manual conversion for array types
A = collect(12:15);
nt = columntable(execute(conn, "SELECT $1 = ANY($2) AS result", Any[13, string("{", join(A, ","), "}")]));
nt[:result][1]
# true

When fetching data, LibPQ.jl automatically maps PostgreSQL types to Julia types. Unknown or unsupported types default to String.

Common mappings include:

• int4 $\rightarrow$ Int32
• varchar $\rightarrow$ String
• float8[] $\rightarrow$ Vector{Float64}
• bool $\rightarrow$ Bool
• timestamp $\rightarrow$ DateTime

Example usage with DataFrames.jl:

using DataFrames
using LibPQ

df = DataFrame(execute(conn, "SELECT 1::int4, 'foo'::varchar, '{1.0, 2.1, 3.3}'::float8[], false, TIMESTAMP '2004-10-19 10:23:54'"))