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 1rPq2j-00Ea4w-TF for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 20:28:18 +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 1rPq2i-007VQk-VV for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 20:28:16 +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 1rPq2i-007VQJ-LA for pgsql-general@lists.postgresql.org; Tue, 16 Jan 2024 20:28:16 +0000 Received: from mail-oi1-x22c.google.com ([2607:f8b0:4864:20::22c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rPq2f-002Az8-Oi for pgsql-general@lists.postgresql.org; Tue, 16 Jan 2024 20:28:15 +0000 Received: by mail-oi1-x22c.google.com with SMTP id 5614622812f47-3bd884146e9so839273b6e.0 for ; Tue, 16 Jan 2024 12:28:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705436891; x=1706041691; darn=lists.postgresql.org; 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=eJflIkRdyvnIFjfZPlFjZZ1U9yDoArGImdavhTjpTo8=; b=e9L3s7XGiN2OXQVKm4NQlAt/72/4tsh+QFE13g7W4A+uU2mnWJOpXVCq38u+ePqBE1 y7HBd4SpTXbrX6iCaUoIxFndpyqZKDy9muf0fZn6y4PuztMPKYHdzNle1Fwftq8FL4rj pFBJACocUzGXM2Sua17Kbt3X4FL7URP+72gE/QNv+tHsWrpEt2C5TQJOfjmGio+UOx+7 RQBY1CZnt3Hgno0mxfIfO+4y+pLFWfesiMH9Tk+Q5+YaxBoCGO/rFGcsdorlDPHNCME7 U/fuAi0dYoD5mEhRlr3qNy3sq4X12ln6B5cfj/C8NKgvUoDr+VyBj+kewRO/YAqqyObL XvdQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705436891; x=1706041691; 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=eJflIkRdyvnIFjfZPlFjZZ1U9yDoArGImdavhTjpTo8=; b=YKOYbkNvRQV1KKwUS4vvcRj5HjGE01cmV1yP0W6kitMdzRkJ+ssbe934XRksM+RJkc zMcqwV1YWrVfx+x3AAGkFVz3GSkpnc7Tf4J0wmFo1RyZfmhz3crCIDO1QX8i3Pi9wgUO /yOVlvYWAK29AvKH7kdu5z8NQkrHYzx02xa7/yKcEDVU7X05iV7tkhukov0ITEmLYnzH cMAPAhRuWpWBntvijk/lrG/YpN5yOZroH9fPWxtVsQ5Cc4jyBlJv8TmmeKrICuBeBFXe horxgGoEzrTD+pb0hx2jSIbZEip7eqfeOFO4IstZFTqQsugiYXmk+d5KYHx14860AFY3 yCnQ== X-Gm-Message-State: AOJu0Yx99GVmXUuAH6V/HvMu6j6UZeHvqT4O/USQz151w6TKtwD3m7e6 u0xb+hNHcktDn25aZYDSHw== X-Google-Smtp-Source: AGHT+IH+PB3An9u4OP4hMXBE+3bBH2rBBxqDjhv+ff6JV7gLk+drCYAeSsn0aPVRJCIZIUg8ptwPLw== X-Received: by 2002:a05:6808:1203:b0:3bc:2492:3672 with SMTP id a3-20020a056808120300b003bc24923672mr9007714oil.56.1705436891690; Tue, 16 Jan 2024 12:28:11 -0800 (PST) Received: from [192.168.2.16] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id i16-20020aca2b10000000b003bd5add1737sm1585266oik.55.2024.01.16.12.28.11 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 16 Jan 2024 12:28:11 -0800 (PST) Message-ID: Date: Tue, 16 Jan 2024 14:27:58 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: postgres sql assistance Content-Language: en-US To: arun chirappurath , pgsql-general@lists.postgresql.org References: From: Jim Nasby In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/16/24 6:34 AM, arun chirappurath wrote: > I am trying to load data from the temp table to the main table and catch > the exceptions inside another table. I don't have a specific answer, but do have a few comments: - There are much easier ways to do this kind of data load. Search for "postgres data loader" on google. - When you're building your dynamic SQL you almost certainly should have some kind of ORDER BY on the queries pulling data from information_schema. SQL never mandates data ordering except when you specifically use ORDER BY, so the fact that your fields are lining up right now is pure luck. - EXCEPTION WHEN others is kinda dangerous, because it traps *all* errors. It's much safer to find the exact error code. An easy way to do that in psql is \errverbose [1]. In this particular case that might not work well since there's a bunch of different errors you could get that are directly related to a bad row of data. BUT, there's also a bunch of errors you could get that have nothing whatsoever to do with the data you're trying to load (like if there's a bug in your code that's building the INSERT statement). - You should look at the other details you can get via GET STACKED DIAGNOSTICS [2]. As far as I can tell, your script as-written will always return the first column in the target table. Instead you should use COLUMN_NAME. Note that not every error will set that though. 1: https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMAND-ERRVERBOSE 2: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS -- Jim Nasby, Data Architect, Austin TX