Small. Fast. Reliable.
Choose any three.

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11. The information shown here has not been updated since that cut-over. These pages are retained for historical reference only.

There is a list of features that SQLite does not support at http://www.sqlite.org/omitted.html. If you find additional features that SQLite does not support, you may want to list them below.


  • 2011-05-06: SQLite doesn't support name subqueries (WITH):

      WITH
        a AS (SELECT 1 a_id, 2 a_ref),
        b AS (SELECT 2 b_id, 3 b_val)
      SELECT *
        FROM a, b WHERE a_ref=b_id;

2001-05-07: But it does support: SELECT * FROM (SELECT 1 a_id, 2 a_ref) as a, (SELECT 2 b_id, 3 b_val) as b WHERE a_ref=b_id;

  • 2009-10-19: SQLite doesn't support functions and subqueries in constraints clauses:

      DEFAULT SELECT AVG(*) FROM table

      CHECK (field > SELECT MIN(field) FROM table)

      ...

  • 2009-08-04: Table and column comments - I have scoured the doco and can't find anything about applying comments to tables or their columns. Easy workaround:

      CREATE TABLE sqlite_tab_comments (
        table_name text,
        comment text
      );

      CREATE TABLE sqlite_col_comments (
        table_name text,
        col_name text,
        comment text
      );

May I also suggest an even easier workaround:

      CREATE TABLE User
      -- A table comment
      (
        uid INTEGER,     -- A field comment
        flags INTEGER,   -- Another field comment
      );

.schema User --> will print out the above exactly

  • 2009-08-03: Can we please have support for creating pivot tables/cross tables? That would be really sweet -> bug 1424. See http://en.wikipedia.org/wiki/Pivot_table for more information.

  • 2011-04-18: vector expressions: (see here for more detail)

    SELECT First, Last, Score
    FROM mytable
    WHERE
        ('John',  'Jordan',  5) <= (First, Last, Score )
        AND (First, Last, Score) <= ('Mike',  'Taylor',  50)
    ORDER BY First, Last, Score
    LIMIT 1

  • 2009-04-01: positioned updates and deletes

      DELETE table WHERE CURRENT OF cursor_name;

  • 2007-12-30: EXTRACT for TIMESTAMP types

      SELECT EXTRACT(YEAR FROM TIMESTAMP '2013-07-02');

    These are also useful in the WHERE clause

  • 2007-12-03 : Multi-row INSERT a.k.a. compound INSERT not supported.

      INSERT INTO table (col1, col2) VALUES ('row1col1', 'row1col2'), ('row2col1', 'row2col2'), ...

Actually, according to the SQL92 standard, a VALUES expression should be able to stand on itself. For example, the following should return a one-column table with three rows:

     VALUES 'john', 'mary', 'paul';

  • 2007-11-13 : STARTING WITH clause not supported

      WHERE <Column> STARTING WITH "Text"

returns an error but works on MSSQL and is in my SQL book.

  • 2007.08.23 : DELETE from multiple tables:

      DELETE t1, t2 FROM t1, t2, t3
      WHERE t1.id=t2.id AND t2.id=t3.id;

      ...or...

      DELETE FROM t1, t2 USING t1, t2, t3 WHERE
      t1.id=t2.id AND t2.id=t3.id;

  • 2006.09.15 : CREATE DOMAIN

      CREATE DOMAIN code_postal_us AS TEXT;

  • 2006.04.28 : DROP CONSTRAINT statement:

      ALTER TABLE myTable DROP CONSTRAINT defPK

  • 2006.04.07 : ANSI 99 windowing functions:

      SELECT department_id, last_name, salary, commission_pct,
      RANK() OVER (PARTITION BY department_id
      ORDER BY salary DESC, commission_pct) comp_rank
      FROM employees
      WHERE department_id = 80

  • 2006.03.06 : select without left join with the *= operator

      SELECT t1.code, t2.code
      FROM table1 t1, table2 t2
      WHERE t1.t2_ref_id *= t2.id

      This is Sybase ASE syntax. Use LEFT JOIN or RIGHT JOIN instead.
      (This is related to "Oracle's join syntax" mentioned below.)

  • 2005.12.27 : Oracle's Named Parameter output syntax. In Oracle, one can declare parameters and select into them as such

      Select A1, A2, A3 into (:p1, :p2, :p3) from TableA

  • 2005.10.06 : This appears to be unsupported: updating multiple columns with subselect

      update T1 set (theUpdatedValue, theOtherValue) =
      (select theTop, theValue from T2 where T2.theKey = T1.theID)

  • 2005.09.25 : free text search capabilities in select statements: Mysql does free text search Match(field_list) Against(keyword) - Now somewhat supported with 3.3.8, and being enhanced.
  • 2005.08.25 : The ALL and ANY quantifiers for comparisons with subquery results aren't supported.

  • 2005.06.01 : Named parts of natural joins. For example: SELECT a.c1 FROM T1 a NATURAL JOIN T1 b. Because sqlite reduces the number of columns kept, the name is lost.

  • 2004.11.17 : INSERTing one record with all VALUES to DEFAULT:

      INSERT INTO example ()
      VALUES ();

    To get the desired behavior you can use:

      INSERT INTO example (rowid) values (null);

  • 2004.08.13 : Oracle's join syntax using (+) and (-):

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1 LEFT JOIN a2 ON a2.b = a1.a

    ...can be written in Oracle as:

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1, a2
      WHERE a1.a = a2.b(+);

  • 2004.07.29 : UPDATE t1, t2 SET t1.f1 = value WHERE t1.f2 = t2.fa

  • 2004.04.25 : a password('') function to mask some values (as used in MySQL) would be fine, I need it, if I give the db out of the house, or is there something I didn't find? Or a simple MD5 function to obscure data using a one way hash. See the MySQL function MD5 or Password for examples.

  • 2004.03.26 : Hierarchical Queries.

      START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)

  • 2004.03.17 : FLOOR and CEILING functions, e.g. "SELECT FLOOR(salary) FROM personnel;"

  • 2004.02.25 : name columns in views (i.e. CREATE VIEW (foo, bar) AS SELECT qux, quo FROM baz;)

    You can use CREATE VIEW v1 AS SELECT qux AS foo, quo AS bar FROM baz;

  • 2004.01.08 : MEDIAN and standard deviation... are they standard? Essential for sqlite standalone executable for shell script users.

    _MEDIAN is difficult because it cannot be done "on-line," i.e., on a stream of data. Following is a solution to MEDIAN credited to David Rozensh tein, Anatoly Abramovich, and Eugene Birger; it is explained here: http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html

      SELECT x.Hours median
      FROM BulbLife x, BulbLife y
      GROUP BY x.Hours
      HAVING
         SUM(CASE WHEN y.Hours <= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND
         SUM(CASE WHEN y.Hours >= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1

  • 2004.01.01 : DISTINCT ON (expr,...) - this is from Postgres, where expr,... must be the leftmost expressions from the ORDER BY clause

  • 2003.10.30 : INSERTing fewer values than columns does not fill the missing columns with the default values; if fewer values than columns in the table are supplied, all columns filled have to be named before the keyword values

  • 2003.09.04 : Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN (SELECT...)

  • 2003.08.29 : UPDATE with a FROM clause (not sure if this is standard, Sybase and Microsoft have it). -> UnsupportedSqlUpdateWithFrom

  • 2003.08.06 : ALTER VIEW, ALTER TRIGGER, ALTER TABLE

  • 2003.08.06 : CREATE DATABASE, DROP DATABASE - Does not seem meaningful for an embedded database engine like SQLite. To create a new database, just do sqlite_open(). To drop a database, delete the file.

  • 2003.08.06 : Schemas - See: http://www.postgresql.org/docs/8.1/static/ddl-schemas.html -> UnsupportedSqlSchemas

  • 2003.08.06 : TRUNCATE (MySQL, Postgresql and Oracle have it... but I don't know if this is a standard command) - SQLite does this automatically when you do a DELETE without a WHERE clause. You can use also VACUUM command. The sqlite DELETE without a WHERE clause is significantly slower though due to the logging of transactions while TRUNCATE doesn't support rollback.

  • 2003.08.06 : ORDER BY myfield ASC NULLS LAST (Oracle)

    You can use ORDER BY CASE WHEN myfield ISNULL THEN <somethingBig> ELSE myfield END

  • 2003.08.06 : CREATE TRIGGER [BEFORE | AFTER | INSTEAD OF] (Oracle)

  • 2003.07.28 : Stored Procedures

  • 2003.07.28 : Rollup and Cube -> UnsupportedSqlRollupAndCube

  • More than one primary key per table, I can specify this with MySQL for example and SQLite returns me an error: more than one primary key specified...

    "More than one primary key" is an oxymoron when you're talking about the relational data model. By definition, a primary key uniquely identfies a row. What's the real problem you're trying to solve?

      A combined primary key is possible in SQLite, for example:

		CREATE TABLE strings (
			string_id INTEGER NOT NULL,
			language_id INTEGER NOT NULL,
			string TEXT,
			PRIMARY KEY (string_id, language_id)
		);

  • SHOW TABLES and DESCRIBE [tablename] would be nice - not sure if they're standard, but they are a rather nice feature of MySQL... -------- No, it's not standard. The standard says it should be a special database called INFORMATION_SCHEMA, wich contains info about all databases, tables, columns, index, views, stored procedures, etc.

There is a way to simulte that functions, See: http://www.sqlite.org/faq.html#q7 -> FAQ for more informations.

Can someone tell me how to fake describe until something like this is implemented? Sorry, I'm too dependent on Oracle apparently :(

  Use the "dot" commands:

  .tables
  .schema
-------------------------
  Or via SQL:

  select sql from sqlite_master where name = 'tablename';

  • SELECT ... FOR UPDATE OF ... is not supported. This is understandable considering the mechanics of SQLite in that row locking is redundant as the entire database is locked when updating any bit of it. However, it would be good if a future version of SQLite supports it for SQL interchageability reasons if nothing else. The only functionality required is to ensure a "RESERVED" lock is placed on the database if not already there.

  • create table wg ( cpf numeric not null, id numeric not null, nome varchar(25), primary key (cpf) foreign key (id) ); the foreign key (id) generate an error, a chance to be supported in the future? foreign key dont supported in sqlite? or generater automaticaly or ?

That's not a legal FOREIGN KEY clause; you have to specify what the foreign key references. SQLite parses, but does not enforce, syntactically-legal FOREIGN KEY specifications; there's a PRAGMA that will retrieve foreign-key information from table definitions, allowing you to enforce such constraints with application code.


FEATURES ADDED IN RECENT VERSIONS
  • ORDER BY and LIMIT on UPDATE/DELETE, e.g. "UPDATE TABLE SET col = 'value' LIMIT 1" (only when compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT)

    Added in 3.6.4

  • IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;"

    Added in 3.3

  • Extended POSIX regular expressions (should be easy, man 3 regcomp, or http:// mirbsd.bsdadvocacy.org/man3/regcomp.htm for reference) SELECT * FROM table WHERE name REGEX '[a-zA-Z]+_{0,3}';

The infrastructure for this syntax now exists, but you have to create a user-defined regex matching function.

  • The EXISTS keyword is not supported (IN is, but IN is only a special case of EXISTS). And what about corelated subqueries ?

    Both supported as of 3.1.

  • Inserting blob using X'AABBCCDD' syntax. (note: supported in Sqlite3)

  • CURRENT-Functions like CURRENT_DATE, CURRENT_TIME are missing Try "SELECT date('now');" or "SELECT datetime('now','localtime');"

    Added as of 3.1

  • ESCAPE clause for LIKE

    Added as of 3.1

  • AUTO_INCREMENT field type. SQLite supports auto_incrementing fields but only if that field is set as "INTEGER PRIMARY KEY".

    Oh god no! Stop the evil from spreading! AUTO_INCREMENT is possibly the worst way of doing unique ids for tables. It requires cached per-connection-handle last_insert_id() values. And you're probably already familiar with how much of a hack THAT is.

    A much better solution would be to give SQLite proper SEQUENCE support. You already have a private table namespace, so using sqlite_sequences to store these wouldn't be such a big deal. This is created when the database is created, and looks something like this, taken from a perl MySQL sequence emulation module.

        create table mysql_sequences (
            sequence_name char(32) not null primary key,
            sequence_start bigint not null default 1,
            sequence_increment bigint not null default 1,
            sequence_value bigint not null default 1
        )

    In fact, why don't you just take a look at the original module (DBIx::MySQLSequence): http://search.cpan.org/~adamk/DBIx-MySQLSequence-1.00/lib/DBIx/MySQLSequence.pm. In fact, why don't you just copy that module, and rewrite using code inside the database.

    The main reason for doing this, is that if you want to insert multiple records which reference each other, and these references are not null, you cannot insert one record until you have inserted the one to which it refers, then fetched the last_insert_id(), added it to the other record, then insert that, and so in. In trivial cases this isn't too bad, but imagine the cases where you have circular references, or don't know the structure of the data in advance at all.

    With sequence support and access to ids before inserting, there are algorithms to resolve these cases. Without it, you are left with things like just outright suspending contraints checking, inserting everything incorrectly, then hoping you can find all the cases of broken values, and fixing them. Which sucks if you don't know the structure beforehand.

    To resolve compatibility issues, just do what you do now with the INTEGER PRIMARY_KEY fields with no default, but allow a DEFAULT SEQUENCENAME.NEXTVAL() or something...

      For better or worse, the requested feature was added in 3.1

  • SELECT t1.ID, (SELECT COUNT(*) FROM t2 WHERE t2.ID=t1.ID) FROM t1
    In other words, in a subselect backreferencing to a field in its parent select.

      Now supported as of 3.1

  • 2004.04.07 : Creating a table in a database db1 based on a table in database db2:

      create table db1.table1 as select * from db2.table1;

      This is supported by ATTACH DATABASE.


REMARK
NOT EXISTS remarks (off topic) -> UnsupportedSqlRemarkOffTopic

What about Apache Derby? It uses the Apache 2.0 license and is easy to embed in Java applications (http://db.apache.org/derby/). -- See SqliteVersusDerby


Tcl related
  • Tcl variable bindings for list types? i.e.:

  set values [list a b c]
  db eval { SELECT * FROM table WHERE x IN ($values) }

SQLite does its own variable interpolation which avoids the (messy) need to do value quoting/escaping (to protect against SQL injection attacks, etc.) but in the case where it's an "IN ($variable)" clause, it treats $variable as a single value instead of a Tcl list of values. Or, maybe I'm doing something wrong. If I am, please let me know: dossy@panoptic.com.