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 1pUPOo-0006J1-BC for psycopg@arkaria.postgresql.org; Tue, 21 Feb 2023 09:57:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pUPOn-0007KB-9k for psycopg@arkaria.postgresql.org; Tue, 21 Feb 2023 09:57:25 +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 1pUPOn-0007Ji-18 for psycopg@lists.postgresql.org; Tue, 21 Feb 2023 09:57:25 +0000 Received: from mail-oa1-x2e.google.com ([2001:4860:4864:20::2e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pUPOi-0006zO-VW for psycopg@lists.postgresql.org; Tue, 21 Feb 2023 09:57:24 +0000 Received: by mail-oa1-x2e.google.com with SMTP id 586e51a60fabf-17213c961dfso3926637fac.0 for ; Tue, 21 Feb 2023 01:57:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=WAfGzCN0oRxMQ5yFo5Ig1fvA95z56T4W9hRgZ8AWL6Q=; b=FOL2+IHNmPLB6HtdzjikHt3HOjtOEEvEI4ApjuZpAuBvoWEa3GKYSmIOonCwb6+JX2 ng0V0c6KMcvcgVwaYkOdq7tpDgr/yJ3/cQL0j2YazW5nubltBuVyXC028prkguzPch7o n6lkeegAESa2G3iT11A/AS54dyg8d/T2N+VRt5pvkQTaJm5COQOHePEJNchTpP6D3+Wr lLGMGX9LBB1pJjtdn6cp2rNvk7AcTYF7+IdhYavwxtatRlsjGfaUxZrDoLKgvDKYouGP p80I3p7YiqT2SjmoAECR+JkSZ/CoBp2jBTUtbjLo2ZLvAVc24NBhRrJsA+fhp0bDXpX/ ZdeQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=WAfGzCN0oRxMQ5yFo5Ig1fvA95z56T4W9hRgZ8AWL6Q=; b=Ydanss7hw8Say6ERE+25l6UH2724aiNYLEr5wzmoiY5XjFYxe0P0yOIc25Bc70DGWn 99LLsTOiNe/lpKsDsbaKbEf5wlwwka/n0C+r/3m8/Unvd2/deo7Nnv8GE6Ucv63jvUAh HkVQ7pW9jD1CUhbPqW9MKOoybOgDZRIO4O+OE2Q62HtWgc7l38POl93S9MDGxyx5Ev6K la1NKeEcfMx4crAwEV9KC//PVZ7O+qXZF90clwP7zND+rfyva/yzYqf96UjHgy62OlMS lv+LGnhMHUWfG7FlINuYWBqUQj2NnpftuLv8mtpwdWLI+yifcUd7zm9THPDuKsKcuS0b 2H9w== X-Gm-Message-State: AO0yUKXxlfVNWEYyYHKaJre1CbVu5w4nJkpCZ/9IRkNBWwG3BD3+2wfL +WC6tnv6lK+K1SLb8e7CmoCl+AUfB9t9iH6Ecns= X-Google-Smtp-Source: AK7set9cGSl/xFb2OHcCbZUKlNTH5njbA/VGl9KNBPppDvJOAmgHIdO4bEMEsRBkvKowpGBZk8zL0NrWCCHbyctM7Qg= X-Received: by 2002:a05:6870:c98c:b0:16a:a605:b047 with SMTP id hi12-20020a056870c98c00b0016aa605b047mr600247oab.69.1676973437480; Tue, 21 Feb 2023 01:57:17 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Daniele Varrazzo Date: Tue, 21 Feb 2023 09:57:05 +0000 Message-ID: Subject: Re: Escape Python to `json[]` for `COPY FROM` PostgreSQL insertion? To: Samuel Marks Cc: psycopg@lists.postgresql.org Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 21 Feb 2023 at 05:34, Samuel Marks wrote: > > How do I insert into a table with a `json[]` column using the `COPY FROM` syntax? you can just use psycopg 3 and pass it the records as tuples with the objects you want to copy. The values of your json[] fields can be a list of `Json` wrappers. https://www.psycopg.org/psycopg3/docs/basic/copy.html#writing-data-row-by-row https://www.psycopg.org/psycopg3/docs/basic/adapt.html#json-adaptation If you want to know the escaping rules, they are in the Postgres documentation: https://www.postgresql.org/docs/current/arrays.html#ARRAYS-INPUT -- Daniele