I know the existence of this discussion: https://sqlite.org/forum/forumpost/415111b8e26732ec5f537912a9c6e63394a86bad0e7e831055e3234843b61e02

I don't see a reason to not support this feature. Given that the underlying implementation of BIGINT and INTEGER is identical in SQLite3, the requirement of INTEGER PRIMARY KEY seems to be just a language limitation on the front end (of the SQL compiler).

I haven't looked at the code of SQLite3, but I don't think there is even a need to touch the AST. Of course, this changes the language syntax a bit so I am not sure how big a change it would be in the lexer/parser etc.

Would it be possible to treat BIGINT PRIMARY KEY (or any other integer type) as identical to INTEGER PRIMARY KEY?

For SQL portability. Right now, if I am writing migration scripts (for creating tables, specifically), I need to use different copies for SQLite and for other databases (e.g. Postgres). If this feature is supported, I would have one SQL script that works for multiple backends.

What happens if you use the SQLite form INTEGER PRIMARY KEY in all the other databases ?

Remember that "INTEGER PRIMARY KEY" is special in it being an alias for the rowid. Which meas you can only store actual integers into that column and they cannot be null.

"BIGINT PRIMARY KEY" will allow you to store any data type in that column.

So making "bigint primary key" the same as "integer primary key" would break anything which is currently using "bigint primary key" and relying on the ability to put anything in there.

sqlite> create table t (f bigint primary key not null);

sqlite> insert into t values (1), ('one');

sqlite> select * from t;
f
1
one

sqlite> create table t2 (f integer primary key not null);

sqlite> insert into t2 values (1), ('one');
Error: stepping, datatype mismatch (20)

sqlite> create table t3 (f bigint primary key);

sqlite> insert into t3 values (null);

sqlite> .nullvalue NuLL

sqlite> select * from t3;
f
NuLL

sqlite> create table t4 (f integer primary key);

sqlite> insert into t4 values (null);

sqlite> select * from t4;
f
1

sqlite>

Fair enough, if this is a backwards incompatible change.

If your PRIMARY KEY exceeds 32bit you're in trouble.

If you are the OP, you are misguided and misinformed I'm afraid.

PostgreSQL's bigint is SQLite's integer (or int). I.e. both are 64-bit.

While SQLite's bigint extension is for arbitrarily large integers.

Which bigint extension for arbitrary large integers? I never heard of it.

In SQLite BIGINT is a kind of synonym for INTEGER in CREATE TABLE statements or CAST expressions. id BIGINT PRIMARY KEY works fine in a CREATE TABLE statement, but not id BIGINT PRIMARY KEY AUTOINCREMENT.

I think that the statement by anonymous about 32bit integers refers to other databases than SQLite. The suggestion was to use id INTEGER PRIMARY KEY AUTOINCREMENT uniformly, but in many databases other than SQLite this enforces 32 bit integers.

Right, I meant the decimal extension. Wrong name and wrong context, since not a datatype but just functions over text.

Didn't know/remember bigint mapped to int/integer in SQLite.
I thought it might not alias the rowid, but it seems to. So all it well.

The OP IMHO clearly thought int meant 32-bit like it does in PostgreSQL.
That's why I pointed out it doesn't.

Given the above, your advise to use bigint for both SQLite and PostgreSQL seems sound.