For the record, CockroachDB does not store the value either, but it allows querying null.
A workaround may be stepping through the full table manually checking for null "in app" and cleaning them that way.
Noticed INSERTS are treated as UPSERT unless you use IF NOT EXISTS... ScyllaDB doesn't give a dump if it's a unique primary key lol. I can live with the UPSERT footgun, though.
SELECT * FROM users;
Then you can plug your PK into.
UPDATE users SET address='' WHERE user_id IN (9affeac3-5d92-111d-779c-55eb6d78a806, ...) IF address=null;
Then you can do your SELECT to do your repair.
SELECT * FROM users WHERE address='';
_______________________
Another footgun: No default values for columns in CREATE TABLE. Very annoying.
Another footgun: Only "=" and "IN (...)" is supported in WHERE for partition keys. https://docs.scylladb.com/stable/cql/dml.html#the-where-clau...
This means, you cannot do UPDATE WHERE user_id > 0 ....
I'm starting to really appreciate the effort CockroachDB went to, to make their version of "CQL" postgres compatible, even though architecturally they are both key-value store databases under the hood... it's just a shame CockroachDB just has far lower performance because of enforced consistency and no way to turn it off.
To be fair, ScyllaDB is removing footguns with every new version, just not fast enough for my taste.
Write Postgres in a way that's fully compatible with CockroachDB, with the intention of transitioning to CockroachDB if needed.
Basically: 1. BIGINT or UUID or TEXT or composite Primary Keys only. 2. Limited use of sequential indexes. (Ex: timestamp. Not the end if the world but creates hotspot, will require a hash sharded index) 3. Avoid advanced features such as stored procedures. 4. Avoid or very light use of foreign keys. (Performance)
Going straight to CockroachDB seems logical as well, but you'll hit the performance wall much sooner on 1-2 servers, and will require a cluster to match it.