Manual

using Postgres re-exports DBInterface. Access package-specific APIs through the Postgres. namespace.

Connecting

Use DBInterface.connect with either a PostgreSQL URI, a libpq-style keyword string, or explicit host/user/password arguments.

using Postgres, Tables

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

DBInterface.close!(conn)

Keyword DSNs and environment defaults are also supported.

conn = DBInterface.connect(
    Postgres.Connection,
    "host=127.0.0.1 port=5432 user=postgres password=postgres dbname=postgres",
)

Querying

DBInterface.execute returns a Tables.jl-compatible result. For small result sets, Tables.rowtable is a convenient way to materialize rows.

rows = Tables.rowtable(DBInterface.execute(conn, "SELECT 1 AS id, 'hello' AS label"))
row = only(rows)
@show row.id row.label

The result object can also be iterated directly.

for row in DBInterface.execute(conn, "SELECT generate_series(1, 3) AS n")
    @show row.n
end

PostgreSQL command completion metadata is available on the result.

result = DBInterface.execute(conn, "UPDATE items SET seen = true WHERE seen = false")
@show Postgres.command_tag(result)
@show Postgres.rows_affected(result)

Typed Results With StructUtils

Postgres.jl result sets implement the StructUtils.jl interface. That means DBInterface.execute can deserialize rows directly into a target type instead of first materializing Tables.rowtable rows.

For a single-row query, pass a concrete struct type as the fourth argument. The query should return exactly one row.

using Postgres, StructUtils

struct CountRow
    count::Int
end

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

For multi-row queries, pass a vector type.

struct UserName
    id::Int
    name::String
end

users = DBInterface.execute(conn, "SELECT id, name FROM users ORDER BY id", (), Vector{UserName})

StructUtils field tags let Julia models keep idiomatic field names while SQL keeps idiomatic column names. Tags for Postgres.jl live under the postgres namespace.

using Dates, Postgres, 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})

The postgres=(name=:column_name,) tag is only needed when a column should map to a differently named field. Columns such as id or name can be left untagged because they already match the Julia field name.

Driver Styles

Connection behavior such as query logging, server notices, and asynchronous notifications is selected by a concrete driver style. Subtype Postgres.AbstractPostgresStyle, overload the documented behavior hooks for that style, and pass an instance with the style connection keyword. The default Postgres.PostgresStyle keeps query logging disabled and reports server notices through Julia's logger.

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
Postgres.notice_callback(::LoggingStyle, notice) = @info "notice" notice
Postgres.notification_callback(::LoggingStyle, notification) = @info "notification" notification

conn = DBInterface.connect(Postgres.Connection, "host=127.0.0.1 user=postgres dbname=postgres"; style=LoggingStyle())

query_logger's info includes the SQL and the bound parameter values, so a logger that writes them out records whatever sensitive data those queries carry. Redact or omit info.params when the log destination is less trusted than the database itself.

AbstractPostgresStyle <: StructUtils.StructStyle

Postgres.query_logging_enabled(::MyStyle) = true
Postgres.query_logger(::MyStyle, event::Symbol, info::NamedTuple) = ...
Postgres.notice_callback(::MyStyle, notice) = ...
Postgres.notification_callback(::MyStyle, notification) = ...

Postgres.query_logging_enabled(style) -> Bool

Postgres.query_logger(style, event::Symbol, info::NamedTuple)

Postgres.notice_callback(style, notice)

Postgres.notification_callback(style, notification::Notification)

Parameters And Prepared Statements

Use PostgreSQL placeholders ($1, $2, ...) and pass a tuple or other iterable of parameter values. Note the use of raw"..." strings so that $1 is not treated as Julia string interpolation.

rows = Tables.rowtable(DBInterface.execute(conn, raw"SELECT $1::int + $2::int AS total", (20, 22)))
@show only(rows).total

Prepared statements can be created explicitly. Postgres.jl caches their named backend statements with LRU eviction while returning an independent handle to each caller. Set statement_cache_maxsize=0 to disable this cache.

stmt = DBInterface.prepare(conn, raw"SELECT $1::text AS value")
rows = Tables.rowtable(DBInterface.execute(stmt, ("prepared",)))
DBInterface.close!(stmt)

Bulk inserts can use DBInterface.executemany.

DBInterface.execute(conn, "CREATE TEMP TABLE demo_many (id int, name text)")
stmt = DBInterface.prepare(conn, raw"INSERT INTO demo_many VALUES ($1, $2)")
DBInterface.executemany(stmt, ([1, 2, 3], ["a", "b", "c"]))
DBInterface.close!(stmt)

Transactions

Postgres.jl supports both its connection-passing helper and the DBInterface transaction API.

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

DBInterface.transaction(conn) do
    DBInterface.execute(conn, "INSERT INTO tx_demo VALUES (2)")
end

Nested transactions use savepoints.

Streaming Results

For larger result sets, Postgres.cursor fetches rows in batches.

cur = Postgres.cursor(conn, "SELECT generate_series(1, 1000) AS n"; fetchsize=100)
try
    for row in cur
        @show row.n
    end
finally
    DBInterface.close!(cur)
end

COPY

Use Postgres.copy_from and Postgres.copy_to for PostgreSQL's COPY protocol.

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")

Cancellation

Postgres.cancel_query! sends a PostgreSQL CancelRequest for a running query on another task.

task = Threads.@spawn DBInterface.execute(conn, "SELECT pg_sleep(10)")
sleep(0.5)
Postgres.cancel_query!(conn)

The running query raises a Postgres.Error with PostgreSQL SQLSTATE 57014.

LISTEN And NOTIFY

Use one connection to listen and another to notify.

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")
notification = Postgres.wait_for_notification(listener; timeout=5.0)
@show notification.channel notification.payload

DBInterface.close!(notifier)
DBInterface.close!(listener)

Type Translation

Postgres.jl maps common PostgreSQL types to Julia values:

• integers, floats, booleans, text, UUIDs, dates, times, timestamps, and bytea map to their natural Julia types.
• json and jsonb are returned as lazy JSON values from JSON.jl.
• numeric maps to DataDecimals.DecimalValue{DataDecimals.Int256} to preserve the value and decimal scale. Values outside its signed 256-bit coefficient range return their original text with a warning.
• timestamp and timestamptz map to Durations.Timestamp{Dates.Microsecond}.
• interval maps to Dates.Period or Dates.CompoundPeriod.
• arrays map to Julia arrays, preserving missing for SQL NULL.
• PostgreSQL range types map to Postgres.PostgresRange{T}.

Custom enum, composite, and range types can be registered on a connection.

DBInterface.execute(conn, "CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')")
Postgres.register_enum!(conn, "mood"; julia_type=Symbol)

row = only(Tables.rowtable(DBInterface.execute(conn, "SELECT 'happy'::mood AS mood")))
@show row.mood

Registering composite and range types follows the same pattern.

Registration controls result decoding. Direct parameter binding for registered enum, composite, and range Julia values is not supported. Bind their PostgreSQL text representation and add an explicit SQL cast when needed.

Session Formats

Text decoding assumes the server renders dates and timestamps in the ISO DateStyle output format and intervals in the postgres IntervalStyle. The driver checks the server-reported settings at connect time and issues a SET for any that differ, preserving the configured date field order (MDY/DMY/ YMD) since it decides how ambiguous input literals like '01/02/2020' are read. The alignment is re-applied on reconnect, but not if the session is changed afterwards: running SET DateStyle = ... or SET IntervalStyle = ... mid-session breaks decoding — intervals and unparseable dates raise errors rather than silently returning wrong values.

Transaction-mode poolers cannot preserve session settings between logical connections. See the Support Policy before using this mode.

Precision And Bit Strings

time, interval, timestamp, and timestamptz values retain microseconds. Timestamps use Durations.Timestamp{Dates.Microsecond}. timestamptz values are normalized to UTC. Select a timestamp as ::text to retain its server-rendered timezone offset. Values outside the Unix-epoch Int64 microsecond range raise an error; its upper limit falls in year 294247, before PostgreSQL's upper limit in year 294276. Read those values as text, request Timestamp{Second} or Timestamp{Millisecond} in a typed result, or register a custom parser.

Typed results can request another Durations.Timestamp{P} resolution. Conversion must be exact and in range. Explicit DateTime fields truncate to milliseconds. Timestamp parameters carry an explicit UTC marker, including in non-UTC sessions. Parameters finer than a microsecond raise InexactError rather than letting PostgreSQL round.

Typed numeric fields can use DataDecimals.Decimal{P,S} or DataDecimals.DecimalValue{T}. Conversion preserves the exact value or throws; it does not round to the target scale. Both decimal types support scalar and array parameters. Explicit decimal fields always throw if conversion cannot be exact.

For untyped numeric results, numeric_overflow=:warn (the default) returns values that cannot fit DecimalValue{Int256} as their original text and emits a warning. The fallback preserves every digit and trailing zero. It also applies to numeric array elements, range bounds, and the special values NaN, Infinity, and -Infinity. Such results can therefore contain decimals and strings.

Use numeric_overflow=:error to reject these values. Set it on ConnectionParams, Connection, DBInterface.connect, or ConnectionPool. For a DSN, use numeric_overflow=error. To read large values without a warning, select them as SQL text or register a custom numeric parser.

params = Postgres.ConnectionParams(host="localhost", dbname="postgres",
                                   numeric_overflow=:error)

using Dates, Durations, DataDecimals

struct Measurement
    recorded_at::Durations.Timestamp{Microsecond}
    value::DataDecimals.Decimal64{4}
end

measurement = DBInterface.execute(conn,
    "SELECT '2024-01-02 03:04:05.123456'::timestamp AS recorded_at, 12.3400::numeric AS value",
    (), Measurement)

boolean and bit(1) map to Bool. Read wider bit(n) values as ::text; decoding them as Bool raises Postgres.PostgresInterfaceError.

Values Without A Julia Representation

A few PostgreSQL values have no faithful Julia equivalent and raise Postgres.PostgresInterfaceError when decoded rather than silently returning a wrong value: infinity/-infinity dates and timestamps, dates in the BC era, and numeric NaN/infinity. "char" columns (the 1-byte internal catalog type) decode to Char, including the zero byte ('\0') and high-bit bytes; note that a '\0' read from such a column cannot be bound back as a text parameter, because PostgreSQL rejects NUL bytes in text — write it with an explicit cast such as 0::"char" instead.