2024-08-24
SQL Types
SQL Types
The table of data types in SQL. DO NOT trust the contents. The primary focus was to list up common types for parsing. Details are untested and inaccurate. Check the official documents before implementation.
SQLite PostgreSQL MySQL Oracle MSSQL
INTEGER* 4 Bytes 4 Bytes 4 Bytes NUMBER(38) INT
INT INTEGER INTEGER INTEGER NUMBER(38) 4 Bytes
TINYINT - - 1 Byte - -
SMALLINT* INTEGER 2 Bytes 2 Bytes NUMBER(38) 2 Bytes
MEDIUMINT - 3 Bytes - -
BIGINT* INTEGER 8 Bytes 8 Bytes - 8 Bytes
UNSIGNED BIG INT INTEGER - - - -
INT1 - - TINYINT - -
INT2 INTEGER SMALLINT SMALLINT - -
INT3 - - MEDIUMINT - -
INT4 - INTEGER INT - -
INT8 INTEGER BIGINT BIGINT - -
SMALLSERIAL - 2 Bytes - - -
SERIAL - 4 Bytes - - -
BIGSERIAL - 8 Bytes - - -
NUMERIC* TEXT ? Bytes DECIMAL NUMBER DECIMAL
DECIMAL* TEXT NUMERIC ? Bytes NUMBER ? Bytes
DEC - - DECIMAL - DECIMAL
FIXED - - DECIMAL - DECIMAL
NUMBER - - - X Bytes -
REAL* REAL 4 Bytes DBL/FLT FLOAT(63) 4 Bytes
FLOAT REAL - ? Bytes FLOAT(126) ? Bytes
FLOAT2 REAL - - - -
FLOAT4 REAL - - - -
FLOAT8 REAL - - - -
DOUBLE PRECISION* REAL 8 Bytes DOUBLE FLOAT(126) FLOAT(53)
DOUBLE REAL - 8 Bytes - -
BINARY_FLOAT - - - 4 Bytes -
BINARY_DOUBLE - - - 8 Bytes -
BOOLEAN* INTEGER 1 Byte TINYINT 1 Byte -
BOOL - BOOLEAN TINYINT BOOLEAN -
BIT* - ? Bytes ? Bytes - ? Bytes
BIT VARYING* - ? Bytes - - -
TEXT TEXT ? Bytes ? Bytes - -
CHARACTER* TEXT ? Bytes - - -
CHAR* TEXT ? Bytes ? Bytes ? Bytes ? Bytes
VARCHAR* TEXT ? Bytes ? Bytes VARCHAR2 ? Bytes
CHARACTER VARYING* TEXT ? Bytes - - -
VARYING CHARACTER TEXT - - - -
NCHAR TEXT - - ? Bytes ? Bytes
NATIVE CHARACTER TEXT - - - -
NVARCHAR TEXT - - - ? Bytes
NTEXT TEXT - - - ? Bytes
VARCHAR2 TEXT - - ? Bytes -
NVARCHAR2 TEXT - - ? Bytes -
BPCHAR TEXT ? Bytes - - -
LONG - - - ? Bytes -
ENUM - - ? Bytes - -
SET - - ? Bytes - -
BLOB* TEXT BYTEA ? Bytes ? Bytes -
CLOB TEXT - - ? Bytes -
NCLOB TEXT - - ? Bytes -
BINARY - - ? Bytes - ? Bytes
VARBINARY - - ? Bytes - ? Bytes
BYTEA - ? Bytes - - -
RAW - - - ? Bytes -
IMAGE - - - - ? Bytes
DATE TEXT 4 Bytes ? Bytes ? Bytes ? Bytes
DATETIME TEXT - ? Bytes - ? Bytes
TIME - 4/8 Bytes ? Bytes - ? Bytes
TIMESTAMP - 8 Bytes ? Bytes ? Bytes -
INTERVAL - 16 Bytes - - -
YEAR - - ? Bytes ? Bytes -
DATETIME2 - - - - ? Bytes
DATETIMEOFFSET - - - - ? Bytes
SMALLDATETIME - - - - ? Bytes
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIMEZONE
INTERVAL <FIELD>
SQLite
NULL
INTEGER 8 bytes
REAL 8 bytes
TEXT
BLOB
PostgreSQL Types
--- Integers
SMALLINT 2
INTEGER 4
BIGINT 8
SMALLSERIAL
SERIAL
BIGSERIAL
--- Decimals
DECIMAL [(p[,s])]
NUMERIC [(p[,s])]
REAL
DOUBLE PRECISION
--- Characters
VARCHAR(n)
CHARACTERVARYING(n)
CHAR(n)
CHARACTER(n)
BPCHAR(n)
TEXT
--- Blob
BYTEA
BLOB
BINARY LARGE OBJECT
--- Boolean
BOOLEAN
BOOL
--- Bit
BIT(n)
BIT VARYING(n)
--- DateTime
DATE
TIMESTAMP [(p)] [WITHOUT TIME ZONE]
TIMESTAMP [(p)] WITH TIME ZONE
TIME [(p)] [WITHOUT TIME ZONE]
TIME [(p)] WITH TIME ZONE
INTERAL [<fields>] [(p)]
<fields> := YEAR, MONTH, DAY
HOUR, MINUTE, SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
--- Geometric
POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE
--- Network
CIDR, INET, MACADDR, MACADDR8
--- TextSearch
TSVECTOR
TSQUERY
--- UUID
UUID
--- Serialize
XML
JSON
JSONB
--- Arrays
TYPE[]
TYPE[][]...
TYPE[n]
TYPE[n][n]...
--- Enums and Structs
TYPE XXX AS ENUM ('...', '...)
TYPE XXX AS (field TYPE, field TYPE)
- https://www.sqlite.org/datatype3.html
- https://www.postgresql.jp/docs/9.4/datatype.html
- https://dev.mysql.com/doc/refman/8.0/ja/data-types.html
- https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Data-Types.html
- https://learn.microsoft.com/en-us/sql/t-sql/data-types/numeric-types?view=sql-server-ver16
- https://www.pgecons.org/wp-content/uploads/PGECons/2012/WG2/04_SchemaMigrationResearch/04_Appendix_01_Built-inDataTypeComparativeTable(Oracle-PostgreSQL).pdf
- https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems