uPg: Supported data types

PostgreSQL provides data type support with a system of buffer parsers and formatters. Please refer to pg_io for more information about the system.

See also

Postgres parsers and formatters

Fundamental PostgreSQL types

The fundamental PostgreSQL types support is provided by the driver. The table below shows supported PostgreSQL types and their mapping to C++ types provided by the driver. Column "Default" marks the PostgreSQL type to which a C++ type is mapped when used as a parameter. Where the C++ type is N/A it means that the PostgreSQL data type is not supported. When there is a C++ type in parenthesis, it is a data type that will be supported later and the C++ type is planned counterpart.

PG type	C++ type	Default
smallint	std::int16_t	+
integer	std::int32_t	+
bigint	std::int64_t	+
smallserial	std::int16_t
serial	std::int32_t
bigserial	std::int64_t
boolean	bool	+
real	float	+
double precision	double	+
numeric(p)	decimal64::Decimal	+
decimal(p)	decimal64::Decimal	+
money	N/A
text	std::string	+
char(n)	std::string
varchar(n)	std::string
"char"	char	+
timestamp	storages::postgres::TimePointWithoutTz	+
timestamptz	storages::postgres::TimePointTz	+
date	utils::datetime::Date	+
time	utils::datetime::TimeOfDay	+
timetz	N/A
interval	std::chrono::microseconds
bytea	container of one-byte type
bit(n)	utils::Flags
	std::bitset<N>
	std::array<bool, N>
bit varying(n)	utils::Flags
	std::bitset<N>
	std::array<bool, N>
uuid	boost::uuids::uuid	+
json	formats::json::Value
jsonb	formats::json::Value	+
int4range	storages::postgres::IntegerRange
	storages::postgres::BoundedIntegerRange
int8range	storages::postgres::BigintRange
	storages::postgres::BoundedBigintRange
inet	utils::ip::AddressV4
	utils::ip::AddressV6
cidr	utils::ip::NetworkV4
	utils::ip::NetworkV6
macaddr	utils::Macaddr
macaddr8	utils::Macaddr8
numrange	N/A
tsrange	N/A
tstzrange	N/A
daterange	N/A

Warning

The library doesn't provide support for C++ unsigned integral types intentionally as PostgreSQL doesn't provide unsigned types and using the types with the database is error-prone.

Timestamp Support aka TimePointTz and TimePointWithoutTz

The driver provides mapping from C++ std::chrono::time_point template type to Postgres timestamp (without time zone) data type.

To read/write timestamp with time zone Postgres data type a storages::postgres::TimePointTz helper type is provided.

PostgreSQL internal timestamp resolution is microseconds.

Example:

    namespace pg = storages::postgres;
 
    // Postgres only supports microsecond resolution
    const auto now = std::chrono::time_point_cast<std::chrono::microseconds>(std::chrono::system_clock::now());
 
    connection->Execute(R"(
    CREATE TABLE tz_sample(
      with_tz     TIMESTAMP WITH    TIME ZONE,
      without_tz  TIMESTAMP WITHOUT TIME ZONE
    )
  )");
 
    // No conversion performed
    constexpr auto kInsertQuery = R"(
    INSERT INTO tz_sample(with_tz, without_tz) VALUES($1, $2)
    RETURNING with_tz, without_tz
  )";
    const auto res = connection->Execute(kInsertQuery, pg::TimePointTz{now}, pg::TimePointWithoutTz{now});
 
    // Both values contain the correct time point
    EXPECT_EQ(res[0][0].As<pg::TimePointTz>().GetUnderlying(), now);
    EXPECT_EQ(res[0][1].As<pg::TimePointWithoutTz>().GetUnderlying(), now);

How not to get skewed times in the PostgreSQL

Postgres has two types corresponding to absolute time (a.k.a. global time; Unix time; NOT local time):

• TIMESTAMP WITH TIME ZONE
• TIMESTAMP

An unfortunate design decision on the PostgreSQL side is that it allows implicit conversion between them, and database applies an offset to the time point when doing so, depending on the timezone of the Postgres database.

Because of this you MUST ensure that you always use the correct type:

• storages::postgres::TimePointTz for TIMESTAMP WITH TIME ZONE;
• storages::postgres::TimePointWithoutTz for TIMESTAMP.

Otherwise, you'll get skewed times in database:

    namespace pg = storages::postgres;
    const auto tz_offset_res = connection->Execute("select extract(timezone from now())::integer");
    const auto tz_offset = std::chrono::seconds{tz_offset_res[0].As<int>()};
 
    const auto now = std::chrono::time_point_cast<std::chrono::microseconds>(std::chrono::system_clock::now());
    connection->Execute(R"(
    CREATE TABLE tz_conversion_sample(
      with_tz    TIMESTAMP WITH    TIME ZONE,
      without_tz TIMESTAMP WITHOUT TIME ZONE
    )
  )");
 
    constexpr auto kInsertQuery = R"(
    INSERT INTO tz_conversion_sample(with_tz, without_tz) VALUES($1, $2)
    RETURNING with_tz, without_tz
  )";
    // ERROR! Types mismatch and are implicitly converted:
    // * TimePointWithoutTz is converted on the DB side and TZ substracted.
    // * TimePointTz is converted on the DB side and TZ added.
    const auto res = connection->Execute(kInsertQuery, pg::TimePointWithoutTz{now}, pg::TimePointTz{now});
 
    // Both values were skewed
    using time_point = std::chrono::system_clock::time_point;
    EXPECT_EQ(res[0][0].As<time_point>() + tz_offset, now);
    EXPECT_EQ(res[0][1].As<time_point>() - tz_offset, now);

There is no way to detect that issue on the userver side, as the implicit conversion is performed by the database itself and it provides no information that the conversion happened.

Arrays in PostgreSQL

The driver supports PostgreSQL arrays provided that the element type is supported by the driver, including user types.

Array parser will throw storages::postgres::DimensionMismatch if the dimensions of C++ container do not match that of the buffer received from the server.

Array formatter will throw storages::postgres::InvalidDimensions if containers on same level of depth have different sizes.

User-defined PostgreSQL types

The driver provides support for user-defined PostgreSQL types:

• domains
• enumerations
• composite types
• custom ranges

For more information please see uPg: PostgreSQL user type mappings.

C++ strong typedefs in PostgreSQL

The driver provides support for C++ strong typedef idiom. For more information see uPg: support for C++ 'strong typedef' idiom

PostgreSQL ranges

PostgreSQL range type support is provided by storages::postgres::Range template.

Geometry types in PostgreSQL

For geometry types the driver provides parsing/formatting from/to on-the-wire representation. The types provided do not define any calculus.

PostgreSQL bytea support

The driver allows reading and writing raw binary data from/to PostgreSQL bytea type.

Reading and writing to PostgreSQL is implemented for std::string, std::string_view and std::vector of char or unsigned char.

Warning

When reading to std::string_view the value MUST NOT be used after the PostgreSQL result set is destroyed.

Bytea() is a helper function for reading and writing binary data from/to a database.

Example usage of Bytea():

        pg::ResultSet res = GetConn()->Execute("select 'foobar'::bytea");
        std::string s = "foobar"s;
 
        // reading a binary string
        std::string received;
        res[0][0].To(pg::Bytea(received));
        EXPECT_EQ(received, s);

        // sending a binary string
        std::string s = "\0\xff\x0afoobar"s;
        pg::ResultSet res = GetConn()->Execute("select $1", pg::Bytea(s));
 
        // reading a binary string
        std::string received;
        res[0][0].To(pg::Bytea(received));
        EXPECT_EQ(received, s);

        // storing a byte vector:
        std::vector<std::uint8_t> bin_str{1, 2, 3, 4, 5, 6, 7, 8, 9};
        auto res = GetConn()->Execute("select $1", pg::Bytea(bin_str));
 
        // reading a byte vector
        std::vector<std::uint8_t> received;
        res[0][0].To(pg::Bytea(received));
        EXPECT_EQ(received, bin_str);

Network types in PostgreSQL

The driver offers data types to store IPv4, IPv6, and MAC addresses, as well as network specifications (CIDR).

Bit string types in PostgreSQL

The driver supports PostgreSQL bit and bit varying types.

Parsing and formatting is implemented for integral values (e.g. uint32_t, uint64_t), utils::Flags, std::array<bool, N> and std::bitset<N>.

Example of using the bit types from tests:

    res = conn->Execute("select 25::bit(8)");
    auto bits = res[0][0].As<std::bitset<8>>();
    EXPECT_EQ(bits, 0b11001);
 
    res = conn->Execute("select $1::text", bits);
    EXPECT_EQ(res[0][0].As<std::string>(), "00011001");
 
    std::uint8_t num{};
    res = conn->Execute("select 42::bit(8)");
    res[0][0].To(pg::Bit(num));
    EXPECT_EQ(num, 42);

PostgreSQL types not covered above

The types not covered above or marked as N/A in the table of fundamental types will be eventually supported later, on request from the driver's users.

---

⇦ uPg: Working with result sets | uPg: Typed PostgreSQL results ⇨