Izenda Data Types

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

Name Description ISO Microsoft SQL Server MySQL Oracle PostgreSQL AWS Redshift
Numeric Numbers, including negative values, integers or real numbers (having decimal points). integer, dec, float, double precision tinyint, smallint, int, bigint, decimal, float, real tinyint, smallint, mediumint, int, integer, bigint, decimal, numeric, fixed, float, double, double precision, real number, binary_float, binary_double, binary_integer smallint, bigint, integer, double precision, real, numeric numeric, int, smallint, integer, real, int2, int4, int8, bigint, float4, float8, double precision, smallserial, serial, bigserial
Text Text, maximum 4,000 chars. character, char varying, character varying, national char, national character, national char varying, nation character varying, nation text char, varchar, nchar, nvarchar, text, ntext char, varchar char, varchar2, nchar, nvarchar2 character, char, character varying, varchar, text character varying, character, bpchar, varchar, nvarchar, text, uuid, bit, bit varying, cidr, inet, macaddr, interval
Datetime Date combined with time of day. timestamp, date smaldatetime, datetime, date, datetime2 date, datetime, timestamp date, timestamp date, timestamp, timestamp without time zone, timestamp with time zone timestamp,timestamp with time zone, datetime, date, timestamp without time zone
Money Currency amount. numeric money, smallmoney N/A N/A money N/A
Boolean TRUE and FALSE values. boolean bit bool, boolean, tinyint(1) number(1) boolean boolean
Image The binary format of an image. N/A image N/A N/A N/A N/A
Lob Binary data. binary large object text, ntext, binary, varbinary binary, varbinary, tinyblob, blob, mediumblob, longblob, tinytext, text, mediumtext, longtext clob, nlob, long, blob, bfile, raw, long raw bytea bytea
Time Time data only (hour, minute, and second values) Time Time Time N/A time, time without time zone, time with time zone time with time zone, time without time zone, time
XML Extensible Markup Language (XML) data type N/A XML N/A XMLType XML XML
JSON JavaScript Object Notation (JSON) data type N/A XML JSON N/A JSON JSON

Default Data Type Mapping

Name Description ISO Microsoft SQL Server MySQL Oracle PostgreSQL AWS Redshift
Numeric Numbers, including negative values, integers or real numbers (having decimal points). dec decimal decimal number numeric numeric
Text Text, maximum 4,000 chars. nation character varying nvarchar varchar nvarchar2 character varying character varying
Datetime Date combined with time of day. timestamp datetime2 datetime date timestamp timestamp
Money Currency amount. numeric money decimal number money numeric
Boolean TRUE and FALSE values. boolean bit tinyint(1) number(1) boolean boolean
Image The binary format of an image. binary large object image longblob blob bytea bytea
Lob Binary data. binary large object varbinary longblob blob bytea bytea
Time Time data only (hour, minute, and second values) Time Time Time N/A time with time zone time with time zone
XML Extensible Markup Language (XML) data type N/A XML N/A XMLType XML XML
JSON JavaScript Object Notation (JSON) data type N/A nvarchar (JSON functions will be supported) JSON N/A JSON JSON

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)