Comparing database column types in MySQL, PostgreSQL and SQLite? (Cross Card)

Question

I am trying to find a way to bind column types through the most used databases: MySQL , PostgreSQL and SQLite .

This is what I have so far, but I am afraid that this has not been done, and I need people with great experience to help me finish any missing types.

MySQL PostgreSQL SQLite TINYINT SMALLINT INTEGER SMALLINT SMALLINT MEDIUMINT INTEGER BIGINT BIGINT BIT BIT INTEGER _______________________________________________________ TINYINT UNSIGNED SMALLINT INTEGER SMALLINT UNSIGNED INTEGER MEDIUMINT UNSIGNED INTEGER INT UNSIGNED BIGINT BIGINT UNSIGNED NUMERIC(20) _______________________________________________________ DOUBLE DOUBLE PRECISION REAL FLOAT REAL REAL DECIMAL DECIMAL REAL NUMERIC NUMERIC REAL _______________________________________________________ BOOLEAN BOOLEAN INTEGER _______________________________________________________ DATE DATE TEXT TIME TIME DATETIME TIMESTAMP _______________________________________________________ TIMESTAMP DEFAULT TIMESTAMP DEFAULT TEXT NOW() NOW() _______________________________________________________ LONGTEXT TEXT TEXT MEDIUMTEXT TEXT TEXT BLOB BYTEA BLOB VARCHAR VARCHAR TEXT CHAR CHAR TEXT _______________________________________________________ columnname INT columnname SERIAL INTEGER PRIMARY AUTO_INCREMENT KEY AUTOINCREMENT 

Answer 1

List of things I would do differently:

MySQL MEDIUMINT is an odd duck (3 bytes). I would avoid this, but otherwise also match it with INTEGER.

MySQL BOOLEAN (alias BOOL, alias TINYINT (1)) is not compatible with the pg boolean type. You may or may not be able to port applications, depending on what they use as logical literals. In MySQL, TRUE and FALSE display 1 and 0 integer values. It looks like the pg type BOOLEAN uses a string literal. Thus, applications may or may not be portable - at least this is not a replacement.

Finally, for the last line in your tabl, I think the SQLite phrase should read:

 INTEGER PRIMARY KEY AUTOINCREMENT 

This is roughly equivalent

 BIGINT PRIMARY KEY AUTO_INCREMENT 

in MySQL. In postgres, the SERIAL data type results in an INTEGER column, and it will be about the same as MySQL

 INTEGER PRIMARY KEY AUTO_INCREMENT 

Postgres also has a BIGSERIAL type, which is the same as SERIAL, but with a BIGINT type instead of an INT type.

What did I miss:

I miss INTEGER (alias INT) for MySQL. This compares to INTEGER on page. Very important omissions: VARCHAR and CHAR. Semantically, VARCHAR in MySQL and PG and CHAR in MySQL and PG are the same, but in MySQL these types have a much shorter maximum length. In MySQL, these types can have a maximum slightly less than 64kb, in pg 1Gb (bytes). The actual length specifier is expressed in number of characters, so if you have a multibyte character set, you must divide the maximum length by the maximum number of characters to get the theoretical maximum length specified for that character set. In SQLite, VARCHAR, and CHAR, map both values ​​to TEXT

The BIT data types in MySQL and PG have roughly the same semantics, but in MySQL, the maximum length of the BIT data type is 64 (bits)

I think the MySQL VARBINARY data type maps best to the PG BYTEA PG data type. (but actually MySQL BLOB types are also mapped to this)

The FLOAT type in MySQL should be equivalent to REAL in postgres (and REAL in SQLite too) The DECIMAL type in MySQL is equivalent to DECIMAL in postgres, except that in postgres the type does not impose an arbitrary limit of accuracy, whereas in MySQL the maximum precision (I believe) 70. (i.e. 70 position positions) For MySQL and Postgres, NUMERIC is an alias for the DECIMAL type.