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 1oxaGT-0004du-AD for pgsql-general@arkaria.postgresql.org; Tue, 22 Nov 2022 20:53:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oxaGR-0003Oc-MM for pgsql-general@arkaria.postgresql.org; Tue, 22 Nov 2022 20:53:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oxaGR-0003Mn-9F for pgsql-general@lists.postgresql.org; Tue, 22 Nov 2022 20:53:07 +0000 Received: from mail-il1-x12c.google.com ([2607:f8b0:4864:20::12c]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oxaGP-0006Xo-5C for pgsql-general@lists.postgresql.org; Tue, 22 Nov 2022 20:53:06 +0000 Received: by mail-il1-x12c.google.com with SMTP id h17so3537899ila.6 for ; Tue, 22 Nov 2022 12:53:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=tJHOgoM73cGg4aSAUNoCxBu8ccA0DPZG0riPLBr26xg=; b=APVYK2Agl0ARsjjK0TSLnUH+1GwguL/7N6nW9r15XhPNZB1tVP0lHGLHZlY//7gUif z0DavmUHaneY5/j4JRuFBquh2E0yGc8Un0mCWzxMT8DfQse8h8yiEHruZx1k552l2YJq LZ3XP4F2tWvGXFPAD2F2/VjxcQCoYbkvbmjKdEfQ1qHwkoH14Ok/k3LrLO9XXALVrWgU XMONpONlHGuELHqE2e5R4B/s7scBhCSkQTisViKxiIXI39qOGgm2UTe1JAftpgxaeeE4 7S9NSUwEnu6Ynu5TA+ykIty9lSYbJx8UjzVmNJjmMMYIDBe+7Jm0ljF//CLU6bMRUHI0 +YHw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=tJHOgoM73cGg4aSAUNoCxBu8ccA0DPZG0riPLBr26xg=; b=pLYnaNVmnCo2QYgnXYk9qzwJKmqwHkTqnkGd5WhDLRKdVVTYDGZ/0FnOC1I7i0Sma4 tLv55c3Xe7wFMfxpeCRDvc5voBRl1Fqq1CL2Txh/lC6Eeog6DxCsmfDlRPXaiMbX7plG dh/rWZSDPIee3b8clCK89i6pbtKk04LvfBqnzriLUno+Z/Wq7Z3JqXLyBPnqq9ZjHt6r gM/dUvYgf6nIR0rkcacmepXLdVnlcArdVoWx5fBw4lAqleN5vxsgs5a1uOpbIOKkCPfx 2rrEFKws6Z5NPx0H7GskuEnNF25DlahfEMAlg/DXfjhWK2Bq7xaXBZ1q5hgpUBoT29rY +rAw== X-Gm-Message-State: ANoB5pmIkN4FQUeNRfntLM4RA1bP3PBGdUYIAwxr6KZ7w5YZodVAx1HB 9TSD2ue41+PO2haxvffRdkovVtasG2g= X-Google-Smtp-Source: AA0mqf68DU6kUT6UMFigpmU+QB7EEBBRDWZpKKgPVCyPDQiiSRXisNIWTeFx6ZaI2q6IZVCxN6QyCw== X-Received: by 2002:a05:6e02:6c4:b0:302:3d55:7a40 with SMTP id p4-20020a056e0206c400b003023d557a40mr11240198ils.254.1669150381970; Tue, 22 Nov 2022 12:53:01 -0800 (PST) Received: from [10.10.50.239] (72-46-51-178.lnk.ne.static.allophone.net. [72.46.51.178]) by smtp.gmail.com with ESMTPSA id q23-20020a05663810d700b0037532823a17sm5620979jad.122.2022.11.22.12.53.00 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 22 Nov 2022 12:53:01 -0800 (PST) Message-ID: <360c4ab3-0ac7-2e6a-a40a-383c226df6eb@gmail.com> Date: Tue, 22 Nov 2022 14:53:01 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.0.2 Subject: Re: Upgrading to v12 Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <9aa1a829-3367-74cf-1630-a91a4d67d41d@aklaver.com> From: Brad White In-Reply-To: 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 On 11/18/2022 6:34 PM, Adrian Klaver wrote: > On 11/18/22 16:05, Brad White wrote: >> >> --> The Microsoft Access database engine stopped the process because >> you and another user are attempting to change the same data at the >> same time. >> >> Code in question: >>       rst!Update  <-- success >>       rst!QtyDeliverable = rst!Quantity >>       rst.Update  <-- fails here >> The wisdom of the internet says that this is most likely with a BIT >> field that has null that Access can't handle. But that isn't the case >> here. Both are int4 fields and both have values before the update. > > > The new PostgreSQL timestamp data type defaults to microsecond > precision. This means that timestamp values are stored like 2002-05-22 > 09:00:00.123456-05. However, Access does not support the extra > precision, so the value that Access uses is 2002-05-22 09:00:00-05. > When one tries to update a record, one gets the error message above > because the value that Access uses in its UPDATE query does not match > the value in the PostgreSQL table, similar to the NULL vs. empty > string conflict that is already reported in this FAQ entry. " > > The above is the problem I usually ran into with Access and Postgres > and updating. > > Is there a timestamp field in the record you are updating? > UPDATE: Yes, there are 5 timestamp fields. It seems unlikely to be the culprit for 3 reasons. 1) It worked fine in v9.4 2) It worked the previous 4 times I saved that record in v12. 3) As the data came from Access, there is no data in any of the fields in the last three decimal places. ex. 45.234000 But as it is the best lead I have, and it could still be the culprit until proven otherwise, I'm working to convert those 5 fields from timestamp to timestamp(3). Of course, PG doesn't allow to edit a table with dependent views. Which means that I'm attempting to modify a script that will allow me to save, drop, restore the views. Of course, PG coerces all table and field names to lowercase unless quoted. So I have to figure how to recognize all table names and add quotes. This table is core to the app, so a LOT of the views reference it. I may not be done anytime soon. Have a good vacation!