Materialize/test/pgtest/copy-from-2.pt

send
Query {"query": "DROP TABLE IF EXISTS t"}
----

until ignore=NoticeResponse
ReadyForQuery
----
CommandComplete {"tag":"DROP TABLE"}
ReadyForQuery {"status":"I"}

send
Query {"query": "CREATE TABLE t (i INT8, t TEXT)"}
----

until
ReadyForQuery
----
CommandComplete {"tag":"CREATE TABLE"}
ReadyForQuery {"status":"I"}

# COPY with both empty string and \N for NULL.
# Note that after receiving the terminating char, no other data should be
# accepted.
send
Query {"query": "COPY t FROM STDIN"}
CopyData "1\tblah\n"
CopyData "2\t\n"
CopyData "3\t\\N\n"
CopyData "\\.\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 3"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
DataRow {"fields":["2",""]}
DataRow {"fields":["3","NULL"]}
CommandComplete {"tag":"SELECT 3"}
ReadyForQuery {"status":"I"}

# Wrong number of columns.
send
Query {"query": "COPY t FROM STDIN"}
CopyData "1\n"
CopyData "\\.\n"
CopyDone
----

until err_field_typs=C
ErrorResponse
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
ErrorResponse {"fields":[{"typ":"C","value":"22P04"}]}
ReadyForQuery {"status":"I"}

# Verify that only one COPY can run at once.
send
Query {"query": "COPY t FROM STDIN"}
Query {"query": "COPY t FROM STDIN"}
----

until err_field_typs=C
ErrorResponse
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
ErrorResponse {"fields":[{"typ":"C","value":"08P01"}]}
ReadyForQuery {"status":"I"}

# Verify that after a COPY has started another statement cannot run.
send
Query {"query": "COPY t FROM STDIN"}
Query {"query": "SELECT 2"}
----

until err_field_typs=C
ErrorResponse
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
ErrorResponse {"fields":[{"typ":"C","value":"08P01"}]}
ReadyForQuery {"status":"I"}

send
Query {"query": "DROP TABLE t"}
Query {"query": "CREATE TABLE t (i INT8, t TEXT)"}
Query {"query": "COPY t FROM STDIN"}
CopyData "1\tblah\n"
CopyData "\\.\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"DROP TABLE"}
ReadyForQuery {"status":"I"}
CommandComplete {"tag":"CREATE TABLE"}
ReadyForQuery {"status":"I"}
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 1"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
CommandComplete {"tag":"SELECT 1"}
ReadyForQuery {"status":"I"}

send
Query {"query": "DROP TABLE t"}
Query {"query": "CREATE TABLE t (i INT8, t TEXT)"}
Query {"query": "COPY t FROM STDIN WITH (DELIMITER ',')"}
CopyData "1,blah\n"
CopyData "2,\\N\n"
CopyData "\\.\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"DROP TABLE"}
ReadyForQuery {"status":"I"}
CommandComplete {"tag":"CREATE TABLE"}
ReadyForQuery {"status":"I"}
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 2"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
DataRow {"fields":["2","NULL"]}
CommandComplete {"tag":"SELECT 2"}
ReadyForQuery {"status":"I"}

send
Query {"query": "DROP TABLE t"}
Query {"query": "CREATE TABLE t (i INT8, t TEXT)"}
Query {"query": "COPY t FROM STDIN WITH (NULL 'NS')"}
CopyData "1\tblah\n"
CopyData "2\t\\N\n"
CopyData "3\tNS\n"
CopyData "\\.\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"DROP TABLE"}
ReadyForQuery {"status":"I"}
CommandComplete {"tag":"CREATE TABLE"}
ReadyForQuery {"status":"I"}
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 3"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
DataRow {"fields":["2","N"]}
DataRow {"fields":["3","NULL"]}
CommandComplete {"tag":"SELECT 3"}
ReadyForQuery {"status":"I"}

send
Query {"query": "COPY t FROM STDIN"}
CopyData "1\n"
CopyData "\\.\n"
CopyDone
----

until err_field_typs=C
ErrorResponse
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
ErrorResponse {"fields":[{"typ":"C","value":"22P04"}]}
ReadyForQuery {"status":"I"}

send
Query {"query": "DROP TABLE t"}
Query {"query": "CREATE TABLE t (i INT8, t TEXT)"}
Query {"query": "COPY t FROM STDIN WITH (DELIMITER ',')"}
CopyData "1,blah\n"
CopyData "2,\n"
CopyData "\\.\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"DROP TABLE"}
ReadyForQuery {"status":"I"}
CommandComplete {"tag":"CREATE TABLE"}
ReadyForQuery {"status":"I"}
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 2"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
DataRow {"fields":["2",""]}
CommandComplete {"tag":"SELECT 2"}
ReadyForQuery {"status":"I"}

send
Query {"query": "DROP TABLE t"}
Query {"query": "CREATE TABLE t (i INT8, t TEXT)"}
Query {"query": "COPY t FROM STDIN WITH (NULL 'NS', DELIMITER '|')"}
CopyData "1|blah\n"
CopyData "2|NS\n"
CopyData "3|\n"
CopyData "\\.\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
ReadyForQuery
ReadyForQuery
----
CommandComplete {"tag":"DROP TABLE"}
ReadyForQuery {"status":"I"}
CommandComplete {"tag":"CREATE TABLE"}
ReadyForQuery {"status":"I"}
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 3"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
DataRow {"fields":["2","NULL"]}
DataRow {"fields":["3",""]}
CommandComplete {"tag":"SELECT 3"}
ReadyForQuery {"status":"I"}

send
Query {"query": "COPY t FROM STDIN WITH (DELIMITER ',')"}
CopyData "1\n"
CopyData "\\.\n"
CopyDone
----

until err_field_typs=C
ErrorResponse
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
ErrorResponse {"fields":[{"typ":"C","value":"22P04"}]}
ReadyForQuery {"status":"I"}

# check Sync message is ignored
send
Query {"query": "COPY t FROM STDIN"}
Sync
CopyData "4\t\\N\n"
Sync
Sync
Sync
CopyData "5\t\\N\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 2"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
DataRow {"fields":["2","NULL"]}
DataRow {"fields":["3",""]}
DataRow {"fields":["4","NULL"]}
DataRow {"fields":["5","NULL"]}
CommandComplete {"tag":"SELECT 5"}
ReadyForQuery {"status":"I"}

# ignore junk data after end of copy marker
send
Query {"query": "COPY t FROM STDIN"}
CopyData "\\.\n"
CopyData "invalid data\n"
CopyDone
Query {"query": "SELECT * FROM t ORDER BY i"}
----

until
ReadyForQuery
ReadyForQuery
----
CopyIn {"format":"text","column_formats":["text","text"]}
CommandComplete {"tag":"COPY 0"}
ReadyForQuery {"status":"I"}
RowDescription {"fields":[{"name":"i"},{"name":"t"}]}
DataRow {"fields":["1","blah"]}
DataRow {"fields":["2","NULL"]}
DataRow {"fields":["3",""]}
DataRow {"fields":["4","NULL"]}
DataRow {"fields":["5","NULL"]}
CommandComplete {"tag":"SELECT 5"}
ReadyForQuery {"status":"I"}

send
Query {"query": "COPY t FROM STDIN WITH (DELIMITER '||')"}
----

until
ErrorResponse
ReadyForQuery
----
ErrorResponse {"fields":[{"typ":"S","value":"ERROR"},{"typ":"C","value":"XX000"},{"typ":"M","value":"COPY delimiter must be a single one-byte character"}]}
ReadyForQuery {"status":"I"}

send
Query {"query": "COPY t FROM STDIN WITH (QUOTE '||')"}
Query {"query": "COPY t FROM STDIN WITH (ESCAPE '||')"}
Query {"query": "COPY t FROM STDIN WITH (HEADER true)"}
----

until
ErrorResponse
ReadyForQuery
ErrorResponse
ReadyForQuery
ErrorResponse
ReadyForQuery
----
ErrorResponse {"fields":[{"typ":"S","value":"ERROR"},{"typ":"C","value":"XX000"},{"typ":"M","value":"COPY quote available only in CSV mode"}]}
ReadyForQuery {"status":"I"}
ErrorResponse {"fields":[{"typ":"S","value":"ERROR"},{"typ":"C","value":"XX000"},{"typ":"M","value":"COPY escape available only in CSV mode"}]}
ReadyForQuery {"status":"I"}
ErrorResponse {"fields":[{"typ":"S","value":"ERROR"},{"typ":"C","value":"XX000"},{"typ":"M","value":"COPY HEADER available only in CSV mode"}]}
ReadyForQuery {"status":"I"}