Thread

1. Type and CAST error on lowest negative integer values for smallint, int and bigint

   Hans Buschmann <buschmann@nidsa.net> — 2024-05-02T11:25:32Z

   I tried to initialize a table with values for smallint columns.
   
   The final goal is to get mask values for logical operations.
   
   
   The insert failed with ERROR: smallint out of range.
   
   
   the same occurs when using a simple select statement like:
   
   select -32768::smallint;
   select -2147483648::int;
   select -9223372036854775808::bigint;
   
   These limit values are taken from the documentation 8.1.1 Integer Types
   
   This occurs on PG16.2 on Windows or Linux (64bit).
   
   This prevents me to enter the binary value 0b10000000_00000000 into a smallint column
   (I could use some other tricks, but this is ugly!)
   
   -----------
   
   postgres=# select version ();
                                                    version
   ----------------------------------------------------------------------------------------------------------
    PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.0.1 20240411 (Red Hat 14.0.1-0), 64-bit
   (1 Zeile)
   
   postgres=# select -32768::smallint;
   ERROR:  smallint out of range
   
   Thank you for looking
   
   
   Hans Buschmann
   

2. Re: Type and CAST error on lowest negative integer values for smallint, int and bigint

   David Rowley <dgrowleyml@gmail.com> — 2024-05-02T11:33:55Z

   On Thu, 2 May 2024 at 23:25, Hans Buschmann <buschmann@nidsa.net> wrote:
   > postgres=# select -32768::smallint;
   > ERROR:  smallint out of range
   
   The precedence order of operations applies the cast before the unary
   minus operator.
   
   Any of the following will work:
   
   postgres=# select cast(-32768 as smallint), (-32768)::smallint,
   '-32768'::smallint;
     int2  |  int2  |  int2
   --------+--------+--------
    -32768 | -32768 | -32768
   (1 row)
   
   David
   
   
   
   

3. AW: Type and CAST error on lowest negative integer values for smallint, int and bigint

   Hans Buschmann <buschmann@nidsa.net> — 2024-05-02T12:14:08Z

   Thank you for your quick response.
   
   
   This was very helpfull and resolved my problem.
   
   
   But for me it is a bit counterintuitive that -32768 is not treated as a negative constant but as a unary operator to a positive constant.
   
   
   It could be helpfull to remind the user of the nature of negative constants and tthe highest precedence of casts in the numeric type section of the doc.
   
   
   Perhaps someone could add an index topic "precedence of operators", since this is a very important information for every computer language.
   
   (I just found out that a topic of "operator precedence" exists already in the index)
   
   
   I just sent this message for the case this could be a problem to be resolved before the next minor versions scheduled for next week.
   
   
   Regards
   
   
   Hans Buschmann
   
   
   ________________________________
   Von: David Rowley <dgrowleyml@gmail.com>
   Gesendet: Donnerstag, 2. Mai 2024 13:33
   An: Hans Buschmann
   Cc: pgsql-hackers@postgresql.org
   Betreff: Re: Type and CAST error on lowest negative integer values for smallint, int and bigint
   
   On Thu, 2 May 2024 at 23:25, Hans Buschmann <buschmann@nidsa.net> wrote:
   > postgres=# select -32768::smallint;
   > ERROR:  smallint out of range
   
   The precedence order of operations applies the cast before the unary
   minus operator.
   
   Any of the following will work:
   
   postgres=# select cast(-32768 as smallint), (-32768)::smallint,
   '-32768'::smallint;
     int2  |  int2  |  int2
   --------+--------+--------
    -32768 | -32768 | -32768
   (1 row)
   
   David