INT(8) Most Negative Value

mark_d_drakemark_d_drake Community Edition User

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

  • Bryan_HBryan_H Vertica Employee Administrator

    This is documented at https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/Numeric/INTEGER.htm
    Specifically (from Notes):
    The range of values is –2^63+1 to 2^63-1.
    2^63 = 9,223,372,036,854,775,808 (19 digits).
    The value –2^63 is reserved to represent NULL.

  • mark_d_drakemark_d_drake Community Edition User
    edited June 2022

    Yes, but why does vertica do this, when every other database that supports native BIGINT allows this value and supports NULL, and why does Vertica throw an error on an insert but store NULL on copy.

    I guess treating the value as NULL is documented but the inconsistent behavoir between INSERT and COPY is questionable ?

Leave a Comment

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