insert

statement error pgcode 42P01 relation "kv" does not exist
INSERT INTO kv VALUES ('a', 'b')

statement ok
CREATE TABLE kv (
  k VARCHAR PRIMARY KEY,
  v VARCHAR,
  UNIQUE INDEX a (v),
  FAMILY (k),
  FAMILY (v)
)

query TT
SELECT * FROM kv
----

statement ok
INSERT INTO kv VALUES ('A')

statement error missing "k" primary key column
INSERT INTO kv (v) VALUES ('a')

statement ok
INSERT INTO kv (k) VALUES ('nil1')

statement ok
INSERT INTO kv (k) VALUES ('nil2')

statement ok
INSERT INTO kv VALUES ('nil3', NULL)

statement ok
INSERT INTO kv VALUES ('nil4', NULL)

statement ok
INSERT INTO kv (k,v) VALUES ('a', 'b'), ('c', 'd')

query T rowsort
SELECT v || 'hello' FROM [INSERT INTO kv VALUES ('e', 'f'), ('g', '') RETURNING v]
----
fhello
hello

statement error pgcode 23505 duplicate key value violates unique constraint "a"\nDETAIL: Key \(v\)=\('f'\) already exists\.
INSERT INTO kv VALUES ('h', 'f')

statement ok
INSERT INTO kv VALUES ('f', 'g')

statement error duplicate key value violates unique constraint "a"\nDETAIL: Key \(v\)=\('g'\) already exists\.
INSERT INTO kv VALUES ('h', 'g')

query TT
SELECT * FROM kv ORDER BY k
----
A     NULL
a     b
c     d
e     f
f     g
g     ·
nil1  NULL
nil2  NULL
nil3  NULL
nil4  NULL

statement ok
CREATE TABLE kv2 (
  k CHAR,
  v CHAR,
  UNIQUE INDEX a (v),
  PRIMARY KEY (k, v)
)

statement ok
INSERT INTO kv2 VALUES ('a', 'b'), ('c', 'd'), ('e', 'f'), ('f', 'g')

query TT rowsort
SELECT * FROM kv2
----
a   b
c   d
e   f
f   g

statement ok
CREATE TABLE kv3 (
  k CHAR PRIMARY KEY,
  v CHAR NOT NULL
)

statement error null value in column "v" violates not-null constraint
INSERT INTO kv3 VALUES ('a')

statement error null value in column "v" violates not-null constraint
INSERT INTO kv3 VALUES ('a', NULL)

statement error null value in column "v" violates not-null constraint
INSERT INTO kv3 (k) VALUES ('a')

query TT
SELECT * FROM kv3
----

statement ok
CREATE TABLE kv4 (
  int INT PRIMARY KEY,
  bit BIT,
  bool BOOLEAN,
  char CHAR,
  float FLOAT
)

statement error could not parse "a" as type int
INSERT INTO kv4 (int) VALUES ('a')

statement ok
INSERT INTO kv4 (int) VALUES (1)

statement error could not parse string as bit array: "a" is not a valid binary digit
INSERT INTO kv4 (int, bit) VALUES (2, 'a')

statement ok
INSERT INTO kv4 (int, bit) VALUES (2, B'1')

statement error could not parse "a" as type bool
INSERT INTO kv4 (int, bool) VALUES (3, 'a')

statement ok
INSERT INTO kv4 (int, bool) VALUES (3, true)

statement error value too long for type CHAR
INSERT INTO kv4 (int, char) VALUES (4, 11)

statement ok
INSERT INTO kv4 (int, char) VALUES (4, 1)

statement ok
INSERT INTO kv4 (int, char) VALUES (5, 'a')

statement ok
INSERT INTO kv4 (int, float) VALUES (6, 1::INT)

statement ok
INSERT INTO kv4 (int, float) VALUES (7, 2.3)

query ITBTR rowsort
SELECT * from kv4
----
1  NULL  NULL  NULL  NULL
2  1     NULL  NULL  NULL
3  NULL  true  NULL  NULL
4  NULL  NULL  1     NULL
5  NULL  NULL  a     NULL
6  NULL  NULL  NULL  1
7  NULL  NULL  NULL  2.3

statement ok
CREATE TABLE kv5 (
  k CHAR PRIMARY KEY,
  v CHAR,
  UNIQUE INDEX a (v, k)
)

statement ok
INSERT INTO kv5 VALUES ('a', NULL)

statement error VALUES lists must all be the same length, expected 1 columns, found 2
INSERT INTO kv5 VALUES ('b'), ('c', DEFAULT)

query TT
SELECT v, k FROM kv5@a
----
NULL a

statement error INSERT has more expressions than target columns, 3 expressions for 2 targets
INSERT INTO kv SELECT 'a', 'b', 'c'

statement error INSERT has more expressions than target columns, 2 expressions for 1 targets
INSERT INTO kv (k) SELECT 'a', 'b'

statement error INSERT has more target columns than expressions, 1 expressions for 2 targets
INSERT INTO kv5 (k, v) SELECT 'a'

# INSERT ... VALUES take a separate code path from INSERT ... SELECT.

statement error INSERT has more expressions than target columns, 3 expressions for 2 targets
INSERT INTO kv VALUES ('a', 'b', 'c')

statement error INSERT has more expressions than target columns, 2 expressions for 1 targets
INSERT INTO kv (k) VALUES ('a', 'b')

statement error INSERT has more target columns than expressions, 1 expressions for 2 targets
INSERT INTO kv5 (k, v) VALUES ('a')

statement ok
CREATE TABLE return (a INT DEFAULT 3, b INT)

query III rowsort
INSERT INTO return (a) VALUES (default), (8) RETURNING a, 2, a+4
----
3 2 7
8 2 12

query III rowsort
INSERT INTO return (b) VALUES (default), (8) RETURNING a, a+4, b
----
3 7 NULL
3 7 8

# All columns returned if none specified.
query II
INSERT INTO return VALUES (default) RETURNING a, b
----
3 NULL

# Test column names
query III colnames
INSERT INTO return VALUES (default) RETURNING a, b AS c, 4 AS d
----
a  c     d
3  NULL  4

# Return a qualified name
query I
INSERT INTO return VALUES (default) RETURNING return.a
----
3

# Can fetch rowid
statement ok
INSERT INTO return VALUES (default) RETURNING rowid != unique_rowid()

query I colnames
INSERT INTO return (a) VALUES (default) RETURNING b
----
b
NULL

query III
INSERT INTO return (b) VALUES (1) RETURNING *, a+1
----
3 1 4

query II colnames
INSERT INTO return VALUES (default) RETURNING *
----
a b
3 NULL

query II colnames,rowsort
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.a, b
----
a b
1 2
3 4

query II colnames,rowsort
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING *
----
a b
1 2
3 4

# Verify we return all columns even if we don't provide a value for all of them.
query II colnames
INSERT INTO return VALUES (1) RETURNING *
----
a b
1 NULL

query II colnames
INSERT INTO return (a) VALUES (1) RETURNING *
----
a b
1 NULL

statement error pq: "return.*" cannot be aliased
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.* as x

query III colnames,rowsort
INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.*, a + b AS c
----
a  b  c
1  2  3
3  4  7

# Table alias
statement ok
INSERT INTO return AS r VALUES (5, 6)

# TODO(knz) after #6092 is fixed
# statement ok
# INSERT INTO return AS r VALUES (5, 6) RETURNING r.a

# #17008: allow fully-qualified table names in RETURNING clauses
statement ok
INSERT INTO return VALUES (5, 6) RETURNING test.return.a

statement error no data source matches pattern: x.\*
INSERT INTO return VALUES (1, 2) RETURNING x.*[1]

statement error column "x" does not exist
INSERT INTO return VALUES (1, 2) RETURNING x[1]

statement ok
CREATE VIEW kview AS VALUES ('a', 'b'), ('c', 'd')

query TT rowsort
SELECT * FROM kview
----
a b
c d

statement error "kview" is not a table
INSERT INTO kview VALUES ('e', 'f')

query TT rowsort
SELECT * FROM kview
----
a b
c d

statement ok
CREATE TABLE abc (
  a INT,
  b INT,
  c INT,
  PRIMARY KEY (a, b),
  INDEX a (a)
)

statement ok
INSERT INTO abc VALUES (1, 2, 10)

# Verify we get the correct message, even though internally the ConditionalPut
# for the index key will also fail.
statement error pgcode 23505 duplicate key value violates unique constraint "abc_pkey"\nDETAIL: Key \(a, b\)=\(1, 2\) already exists\.
INSERT INTO abc VALUES (1, 2, 20)

statement ok
CREATE TABLE decimal (
  a DECIMAL PRIMARY KEY
)

statement ok
INSERT INTO decimal VALUES (4)

# Verify that the "blind" ConditionalPut optimization correctly handles a batch
# with two CPuts of the same key.
statement ok
CREATE TABLE blindcput (
  x INT,
  v INT,
  PRIMARY KEY (x)
)

# The optimization thresholds at 10 k/v operations, so we need at least that
# many in one batch to trigger it.
statement error duplicate key value violates unique constraint "blindcput_pkey"\nDETAIL: Key \(x\)=\(1\) already exists\.
INSERT INTO blindcput values (1, 1), (2, 2), (3, 3), (4, 4), (1, 5)

statement ok
CREATE TABLE nocols()

statement error INSERT has more expressions than target columns, 2 expressions for 0 targets
INSERT INTO nocols VALUES (true, default)

statement error at or near "k": syntax error: unimplemented
INSERT INTO kv (kv.k) VALUES ('hello')

statement error at or near "\*": syntax error: unimplemented
INSERT INTO kv (k.*) VALUES ('hello')

statement error at or near "v": syntax error: unimplemented
INSERT INTO kv (k.v) VALUES ('hello')



statement ok
CREATE TABLE insert_t (x INT, v INT)

statement ok
CREATE TABLE select_t (x INT, v INT)

statement ok
INSERT INTO select_t VALUES (1, 9), (8, 2), (3, 7), (6, 4)

# Check that INSERT supports ORDER BY (MySQL extension)
query II rowsort
INSERT INTO insert_t TABLE select_t ORDER BY v DESC LIMIT 3 RETURNING x, v
----
1  9
3  7
6  4

# Check that INSERT supports LIMIT (MySQL extension)

statement ok
TRUNCATE TABLE insert_t

statement ok
INSERT INTO insert_t SELECT * FROM select_t LIMIT 1

query II
SELECT * FROM insert_t
----
1  9

statement ok
TRUNCATE TABLE insert_t

statement ok
INSERT INTO insert_t (SELECT * FROM select_t LIMIT 1)

query II
SELECT * FROM insert_t
----
1  9

statement error pq: multiple LIMIT clauses not allowed
INSERT INTO insert_t (VALUES (1,1), (2,2) LIMIT 1) LIMIT 1

statement error pq: multiple ORDER BY clauses not allowed
INSERT INTO insert_t (VALUES (1,1), (2,2) ORDER BY 2) ORDER BY 2

statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET
INSERT INTO insert_t (VALUES (1, DEFAULT), (2,'BBB') LIMIT 1)

statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET
INSERT INTO insert_t (VALUES (1, DEFAULT), (2,'BBB')) LIMIT 1

subtest string_bytes_conflicts

statement ok
CREATE TABLE bytes_t (
  b BYTES PRIMARY KEY
)

statement ok
INSERT INTO bytes_t VALUES ('byte')

statement ok
CREATE TABLE string_t (
  s STRING PRIMARY KEY
)

statement ok
INSERT INTO string_t VALUES ('str')

query error value type string doesn't match type bytes of column "b"
INSERT INTO bytes_t SELECT * FROM string_t

statement ok
INSERT INTO string_t SELECT * FROM bytes_t

subtest string_width_check

statement ok
CREATE TABLE sw (
 a CHAR,
 b CHAR(3),
 c VARCHAR,
 d VARCHAR(3),
 e STRING,
 f STRING(3),
 g "char",
 ac CHAR COLLATE en,
 bc CHAR(3) COLLATE en,
 cc VARCHAR COLLATE en,
 dc VARCHAR(3) COLLATE en,
 ec STRING COLLATE en,
 fc STRING(3) COLLATE en,
 FAMILY "primary" (a, b, c, d, e, f, g, ac, bc, cc, dc, ec, fc, rowid)
)

query T
SELECT create_statement FROM [SHOW CREATE TABLE sw]
----
CREATE TABLE public.sw (
  a CHAR NULL,
  b CHAR(3) NULL,
  c VARCHAR NULL,
  d VARCHAR(3) NULL,
  e STRING NULL,
  f STRING(3) NULL,
  g "char" NULL,
  ac CHAR COLLATE en NULL,
  bc CHAR(3) COLLATE en NULL,
  cc VARCHAR COLLATE en NULL,
  dc VARCHAR(3) COLLATE en NULL,
  ec STRING COLLATE en NULL,
  fc STRING(3) COLLATE en NULL,
  rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
  CONSTRAINT sw_pkey PRIMARY KEY (rowid ASC)
)

statement ok
INSERT INTO sw VALUES (
   'a', 'b', 'c', 'd', 'e', 'f', 'g',
   'A' COLLATE en, 'B' COLLATE en, 'C' COLLATE en, 'D' COLLATE en, 'E' COLLATE en, 'F' COLLATE en)

statement ok
INSERT INTO sw VALUES (
   '', '', '', '', '', '', '',
   '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en)

statement error value too long for type CHAR
INSERT INTO sw(a) VALUES ('ab')

statement error value too long for type CHAR COLLATE en
INSERT INTO sw(ac) VALUES ('ab' COLLATE en)

statement ok
INSERT INTO sw (b, c, d, e, f, g, bc, cc, dc, ec, fc) VALUES (
   'b22', 'c22', 'd22', 'e22', 'f22', 'g22',
   'B22' COLLATE en, 'C22' COLLATE en, 'D22' COLLATE en, 'E22' COLLATE en, 'F22' COLLATE en)

statement error value too long for type CHAR\(3\)
INSERT INTO sw(b) VALUES ('abcd')

statement error value too long for type CHAR\(3\) COLLATE en
INSERT INTO sw(bc) VALUES ('abcd' COLLATE en)

statement error value too long for type VARCHAR\(3\)
INSERT INTO sw(d) VALUES ('abcd')

statement error value too long for type VARCHAR\(3\) COLLATE en
INSERT INTO sw(dc) VALUES ('abcd' COLLATE en)

statement error value too long for type STRING\(3\)
INSERT INTO sw(f) VALUES ('abcd')

statement error value too long for type STRING\(3\) COLLATE en
INSERT INTO sw(fc) VALUES ('abcd' COLLATE en)

subtest regression_26742

statement ok
CREATE TABLE ct(x INT, derived INT AS (x+1) STORED)

statement error value type varchar doesn't match type int of column "x"
INSERT INTO ct(x) SELECT c FROM sw

subtest contraint_check_validation_ordering

# Verification of column constraints vs CHECK handling. The column
# constraint verification must take place first.
#
# This test requires that the error message for a CHECK constraint
# validation error be different than a column validation error. So we
# test the former first, as a sanity check.
statement ok
CREATE TABLE tn(x INT NULL CHECK(x IS NOT NULL), y CHAR(4) CHECK(length(y) < 4));

statement error failed to satisfy CHECK constraint
INSERT INTO tn(x) VALUES (NULL)

statement error failed to satisfy CHECK constraint
INSERT INTO tn(y) VALUES ('abcd')

# Now we test that the column validation occurs before the CHECK constraint.
statement ok
CREATE TABLE tn2(x INT NOT NULL CHECK(x IS NOT NULL), y CHAR(3) CHECK(length(y) < 4));

statement error null value in column "x" violates not-null constraint
INSERT INTO tn2(x) VALUES (NULL)

statement error value too long for type CHAR\(3\)
INSERT INTO tn2(x, y) VALUES (123, 'abcd')

subtest fk_contraint_check_validation_ordering

# Verify that column constraints and CHECK handling occur before
# foreign key validation.
statement ok
CREATE TABLE src(x VARCHAR PRIMARY KEY);
  INSERT INTO src(x) VALUES ('abc');
  CREATE TABLE derived(x CHAR(3) REFERENCES src(x),
                       y VARCHAR CHECK(length(y) < 4) REFERENCES src(x))

# Sanity check that the FK constraints gets actually validated
statement error foreign key
INSERT INTO derived(x) VALUES('xxx')

statement error value too long for type CHAR\(3\)
INSERT INTO derived(x) VALUES('abcd')

statement error failed to satisfy CHECK constraint
INSERT INTO derived(y) VALUES('abcd')

subtest regression_29494

statement ok
CREATE TABLE t29494(x INT); INSERT INTO t29494 VALUES (12)

statement ok
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123

# Check that the new column is not visible
query T
SELECT create_statement FROM [SHOW CREATE t29494]
----
CREATE TABLE public.t29494 (
  x INT8 NULL,
  rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
  CONSTRAINT t29494_pkey PRIMARY KEY (rowid ASC)
)

# Check that the new column is not usable in RETURNING.
statement error column "y" does not exist
INSERT INTO t29494(x) VALUES (123) RETURNING y

statement ok
ROLLBACK

statement ok
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123

query I
INSERT INTO t29494(x) VALUES (123) RETURNING *
----
123

statement ok
COMMIT

subtest regression_32759_33012

statement ok
CREATE TABLE t32759 (
  x INT, y STRING DEFAULT 'b' NOT NULL, z INT,
  FAMILY "primary" (x, z, rowid)
)

statement ok
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER TABLE t32759 DROP COLUMN y

# Check that the dropped column is not visible
query T
SELECT create_statement FROM [SHOW CREATE t32759]
----
CREATE TABLE public.t32759 (
  x INT8 NULL,
  z INT8 NULL,
  rowid INT8 NOT VISIBLE NOT NULL DEFAULT unique_rowid(),
  CONSTRAINT t32759_pkey PRIMARY KEY (rowid ASC)
)

# Check that values cannot be inserted into the dropped column.
statement error column "y" does not exist
INSERT INTO t32759(x, y, z) VALUES (2, 'c', 2)

statement ok
ROLLBACK

statement ok
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER TABLE t32759 DROP COLUMN y

query II colnames
INSERT INTO t32759(x, z) VALUES (1, 4) RETURNING *
----
x  z
1  4

statement ok
COMMIT

# Test ORDER BY with computed ordering column (requires extra column).

statement ok
CREATE TABLE xy(x INT PRIMARY KEY, y INT);
CREATE TABLE ab(a INT PRIMARY KEY, b INT);
INSERT INTO ab VALUES (1, 1), (2, 2)

query II rowsort
INSERT INTO xy (x, y) SELECT a, b FROM ab ORDER BY -b LIMIT 10 RETURNING *;
----
2  2
1  1

statement ok
DROP TABLE xy; DROP TABLE ab

subtest regression_35611

statement ok
CREATE TABLE t35611(a INT PRIMARY KEY, CHECK (a > 0))

statement ok
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER TABLE t35611 ADD COLUMN b INT

statement ok
INSERT INTO t35611 (a) VALUES (1)

statement ok
COMMIT

# ------------------------------------------------------------------------------
# Regression for #35364.
# ------------------------------------------------------------------------------
subtest regression_35364

statement ok
CREATE TABLE t35364(x DECIMAL(1,0) CHECK (x = 0))

statement ok
INSERT INTO t35364(x) VALUES (0.1)

query T
SELECT x FROM t35364
----
0

statement ok
DROP TABLE t35364

subtest generated_as_identity
statement ok
CREATE TABLE generated_as_id_t (
  a INT UNIQUE,
  b INT GENERATED ALWAYS AS IDENTITY,
  c INT GENERATED BY DEFAULT AS IDENTITY
)

statement ok
INSERT INTO generated_as_id_t (a) VALUES (1), (2), (3)

query III
SELECT * FROM generated_as_id_t ORDER BY a
----
1  1  1
2  2  2
3  3  3

statement error pq: cannot insert into column "b"\nDETAIL: Column "b" is an identity column defined as GENERATED ALWAYS
INSERT INTO generated_as_id_t (a, b) VALUES (4, 10)

statement ok
INSERT INTO generated_as_id_t (a, c) VALUES (4, 10)

query III
SELECT * FROM generated_as_id_t ORDER BY a
----
1  1  1
2  2  2
3  3  3
4  4  10

subtest generated_as_identity_with_seq_option
statement ok
CREATE TABLE gen_as_id_seqopt (
  a INT UNIQUE,
  b INT GENERATED ALWAYS AS IDENTITY (START 2 INCREMENT 3),
  c INT GENERATED BY DEFAULT AS IDENTITY (START 3 INCREMENT 4)
)

statement ok
INSERT INTO gen_as_id_seqopt (a) VALUES (7), (8), (9)

query III
SELECT * FROM gen_as_id_seqopt ORDER BY a
----
7  2  3
8  5  7
9  8  11

statement error pq: cannot insert into column "b"\nDETAIL: Column "b" is an identity column defined as GENERATED ALWAYS
INSERT INTO gen_as_id_seqopt (a, b) VALUES (10, 2)

query III
SELECT * FROM gen_as_id_seqopt ORDER BY a
----
7  2  3
8  5  7
9  8  11

statement ok
INSERT INTO gen_as_id_seqopt (a, c) VALUES (10, 2)

query III
SELECT * FROM gen_as_id_seqopt ORDER BY a
----
7   2   3
8   5   7
9   8   11
10  11  2

# Regression test for hitting an internal error in the vectorized unordered
# distinct when NULLs are present in the rows being inserted (#74795).
statement ok
CREATE TABLE t74795 (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id TEXT NOT NULL,
    deletion_request_id TEXT,
    UNIQUE INDEX (account_id, deletion_request_id)
);
INSERT INTO t74795
    (account_id)
VALUES
    ('foo'),
    ('foo'),
    ('foo')
ON CONFLICT (account_id, deletion_request_id) DO NOTHING;

query TT nosort
SELECT account_id, deletion_request_id FROM t74795
----
foo  NULL
foo  NULL
foo  NULL