Module sqlx_postgres::types

Conversions between Rust and Postgres types.

Types

Rust type	Postgres type(s)
bool	BOOL
i8	“CHAR”
i16	SMALLINT, SMALLSERIAL, INT2
i32	INT, SERIAL, INT4
i64	BIGINT, BIGSERIAL, INT8
f32	REAL, FLOAT4
f64	DOUBLE PRECISION, FLOAT8
&str, String	VARCHAR, CHAR(N), TEXT, NAME, CITEXT
&[u8], Vec<u8>	BYTEA
()	VOID
PgInterval	INTERVAL
PgRange<T>	INT8RANGE, INT4RANGE, TSRANGE, TSTZRANGE, DATERANGE, NUMRANGE
PgMoney	MONEY
PgLTree	LTREE
PgLQuery	LQUERY
PgCiText	CITEXT1

¹ 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 type	Postgres type(s)
bigdecimal::BigDecimal	NUMERIC

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 2⁶³ 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 type	Postgres type(s)
rust_decimal::Decimal	NUMERIC

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 2⁹⁶ - 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 type	Postgres type(s)
chrono::DateTime<Utc>	TIMESTAMPTZ
chrono::DateTime<Local>	TIMESTAMPTZ
chrono::NaiveDateTime	TIMESTAMP
chrono::NaiveDate	DATE
chrono::NaiveTime	TIME
[PgTimeTz]	TIMETZ

time

Requires the time Cargo feature flag.

Rust type	Postgres type(s)
time::PrimitiveDateTime	TIMESTAMP
time::OffsetDateTime	TIMESTAMPTZ
time::Date	DATE
time::Time	TIME
[PgTimeTz]	TIMETZ

uuid

Requires the uuid Cargo feature flag.

Rust type	Postgres type(s)
uuid::Uuid	UUID

ipnetwork

Requires the ipnetwork Cargo feature flag.

Rust type	Postgres type(s)
ipnetwork::IpNetwork	INET, CIDR
std::net::IpAddr	INET, 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 type	Postgres type(s)
mac_address::MacAddress	MACADDR

bit-vec

Requires the bit-vec Cargo feature flag.

Rust type	Postgres type(s)
bit_vec::BitVec	BIT, VARBIT

json

Requires the json Cargo feature flag.

Rust type	Postgres type(s)
Json<T>	JSON, JSONB
serde_json::Value	JSON, JSONB
&serde_json::value::RawValue	JSON, 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

• Oid
  The PostgreSQL OID type stores an object identifier, used internally by PostgreSQL as primary keys for various system tables.
• PgCiText
  Case-insensitive text (citext) support for Postgres.
• PgInterval
• PgLQuery
  Container for a Label Tree Query (lquery) in Postgres.
• PgLQueryVariant
• PgLQueryVariantFlag
  Modifiers that can be set to non-star labels
• PgLTree
  Container for a Label Tree (ltree) in Postgres.
• PgLTreeLabel
• PgMoney
  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.
• PgRange

Enums

• PgLQueryLevel
• PgLTreeParseError
  Represents ltree specific errors

Traits

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