users has a bunch of nullable columns

[clokep]

Additionally:

• name is a nullable field (?!?!?!)

With that said, on matrix.org:

matrix=> select count(*) from users;
  count   
══════════
 62887652
(1 row)

Time: 12499.198 ms (00:12.499)
matrix=> select count(*) from users where name IS NULL;
 count 
═══════
     0
(1 row)

_Originally posted by @DMRobertson in https://github.com/matrix-org/synapse/pull/16434#discussion_r1347991716_

[DMRobertson]

I think this is just ancient history. I managed to dig up:

https://github.com/matrix-org/synapse/blame/af27b84ff769ceb9fe1aaa10c9435586be4c6867/synapse/storage/schema/full_schemas/16/users.sql#L15

https://github.com/matrix-org/synapse/blame/8c8354e85a702ef36de15db50d294e1a724ced7d/synapse/storage/schema/full_schemas/11/users.sql#L15-L22

[reivilibre]

we should migrate this to be NOT NULL as a matter of principle. Thank goodness there aren't any entries with NULL values!

[DMRobertson]

matrix=> \d users
                          Table "matrix.users"
           Column           │   Type   │ Collation │ Nullable │ Default 
════════════════════════════╪══════════╪═══════════╪══════════╪═════════
 name                       │ text     │           │          │ 
 password_hash              │ text     │           │          │ 
 creation_ts                │ bigint   │           │          │ 
 admin                      │ smallint │           │ not null │ 0
 upgrade_ts                 │ bigint   │           │          │ 
 is_guest                   │ smallint │           │ not null │ 0
 appservice_id              │ text     │           │          │ 
 consent_version            │ text     │           │          │ 
 consent_server_notice_sent │ text     │           │          │ 
 user_type                  │ text     │           │          │ 
 deactivated                │ smallint │           │ not null │ 0
 shadow_banned              │ boolean  │           │          │ 
 consent_ts                 │ bigint   │           │          │ 
 approved                   │ boolean  │           │          │ 
 locked                     │ boolean  │           │ not null │ false
Indexes:
    "users_creation_ts" btree (creation_ts)
    "users_lower_name" btree (lower(name))
    "users_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "per_user_experimental_features" CONSTRAINT "per_user_experimental_features_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(name)
    TABLE "users_to_send_full_presence_to" CONSTRAINT "users_to_send_full_presence_to_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(name)

Lots of other surprisingly nullable fields here, e.g. creation_ts.