Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pzwMH-0002GO-L7 for pgsql-hackers@arkaria.postgresql.org; Fri, 19 May 2023 09:25:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pzwMG-0008Fy-87 for pgsql-hackers@arkaria.postgresql.org; Fri, 19 May 2023 09:25:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pzwME-0008Fp-IN for pgsql-hackers@lists.postgresql.org; Fri, 19 May 2023 09:25:07 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pzwMA-000eEn-3A for pgsql-hackers@lists.postgresql.org; Fri, 19 May 2023 09:25:05 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id E80825C00C6 for ; Fri, 19 May 2023 05:25:00 -0400 (EDT) Received: from imap48 ([10.202.2.98]) by compute2.internal (MEProxy); Fri, 19 May 2023 05:25:00 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=compiler.org; h= cc:content-type:content-type:date:date:from:from:in-reply-to :message-id:mime-version:reply-to:sender:subject:subject:to:to; s=fm1; t=1684488300; x=1684574700; bh=JorhajSDybKVUgpOFWXK9LXnY EdRnN1fgCYs3AkW+7Q=; b=sUCCbdV9ag9rOqZ75pSSHiplVdMDFVZeKEInJSNkc 67+GLPKCBBndPFT4UvaA0XuRC6jr27vHVCJzSi84nBiQHcKWepAqLKV4YBjyMWMC /kvd1Ykjq/FQU665AhS43u6EKRmJUIWx4x9QGhuq2ROVlD21j5OBT6Bbv/hB33Yh wuCuHgQRIvmspOMjQdMqi0N5rG2lT2M75HfKik/6dhT+wlhnfJ6L8R1erK5M9Iha VBF6tpF/THqfdViq1AR06mirCBmyMQHwqIOi1wPURZCT5OkPyPqn+5QN3ZQJLrhq /lKM/mgArxiKWynyDDCpb74ZEbH3+4313RJeLOZm45W/g== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:message-id :mime-version:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1684488300; x=1684574700; bh=JorhajSDybKVUgpOFWXK9LXnYEdRnN1fgCY s3AkW+7Q=; b=DjyV7oVNV5pnS/wZDBh6AqKNORHke6IDVBMb4gq0U3rXzeAy+pA NIPO1sqkZj9+u0LN53G5paq3GIfcvyQK6blNGyp4k6yCMlAlH1IjLkTzs6czOpEY oy/3QR6dsieXzIk/660ln1/4jAqdPYRPrz4D0ojc/gdsWQccVKFvJHHpEsUch5xn 9LPCJyiHHHHPP0qbNfkFg0mGu9nZK8WkNwdPiouNPA83nEBVmJudiF7eLJVGX6SE koH3CEap8PM4rjyCtuAYDZQwpvO9xfaaZBGCOzBeAi8Mv4HgEkKhkqysBC18cej9 6ye9QtxgjIld07piO+gk8N494odzkVODQQQ== X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfeeihedgudehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfffhffvufgtsehmtderre erredtnecuhfhrohhmpedflfhovghlucflrggtohgsshhonhdfuceojhhovghlsegtohhm phhilhgvrhdrohhrgheqnecuggftrfgrthhtvghrnhepueeiieefuddtveeiheegveetgf eifeehuefhvdeuhfevieelffdvhfegkeeljeegnecuffhomhgrihhnpegvgigrmhhplhgv rdgtohhmpdhpohhsthhgrhgvshhqlhdrohhrghenucevlhhushhtvghrufhiiigvpedtne curfgrrhgrmhepmhgrihhlfhhrohhmpehjohgvlhestghomhhpihhlvghrrdhorhhg X-ME-Proxy: Feedback-ID: ic6394509:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id A3DAB31A0063; Fri, 19 May 2023 05:25:00 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.9.0-alpha0-431-g1d6a3ebb56-fm-20230511.001-g1d6a3ebb Mime-Version: 1.0 Message-Id: <748b7bfc-9f16-4bea-b7e1-4a59dc9354f5@app.fastmail.com> Date: Fri, 19 May 2023 11:24:27 +0200 From: "Joel Jacobson" To: pgsql-hackers@lists.postgresql.org Subject: New COPY options: DELIMITER NONE and QUOTE NONE Content-Type: multipart/mixed; boundary=b9b4d065b66c4400b8dc53a00cf8e924 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --b9b4d065b66c4400b8dc53a00cf8e924 Content-Type: multipart/alternative; boundary=5ceaa8ef96704548930645b8fbf5f35f --5ceaa8ef96704548930645b8fbf5f35f Content-Type: text/plain The thread "Should CSV parsing be stricter about mid-field quotes?" [1] forked into a new topic, with two new ideas, hence this new thread. 1. COPY ... QUOTE NONE In the [1] thread, Andrew Dunstan suggested a trick on how to deal with unquoted but delimited files, such as TSV-files produced by Google Sheets: > You can use CSV mode pretty reliably for TSV files. > The trick is to use a quoting char that shouldn't appear, > such as E'\x01' as well as setting the delimiter to E'\t'. > Yes, it's far from obvious. Would it be an improvement to allow specifying `QUOTE NONE` instead? quotes.tsv: id quote 1 "E = mc^2" -- Albert Einstein COPY quotes FROM '/tmp/quotes.tsv' WITH CSV HEADER DELIMITER E'\t' QUOTE NONE; SELECT * FROM quotes; id | quote ----+------------------------------- 1 | "E = mc^2" -- Albert Einstein (1 row) 2. COPY ... DELIMITER NONE This is meant to improve the use-case when wanting to import e.g. an unstructured log file line-by-line into a single column. The current trick I've been using is similar to the first one, that is, to specify a non-existing delimiter. But that involves having to find some non-existing byte, which is error-prone since future log files might suddenly start to contain it. So I think it would be better to be to be explicit about not wanting to delimit fields at all, treating the entire whole line as a column. Example: % cat /tmp/apache.log 192.168.1.1 - - [19/May/2023:09:54:17 -0700] "GET /index.html HTTP/1.1" 200 431 "http://www.example.com/home.html" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3" 192.168.1.2 - - [19/May/2023:09:55:12 -0700] "POST /form.php HTTP/1.1" 200 512 "http://www.example.com/form.html" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3" CREATE TABLE unstructured_log (whole_line text); COPY unstructured_log FROM '/tmp/apache.log' WITH CSV DELIMITER NONE QUOTE NONE; SELECT * FROM unstructured_log; whole_line ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 192.168.1.1 - - [19/May/2023:09:54:17 -0700] "GET /index.html HTTP/1.1" 200 431 "http://www.example.com/home.html" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3" 192.168.1.2 - - [19/May/2023:09:55:12 -0700] "POST /form.php HTTP/1.1" 200 512 "http://www.example.com/form.html" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3" (2 rows) I hacked together a broken patch just to demonstrate the idea on syntax and basic idea. The `COPY ... FROM` examples above works. But it doesn't work at all for `COPY ... TO`, since it output \0 byte as delimiter and quote in the output, which is of course not what we want. Just wanted some feedback to see if there is any broader interest in this, before proceeding and looking into how to implement it properly. Is this something we want or are there just a few of us who have needed this in the past? /Joel [1] https://www.postgresql.org/message-id/31c81233-d707-0d2a-8111-a915f463459b%40dunslane.net --5ceaa8ef96704548930645b8fbf5f35f Content-Type: text/html Content-Transfer-Encoding: quoted-printable
The thread "Sho= uld CSV parsing be stricter about mid-field quotes?" [1] forked
into a new topic, with two new ideas, hence this new thread.

1. COPY ... QUOTE NONE

<= div>In the [1] thread, Andrew Dunstan suggested a trick on how to d= eal with
unquoted but delimited files, such as TSV-files p= roduced by Google Sheets:

> You can use = CSV mode pretty reliably for TSV files.
> The trick is = to use a quoting char that shouldn't appear,
> such as = E'\x01' as well as setting the delimiter to E'\t'.
> Ye= s, it's far from obvious.

Would it be an im= provement to allow specifying `QUOTE NONE` instead?

quotes.tsv:
id quote
1 "E =3D mc^2"= -- Albert Einstein

COPY quotes FROM '/tmp/= quotes.tsv' WITH CSV HEADER DELIMITER E'\t' QUOTE NONE;
SELECT * FROM quotes;
id |   &= nbsp;         quote
----+-------------------------------
  1 | "E =3D m= c^2" -- Albert Einstein
(1 row)

2. COPY ... DELIMITER NONE

This is meant= to improve the use-case when wanting to import e.g. an
un= structured log file line-by-line into a single column.
The current trick I've been using is similar to the first on= e,
that is, to specify a non-existing delimiter. But that = involves having to find
some non-existing byte, which is e= rror-prone since future log files might
suddenly start to = contain it. So I think it would be better to be to be explicit
<= /div>
about not wanting to delimit fields at all, treating the entir= e whole line as a column.

Example:

% cat /tmp/apache.log
192.168.= 1.2 - - [19/May/2023:09:55:12 -0700] "POST /form.php HTTP/1.1" 200 512 "= http://www.example.com/form= .html" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36= (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3"
CREATE TABLE unstructured_log (whole_line text);
<= div>COPY unstructured_log FROM '/tmp/apache.log' WITH CSV DELIMITER NONE= QUOTE NONE;
SELECT * FROM unstructured_log;
            = ;            = ;            = ;            = ;            = ;            = ;            = ;            = ;            = ;   whole_line
---------------------------------= ------------------------------------------------------------------------= ------------------------------------------------------------------------= --------------------------------------------------------
1= 92.168.1.1 - - [19/May/2023:09:54:17 -0700] "GET /index.html HTTP/1.1" 2= 00 431 "http://www.example.= com/home.html" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKi= t/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3"
192.168.1.2 - - [19/May/2023:09:55:12 -0700] "POST /form.php HTTP/= 1.1" 200 512 "http://www.ex= ample.com/form.html" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Appl= eWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3"
(2 rows)

I hacked together a br= oken patch just to demonstrate the idea on syntax
and basi= c idea. The `COPY ... FROM` examples above works.
But it d= oesn't work at all for `COPY ... TO`, since it output \0 byte as
delimiter and quote in the output, which is of course not what we= want.

Just wanted some feedback to see if = there is any broader interest in this,
before proceeding a= nd looking into how to implement it properly.

Is this something we want or are there just a few of us who have need= ed this in the past?

/Joel
--5ceaa8ef96704548930645b8fbf5f35f-- --b9b4d065b66c4400b8dc53a00cf8e924 Content-Disposition: attachment; filename="copy_delimiter_quote_none.patch" Content-Type: application/octet-stream; name="copy_delimiter_quote_none.patch" Content-Transfer-Encoding: BASE64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2NvcHkuYyBiL3NyYy9iYWNrZW5k L2NvbW1hbmRzL2NvcHkuYwppbmRleCBmMTRmYWUzMzA4Li5mNDM0OGE2MTk3IDEwMDY0NAot LS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy9jb3B5LmMKKysrIGIvc3JjL2JhY2tlbmQvY29t bWFuZHMvY29weS5jCkBAIC02MTEsNyArNjExLDcgQEAgUHJvY2Vzc0NvcHlPcHRpb25zKFBh cnNlU3RhdGUgKnBzdGF0ZSwKIAl9CiAKIAkvKiBPbmx5IHNpbmdsZS1ieXRlIGRlbGltaXRl ciBzdHJpbmdzIGFyZSBzdXBwb3J0ZWQuICovCi0JaWYgKHN0cmxlbihvcHRzX291dC0+ZGVs aW0pICE9IDEpCisJaWYgKHN0cmxlbihvcHRzX291dC0+ZGVsaW0pID4gMSkKIAkJZXJlcG9y dChFUlJPUiwKIAkJCQkoZXJyY29kZShFUlJDT0RFX0ZFQVRVUkVfTk9UX1NVUFBPUlRFRCks CiAJCQkJIGVycm1zZygiQ09QWSBkZWxpbWl0ZXIgbXVzdCBiZSBhIHNpbmdsZSBvbmUtYnl0 ZSBjaGFyYWN0ZXIiKSkpOwpAQCAtNjY5LDEyICs2NjksMTUgQEAgUHJvY2Vzc0NvcHlPcHRp b25zKFBhcnNlU3RhdGUgKnBzdGF0ZSwKIAkJCQkoZXJyY29kZShFUlJDT0RFX0ZFQVRVUkVf Tk9UX1NVUFBPUlRFRCksCiAJCQkJIGVycm1zZygiQ09QWSBxdW90ZSBhdmFpbGFibGUgb25s eSBpbiBDU1YgbW9kZSIpKSk7CiAKLQlpZiAob3B0c19vdXQtPmNzdl9tb2RlICYmIHN0cmxl bihvcHRzX291dC0+cXVvdGUpICE9IDEpCisJaWYgKG9wdHNfb3V0LT5jc3ZfbW9kZSAmJiBz dHJsZW4ob3B0c19vdXQtPnF1b3RlKSA+IDEpCiAJCWVyZXBvcnQoRVJST1IsCiAJCQkJKGVy cmNvZGUoRVJSQ09ERV9GRUFUVVJFX05PVF9TVVBQT1JURUQpLAogCQkJCSBlcnJtc2coIkNP UFkgcXVvdGUgbXVzdCBiZSBhIHNpbmdsZSBvbmUtYnl0ZSBjaGFyYWN0ZXIiKSkpOwogCi0J aWYgKG9wdHNfb3V0LT5jc3ZfbW9kZSAmJiBvcHRzX291dC0+ZGVsaW1bMF0gPT0gb3B0c19v dXQtPnF1b3RlWzBdKQorCWlmIChvcHRzX291dC0+Y3N2X21vZGUgJiYKKwkJb3B0c19vdXQt PmRlbGltWzBdID09IG9wdHNfb3V0LT5xdW90ZVswXSAmJgorCQlvcHRzX291dC0+ZGVsaW1b MF0gIT0gJ1wwJyAmJgorCQlvcHRzX291dC0+cXVvdGVbMF0gIT0gJ1wwJykKIAkJZXJlcG9y dChFUlJPUiwKIAkJCQkoZXJyY29kZShFUlJDT0RFX0lOVkFMSURfUEFSQU1FVEVSX1ZBTFVF KSwKIAkJCQkgZXJybXNnKCJDT1BZIGRlbGltaXRlciBhbmQgcXVvdGUgbXVzdCBiZSBkaWZm ZXJlbnQiKSkpOwpAQCAtNjg1LDcgKzY4OCw3IEBAIFByb2Nlc3NDb3B5T3B0aW9ucyhQYXJz ZVN0YXRlICpwc3RhdGUsCiAJCQkJKGVycmNvZGUoRVJSQ09ERV9GRUFUVVJFX05PVF9TVVBQ T1JURUQpLAogCQkJCSBlcnJtc2coIkNPUFkgZXNjYXBlIGF2YWlsYWJsZSBvbmx5IGluIENT ViBtb2RlIikpKTsKIAotCWlmIChvcHRzX291dC0+Y3N2X21vZGUgJiYgc3RybGVuKG9wdHNf b3V0LT5lc2NhcGUpICE9IDEpCisJaWYgKG9wdHNfb3V0LT5jc3ZfbW9kZSAmJiBzdHJsZW4o b3B0c19vdXQtPmVzY2FwZSkgPiAxKQogCQllcmVwb3J0KEVSUk9SLAogCQkJCShlcnJjb2Rl KEVSUkNPREVfRkVBVFVSRV9OT1RfU1VQUE9SVEVEKSwKIAkJCQkgZXJybXNnKCJDT1BZIGVz Y2FwZSBtdXN0IGJlIGEgc2luZ2xlIG9uZS1ieXRlIGNoYXJhY3RlciIpKSk7CkBAIC03MjIs MTQgKzcyNSwxNiBAQCBQcm9jZXNzQ29weU9wdGlvbnMoUGFyc2VTdGF0ZSAqcHN0YXRlLAog CQkJCSBlcnJtc2coIkNPUFkgZm9yY2UgbnVsbCBvbmx5IGF2YWlsYWJsZSB1c2luZyBDT1BZ IEZST00iKSkpOwogCiAJLyogRG9uJ3QgYWxsb3cgdGhlIGRlbGltaXRlciB0byBhcHBlYXIg aW4gdGhlIG51bGwgc3RyaW5nLiAqLwotCWlmIChzdHJjaHIob3B0c19vdXQtPm51bGxfcHJp bnQsIG9wdHNfb3V0LT5kZWxpbVswXSkgIT0gTlVMTCkKKwlpZiAoc3RyY2hyKG9wdHNfb3V0 LT5udWxsX3ByaW50LCBvcHRzX291dC0+ZGVsaW1bMF0pICE9IE5VTEwgJiYKKwkJb3B0c19v dXQtPmRlbGltWzBdICE9ICdcMCcpCiAJCWVyZXBvcnQoRVJST1IsCiAJCQkJKGVycmNvZGUo RVJSQ09ERV9GRUFUVVJFX05PVF9TVVBQT1JURUQpLAogCQkJCSBlcnJtc2coIkNPUFkgZGVs aW1pdGVyIG11c3Qgbm90IGFwcGVhciBpbiB0aGUgTlVMTCBzcGVjaWZpY2F0aW9uIikpKTsK IAogCS8qIERvbid0IGFsbG93IHRoZSBDU1YgcXVvdGUgY2hhciB0byBhcHBlYXIgaW4gdGhl IG51bGwgc3RyaW5nLiAqLwogCWlmIChvcHRzX291dC0+Y3N2X21vZGUgJiYKLQkJc3RyY2hy KG9wdHNfb3V0LT5udWxsX3ByaW50LCBvcHRzX291dC0+cXVvdGVbMF0pICE9IE5VTEwpCisJ CXN0cmNocihvcHRzX291dC0+bnVsbF9wcmludCwgb3B0c19vdXQtPnF1b3RlWzBdKSAhPSBO VUxMICYmCisJCW9wdHNfb3V0LT5xdW90ZVswXSAhPSAnXDAnKQogCQllcmVwb3J0KEVSUk9S LAogCQkJCShlcnJjb2RlKEVSUkNPREVfRkVBVFVSRV9OT1RfU1VQUE9SVEVEKSwKIAkJCQkg ZXJybXNnKCJDU1YgcXVvdGUgY2hhcmFjdGVyIG11c3Qgbm90IGFwcGVhciBpbiB0aGUgTlVM TCBzcGVjaWZpY2F0aW9uIikpKTsKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3BhcnNlci9n cmFtLnkgYi9zcmMvYmFja2VuZC9wYXJzZXIvZ3JhbS55CmluZGV4IDZmNWFhOGEzY2IuLmUw YzIwMDJiNTAgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3BhcnNlci9ncmFtLnkKKysrIGIv c3JjL2JhY2tlbmQvcGFyc2VyL2dyYW0ueQpAQCAtMzQwOSw2ICszNDA5LDEwIEBAIGNvcHlf b3B0X2l0ZW06CiAJCQkJewogCQkJCQkkJCA9IG1ha2VEZWZFbGVtKCJkZWxpbWl0ZXIiLCAo Tm9kZSAqKSBtYWtlU3RyaW5nKCQzKSwgQDEpOwogCQkJCX0KKwkJCXwgREVMSU1JVEVSIE5P TkUKKwkJCQl7CisJCQkJCSQkID0gbWFrZURlZkVsZW0oImRlbGltaXRlciIsIChOb2RlICop IG1ha2VTdHJpbmcoIlwwIiksIEAxKTsKKwkJCQl9CiAJCQl8IE5VTExfUCBvcHRfYXMgU2Nv bnN0CiAJCQkJewogCQkJCQkkJCA9IG1ha2VEZWZFbGVtKCJudWxsIiwgKE5vZGUgKikgbWFr ZVN0cmluZygkMyksIEAxKTsKQEAgLTM0MjUsNiArMzQyOSwxMCBAQCBjb3B5X29wdF9pdGVt OgogCQkJCXsKIAkJCQkJJCQgPSBtYWtlRGVmRWxlbSgicXVvdGUiLCAoTm9kZSAqKSBtYWtl U3RyaW5nKCQzKSwgQDEpOwogCQkJCX0KKwkJCXwgUVVPVEUgTk9ORQorCQkJCXsKKwkJCQkJ JCQgPSBtYWtlRGVmRWxlbSgicXVvdGUiLCAoTm9kZSAqKSBtYWtlU3RyaW5nKCJcMCIpLCBA MSk7CisJCQkJfQogCQkJfCBFU0NBUEUgb3B0X2FzIFNjb25zdAogCQkJCXsKIAkJCQkJJCQg PSBtYWtlRGVmRWxlbSgiZXNjYXBlIiwgKE5vZGUgKikgbWFrZVN0cmluZygkMyksIEAxKTsK --b9b4d065b66c4400b8dc53a00cf8e924--