--
-- 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;
ERROR:  table "ins_tbl" does not exist
CREATE TABLE INS_TBL (id integer, name text, primary key (id));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'ins_tbl_pkey' for table 'ins_tbl'
-- 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');
ERROR:  Cannot insert a duplicate key into unique index ins_tbl_pkey
-- 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');
ERROR:  Cannot insert a duplicate key into unique index ins_tbl_pkey
INSERT INTO INS_TBL (name) VALUES ('bobo');
ERROR:  ExecAppend: Fail to add null value in not null attribute id
INSERT INTO INS_TBL (id) VALUES (13);
INSERT INTO INS_TBL (id) VALUES (14), (15);
-- everything got in?
SELECT * FROM INS_TBL;
 id |  name  
----+--------
  1 | fred
  2 | bob
  3 | george
  4 | jeb
  5 | larry
  6 | curly
  7 | moe
 13 | 
 14 | 
 15 | 
(10 rows)

DROP TABLE INS_TBL;
CREATE TABLE INS_TBL (id serial, name text, primary key (id));
NOTICE:  CREATE TABLE will create implicit sequence 'ins_tbl_id_seq' for SERIAL column 'ins_tbl.id'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'ins_tbl_pkey' for table 'ins_tbl'
-- 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;
 id |  name  
----+--------
  1 | 
  2 | jane
  3 | fred
  4 | bob
  5 | arthur
  6 | george
  7 | jeb
  8 | larry
  9 | curly
 10 | moe
(10 rows)

-- simple INSERT ... SELECT ...
DROP TABLE INS_TBL2;
ERROR:  table "ins_tbl2" does not exist
CREATE TABLE INS_TBL2 (id integer, name text, primary key (id));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'ins_tbl2_pkey' for table 'ins_tbl2'
INSERT INTO INS_TBL2 SELECT id, name FROM INS_TBL;
SELECT * FROM INS_TBL2;
 id |  name  
----+--------
  1 | 
  2 | jane
  3 | fred
  4 | bob
  5 | arthur
  6 | george
  7 | jeb
  8 | larry
  9 | curly
 10 | moe
(10 rows)

DROP TABLE INS_TBL;
DROP SEQUENCE INS_TBL_ID_SEQ;
DROP TABLE INS_TBL2;
-- some other, more complicated INSERT ... VALUES ...
DROP TABLE foo;
ERROR:  table "foo" does not exist
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;
 f1 | f2 
----+----
  1 |  2
  3 |  4
  0 |  4
  3 |  5
  0 |  4
  3 |  0
(6 rows)

DROP TABLE foo;