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 1pmKR8-0007XL-G9 for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 20:17:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmKR7-00024a-07 for pgsql-general@arkaria.postgresql.org; Tue, 11 Apr 2023 20:17:53 +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 1pmKR6-00024F-Gu for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 20:17:52 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmKR3-0007fn-Ot for pgsql-general@lists.postgresql.org; Tue, 11 Apr 2023 20:17:51 +0000 Message-ID: Date: Tue, 11 Apr 2023 23:17:43 +0300 MIME-Version: 1.0 Subject: Re: Guidance on INSERT RETURNING order Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com> From: Achilleas Mantzios In-Reply-To: <95fbd8c8-2f7a-38a1-4a41-b2ac3763d89c@aklaver.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Στις 11/4/23 23:06, ο/η Adrian Klaver έγραψε: > On 4/11/23 12:47, Federico wrote: >> Hello list, >> > https://www.sqlite.org/lang_returning.html#limitations_and_caveats >> >> Searching the archive seems that a using the INSERT SELECT ORDER BY >> form should be a better solution, >> so the above insert should be rewritten as >> >>      INSERT INTO t(data) >>      SELECT data FROM (VALUES ('a', 1), ('b', 2), ('c', 3)) as vv(data, >> num) ORDER BY num >>      RETURNING id > > Or > > with i as (INSERT INTO t(data) VALUES ('a', 1), ('b', 2), ('c', 3) > returning id) > select i.id from i order by id; +1 for this version! > >> Sorry for the long email, >> Thanks >> >>      Federico >> >> > -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt