Was trying to migrate a database from MySQL to PostgreSQL recently when I “discovered” that UNSIGNED is not supported in PostgreSQL, as it is not part of the ANSI SQL standard which it follows closely. Other findings were that non-aggregate columns are not allowed in queries with GROUP BY clauses (which MySQL/SQLite “allows” by selecting the value from a random record in each group), and that the auto-increment of the primary key goes out of sync if any record is inserted with the value for the primary key specified (see this article on how to manually re-sync it).
BIGINT has a range of -9223372036854775808 to 9223372036854775807, while BIGINT UNSIGNED has a range of 0 to 18446744073709551615 (supported in MySQL/SQLite but not in PostgreSQL). This got me wondering if programming languages can even support such large numbers out of the box, without use of special third party libraries.
Did up code samples for JavaScript (applies to Node.js as well), PHP and Python to test the following:
- Set variable to integer 9223372036854775807 (2^63 – 1)
- Cast string to integer
- Integer arithmetic
- Set variable to float using UNIX timestamp with microsecond precision in decimals, set beyond year 2038
- Cast string to float
- Float arithmetic
- Convert textual representation of date (beyond year 2038) to Date object
- Convert UNIX timestamp with microsecond precision in decimals (beyond year 2038) to Date object
JavaScript / Node.js:
Failed integer tests. Note that the PostgreSQL driver for Node.js returns strings for BIGINT datatype and while integers can be returned by overriding the default type parser, values larger than 9007199254740991 (1000x smaller than BIGINT max value) will still cause parseInt() to fail.
let sprintf = function (format) {
let result = format;
for (let i = 1; i < arguments.length; i++) {
result = result.replace('%s', arguments[i]);
}
return result;
};
// Smaller range may cause issues for large database record IDs or values
let maxInt = Number.MAX_SAFE_INTEGER; // 2^53 - 1
let intValue = 9223372036854775807; // 2^63 - 1
let intStr = '9223372036854775807';
let intStrAsInt = parseInt(intStr);
let intSum = 9223372036854775806 + 1;
console.log(sprintf("Integer Test:\n%s\n%s\n%s\n%s\n\n", maxInt, intValue, intStrAsInt, intSum));
// Integer Test:
// 9007199254740991
// 9223372036854776000
// 9223372036854776000
// 9223372036854776000
let isoDate = '2040-01-02T03:04:05.678901Z';
let floatValue = 2209086245.678901; // UNIX timestamp for ISO date
let floatStr = '2209086245.678901';
let floatStrAsFloat = parseFloat(floatStr);
let floatSum = 2209086244.677900 + 1.001001;
console.log(sprintf("Float Test:\n%s\n%s\n%s\n\n", floatValue, floatStrAsFloat, floatSum));
// Float Test:
// 2209086245.678901
// 2209086245.678901
// 2209086245.6789007
let dateFromIsoDate = new Date(isoDate);
let dateFromEpoch = new Date(floatValue * 1000);
console.log(sprintf(
"Date Test:\n%s\n%s\n%s\n%s\n%s\n%s\n\n",
isoDate,
floatValue,
dateFromIsoDate.toISOString(),
(dateFromIsoDate.getTime() / 1000),
dateFromEpoch.toISOString(),
(dateFromEpoch.getTime() / 1000),
));
// Date Test:
// 2040-01-02T03:04:05.678901Z
// 2209086245.678901
// 2040-01-02T03:04:05.678Z
// 2209086245.678
// 2040-01-02T03:04:05.678Z
// 2209086245.678
PHP:
<?php
$maxInt = PHP_INT_MAX; // 2^63 - 1 on 64-bit systems
$intValue = 9223372036854775807; // 2^63 - 1
$intStr = '9223372036854775807';
$intStrAsInt = intval($intStr);
$intSum = 9223372036854775806 + 1;
printf("Integer Test:\n%d\n%d\n%d\n%d\n\n", $maxInt, $intValue, $intStrAsInt, $intSum);
// Integer Test:
// 9223372036854775807
// 9223372036854775807
// 9223372036854775807
// 9223372036854775807
// Only up to 4 decimal places shown if %s format specifier is used
// Prints 2209086245.6789007 if %.7f format specifier is used
$isoDate = '2040-01-02T03:04:05.678901Z';
$floatValue = 2209086245.678901; // UNIX timestamp for ISO date
$floatStr = '2209086245.678901';
$floatStrAsFloat = floatval($floatStr);
$floatSum = 2209086244.677900 + 1.001001;
printf("Float Test:\n%f\n%f\n%f\n\n", $floatValue, $floatStrAsFloat, $floatSum);
// Float Test:
// 2209086245.678901
// 2209086245.678901
// 2209086245.678901
$dateFromIsoDate = new DateTimeImmutable($isoDate);
$dateFromEpoch = DateTimeImmutable::createFromFormat('U.u', $floatValue);
$format = 'Y-m-d\TH:i:s.up';
printf(
"Date Test:\n%s\n%.6f\n%s\n%s\n%s\n%s\n\n",
$isoDate,
$floatValue,
$dateFromIsoDate->format($format),
$dateFromIsoDate->format('U.u'),
$dateFromEpoch->format($format),
$dateFromEpoch->format('U.u'),
);
// Date Test:
// 2040-01-02T03:04:05.678901Z
// 2209086245.678901
// 2040-01-02T03:04:05.678901Z
// 2209086245.678901
// 2040-01-02T03:04:05.678900+00:00
// 2209086245.678900
Python:
from datetime import datetime
import sys
# No more sys.maxint in Python 3 as there is no longer a limit to the value of integers
max_int = sys.maxsize; # 2^63 - 1
int_value = 9223372036854775807; # 2^63 - 1
int_str = '9223372036854775807';
int_str_as_int = int(int_str);
int_sum = 9223372036854775806 + 1;
print("Integer Test:\n{0}\n{1}\n{2}\n{3}\n\n".format(max_int, int_value, int_str_as_int, int_sum));
# Integer Test:
# 9223372036854775807
# 9223372036854775807
# 9223372036854775807
# 9223372036854775807
iso_date = '2040-01-02T03:04:05.678901Z';
float_value = 2209086245.678901; # UNIX timestamp for ISO date
float_str = '2209086245.678901';
float_str_as_float = float(float_str);
float_sum = 2209086244.677900 + 1.001001;
print("Float Test:\n{0}\n{1}\n{2}\n\n".format(float_value, float_str_as_float, float_sum));
# Float Test:
# 2209086245.678901
# 2209086245.678901
# 2209086245.6789007
date_from_iso_date = datetime.fromisoformat(iso_date.replace('Z', '+00:00')); # does not work with Z hence replace
date_from_epoch = datetime.fromtimestamp(float_value);
print("Date Test:\n{0}\n{1}\n{2}\n{3}\n{4}\n{5}\n\n".format(
iso_date,
float_value,
date_from_iso_date.isoformat(),
date_from_iso_date.timestamp(),
date_from_epoch.isoformat(),
date_from_epoch.timestamp(),
));
# Date Test:
# 2040-01-02T03:04:05.678901Z
# 2209086245.678901
# 2040-01-02T03:04:05.678901+00:00
# 2209086245.678901
# 2040-01-02T11:04:05.678901
# 2209086245.678901