Postgres.jl

Postgres.jl is a PostgreSQL client written in Julia that implements the v3 wire protocol with DBInterface and Tables integration.

Installation

import Pkg
Pkg.add("Postgres")

Quick start

using Postgres
DBInterface.connect(Postgres.Connection, "host=127.0.0.1;port=5432;user=postgres;password=postgres;dbname=postgres") do conn
    row = only(DBInterface.execute(conn, "SELECT 1 AS a"))
    @show row.a
end

Connections

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "postgresql://postgres:postgres@127.0.0.1:5432/postgres?sslmode=disable")
DBInterface.close!(conn)

Connection options support:

• libpq-style keyword strings such as host=127.0.0.1 port=5432 user=postgres dbname=postgres.
• PostgreSQL URIs such as postgresql://postgres:postgres@127.0.0.1:5432/postgres.
• Environment defaults: PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, PGAPPNAME, PGCONNECT_TIMEOUT, PGOPTIONS, TLS-related PGSSL* variables, and PGGSSENCMODE, PGKRBSRVNAME, PGGSSDELEGATION.
• sslmode values: disable, prefer (the default), require, verify-full. Only verify-full verifies the server's certificate; require encrypts without authenticating the server, and the default prefer falls back to an unencrypted connection if the server declines TLS. Use verify-full with sslrootcert when the connection needs to be authenticated.
• TLS files: sslrootcert, sslcert, sslkey, and sslcapath (sslcapath is a fallback CA bundle or directory, used only when sslrootcert is unset and ignored otherwise). sslservername overrides the TLS server name when connecting to a pre-resolved address; under verify-full it is also the name the certificate is verified against, so it must name the server you intend to authenticate.
• gssencmode values: disable (the default), prefer, require. GSSAPI (Kerberos) encryption is negotiated before TLS, as in libpq, through the operating system's Kerberos library and ticket cache (kinit); prefer only tries it when a ticket is available and otherwise follows sslmode. krbsrvname sets the Kerberos service name (default postgres; Active Directory servers often need POSTGRES) and gssdelegation forwards the ticket to the server. GSSAPI authentication (gss in pg_hba.conf) is answered automatically with the same library.
• connect_timeout (seconds) and statement_timeout (milliseconds).
• application_name and statement_cache_maxsize.
• options: server command-line options applied when the session starts, as in libpq (PGOPTIONS). For example options='-c search_path=myschema' sets the default schema. The value is sent in the startup packet, so it also applies after an automatic reconnect.

See the support policy for tested Julia and PostgreSQL versions, TLS limits, and transaction-pooler requirements.

You can also use ConnectionParams:

using Postgres, DBInterface
params = Postgres.ConnectionParams(host="127.0.0.1", user="postgres", password="postgres", dbname="postgres", sslmode="disable")
conn = DBInterface.connect(Postgres.Connection, params)
DBInterface.close!(conn)

Queries and prepared statements

using Postgres, DBInterface, Tables
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
rows = Tables.rowtable(DBInterface.execute(conn, raw"SELECT $1::int AS val", (42,)))
@show rows[1].val
stmt = DBInterface.prepare(conn, raw"SELECT $1::int AS val")
rows = Tables.rowtable(DBInterface.execute(stmt, (7,)))
DBInterface.close!(stmt)
DBInterface.close!(conn)

Postgres.jl can also deserialize result rows directly into structs through StructUtils.jl. If column names match field names, pass the target type as the fourth DBInterface.execute argument.

using Postgres, DBInterface, StructUtils

struct CountRow
    count::Int
end

row = DBInterface.execute(conn, "SELECT count(*)::int AS count FROM users", (), CountRow)
@show row.count

Use StructUtils.@tags with the postgres namespace when table columns use a different naming convention than Julia fields.

using Dates, Postgres, DBInterface, StructUtils

StructUtils.@tags struct ProfileSummary
    profileId::Int &(postgres=(name=:profile_id,),)
    firstName::Union{Missing, String} &(postgres=(name=:first_name,),)
    lastName::Union{Missing, String} &(postgres=(name=:last_name,),)
    createdAt::DateTime &(postgres=(name=:created_at,),)
end

profile = DBInterface.execute(conn, raw"""
    SELECT profile_id, first_name, last_name, created_at
    FROM profiles
    WHERE profile_id = $1
    """, (profile_id,), ProfileSummary)

profiles = DBInterface.execute(conn, """
    SELECT profile_id, first_name, last_name, created_at
    FROM profiles
    ORDER BY created_at DESC
    LIMIT 10
    """, (), Vector{ProfileSummary})

Postgres.command_tag(result) and Postgres.rows_affected(result) expose PostgreSQL command completion metadata.

Explicit named prepared statements use an LRU backend cache. Caller handles are independent. Set statement_cache_maxsize=0 to disable this cache.

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres"; statement_cache_maxsize=10)
Postgres.set_statement_cache_maxsize!(conn, 5)
cached = Postgres.get_cached_statements(conn)
Postgres.clear_statement_cache!(conn)
DBInterface.close!(conn)

Transactions

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
Postgres.transaction(conn) do tx
    DBInterface.execute(tx, "CREATE TEMP TABLE tx_demo (id int)")
    DBInterface.execute(tx, "INSERT INTO tx_demo VALUES (1)")
end
Postgres.@transaction conn begin
    DBInterface.execute(conn, "INSERT INTO tx_demo VALUES (2)")
end
DBInterface.close!(conn)

Nested transactions are implemented with savepoints.

COPY protocol

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
DBInterface.execute(conn, "CREATE TEMP TABLE copy_demo (id int, name text)")
Postgres.copy_from(conn, "COPY copy_demo (id, name) FROM STDIN", "1\talpha\n2\tbeta\n")
bytes = Postgres.copy_to(conn, "COPY copy_demo TO STDOUT (FORMAT BINARY)")
Postgres.copy_from(conn, "COPY copy_demo FROM STDIN (FORMAT BINARY)", bytes)
DBInterface.close!(conn)

LISTEN/NOTIFY

using Postgres, DBInterface
listener = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
notifier = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
Postgres.listen!(listener, "events")
Postgres.notify!(notifier, "events", "hello")
notice = Postgres.wait_for_notification(listener; timeout=5.0)
@show notice.channel notice.payload
DBInterface.close!(notifier)
DBInterface.close!(listener)

Cursor streaming

using Postgres, DBInterface
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
cur = Postgres.cursor(conn, "SELECT generate_series(1, 5) AS n"; fetchsize=2)
for row in cur
    @show row.n
end
DBInterface.close!(cur)
DBInterface.close!(conn)

Type registry

using Postgres, DBInterface, Tables
conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres")
DBInterface.execute(conn, "CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')")
Postgres.register_enum!(conn, "mood")
row = only(Tables.rowtable(DBInterface.execute(conn, "SELECT 'happy'::mood AS mood")))
@show row.mood
DBInterface.close!(conn)

numeric values use DataDecimals.DecimalValue{DataDecimals.Int256} (with a warning and exact text fallback for values beyond its storage range), interval values as Dates.Period or Dates.CompoundPeriod, and range types as Postgres.PostgresRange{T}. Timestamps use Durations.Timestamp{Dates.Microsecond} and retain all six fractional digits. Custom enum, composite, and range registration controls result decoding. Those custom Julia values are not accepted as direct query parameters; bind a PostgreSQL text representation with an explicit SQL cast instead.

Query logging and driver styles

Driver behavior — query logging, server notices, asynchronous notifications — is customized by defining a driver "style": subtype Postgres.AbstractPostgresStyle, overload the behavior hooks for it, and pass an instance via the style connection keyword.

using Postgres, DBInterface

struct LoggingStyle <: Postgres.AbstractPostgresStyle end
Postgres.query_logging_enabled(::LoggingStyle) = true
Postgres.query_logger(::LoggingStyle, event::Symbol, info::NamedTuple) = @info "query" event info.success info.duration_ns

conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1;user=postgres;password=postgres;dbname=postgres"; style=LoggingStyle())
DBInterface.execute(conn, "SELECT 1")
DBInterface.close!(conn)

Connection pooling

using Postgres, DBInterface
pool = Postgres.ConnectionPool(Postgres.Connection, "127.0.0.1", "postgres", "postgres"; dbname="postgres", limit=5)
Postgres.with_connection(pool) do conn
    DBInterface.execute(conn, "SELECT 1")
end
DBInterface.close!(pool)

Errors and cancellation

Postgres.Error represents server errors and includes SQLSTATE codes; Postgres.PostgresInterfaceError covers client-side failures. Use Postgres.cancel_query!(conn) to send a CancelRequest to the server.

Testing and contributing

Run julia --project -e 'using Pkg; Pkg.test()' from this repository. Database tests need a running Linux Docker daemon and OpenSSL on PATH. Set POSTGRES_REQUIRE_INTEGRATION=true to fail if those tests cannot run. Without Docker, the suite runs parser and API checks only.

The suite includes seeded fuzz tests for connection strings, protocol framing, binary arrays, composite values, and temporal precision. For a bug report, include the Julia, Postgres.jl, and PostgreSQL versions and a small reproducer. Remove passwords, connection secrets, and private data first.