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 1os8vN-00020I-6U for pgsql-general@arkaria.postgresql.org; Mon, 07 Nov 2022 20:40:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1os8vL-0007BW-6i for pgsql-general@arkaria.postgresql.org; Mon, 07 Nov 2022 20:40:51 +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 1os8vK-0007BN-QQ for pgsql-general@lists.postgresql.org; Mon, 07 Nov 2022 20:40:50 +0000 Received: from mail-oa1-x29.google.com ([2001:4860:4864:20::29]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1os8vE-0008NJ-1L for pgsql-general@lists.postgresql.org; Mon, 07 Nov 2022 20:40:50 +0000 Received: by mail-oa1-x29.google.com with SMTP id 586e51a60fabf-1322d768ba7so14055028fac.5 for ; Mon, 07 Nov 2022 12:40:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=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=ca4Kp4B37uhntM5d/rjNTU7lYNmHmzUCUsn01l2Sj7M=; b=DCitrTUXRHYHXu251hOD2f0CaIbFxCDyKbxHCx4hoFUGBCE4jQ/IRoGkm8WwSXBPRL EzO/pQ5uhUaP9e8kKSDoY26hXiRsY9HFcui7HNBBe8XzXHh3eKGnvauyQBa2RAWRJmkL KkK+D6mCAxcslYw/+6zhmCZrZm6SyKWykp9VFLxWYkj43OwQEzgeBkDL+PNUysCX7EDx otvx9vTLmv+l+JYfLyn9ykP+6UPh+iSiejqTZ7lHSYO7vgsSmvkgoOpFvbsniIxkU6Kn Ni6sMw+qeVDD19Ky78k+4dUpWivI0gBwD5CfgQHpabUYYjRRysrblKBZOH7lH4+7gscn 5jGQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=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=ca4Kp4B37uhntM5d/rjNTU7lYNmHmzUCUsn01l2Sj7M=; b=kY2RQZqZw15uXYvxuMNpqRP7yLSQ6USkviRbNbDdRTfTp0lx/7/Dl7StSgI0QSxUXW +/OZvojL6IXl3/FZ37F2HACsv7H14Q30E2DtqteaDbIcXI8oz5cbSBIhjPtvKzA+UzaX GdTEkUHJmoz8Us8tNmVRRDhbRgU3/oJoK8rXA8QJWeSZkg1FRnBxmCB2XUsfvs/hKCpj kn67fBeS/TTZbn4pJQAYqUOn28+lZ49RExlh1YC0TQq4VaDJVBX09j+7OJAAC+qcvAbK WLtan9c9sXRcXyHZelLiC0f2RyzpxCj8hjNzl6L5Snn9dZa7WYgwCIkZhRSDUMP/5KM5 s0kQ== X-Gm-Message-State: ACrzQf2rDjmIShd9qMWOYWRuQkNG94WpbGv6LNhJKCNSdoRqPqEFO6Vc rEY0WEK0uzxYOEMkvBJZrtUGJkpZIEI= X-Google-Smtp-Source: AMsMyM4TUmJglzsp0RQv19Yxet656kt+Iae2hGlP9J5g323nOwmdKH0OqatFCnpeIPeEv4gTbRqB8g== X-Received: by 2002:a05:6870:9a27:b0:140:6f4c:f532 with SMTP id fo39-20020a0568709a2700b001406f4cf532mr11141420oab.209.1667853641561; Mon, 07 Nov 2022 12:40:41 -0800 (PST) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id o30-20020a056870911e00b0012779ba00fesm3645015oae.2.2022.11.07.12.40.41 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Nov 2022 12:40:41 -0800 (PST) Content-Type: multipart/alternative; boundary="------------LIhZTJoRCFXCod011TIqLI0Z" Message-ID: <34c71bc8-e4cc-05f8-c0e5-211754c55940@gmail.com> Date: Mon, 7 Nov 2022 14:40:40 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: copy file from a client app to remote postgres isntance Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------LIhZTJoRCFXCod011TIqLI0Z Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/7/22 10:57, Вадим Самохин wrote: > Hi all, > I have an application that must copy a local file in csv format to a > postgres table on a remote host. The closest solution is this one > (https://stackoverflow.com/a/9327519/618020 > ). It boils down to specifying > a \copy meta-command in a psql command: > |psql -U %s -p %s -d %s -f - < |and executing it. B|ut it's quite an unnatural way to write database > code. Has anything changed in the last ten years? Or, is there a better > wayto copy file contents in a remote database? I'd write a small Python script, using the csv module to read the data and psycopg2 to load it. -- Angular momentum makes the world go 'round. --------------LIhZTJoRCFXCod011TIqLI0Z Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 11/7/22 10:57, Вадим Самохин wrote:
Hi all,
I have an application that must copy a local file in csv format to a postgres table on a remote host. The closest solution is this one (https://stackoverflow.com/a/9327519/618020). It boils down to specifying a \copy meta-command in a psql command:
psql -U %s -p %s -d %s -f - <<EOT\n here hoes a \copy meta-command \nEOT\n

and executing it. But it's quite an unnatural way to write database code. Has anything changed in the last ten years? Or, is there a better way to copy file contents in a remote database?

I'd write a small Python script, using the csv module to read the data and psycopg2 to load it.

--
Angular momentum makes the world go 'round.
--------------LIhZTJoRCFXCod011TIqLI0Z--