Comparison of database column types in MySQL, PostgreSQL, and SQLite? (Cross-Mapping)
List of things I'd do differently:
MEDIUMINT in MySQL is an odd duck (3 bytes). I would avoid it, but otherwise map it to INTEGER too.
The MySQL BOOLEAN (alias BOOL, alias TINYINT(1) ) is not compatible with the pg boolean type. You may or may not be able to port apps depending on what they use as boolean literals. In MySQL, TRUE and FALSE map to 1 and 0 integer values. It looks like the pg BOOLEAN type uses string literal notation. So apps may or may not be portable - at least it is no drop in replacement.
Finally, for the last line in your tabl I think the SQLite phrase should read:
INTEGER PRIMARY KEY AUTOINCREMENT
This is roughly equivalent to
BIGINT PRIMARY KEY AUTO_INCREMENT
in MySQL. In postgres, the SERIAL datatype results in an INTEGER column, and this will about the same as MySQL's
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 I missed:
I am missing INTEGER (alias INT) for MySQL. It is comparable to INTEGER in pg. 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 of a little less than 64kb, in pg 1Gb (bytes). The actual length specifier is expressed in the number of characters, so if you have a multi-byte character set, you have to divide the maximum lenght by the maximum number of characters to get the theoretical maximum length specified for that characterset. In SQLite, VARCHAR and CHAR map both to TEXT
The BIT datatypes 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 is best comparable to PG's BYTEA datatype. (but indeed MySQL's BLOB types also map to that)
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 arbtrary limit on the precision, whereas in MySQL the maximum precision is (i believe) 70. (that is, 70 number positions) For both MySQL and Postgres, NUMERIC is an alias for the DECIMAL type.
Xeoncross
PHP, Javascript, and Go Application developer responsible for over 50 open source projects and libraries at https://github.com/xeoncross By default I build Go backends with AngularJS frontends. Thanks to Ionic and Electron this even works for mobile and desktop apps. Bash, PHP, Python, Node.js, and random linux libraries are used for specific tasks because of the size of the ecosystems or libraries for odd jobs.
Updated on July 08, 2022Comments
-
Xeoncross almost 2 years
I am trying to find some way to relate column types across the the most used Databases: MySQL, PostgreSQL, and SQLite.
Here is what I have so far, but I'm afraid it's not done and I need some people with more 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
-
Evan Carroll over 14 yearsthere is another difference, in Pg you only use varchar() when you have a reasonable reason to invoke the constraint it provides. In MySQL you do it to have a large internally inlined blob of text that runs quickly. In Pg it runs slower, and takes up my room. In Pg you would almost never use varchar().
-
Roland Bouman over 14 yearsEvanCarrol, that's interesting. So what should one use in pg to store fairly small bits of text like person names, product names, short (say, less than 255) descriptions? just TEXT?
-
user340140 over 10 yearspostgres booleans are not string literal types, they only look that way. if you cast them to integer the come out o,1, or NULL as apropriate, conversely if you have 0,1 or NULL integers you can cast them to boolean. COPY...FROM accepts numbers but INSERT needs an explicit cast or quotes around the number. so '0', cast ( 0 as boolen),'f', and false will all work in an insert expecting boolean.