Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gCOah-0006z9-Hr for pgsql-sql@arkaria.postgresql.org; Tue, 16 Oct 2018 12:36:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gCOaf-0002Iu-TG for pgsql-sql@arkaria.postgresql.org; Tue, 16 Oct 2018 12:36:49 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gCOaf-0002Ih-Is for pgsql-sql@lists.postgresql.org; Tue, 16 Oct 2018 12:36:49 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gCOac-0002BI-Sz for pgsql-sql@postgresql.org; Tue, 16 Oct 2018 12:36:48 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 184-v6so8821345ybg.1 for ; Tue, 16 Oct 2018 05:36:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; h=to:references:from:openpgp:autocrypt:subject:message-id:date :user-agent:mime-version:in-reply-to:content-language :content-transfer-encoding; bh=B9QXeyjv+jTAHE5av8J43h1vkc7GvmMUlT5rWOunalk=; b=h51+MzEhZpIAhkp77CD9vwsQjaNbUwpdV7vL7kZMrSd3BWb+CM6qLu8Cm3/swGYlzr 4oSSGtN2PuS6pSDQFLLfFdCqu6YGw5EfHB0fa0wD0kDkdanKMuQQjhxRV8PJODpAO0sI KpsV6SzxtSTvhPtxz7Xam3+uSGwxwyubuq+Ffk+aUPIIvppTmVc9hcGWTgRfMzLOVTsZ VGecYmjydMXugzSZEtKRXtJG2yEjeabH9WcA4rmJTP1C3iSlw25VpfQacnLncrBzyncY UQJKZgETNOZ0ubVdf6CNU8ahDUZ/7rj1t0ZfLyqPbJoRLA3UMziFVN1wz4GERyweXz3b k7gA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:references:from:openpgp:autocrypt:subject :message-id:date:user-agent:mime-version:in-reply-to :content-language:content-transfer-encoding; bh=B9QXeyjv+jTAHE5av8J43h1vkc7GvmMUlT5rWOunalk=; b=BnOJ55wrdz/rpfT2a6MdRtPY6U9lHnASWUp4N5wd1H+Q6FjHg8NJFyvdO98yCzSiD/ xwMUA3RV4sqr8fzVjNypPWAvrrGcsxMJcnriI5q7SQmbFUr20wEJJT+w428vJQzvkr7o o0HIZMiEBm+7dGzUiwA6BbAI5G+zqsQR0As6v/hsShYnjwR2ZjNAMRzdam/XUtU2rM13 lnTyiqggHVBbTU5zoyqzwVE1SscLohhLVJrMjzRwzBdXe4/B4SXcrMWUMIM/ljwTZNao kZwyErrFMd8KGjAWMjB5OOTj9hZWm4NqDLs3unx6jSL25pXM8j4EKgLjuAKgNGW0Fhjg FGkA== X-Gm-Message-State: ABuFfojC8gug4WldeUmTQWPpFk/zDbqQXqKW4/zBF4V5IFmALNYDvVRu rKcLighFKxsrEgoU5z64IyML/dl85zE= X-Google-Smtp-Source: ACcGV62MkYPwL0f736KH/lIYxBB4PlJvw8JjZuaT/J2m/zNaj0KvLQF3F6zZxAf86VHADoTrRcM0bQ== X-Received: by 2002:a25:d6d2:: with SMTP id n201-v6mr11158549ybg.113.1539693405391; Tue, 16 Oct 2018 05:36:45 -0700 (PDT) Received: from [10.52.143.202] ([161.243.232.210]) by smtp.gmail.com with ESMTPSA id z20-v6sm4745968ywz.75.2018.10.16.05.36.44 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 16 Oct 2018 05:36:44 -0700 (PDT) To: =?UTF-8?Q?Olivier_Lepr=c3=aatre?= , pgsql-sql@postgresql.org References: <5bc34756.1c69fb81.22161.822d@mx.google.com> From: Joe Conway Openpgp: preference=signencrypt Autocrypt: addr=mail@joeconway.com; prefer-encrypt=mutual; keydata= xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzR9Kb2UgQ29ud2F5 IDxtYWlsQGpvZWNvbndheS5jb20+wsF8BBMBAgAmAhsDBwsJCAcDAgEGFQgCCQoLBBYCAwEC HgECF4AFAlpoksUCGQEACgkQMyt+aLaZQ0p4XQ/9F4WSf4ZFuL3ae356ApVqnLxphHXeXHli ePZe7qosvXMQLN9Y2m5cQwqojsQh4pZujIi4Iwxw7ORg6HbMO8dMhyu8TlDavErcgO561hG7 /TCcCLf5SkadxBqYWAJH09W8S2iC3gQtS+qs4Y7sIvRi881EsPzG5Lrzu7Ei8ufqsSnbLUQk zAaKjK3IzAFvzA3VCg/8Aqhf8obhqL0j5I5DNOpAt2U4gkEzIrX1g5xSXpRLMHLEK7hPViY8 pFQyRvyZioE4hckNsnidCgiv4HefJUFRIVxDkHBeLhTzqTPloesFT/bskhckIS6F4suMx044 WTTW6UiARuWw3aaBTmxay2rjPCsGaJPawzY0O3HlNlZherCzuBpa+Qk1kMLkcxMlYjCrMgIR YyBpIK46ltPQaog4Ujh21w1Z8m/ufG5v/HTuLdXM9hGfTlW7aY6psCAV/MwfycDHLRMc+UY7 W2cp6DlWQ7GHLP+vzvBcl5Odi04EhcYnHMZjKvRFsxhE0LZcTejrtxPkDgR9G59irlIx1+py ziK2B0hs7Er1Yq7qQGQiQGLoglSNDsvsUeyMGyabasmUd5xrRO35SpGv+hyYGAZ3Pyk9Vtxl CO7BJkZiV9YSMHp6hOdmYZd4SZRChd9sKd6eSw1xIlBALz1ZHuQVMBuZvuVGFsS81Zqckb/2 D5rOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMvav/KE Nd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFVdWnl w7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5VugWNl PZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv5ZhP RpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc2OrW o7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQbauD Qw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT0S5y OgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4eNrxU 0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA81uM j49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEIAAkF AkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4Fia3 79JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBGHwLE OTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZE0C1 xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6XlndFrDl Y22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl0ZJP 3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcHYebV JZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs5egE 3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1c9Jb PJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+1iJN Enp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q= Subject: Re: array problem with double quotes Message-ID: <2b14bcf3-a159-48e9-b5fd-eb3170331fc8@joeconway.com> Date: Tue, 16 Oct 2018 08:36:44 -0400 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <5bc34756.1c69fb81.22161.822d@mx.google.com> Content-Type: text/plain; charset=windows-1252 Content-Language: en-US Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 10/14/2018 09:40 AM, Olivier Leprêtre wrote: > Is there a way to create arrays without postgres adding double quotes > when there are commas ? > > select v1,v2 from (values ('co',array[['ab'],['ab,da']])) sub (v1,v2) 'ab,da' is a single string literal element in the above SQL > where I wanted > > "co";"{{ab},{ab,da}}" If you do this: ---- select v1,v2 from (values ('co',array[['ab'],['ab','da']])) sub (v1,v2); ERROR: multidimensional arrays must have array expressions with matching dimensions ---- Doesn't work because you have differing dimensions. But these will work: ---- select v1,v2 from (values ('co',array[['ab',null],['ab','da']])) sub (v1,v2); v1 | v2 ----+--------------------- co | {{ab,NULL},{ab,da}} (1 row) ---- or ---- select v1,v2 from (values ('co','{{ab,null},{ab,da}}'::text[])) sub (v1,v2); v1 | v2 ----+--------------------- co | {{ab,NULL},{ab,da}} (1 row) ---- HTH, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development