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 1os6Ul-0003Z0-M4 for pgsql-general@arkaria.postgresql.org; Mon, 07 Nov 2022 18:05:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1os6Uk-00077i-GC for pgsql-general@arkaria.postgresql.org; Mon, 07 Nov 2022 18:05:14 +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 1os6Uk-00077Z-2J for pgsql-general@lists.postgresql.org; Mon, 07 Nov 2022 18:05:14 +0000 Received: from mail-pl1-x62e.google.com ([2607:f8b0:4864:20::62e]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1os6Uh-00079N-F8 for pgsql-general@lists.postgresql.org; Mon, 07 Nov 2022 18:05:13 +0000 Received: by mail-pl1-x62e.google.com with SMTP id l2so11748292pld.13 for ; Mon, 07 Nov 2022 10:05:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=xRPtW7VFFnnN/yI0bTCSg3RKddCL8A31PbI5FaNg/DE=; b=LExi3e093upISsB+zGAAz2nAA5LCHWKlQMJ1ISBDyoCWNGH5z1GEROkBwEX7Lf+NCy ds4ANfyaoFqgmby4vfk/0E+NFDwzZk5Mtsj+4zhU9viTK/eIH0RTyt6HCoH6GXrgD6u7 G1otf1ofTMNL1TAIsUnCnyqm4MvubR4aBPRELwyBB/iXCk3HB0cdtkO/H/EB5vzrjCq9 A6Yt2ORnYMZwD1LmGlhV5x1YfihWBz2+N9/TAnHyVaPBmSTpxvaaisQSEY440XdDZnyi Ym1gy3bDXiz7e7dN0pJYA4KyXTwtrQFhTYniexru0ouM7NhVxLyc0jkHSRfZSRINy+46 KJ5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc: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=xRPtW7VFFnnN/yI0bTCSg3RKddCL8A31PbI5FaNg/DE=; b=72Xt5WKsyVB+ZMJBWrwDJaQCYZ5bUN+R89SHkyVywVje3NVWt6l/BjJfl+hG0TIeJV aj10mTsF14Rv8mIaFrUNyree5vjWZbXuonZDAiFe1F6KOEX4BbtspQhVT7kKExVp6bKS q3I7oWJjCLNLM4+QwpfMHW7aDLZ5/HkvsALpUJucOaQ8nHta6I+UtIaKu+sBBEX8s9G7 5B6aPGHxHdw8SHgn7Un9IwMBThiY03Jjg/TAkU9BJuJvw2ugbMOIoS2mi6FySnv+HJjF rbjALInbt+BQC/vmPBvRl/S//R4wI5oJCYXJrl2G5I4NgSZBXk5YO7Xxu+5wSx28bhOn ZTQA== X-Gm-Message-State: ACrzQf1ouqSqjUw+NE+/PMWpMfHgNHpHatLbPBD62bze+G0q/CSCo9nd EZHkcgAdzukT/R7HHfEfAyo= X-Google-Smtp-Source: AMsMyM5BeYe/cFUaD9HW+2JryKqikSA7mgEgmjRpvmRbpiRud4ltQecfpZJbwZ3T4sB73jB00lM4/g== X-Received: by 2002:a17:902:e8cd:b0:186:9efc:6790 with SMTP id v13-20020a170902e8cd00b001869efc6790mr52027192plg.91.1667844309123; Mon, 07 Nov 2022 10:05:09 -0800 (PST) Received: from [10.128.56.149] ([155.98.131.5]) by smtp.gmail.com with ESMTPSA id u9-20020a17090a3fc900b00200461cfa99sm6463253pjm.11.2022.11.07.10.05.08 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 07 Nov 2022 10:05:08 -0800 (PST) Content-Type: multipart/alternative; boundary="------------kn0ViD7YRYaFws0GoCeurI07" Message-ID: <8faa81f6-cce4-f009-c035-d8e0e15e4760@gmail.com> Date: Mon, 7 Nov 2022 11:05:07 -0700 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-CA To: =?UTF-8?B?0JLQsNC00LjQvCDQodCw0LzQvtGF0LjQvQ==?= Cc: pgsql-general@lists.postgresql.org References: From: Rob Sargent 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. --------------kn0ViD7YRYaFws0GoCeurI07 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 11/7/22 10:51, Вадим Самохин wrote: > Well, actually, just ordinary 3 tier architecture. Simple UI connected > via restful API with backend written in php, which copies some data in > a remote database, that's pretty much it. > > пн, 7 нояб. 2022 г. в 20:30, Rob Sargent : > > On 11/7/22 09: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 - <> meta-command \nEOT\n ||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? > There are bulk copy routines available. What is your architecture? > We generally "bottom post" in this group. Most things I find on the web suggest send the csv file to server and run COPY there.  Some show iterating over the csv, but critical to get csv off the client. --------------kn0ViD7YRYaFws0GoCeurI07 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 11/7/22 10:51, Вадим Самохин wrote:
Well, actually, just ordinary 3 tier architecture. Simple UI connected via restful API with backend written in php, which copies some data in a remote database, that's pretty much it.

пн, 7 нояб. 2022 г. в 20:30, Rob Sargent <robjsargent@gmail.com>:
On 11/7/22 09: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?
There are bulk copy routines available.  What is your architecture? 
We generally "bottom post" in this group.

Most things I find on the web suggest send the csv file to server and run COPY there.  Some show iterating over the csv, but critical to get csv off the client.


      
--------------kn0ViD7YRYaFws0GoCeurI07--