I have 2 issues with Vertica's INT implementation and the Most Negative possible value.
For all other databases I have tested the most negative value for a BIGINT/INT(8) column appears to be -9223372036854775808, e.g. postgres
yadamu=# create table BIGINT_TEST (MAX_VALUE BIGINT, MIN_VALUE BIGINT);
CREATE TABLE
yadamu=# \d BIGINT_TEST
Table "public.bigint_test"
Column | Type | Collation | Nullable | Default
-----------+--------+-----------+----------+---------
max_value | bigint | | |
min_value | bigint | | |
yadamu=# insert into BIGINT_TEST(MAX_VALUE) VALUES ('9223372036854775807');
INSERT 0 1
yadamu=# insert into BIGINT_TEST(MAX_VALUE) VALUES ('-9223372036854775808');
INSERT 0 1
yadamu=# select * from BIGINT_TEST;
max_value | min_value
----------------------+-----------
9223372036854775807 |
-9223372036854775808 |
(2 rows)
However the same test with Vertica yields
VMart=> create table BIGINT_TEST (MAX_VALUE INT, MIN_VALUE INT);
CREATE TABLE
VMart=> \d BIGINT_TEST
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------------+-----------+------+------+---------+----------+-------------+-------------
public | BIGINT_TEST | MAX_VALUE | int | 8 | | f | f |
public | BIGINT_TEST | MIN_VALUE | int | 8 | | f | f |
(2 rows)
VMart=> insert into BIGINT_TEST(MAX_VALUE) VALUES ('9223372036854775807');
OUTPUT
--------
1
(1 row)
VMart=> insert into BIGINT_TEST(MAX_VALUE) VALUES ('-9223372036854775808');
ERROR 5409: Value "-9223372036854775808" is out of range for type int8
Any idea why this is?
Second when importing data from CSV containing the value -9223372036854775808, the value appears to treated as NULL, rather than generating an error.
VMart=> COPY BIGINT_TEST from STDIN PARSER fcsvparser(type='rfc4180', header=false, trim=false) NULL '' REJECTED DATA AS TABLE "BIGINT_BAD_DATA" NO COMMIT;
NOTICE 5795: COPY is not going to commit. Creating a TEMPORARY rejection table, which will be dropped at end-of-session
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
@>> "9223372036854775807","-9223372036854775808"
@>> \.
VMart=> select * from BIGINT_TEST
VMart-> ;
MAX_VALUE | MIN_VALUE
---------------------+-----------
9223372036854775807 |
9223372036854775807 |
(2 rows)
VMart=>
Note I added the '@' characters when posting as the >> seemed to confuse the MARKDOWN