Renaming ROWID

(1) By anonymous on 2025-01-01 09:24:24 [source]

SQLite adds a null value for INTEGER PRIMARY KEY in the row. I guess it is for backward compatibility.

Can I rename ROWID without adding INTEGER PRIMARY KEY? So I can select for example SELECT PK FROM t but get ROWID? Or If I want to have a custom name, I must add an INTEGER PRIMARY KEY and pay the one byte extra par row?

As a side note, I think it should be noted on the page of INTEGER PRIMARY KEY (https://www.sqlite.org/lang_createtable.html#rowid) and not only the format page. Sometimes saving that one byte is important. After years of working with SQLite I thought INTEGER PRIMARY KEY is an only naming thing and not allocating storage for itself.

(2.2) By cj (sqlitening) on 2025-01-01 13:00:06 edited from 2.1 in reply to 1 [link] [source]

Open :memory:
create table t1(c1 integer,c2 text)
insert into t1(c1,c2) values(null,'yes you can')
select rowid AS col1,c2 AS col2 from t1
select rowid F1,c2 F2 from t1

Try this and see error message asking for a PRIMARY key:
create table t1(c1 integer,c2 text) without rowid

Null increments primary key when used like this:
create table t1(c1 integer primary key,c2 text)
insert into t1(c1,c2) values(null,'yes you can')

(3) By anonymous on 2025-01-01 13:23:42 in reply to 2.2 [link] [source]

I think you misunderstood my question. SQLite format adds a NULL in place of the INTEGER PRIMARY KEY and use the ROWID when you use its name. I want to see if there is a way to skip that extra byte and rename ROWID to something else in my schema.

(4) By Aask (AAsk1902) on 2025-01-01 13:41:33 in reply to 2.2 [link] [source]

You cannot actually rename ROWID with

create table t1(c1 integer primary key,c2 text);

since

select rowid from t1;

remains valid; the use of rowid and c1 become interchangeable, that is c1 is an alias for rowid.

(5) By Richard Hipp (drh) on 2025-01-01 13:46:52 in reply to 1 [link] [source]

Yes, there is a one-byte overhead per row in the on-disk file format for having an INTEGER PRIMARY KEY. There is no work-around. I'm sorry that that disappoints you. That design decision was made in 2001 and cannot now be easily changed.

(6) By anonymous on 2025-01-01 14:52:52 in reply to 5 [link] [source]

I can not fathom how you can keep this format stable for such a long time. That is a great job.

Please consider noting the update to the documentation. Most will not notice it, but as you noted at many places about the quirks, I think you should add this one too so future people can be aware of it.

Just as an idea, someone can do something like this:

CREATE TABLE IF NOT EXISTS wordcount(
  word TEXT PRIMARY KEY,
  cnt INTEGER
) ROWID as PK;

Not much useful for almost all people, but as SQLite is used for application file format, these bytes count.

(7.1) By cj (sqlitening) on 2025-01-01 19:49:34 edited from 7.0 in reply to 6 [link] [source]

//Save bytes with this. I goofed it updates all cnts. Hopefully corrected, now.

create table if not exists t(
 Word text primary key,
 Cnt integer default 1) without rowid;

insert into t(Word) values('Test') on conflict(Word) DO Update Set Cnt= Cnt + 1 where Word = excluded.Word;
select * from t;

That was a great link you supplied  https://www.sqlite.org/lang_createtable.html#rowid

(8) By anonymous on 2025-01-02 06:57:59 in reply to 7.1 [link] [source]

cj I am walking about ROWID tables, the one you showed is not one. In summary, ROWID tables have a special key stored as varint in the row.

    CREATE TABLE T1(Value TEXT); -- There is a hidden ROWID

But when you have a INTEGER PRIMARY KEY, SQLite acts as if that PK is now the ROWID, so you can access it by the name of PK or ROWID.

    CREATE TABLE T2(PK INTEGER PRIMARY KEY, Value TEXT);

T1 and T2 are the same, SQL wise, but in the format, SQLite stores PK in the place of ROWID and in the place of PK, it saves a zero to I guess for compatibility reason. As result, all rows of INTEGER PRIMARY KEY tables, has one byte of zero as overhead. Negligible for most cases, but it can add up if you are keeping a high number of logs or numbers in a table. For example if you have a table with 1B of rows as:

CREATE TABLE T2(PK INTEGER PRIMARY KEY, Value Number);

You will have around 1GB more (around 7%) overhead for just having that PK, and you will not have it if you keep ROWID as is and not "rename" it.

(9) By niklasb on 2025-01-02 08:39:13 in reply to 8 [link] [source]

Would you maybe regain that 1GB if you defined table T2 like this instead?

CREATE TABLE T2(PK INTEGER PRIMARY KEY, Value Number) WITHOUT ROWID;

(10) By SeverKetor on 2025-01-02 08:48:00 in reply to 9 [link] [source]

I did a small test earlier. Three databases with one table each, with 1,000,000 rows.

CREATE TABLE A (V INT);
CREATE TABLE B (ID INTEGER PRIMARY KEY, V INT);
CREATE TABLE C (ID INTEGER PRIMARY KEY, V INT) WITHOUT ROWID;
INSERT INTO <table> SELECT * FROM generate_series(1,1000000);
A: 10772 kB
B: 11756 kB
C: 11724 kB

This was after vacuuming the DBs. A and B stayed the same, but interestingly C went from 13372 kB down to 11724 kB. So yes it did save size, but it's really not worth it.

(11) By curmudgeon on 2025-01-02 09:29:49 in reply to 8 [link] [source]

I don't know if it's relevant to you but remember that the rowid for a record can change after a vacuum whereas the INTEGER PRIMARY KEY can't.

(12) By punkish on 2025-01-02 12:18:43 in reply to 8 [link] [source]

You envision being able to store 1B rows of log files on a device, but you fret about 1GB of space on that same device? If you are tight on space, shouldn't you be storing a billion rows (that are likely to become 2B after some time) on a device where space is plentiful and cheap?