Skip to main content

Izenda Data Types

Izenda Data Types

All DBMS (Database Management System) enforce classifying data into data types, for data integrity and performance (disk reading and writing). However, each DBMS uses a data type terminology different from standard SQL, and even the standard terminology may be too technical for end-users to understand.

For example: the case with Boolean data types.

Izenda presents end-users with a generic data type system using familiar names.

List of Data Types

NameDescriptionISOMicrosoft SQL ServerMySQLOraclePostgreSQLAWS Redshift
NumericNumbers, including negative values, integers or real numbers (having decimal points).integer, dec, float, double precisiontinyint, smallint, int, bigint, decimal, float, realtinyint, smallint, mediumint, int, integer, bigint, decimal, numeric, fixed, float, double, double precision, realnumber, binary_float, binary_double, binary_integersmallint, bigint, integer, double precision, real, numericnumeric, int, smallint, integer, real, int2, int4, int8, bigint, float4, float8, double precision, smallserial, serial, bigserial
TextText, maximum 4,000 chars.character, char varying, character varying, national char, national character, national char varying, nation character varying, nation textchar, varchar, nchar, nvarchar, text, ntextchar, varcharchar, varchar2, nchar, nvarchar2character, char, character varying, varchar, textcharacter varying, character, bpchar, varchar, nvarchar, text, uuid, bit, bit varying, cidr, inet, macaddr, interval
DatetimeDate combined with time of day.timestamp, datesmaldatetime, datetime, date, datetime2date, datetime, timestampdate, timestampdate, timestamp, timestamp without time zone, timestamp with time zonetimestamp,timestamp with time zone, datetime, date, timestamp without time zone
MoneyCurrency amount.numericmoney, smallmoneyN/AN/AmoneyN/A
BooleanTRUE and FALSE values.booleanbitbool, boolean, tinyint(1)number(1)booleanboolean
ImageThe binary format of an image.N/AimageN/AN/AN/AN/A
LobBinary data.binary large objecttext, ntext, binary, varbinarybinary, varbinary, tinyblob, blob, mediumblob, longblob, tinytext, text, mediumtext, longtextclob, nlob, long, blob, bfile, raw, long rawbyteabytea
TimeTime data only (hour, minute, and second values)TimeTimeTimeN/Atime, time without time zone, time with time zonetime with time zone, time without time zone, time
XMLExtensible Markup Language (XML) data typeN/AXMLN/AXMLTypeXMLXML
JSONJavaScript Object Notation (JSON) data typeN/AXMLJSONN/AJSONJSON

Default Data Type Mapping

NameDescriptionISOMicrosoft SQL ServerMySQLOraclePostgreSQLAWS Redshift
NumericNumbers, including negative values, integers or real numbers (having decimal points).decdecimaldecimalnumbernumericnumeric
TextText, maximum 4,000 chars.nation character varyingnvarcharvarcharnvarchar2character varyingcharacter varying
DatetimeDate combined with time of day.timestampdatetime2datetimedatetimestamptimestamp
MoneyCurrency amount.numericmoneydecimalnumbermoneynumeric
BooleanTRUE and FALSE values.booleanbittinyint(1)number(1)booleanboolean
ImageThe binary format of an image.binary large objectimagelongblobblobbyteabytea
LobBinary data.binary large objectvarbinarylongblobblobbyteabytea
TimeTime data only (hour, minute, and second values)TimeTimeTimeN/Atime with time zonetime with time zone
XMLExtensible Markup Language (XML) data typeN/AXMLN/AXMLTypeXMLXML
JSONJavaScript Object Notation (JSON) data typeN/Anvarchar (JSON functions will be supported)JSONN/AJSONJSON

Data Type Precedence

When an operator combines two expressions of different data types, the expression of the lower precedence data type is converted to the higher precedence data type. If the conversion is not available, an error is returned.

  1. Datetime (highest)
  2. Numeric
  3. Money
  4. Boolean
  5. Text
  6. Image
  7. Lob
  8. Time
  9. XML
  10. JSON (lowest)

Related articles

Comments

0 comments

Please sign in to leave a comment.