[YSQL] Please support insert multiple values

[teochenglim]

INSERT INTO [mask_table] ( COLUME ) VALUES ( value1 ) , ( value2 ) is not working. 

Please support this popular feature.

[ndeodhar]

@teochenglim This is already supported, for example:

yugabyte=# create table foo(a int primary key, b int);
CREATE TABLE
yugabyte=# insert into foo(a) values (1),(2),(3);
INSERT 0 3
yugabyte=# select * from foo;
 a | b 
---+---
 1 |  
 2 |  
 3 |  
(3 rows)

Can you add more details on the query that you tried and the error that you got?

[teochenglim]

Sure. But my case is slightly complex than yours.

drop table a;
drop table b;
drop table c;
CREATE TABLE B (
    id SERIAL PRIMARY KEY,
    name VARCHAR(3) UNIQUE
);

CREATE TABLE C (
    id SERIAL PRIMARY KEY,
    num INT UNIQUE
);

INSERT INTO B (name) VALUES ('1'), ('2'), ('3'), ('4');
INSERT INTO C (num) VALUES (1), (2), (3), (4);

CREATE TABLE A (
    id SERIAL PRIMARY KEY,
    AA VARCHAR(3) NOT NULL REFERENCES B ( name ),
    AB INTEGER NOT NULL REFERENCES C ( num ),
    AC TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);

INSERT INTO A (AB, AA) VALUES
(4, 'AAA'),
(4, 'AAB'),
(4, 'AAC'),
(4, 'AAD'),
(4, 'AAE'),
(4, 'AAF'),
(4, 'AAG'),
(4, 'AAH'),
(4, 'AAI'),
(4, 'AAJ'),
(4, 'AAK');
ERROR:  Operation only supported in SERIALIZABLE isolation level
HINT:  See https://github.com/YugaByte/yugabyte-db/issues/1199. Click '+' on the description to raise its priority

[ndeodhar]

That error is related to foreign keys and not to multiple insert values.
Currently, we only support foreign keys in serializable isolation. (We will change that soon to work with any isolation level).

To make this work, you can do

SET default_transaction_isolation='serializable';

INSERT INTO A (AB, AA) VALUES
(4, 'AAA'),
(4, 'AAB'),
(4, 'AAC'),
(4, 'AAD'),
(4, 'AAE'),
(4, 'AAF'),
(4, 'AAG'),
(4, 'AAH'),
(4, 'AAI'),
(4, 'AAJ'),
(4, 'AAK');

[teochenglim]

Good to know. thanks.

[hectorgcr]

The foreign keys issue with default isolation level is being tracked by #1199. Closing this particular issue since INSERT with multiple value lists is already supported.