4. Types Mappping

This is a quick guide to show how the PostgreSQL types are mapped into dbExpress types by the pgExpress Driver:

Table 2.1. 

oidTIntegerFieldAs of pgExpress 4.30, OID fields will always be mapped and int4 to avoid confusion with Large Objects
int8TLargeIntField/TStringField/TIntField The dbExpress technology does not have native support for 64 bit integers; however, by using a hack, we provide native Int8 support. Please note the TIndexDef issues associated with native Int8 usage in the link here. This mapping is controlled by the Int8Mode Special Param.
charTStringFieldFixed length. The same UNICODE/WideChar notes from varchar type apply.

TStringField.Size will be set to the maximum length. It's not adviced to set a maximum value too long in the table definition to keep the memory requirements low in the dbExpress row buffers. See here for details. If UnicodeAsWideChar is set to True, and the client encoding is UNICODE, the fields will be mapped as TWideStringField.

If you want/need large values, try using the TEXT field type which maps into a BLOB/Memo (TMemoField) field. This field reads up to to 32kb (dbExpress limitation).

varchar fields with no size specifier is identical to a text field and will be mapped in pgExpress just as a text field.

textTBLOBField/MemoCheck our FAQ for more info on this. Text fields mapped as Memo will be faster and more efficient in terms of memory. You can also map them as strings (padded at 32Kb due to dbExpress limitation); check here.
XMLTBLOBField/MemoSee notes for text fields.
JSONTBLOBField/MemoNew type from PostgreSQL v9.2.
numericTFMTBCDFieldVCL/CLX TFMTBCDFields support up to 32 digits. If you have a field with more then that, it will get mapped as TStringField.Note: TFMTBCDField is used instead of TBCDField because it allows greater precision.
timeTTimeFieldSee note below. The time field type support fractions.
timetzTTimeFieldTimezones and second fractions aren't supported by VCL/CLX and they can give your trouble when used in a WHERE clause on INSERT/UPDATE/DELETE queries. Please refer to our FAQ for more info.
dateTDateFieldTimezones are not supported by VCL/CLX
timestampTTimeStampFieldSee note from fractions on the timetz field.
timestamptzTSQLTimeStampFieldSee notes from timezones and fractions on the timetz field.
nameTStringFieldFixed length; name is a field type used internally by PostgreSQL.
bpcharTStringFieldFixed length.
_bpcharTStringFieldFixed length.
float4TFloatFieldActually TFloatField can support more precision and scale then float4 fields and are recommended. You can teorically have a overflow or underflow if someone tries to insert a bad value.
abstime/reltimeTTimeFieldSee note for time fields.
interval/tintervalTStringFieldSince there is no interval type in VCL, we have to map these types as strings.
byteaTVarBytesField, Blob/Graphic or Blob/binary See ByteaMode Special Param. TVarBytesFieldReads up to 32kb (dbExpress limitation).
moneyTCurrencyField The money field is obsolete. The PostgreSQL documentation advices to use numeric or float8 fields instead.
loTBlobField/Binary Following the PostgreSQL ODBC driver convention, the 'lo' field represents Large Objects. Consider using Bytea fields as BLOBs instead.
OtherTStringFieldUnknown fields are mapped by default as Strings. You can turn this off (and thus don't use the fields at all) by toggling the MapUnknownAsString special param.