If I want to take a string and create a table (this fails in strict mode),
CREATE TABLE g
AS SELECT CAST('2147483699' AS int);
How do I tell it that I want a bigint type? I'll also get an int(10)
type. SELECT
ing from it is humorous too,
+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
| 2147483647 |
+---------------------------+
The data is actually wrong, and it silently failed. I know that's a known issue with this database having really poor defaults, but I how can I create the table as a bigint?
CREATE TABLE g
AS SELECT CAST('2147483699' AS bigint);
Doing that I get this error,
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'bigint)' at line 2
How do I cast an interger to a bigint?
PostgreSQL
With PostgreSQL, it's pretty simple. In addition to throwing an exception if you cast to an int
, you cast to a bigint
using
SELECT CAST('2147483699' AS bigint);
int8
------------
2147483699
CREATE TABLE g AS SELECT cast(lpad('2147483699', 20, '0') AS unsigned);
. That gives me abigint(20)
fori
ing
on a MySQL 5.7. But I don't know if it's any good for MariaDB.