4.1.1 PostgreSQL Types🔗ℹ

This section applies to connections created with postgresql-connect.

The following table lists the PostgreSQL types known to this library, using the names listed in pg_type.typname, along with their corresponding Racket representations.

The 'char1 type, written "char" in PostgreSQL’s SQL syntax (the quotation marks are significant), is one byte, essentially a tiny integer written as a character.

A SQL value of type decimal is converted to either an exact rational or +nan.0. When converting Racket values to SQL decimal, exact rational values representable by finite decimal strings are converted without loss of precision. (Precision may be lost, of course, if the value is then stored in a database field of lower precision.) Other real values are converted to decimals with a loss of precision. In PostgreSQL, numeric and decimal refer to the same type.

A SQL timestamp with time zone is converted to a Racket sql-timestamp in UTC—that is, with a tz field of 0. If a Racket sql-timestamp without a time zone (tz is #f) is given for a parameter of type timestamp with time zone, it is treated as a timestamp in UTC. See also PostgreSQL Timestamps and Time Zones.

The geometric types such as 'point are represented by structures defined in the db/util/geometry and db/util/postgresql modules.

PostgreSQL user-defined domains are supported in query results if the underlying type is supported. Recordset headers and prepared-statement-result-types report them in terms of the underlying type. Parameters with user-defined domain types are not currently supported. As a workaround, cast the parameter to the underlying type. For example, if the type of $1 is a domain whose underlying type is integer, then replace $1 with ($1::integer).

For each type in the table above, the corresponding array type is also supported, using the pg-array structure. Use the = ANY syntax with an array parameter instead of dynamically constructing a SQL IN expression:

A list may be provided for an array parameter, in which case it is automatically converted using list->pg-array. The type annotation can be dropped when the array type can be inferred from the left-hand side.

PostgreSQL defines many other types, such as network addresses and row types. These are currently not supported, but support may be added in future versions of this library.

4.1.2 MySQL Types🔗ℹ

This section applies to connections created with mysql-connect.

[1] Fields of type CHAR and BINARY are both reported as 'string; they are distinguished by the absence or presence of 'binary in the type’s flags, respectively. Likewise, VARCHAR and VARBINARY are reported as 'var-string; and TEXT and BLOB are reported as 'blob; and they are distinguished by the absence or presence of a 'binary flag. Both TEXT and BLOB types have the 'blob flag set.

[2] MySQL’s TIME type represents time intervals, which may not correspond to times of day (for example, the interval may be negative or larger than 24 hours). In conversion from MySQL results to Racket values, those TIME values that represent times of day are converted to sql-time values; the rest are represented by sql-interval values. (See also sql-time->sql-interval and sql-interval->sql-time.)

[3] The JSON type (introduced in MySQL 5.7) is represented by jsexpr? values, but that representation overlaps with the representation of MySQL character types (eg, VARCHAR), and the values are interpreted in incompatible ways. Query parameters are only sent as JSON if they are wrapped with mysql-json; JSON result values are returned as unwrapped jsexpr? values.

[4] The VECTOR type (introduced in MySQL 9.0) is represented by non-empty flvectors (flvector?). Beware that flvectors store double-precision floating-point numbers, but MySQL VECTOR fields are single-precision.

[5] Geometry values (geometry2d?) are sent using their “well-known binary” encoding. For servers version 8.0 and later, the values are sent as 'blob; for servers version 5.7.x and earlier, values are sent as 'geometry.

The MySQL enum and set types are not supported. As a workaround, cast them to or from either integers or strings.

4.1.3 Cassandra Types🔗ℹ

This section applies to connections created with cassandra-connect.

4.1.4 SQLite Types🔗ℹ

This section applies to connections created with sqlite3-connect.

The following table lists the SQLite types known to this library, along with their corresponding Racket representations.

SQLite does not report specific parameter and result types for prepared queries. Instead, they are assigned the pseudo-type 'any. Conversion of Racket values to parameters accepts strings, bytes, and real numbers.

An exact integer that cannot be represented as a 64-bit signed integer is converted as real, not integer.

4.1.5 ODBC Types🔗ℹ

This section applies to connections created with odbc-connect or odbc-driver-connect.

[1] The type of a string parameter is determined by the connection’s character mode; see odbc-connect’s #:character-mode argument. Depending on connection quirks, the type may also be set as SQL_LONGVARCHAR or SQL_WLONGVARCHAR.

[2] Depending on connection quirks, the type may also be set as SQL_LONGVARBINARY.

Interval types are not currently supported for ODBC connections.

Changed in version 1.14 of package db-lib: Added support for the DB2 types. Exact numbers are sent as SQL_NUMERIC if possible.

4.2.1 SQL NULL🔗ℹ

SQL NULL is translated into the unique sql-null value.

4.2.2 Dates and Times🔗ℹ

The DATE, TIME (WITH TIME ZONE and without), TIMESTAMP (WITH TIME ZONE and without), and INTERVAL SQL types are represented by the following structures.

See also Datetime Type Utilities for more functions on datetime values.

Dates with zero-valued month or day components are a MySQL extension.

The tz field indicates the time zone offset as the number of seconds east of GMT (as in srfi/19). If tz is #f, the time or timestamp does not carry time zone information.

The sql-time and sql-timestamp structures store fractional seconds to nanosecond precision for compatibility with srfi/19. Note, however, that database systems generally do not support nanosecond precision; PostgreSQL, for example, only supports microsecond precision.

That is, an interval consists of two groups of components: year-month and day-time, and normalization is done only within groups. In fact, the SQL standard recognizes those two types of intervals separately (see sql-year-month-interval? and sql-day-time-interval?, below), and does not permit combining them. Intervals such as 1 month 3 days are a PostgreSQL extension.

If interval is out of range, the failure value is called, if it is a procedure, or returned, otherwise.

4.2.3 Bits🔗ℹ

The BIT and BIT VARYING (VARBIT) SQL types are represented by bit-vectors (data/bit-vector).

The following functions are provided for backwards compatibility. They are deprecated and will be removed in a future release of Racket.