5 ms·

> statically typing guarantees int will be an int. You can't store string there. What else do you need? Something along the lines of SQL's `check` or Postgres'

> statically typing guarantees int will be an int. You can't store string there. What else do you need? Something along the lines of SQL's `check` or Postgres's domains? > queries have to specify the table that they query How do you imagine not doing that, something like "pull some things and do stuff with it"? I think we are quite far from that kind of reality. > certain table has certain column I don't get it. It either has, or it doesn't. In one case you get a value, in other one — SQL error. If that's so vital, pull up some schema information and check before running an app.

deathanatos 9y ago
> What else do you need? Me personally: sum types. (Some languages call these tagged unions. Rust calls it an enum, but note that enum here does not mean "an integer under the hood") We have the case where we have an entity who, as part of its primary key, has a value that is either a valid integer or a sort of "Empty" value. It's part of the key, so I can't use a nullable integer to describe this column, as doing so would prevent me guaranteeing uniqueness (the "empty" value is only valid once, unlike a NULL in a unique constraint). I can use (bool, int) column pair and some check constraints, but it leaves the integer exposed to poorly formed queries, such as SUM(the_integer_part). If I can dedicate a "special value" in the integer, I can use just the integer; that's similarly brittle. (SUM — and most other arithmetic — is valid, but ONLY if the column isn't the "Empty" value.) It'd be nice to be able to model the table as something like, CREATE TABLE ( ... count_or_empty union { int | empty } NOT NULL, ... PRIMARY KEY (..., count_or_empty, ...) ); If the DB supported sum types, the type of count_or_empty here could deliberately be a union, which would not be compatible with SUM. You'd need to "unwrap" the union prior to doing such operations on it (check if it's Empty or not) and then do the appropriate thing: trying to blindly SUM on it would be a static error.
- elmigranto 9y ago
  Interesting. Well, you could write a custom type for Postgres as a C extension with all those collations, indexing and other requirements, I know that's not quite SQL or out-of-the-box kind of solution, but still, there are options if you really need it.
- infogulch 9y ago
  EDIT: SEE REPLY How about (SQL Server): CREATE TABLE #P ( name VARCHAR(100), count_empty BIT NOT NULL, count INT NULL, count_key AS ISNULL(count,0), CHECK ((count_empty = 0 AND count IS NOT NULL) OR (count_empty = 1 AND count IS NULL)), PRIMARY KEY (name, count_empty, count_key) ); I think this should give the semantics you're looking for (if not the ergonomics). COUNT,SUM,AVG etc should work correctly on the "count" column (btw, I would consider using a different name ;P), and the check+primary key should enforce the constraints. You might add a before update trigger to set count to NULL when count_empty = 1, and hiding the actual table behind a view to hide the internals of the count_key column.
  - infogulch 9y ago
    Whoa I was way overcomplicating this. This is all you need, semantics AND ergonomics included: CREATE TABLE P ( name VARCHAR(100), count INT NULL, count_empty AS CASE WHEN count IS NULL THEN 1 ELSE 0 END, count_key AS ISNULL(count,0), PRIMARY KEY (name, count_empty, count_key) ); No trigger or updating two fields needed at all. Just update "count". If you insert NULL twice it will yell at you.

deathanatos 9y ago

> What else do you need? Me personally: sum types. (Some languages call these tagged unions. Rust calls it an enum, but note that enum here does not mean "an integer under the hood") We have the case where we have an entity who, as part of its primary key, has a value that is either a valid integer or a sort of "Empty" value. It's part of the key, so I can't use a nullable integer to describe this column, as doing so would prevent me guaranteeing uniqueness (the "empty" value is only valid once, unlike a NULL in a unique constraint). I can use (bool, int) column pair and some check constraints, but it leaves the integer exposed to poorly formed queries, such as SUM(the_integer_part). If I can dedicate a "special value" in the integer, I can use just the integer; that's similarly brittle. (SUM — and most other arithmetic — is valid, but ONLY if the column isn't the "Empty" value.) It'd be nice to be able to model the table as something like, CREATE TABLE ( ... count_or_empty union { int | empty } NOT NULL, ... PRIMARY KEY (..., count_or_empty, ...) ); If the DB supported sum types, the type of count_or_empty here could deliberately be a union, which would not be compatible with SUM. You'd need to "unwrap" the union prior to doing such operations on it (check if it's Empty or not) and then do the appropriate thing: trying to blindly SUM on it would be a static error.

elmigranto 9y ago
Interesting. Well, you could write a custom type for Postgres as a C extension with all those collations, indexing and other requirements, I know that's not quite SQL or out-of-the-box kind of solution, but still, there are options if you really need it.
infogulch 9y ago
EDIT: SEE REPLY How about (SQL Server): CREATE TABLE #P ( name VARCHAR(100), count_empty BIT NOT NULL, count INT NULL, count_key AS ISNULL(count,0), CHECK ((count_empty = 0 AND count IS NOT NULL) OR (count_empty = 1 AND count IS NULL)), PRIMARY KEY (name, count_empty, count_key) ); I think this should give the semantics you're looking for (if not the ergonomics). COUNT,SUM,AVG etc should work correctly on the "count" column (btw, I would consider using a different name ;P), and the check+primary key should enforce the constraints. You might add a before update trigger to set count to NULL when count_empty = 1, and hiding the actual table behind a view to hide the internals of the count_key column.
- infogulch 9y ago
  Whoa I was way overcomplicating this. This is all you need, semantics AND ergonomics included: CREATE TABLE P ( name VARCHAR(100), count INT NULL, count_empty AS CASE WHEN count IS NULL THEN 1 ELSE 0 END, count_key AS ISNULL(count,0), PRIMARY KEY (name, count_empty, count_key) ); No trigger or updating two fields needed at all. Just update "count". If you insert NULL twice it will yell at you.