mirror of
https://github.com/tursodatabase/libsql.git
synced 2024-12-15 23:48:49 +00:00
160 lines
4.3 KiB
Plaintext
160 lines
4.3 KiB
Plaintext
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c DEFAULT 0);
|
|
CREATE UNIQUE INDEX t1x1 ON t1(b);
|
|
INSERT INTO t1(a,b) VALUES(1,2) ON CONFLICT DO NOTHING;
|
|
INSERT INTO t1(a,b) VALUES(1,99),(99,2) ON CONFLICT DO NOTHING;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b) VALUES(2,3) ON CONFLICT(a) DO NOTHING;
|
|
INSERT INTO t1(a,b) VALUES(2,99) ON CONFLICT(a) DO NOTHING;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b) VALUES(3,4) ON CONFLICT(b) DO NOTHING;
|
|
INSERT INTO t1(a,b) VALUES(99,4) ON CONFLICT(b) DO NOTHING;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b) VALUES(5,6) ON CONFLICT(b COLLATE binary) DO NOTHING;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b) VALUES(1,2),(3,2),(4,20),(5,20)
|
|
ON CONFLICT(b) WHERE b>10 DO NOTHING;
|
|
SELECT *, 'x' FROM t1 ORDER BY b, a;
|
|
|
|
|
|
DROP TABLE IF EXISTS t2;
|
|
CREATE TABLE t2(a TEXT UNIQUE, b INT DEFAULT 1);
|
|
INSERT INTO t2(a) VALUES('one'),('two'),('three');
|
|
PRAGMA count_changes=ON;
|
|
INSERT INTO t2(a) VALUES('one'),('one'),('three'),('four')
|
|
ON CONFLICT(a) DO UPDATE SET b=b+1;
|
|
|
|
|
|
PRAGMA count_changes=OFF;
|
|
SELECT a, b FROM t2 ORDER BY a;
|
|
|
|
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(x INTEGER PRIMARY KEY, y INT UNIQUE);
|
|
INSERT INTO t1(x,y) SELECT 1,2 WHERE true
|
|
ON CONFLICT(x) DO UPDATE SET y=max(t1.y,excluded.y) AND true;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(b UNIQUE, a INT PRIMARY KEY) WITHOUT ROWID;
|
|
INSERT OR IGNORE INTO t1(a) VALUES('1') ON CONFLICT(a) DO NOTHING;
|
|
PRAGMA integrity_check;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT OR IGNORE INTO t1(a) VALUES('1'),(1) ON CONFLICT(a) DO NOTHING;
|
|
PRAGMA integrity_check;
|
|
|
|
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT, d INT, e INT);
|
|
CREATE UNIQUE INDEX t1b ON t1(b);
|
|
CREATE UNIQUE INDEX t1e ON t1(e);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(e) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(a) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(b) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a INT, b INT, c INT, d INT, e INT);
|
|
CREATE UNIQUE INDEX t1a ON t1(a);
|
|
CREATE UNIQUE INDEX t1b ON t1(b);
|
|
CREATE UNIQUE INDEX t1e ON t1(e);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(e) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(a) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(b) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) WITHOUT ROWID;
|
|
CREATE UNIQUE INDEX t1a ON t1(a);
|
|
CREATE UNIQUE INDEX t1b ON t1(b);
|
|
CREATE UNIQUE INDEX t1e ON t1(e);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(e) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(a) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DELETE FROM t1;
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,3,4,5);
|
|
INSERT INTO t1(a,b,c,d,e) VALUES(1,2,33,44,5)
|
|
ON CONFLICT(b) DO UPDATE SET c=excluded.c;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
DROP TABLE IF EXISTS t0;
|
|
CREATE TABLE t0(c0 REAL UNIQUE, c1);
|
|
CREATE UNIQUE INDEX test800i0 ON t0(0 || c1);
|
|
INSERT INTO t0(c0, c1) VALUES (1, 2), (2, 1);
|
|
INSERT INTO t0(c0) VALUES (1) ON CONFLICT(c0) DO UPDATE SET c1=excluded.c0;
|
|
PRAGMA integrity_check;
|
|
REINDEX;
|
|
|
|
|
|
CREATE VIEW t1(a) AS SELECT 1;
|
|
CREATE TRIGGER t1r1 INSTEAD OF INSERT ON t1 BEGIN
|
|
SELECT 2;
|
|
END;
|
|
|
|
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE);
|
|
INSERT INTO t1(b) VALUES(22);
|
|
INSERT INTO t1 VALUES(2,22) ON CONFLICT (b) DO NOTHING;
|
|
SELECT * FROM t1;
|
|
|
|
|
|
CREATE TABLE t1(a INT, b INT);
|
|
CREATE UNIQUE INDEX t1x ON t1(b+3);
|
|
|