Anyone have thoughts on writing a query in SQLDelight where my goal is to see if the table is empty? Currently I have this

sizeOfTable:
SELECT COUNT(*);

but open to other ideas if anyone knows better. 😅

Sounds very reasonable to me

you could also do

== 0

to get a boolean

Cool. tableIsEmpty: SELECT _COUNT_(*) == 0; it is. thanks

don't think that's valid SQL, but even if it is that is effectively

LIMIT false

Well apparently my table is empty (according to android studio db inspector), but SELECT _COUNT_(*) == 0 returns false. I wonder if I need to add the table name to the select statement.

Yeah sorry I kind of assumed you had a

FROM

in there

rookie mistake on my part 😂

I'm surprised

SELECT COUNT(*) == 0

even works

sqlite> SELECT COUNT(*);
1

sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*);
QUERY PLAN
`--SCAN CONSTANT ROW

sqlite> EXPLAIN SELECT COUNT(*);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    0   Start at 1
1     Null           0     1     1                    0   r[1..1]=NULL
2     AggStep        0     0     1     count(0)       0   accum=r[1] step(r[0])
3     AggFinal       1     0     0     count(0)       0   accum=r[1] N=0
4     Copy           1     2     0                    0   r[2]=r[1]
5     ResultRow      2     1     0                    0   output=r[2]
6     Halt           0     0     0                    0

weird

SELECT COUNT(*)

of any table (regardless of number of columns) is fine but

SELECT *

of a zero-column table isn't, I guess?

I assume you’re in SQLite. MySQL Innodb is surprisingly slow at count queries and must do a full table scan! https://www.percona.com/blog/count-vs-countcol-in-mysql/