-1

Consider the following table.

CREATE TABLE IF NOT EXISTS wsfiles
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  website_id integer NOT NULL,
  parent_id integer,
  archive_id integer,
  path character varying,
  path_sha character varying NOT NULL,
  target character varying,
  name character varying NOT NULL,
  is_directory boolean
  CONSTRAINT uniq_file_website_backup UNIQUE (archive_id, path_sha) ON CONFLICT REPLACE
);

And I need to create the following unique partial index.

CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_directory ON wsfiles(path_sha) WHERE is_directory = 1;

But when executing the above schema I am getting a syntax error near the WHERE clause in CREATE UNIQUE INDEX query. This is working well in my local machine and the problem occurs when I am executing on the production machine. 

Error: near line 98: near "WHERE": syntax error

Production machine sqlite3 version is 3.7.17

Local machine sqlite3 version is 3.8.5

Yasitha
  • 2,233
  • 4
  • 24
  • 36

1 Answers1

1

The documentation says:

Partial indexes have been supported in SQLite since version 3.8.0.

CL.
  • 173,858
  • 17
  • 217
  • 259