--
-- INSERT
--
-- For now, just some simple test cases for insert. Other regression tests
-- also do inserts and many can fail if inserts are not working properly.

-- prepare the table...

DROP TABLE INS_TBL;
CREATE TABLE INS_TBL (id integer, name text, primary key (id));
-- test single inserts
INSERT INTO INS_TBL VALUES (1, 'fred');
INSERT INTO INS_TBL VALUES (2, 'bob');
-- test duplicate primary key
INSERT INTO INS_TBL VALUES (1, 'arthur');
-- test multiple inserts
INSERT INTO INS_TBL VALUES (3, 'george'), (4, 'jeb'), (5, 'larry'), (6, 'curly'), (7, 'moe');
-- test that multiple insertion is atomic
INSERT INTO INS_TBL VALUES (8, 'jane'), (9, 'mary'), (4, 'mike'), (10, 'harry');

INSERT INTO INS_TBL (name) VALUES ('bobo');
INSERT INTO INS_TBL (id) VALUES (13);
INSERT INTO INS_TBL (id) VALUES (14), (15);

-- everything got in?
SELECT * FROM INS_TBL;

DROP TABLE INS_TBL;

CREATE TABLE INS_TBL (id serial, name text, primary key (id));
-- check default values
INSERT INTO INS_TBL DEFAULT VALUES;
INSERT INTO INS_TBL (name) VALUES ('jane');
INSERT INTO INS_TBL (name) VALUES ('fred'), ('bob'), ('arthur'), ('george'), ('jeb'), ('larry'), ('curly'), ('moe');
SELECT * FROM INS_TBL;

-- simple INSERT ... SELECT ...
DROP TABLE INS_TBL2;
CREATE TABLE INS_TBL2 (id integer, name text, primary key (id));
INSERT INTO INS_TBL2 SELECT id, name FROM INS_TBL;
SELECT * FROM INS_TBL2;

DROP TABLE INS_TBL;
DROP SEQUENCE INS_TBL_ID_SEQ;
DROP TABLE INS_TBL2;

-- some other, more complicated INSERT ... VALUES ...
DROP TABLE foo;
CREATE TABLE foo (f1 int, f2 int);
INSERT INTO foo VALUES (1, 2), (3, 4);
INSERT INTO foo VALUES (0, 4), (3, 1 + (SELECT MAX(f2) FROM foo));
INSERT INTO foo VALUES (0, 4), (3, CASE WHEN 1 = ANY (SELECT MAX(f2) FROM foo) THEN 1 ELSE 0 END);
SELECT * FROM foo;
DROP TABLE foo;