Skip to content

sqlite.md

Latest commit

 

History

History
58 lines (39 loc) · 1.8 KB

sqlite.md

File metadata and controls

58 lines (39 loc) · 1.8 KB

SQLite

Field with the current unix timestamp by default

"unix_timestamp" integer NOT NULL DEFAULT(strftime('%s', 'NOW'))
"unix_timestamp_hires" float NOT NULL DEFAULT(time_hires())

Field with the current timestamp by default

"timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
"timestamp_with_milliseconds" timestamp NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'NOW'))

Autogenerated UUID

DEFAULT( uuidv4() )
DEFAULT( uuidv7() )

Notifications

SELECT sqlite_notify( 'event-name', payload );

Collation

An index can be used to speed up a search only if it uses the same collation as the query.

By default, an index takes the collation from the table column, so you could change the table definition:

CREATE TABLE a (
    name1 text COLLATE NOCASE, -- this column is case insensitive
    name2 text                 -- this column is case sensitive
);

CREATE INDEX IF NOT EXISTS a_name1_nocase_idx ON a ( name1 COLLATE NOCASE, name2 COLLATE NOCASE );

-- will return results, regardless to the case, because column is declared as case insensitive
SELECT * FROM a WHERE name1 = 'UPPER_case' COLLATE NO CASE;

-- will return only results in the same case
SELECT * FROM a WHERE name2 = 'UPPER_case';

-- will return results regardless to the case
SELECT * FROM a WHERE name2 = 'UPPER_case' COLLATE NO CASE;

FAQ

SQLite will NOT use the second column of an index if the first column was an inequality expression (eg. customer > 33).

If you create two separate indices, only one of them will be used, the other expression will be evaluated on the result set generated by the first. (on an Oracle, it could execute the two index search and intersect the result sets, if optimization is cost based and certain criteria meets, but thats a rare case).