Datatypes
The classes
RWDBColumn and
RWDBValue have an associated
RWDBValue::ValueType that specifies the datatype of the database column or field being referenced. Mapping between this datatype and the database vendor type runs in both directions:
• When fetching data or retrieving information about tables, the DB Access Module for PostgreSQL converts the PostgreSQL specific datatype information provided by the database into RWDBValue::ValueTypes. This mapping is shown in Table 1, which also gives the C++ datatype corresponding to the RWDBValue::ValueType.
• When creating tables, the DB Access Module for PostgreSQL maps the given RWDBValue::ValueType into a PostgreSQL specific type. This mapping is shown in Table 2.
The
RWDBValue::ValueTypes, accessible through the
type() method, are uniform across all Access Modules. For a complete explanation of the DB Interface Module data model, see
Chapter 7, “The Data Model,” in the
DB Interface Module User’s Guide, and
RWDBValue in the
SourcePro API Reference Guide.
Table 1 – Type mapping when retrieving data
PostgreSQL Datatype | RWDBValue:: ValueType | C++ Datatype |
bigint | LongLong | long long |
bigserial | LongLong | long long |
bit bit varying(n) | String | |
boolean | String | |
box | String | |
bytea | Blob | |
char(n) | String | |
varchar(n) | String | |
cidr | String | |
circle | String | |
date | Date | |
double precision | Double | double |
inet | String | |
integer | Long | long int |
interval(p) | Duration | |
line | String | |
lseg | String | |
macaddr | String | |
money | String | |
numeric | Decimal | |
path | String | |
point | String | |
polygon | String | |
real | Float | float |
smallint | Short | short int |
serial | Long | long int |
text | String | |
time(p) | DateTime | |
time(p) with time zone1 | DateTime | |
timestamp(p)1 | DateTime | |
timestamp(p) with time zone1 | DateTime | |
Table 2 – Type mapping when creating tables
RWDBValue::ValueType | PostgreSQL Datatype |
Blob | bytea |
Char UnsignedChar Tiny UnsignedTiny Short UnsignedShort | smallint |
Date | date |
DateTime | timestamp[(p)] using precision of column if specified |
Decimal | numeric(p,s) using precision and scale of column if specified |
Duration | interval[(p)] using precision of column if specified |
Double | double precision |
Float | real |
Int UnsignedInt | integer |
Long UnsignedLong | integer |
LongLong UnsignedLongLong | bigint |
String | varchar(n) if storageLength is specified, else text |
Restrictions on the Use of Datatypes
The PostgreSQL database places restrictions on the use of certain datatypes. Where possible, the DB Interface Module is flexible about datatypes. However, there are some situations where conversions are not possible. In these cases your applications must be aware of the contexts in which certain datatypes can be used.
Table 3 outlines the known restrictions associated with each DB Interface Module type.
Table 3 – Restrictions on the use of DB Interface Module datatypes
RWDBValue::ValueType | Restrictions |
Blob | No restrictions |
Char UnsignedChar | No restrictions |
Date | Only the PostgreSQL DateStyle ISO, MDY is supported. |
DateTime | Accuracy provided by PostgreSQL is microseconds, but RWDateTime is limited to milliseconds. Only the PostgreSQL DateStyle ISO 8601 format in MDY mode is supported. |
TimeTuple | Only the PostgreSQL DateStyle ISO 8601 format in MDY mode is supported. |
TimeTupleOffset | This datatype is not supported. |
Decimal | No restrictions |
Double | No restrictions |
Duration | No restrictions |
Float | No restrictions |
Int UnsignedInt | No restrictions |
Long UnsignedLong | No restrictions |
MBString | This datatype is not supported. |
LongLong UnsignedLongLong | No restrictions |
LongDouble | No restrictions |
String | No restrictions |
Short UnsignedShort | No restrictions |
WString | This datatype is not supported. |
UString | This datatype is not supported. |
Serial Columns in Tables
If a table contains a SERIAL column, PostgreSQL gives you two options:
• You may explicitly specify a value for the column.
• You may automatically assign a value to the column when a row is inserted.
Therefore, to let PostgreSQL assign the value, you must skip that column when using an
RWDBInserter. Please see the method
RWDBInserter::operator[] in the
DB Interface Module Class Reference.
When reading a table that contains one or more serial columns, serial columns are treated as regular columns.