sql: make the CockroachDB integer types more compatible with postgres #26925
Description
| Type | CockroachDB | Postgres |
|---|---|---|
int |
primitive type, 64-bit | alias for int4, never shows up in vtables |
int2 |
alias for int (thus wrong width), shows up as "int2" in certain vtables |
primitive type, 16-bit |
int4 |
alias for int (thus wrong width), shows up as "int4" in certain vtables |
primitive type, 32-bit |
int8 |
alias for int, shows up as "int8" in certain vtables |
primitive type, 64-bit |
bigint |
alias for int, shows up as "bigint" in certain vtables |
alias for int8, never shows up in vtables |
smallint |
alias for int (thus wrong width) shows up as "smallint" in certain vtables |
alias for int2, never shows up in vtables |
serial |
special form for int (thus wrong width) with default unique_rowid() |
special form for int4, create sequence and default nextval(seqname) |
bigserial |
alias for serial |
special form for int8, create sequence and default nextval(seqname) |
smallserial |
alias for serial (thus wrong width) |
special form for int2, create sequence and default nextval(seqname) |
bit |
near-alias for int, shows up as bit in certain vtables, max 64-bit |
primitive type, arbitrarily length byte array with bit input/output representations |
Problems:
- CockroachDB types
int,int2,int4,serial,smallserial,bithave data width that are fully incompatible with postgresql - CockroachDB incorrectly preserves the names "bigint" and "smallint" whereas postgres desugars them to int8 and int2 during parsing.
- PostgresDB
serialtypes uses sequences, CockroachDB does not do this bithas incorrectly a maximum size (and its underlying implementation is currently incorrect)
Fixing this would comprehensively adress #24062 #22607 #26730 #25098 #24686 and possibly others.
Informs #26128.
Jira issue: CRDB-4979
Epic CRDB-60813