Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

INT(8) Most Negative Value

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

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.