Oops, You Wrote a Database

Re:Oops, You Wrote a Database by ttfkam on Sunday February 26, 2023 @11:53AM (#34947006)

Yeah, the "lacks type safety" comment threw me too. SQL has some of the most rigid type safety in my daily interactions in computing.

    • "Exactly one"
    • "Optional"
    • "Text but matching this pattern"
    • "A 16-bit integer from 1 to 1000"
    • "Many to many without any duplicates"
    • "A unique combination of id and a time range"
    • "This field can be NULL but only if this other field is NOT NULL"

[ Parent | on HN ]

Re:Oops, You Wrote a Database by yencabulator on Sunday February 26, 2023 @02:03PM (#34948300)

Now store a 64-bit unsigned integer in Postgres.

[ Parent | on HN ]

Re:Oops, You Wrote a Database by anarazel on Sunday February 26, 2023 @03:01PM (#34948808)

CREATE DOMAIN unsigned_64bit_int AS numeric CONSTRAINT range CHECK (VALUE >= 0 AND VALUE < 2^64 AND round(VALUE) = VALUE);

[ Parent | on HN ]

Re:Oops, You Wrote a Database by crabbone on Sunday February 26, 2023 @03:29PM (#34949044)

Use bigint with check-between constraint?

Maybe inconvenient, but not impossible... but if this is the kind of problem you have, then, try in your favorite language to create integers that are 63 bit long or 65, and see how it goes? Looks like the comparison won't be in that language's favor.

[ Parent | on HN ]

Re:Oops, You Wrote a Database by yencabulator on Monday February 27, 2023 @02:40PM (#34960918)

Postgres bigint is a signed 64-bit integer, not an unsigned one. https://www.postgresql.org/docs/current/datatype-numeric.htm...

Trying to use it to store the full range of unsigned values would mean awkward math everywhere, to convert between the two ranges.

Lots of software uses uint64s, for e.g. as identifiers handed out by some API, and not being able to store them conveniently definitely hurts.

[ Parent | on HN ]

Re:Oops, You Wrote a Database by ttfkam on Monday February 27, 2023 @02:24PM (#34960729)

Now store a 64-bit unsigned integer in Java. Or TypeScript. Or Lua. Or Python.

Now explain why you need an unsigned bigint in this set-oriented language. What's your use case?

That said, not a limitation of SQL, just Postgres, who avoided it for similar maintenance and interoperability issues that Java avoided them. MySQL supports unsigned bigint just fine. There is nothing in the SQL spec that forbids unsigned integer values.

[ Parent | on HN ]

Re:Oops, You Wrote a Database by ttfkam on Tuesday February 28, 2023 @01:42PM (#34973102)

Apparently there's an extension for that! https://github.com/petere/pguint

[ Parent | on HN ]