Module sqlx::postgres::types

source ·
Available on crate feature postgres only.
Expand description

Conversions between Rust and Postgres types.

§Types

Rust typePostgres type(s)
boolBOOL
i8“CHAR”
i16SMALLINT, SMALLSERIAL, INT2
i32INT, SERIAL, INT4
i64BIGINT, BIGSERIAL, INT8
f32REAL, FLOAT4
f64DOUBLE PRECISION, FLOAT8
&str, StringVARCHAR, CHAR(N), TEXT, NAME, CITEXT
&[u8], Vec<u8>BYTEA
()VOID
PgIntervalINTERVAL
PgRange<T>INT8RANGE, INT4RANGE, TSRANGE, TSTZRANGE, DATERANGE, NUMRANGE
PgMoneyMONEY
PgLTreeLTREE
PgLQueryLQUERY
PgCiTextCITEXT1

1 SQLx generally considers CITEXT to be compatible with String, &str, etc., but this wrapper type is available for edge cases, such as CITEXT[] which Postgres does not consider to be compatible with TEXT[].

§bigdecimal

Requires the bigdecimal Cargo feature flag.

Rust typePostgres type(s)
bigdecimal::BigDecimalNUMERIC
§Note: BigDecimal Has a Larger Range than NUMERIC

BigDecimal can represent values with a far, far greater range than the NUMERIC type in Postgres can.

NUMERIC is limited to 131,072 digits before the decimal point, and 16,384 digits after it. See Section 8.1, Numeric Types of the Postgres manual for details.

Meanwhile, BigDecimal can theoretically represent a value with an arbitrary number of decimal digits, albeit with a maximum of 263 significant figures.

Because encoding in the current API design must be infallible, when attempting to encode a BigDecimal that cannot fit in the wire representation of NUMERIC, SQLx may instead encode a sentinel value that falls outside the allowed range but is still representable.

This will cause the query to return a DatabaseError with code 22P03 (invalid_binary_representation) and the error message invalid scale in external "numeric" value (though this may be subject to change).

However, BigDecimal should be able to decode any NUMERIC value except NaN, for which it has no representation.

§rust_decimal

Requires the rust_decimal Cargo feature flag.

Rust typePostgres type(s)
rust_decimal::DecimalNUMERIC
§Note: rust_decimal::Decimal Has a Smaller Range than NUMERIC

NUMERIC is can have up to 131,072 digits before the decimal point, and 16,384 digits after it. See Section 8.1, Numeric Types of the Postgres manual for details.

However, rust_decimal::Decimal is limited to a maximum absolute magnitude of 296 - 1, a number with 67 decimal digits, and a minimum absolute magnitude of 10-28, a number with, unsurprisingly, 28 decimal digits.

Thus, in contrast with BigDecimal, NUMERIC can actually represent every possible value of rust_decimal::Decimal, but not the other way around. This means that encoding should never fail, but decoding can.

§chrono

Requires the chrono Cargo feature flag.

Rust typePostgres type(s)
chrono::DateTime<Utc>TIMESTAMPTZ
chrono::DateTime<Local>TIMESTAMPTZ
chrono::NaiveDateTimeTIMESTAMP
chrono::NaiveDateDATE
chrono::NaiveTimeTIME
PgTimeTzTIMETZ

§time

Requires the time Cargo feature flag.

Rust typePostgres type(s)
time::PrimitiveDateTimeTIMESTAMP
time::OffsetDateTimeTIMESTAMPTZ
time::DateDATE
time::TimeTIME
PgTimeTzTIMETZ

§uuid

Requires the uuid Cargo feature flag.

Rust typePostgres type(s)
uuid::UuidUUID

§ipnetwork

Requires the ipnetwork Cargo feature flag.

Rust typePostgres type(s)
ipnetwork::IpNetworkINET, CIDR
std::net::IpAddrINET, CIDR

Note that because IpAddr does not support network prefixes, it is an error to attempt to decode an IpAddr from a INET or CIDR value with a network prefix smaller than the address’ full width: /32 for IPv4 addresses and /128 for IPv6 addresses.

IpNetwork does not have this limitation.

§mac_address

Requires the mac_address Cargo feature flag.

Rust typePostgres type(s)
mac_address::MacAddressMACADDR

§bit-vec

Requires the bit-vec Cargo feature flag.

Rust typePostgres type(s)
bit_vec::BitVecBIT, VARBIT

§json

Requires the json Cargo feature flag.

Rust typePostgres type(s)
Json<T>JSON, JSONB
serde_json::ValueJSON, JSONB
&serde_json::value::RawValueJSON, JSONB

Value and RawValue from serde_json can be used for unstructured JSON data with Postgres.

Json<T> can be used for structured JSON data with Postgres.

§Composite types

User-defined composite types are supported through a derive for Type.

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);
#[derive(sqlx::Type)]
#[sqlx(type_name = "inventory_item")]
struct InventoryItem {
    name: String,
    supplier_id: i32,
    price: BigDecimal,
}

Anonymous composite types are represented as tuples. Note that anonymous composites may only be returned and not sent to Postgres (this is a limitation of postgres).

§Arrays

One-dimensional arrays are supported as Vec<T> or &[T] where T implements Type.

§Enumerations

User-defined enumerations are supported through a derive for Type.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
#[derive(sqlx::Type)]
#[sqlx(type_name = "mood", rename_all = "lowercase")]
enum Mood { Sad, Ok, Happy }

Rust enumerations may also be defined to be represented as an integer using repr. The following type expects a SQL type of INTEGER or INT4 and will convert to/from the Rust enumeration.

#[derive(sqlx::Type)]
#[repr(i32)]
enum Mood { Sad = 0, Ok = 1, Happy = 2 }

Structs§

  • The PostgreSQL OID type stores an object identifier, used internally by PostgreSQL as primary keys for various system tables.
  • Case-insensitive text (citext) support for Postgres.
  • Container for a Label Tree Query (lquery) in Postgres.
  • Modifiers that can be set to non-star labels
  • Container for a Label Tree (ltree) in Postgres.
  • The PostgreSQL MONEY type stores a currency amount with a fixed fractional precision. The fractional precision is determined by the database’s lc_monetary setting.
  • Represents a moment of time, in a specified timezone.

Enums§

Traits§

  • Provides information necessary to encode and decode Postgres arrays as compatible Rust types.