Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9QIz-001cJv-V6 for pgsql-general@arkaria.postgresql.org; Mon, 28 Apr 2025 15:22:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u9QIy-001nMM-1f for pgsql-general@arkaria.postgresql.org; Mon, 28 Apr 2025 15:22:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u9QIx-001nME-NP for pgsql-general@lists.postgresql.org; Mon, 28 Apr 2025 15:22:00 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u9QIw-0001Ue-0s for pgsql-general@lists.postgresql.org; Mon, 28 Apr 2025 15:22:00 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-2c2ada8264aso2712299fac.2 for ; Mon, 28 Apr 2025 08:21:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1745853716; x=1746458516; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=50CxmockMJAfg1WVBlmfX9q+TbEhD5fekop00RvroXM=; b=AP7M2xuLYCKHfqyRKO96LXHrCx1Q4ItA0m0L4msnobhpFtwR70FEOwmW9nFg6sx4bY U9nk/FKYbavoQStYedcwGRK0MGgSoHvrBvm6k0ECVodXWyQrxtUE4CwQBZIcchcoAT2T Zp5fV/frQx5GRXWUbYlUIhTnaSbiSjd1qD+FFKnXKk19tvl+hsDfwPlG61AMHTyJI8qb 06JB2V8QngqhTpnVrylL2kb9LsyHOZD84RPileQs/JEXVn1VekVdQFwPiTXrm8dKB+kM tTBKoyO33OYMFEdhF0zPMLFEw0049PlvnVJfRsf29rMIwtBCOnAHx7/Q4ccG6o1F2MIU SIrw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1745853716; x=1746458516; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=50CxmockMJAfg1WVBlmfX9q+TbEhD5fekop00RvroXM=; b=pJEc9zv3IFY94BY4M/g9K7887AJ6rVG2hj8McgRFJmg1Bm1I8In8hqpoQOwIXbqI+p /M6zlfIh/hDyVFtVmaK+VdKxIj0JuMSKumX/XHU98AZIhbGVJSXOItkwrGzedE0CbmBx 5HvlKazJpsqEmaE1ZNwT42ZJDj8JUppPVZ8hmBsrpda9rJe07TuQFf7sCKsGijiDkDOr xAjXdClG57gJnXAEYrOh6l+/QLuYF1aTfRR+O++O86iY9+whTGy/Cqhoeg5Qy+v431av pttQ7n4i0zgTKTfFxj5jeYTY7jE6MDnvJ/mHM1D44vU13/rJZKxk5ZZsfHJZj9u4h60W YIRA== X-Forwarded-Encrypted: i=1; AJvYcCUv6jlXSak37QwZi2aap1mKkea+6GpQtXR8RDQqf5IOVkyUUTDBKT7XP+QkFw78Nq7b0waMWGaHwdXMVMK3@lists.postgresql.org X-Gm-Message-State: AOJu0YwChDUDIYSMh49xb/orVt5DALHn+LvtJSa6C7d9qrUDdJQW6Abq UvpeTA1qefs5AuktnAtA3WR3dDoDVPSigLTRSRTwZG6gYfUNPjfGaVFNpRcORsPjSLq4K4vrlQJ n2QTynN3eIPLZIbtqe4vlhzLqNX8= X-Gm-Gg: ASbGncsuJB1fhKZFLyVUaq6VNX0T3FswuUZH84/bWckZ0EJFTM+UyPoeXBfoRtuFMCi jC2ux7iisGW/4fpsBV1bVXbKOnOqFTrfgWGEYAuNI23+21wQimZ/RCBoDfa0a+JEI0Dk8niVXf2 ummVk65euyZRwSoLABJfUj X-Google-Smtp-Source: AGHT+IG56rsZDwlSrqeNbePJch7Be88JgGXbAjggq9+OcUrTVdcVGZ0XK2SF4ah2YQbFbyMVI7YgA3XSpmM/7f9OTaY= X-Received: by 2002:a05:6870:164a:b0:2d6:72a4:4c24 with SMTP id 586e51a60fabf-2d99de4dffcmr7093613fac.32.1745853715684; Mon, 28 Apr 2025 08:21:55 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:6802:1e0e:b0:589:13f9:e937 with HTTP; Mon, 28 Apr 2025 08:21:55 -0700 (PDT) In-Reply-To: <774865.1745848449@sss.pgh.pa.us> References: <697017.1745837694@sss.pgh.pa.us> <80f95342-cc09-4236-a2d7-68538fbfc41b@wikimedia.org> <774865.1745848449@sss.pgh.pa.us> From: "David G. Johnston" Date: Mon, 28 Apr 2025 08:21:55 -0700 X-Gm-Features: ATxdqUEGyi4-E4HjbQdv9d1XkkslWu0AMRIawu5jExeDfEKkNht9Dcb2APzszG4 Message-ID: Subject: Re: Upsert error "column reference is ambiguous" To: Tom Lane Cc: Tim Starling , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000008da47b0633d83e0d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008da47b0633d83e0d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Monday, April 28, 2025, Tom Lane wrote: > > AFAIK, "ON CONFLICT" is a Postgres-ism. Exactly which constructs > in exactly which other databases are you citing as precedent? > I confirmed the SQLite reference from the original email. =E2=80=9CThe upsert above inserts the new vocabulary word "jovial" if that = word is not already in the dictionary, or if it is already in the dictionary, it increments the counter. The "count+1" expression could also be written as "vocabulary.count". PostgreSQL requires the second form, but SQLite accepts either.=E2=80=9D https://sqlite.org/lang_upsert.html David J. --0000000000008da47b0633d83e0d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Monday, April 28, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
AFAIK, "ON CONFLICT" is a Postgres-ism.=C2=A0 Exactly which const= ructs
in exactly which other databases are you citing as precedent?

I confirmed the SQLite reference from the = original email.

=E2=80=9C= The upsert above inserts the new vocabulary word "jovial" if that= word is not already in the dictionary, or if it is already in the dictiona= ry, it increments the counter. The "count+1" expression could als= o be written as "vocabulary.count". PostgreSQL requires the secon= d form, but SQLite accepts either.=E2=80=9D


David J.

--0000000000008da47b0633d83e0d--