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 1osqGT-0001Ya-BM for pgsql-general@arkaria.postgresql.org; Wed, 09 Nov 2022 18:57:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1osqGS-0004R9-7Q for pgsql-general@arkaria.postgresql.org; Wed, 09 Nov 2022 18:57:32 +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 1osqGR-0004MH-Qw for pgsql-general@lists.postgresql.org; Wed, 09 Nov 2022 18:57:31 +0000 Received: from mail-oa1-x31.google.com ([2001:4860:4864:20::31]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1osqGN-0007lt-Lf for pgsql-general@lists.postgresql.org; Wed, 09 Nov 2022 18:57:31 +0000 Received: by mail-oa1-x31.google.com with SMTP id 586e51a60fabf-13b6c1c89bdso20628464fac.13 for ; Wed, 09 Nov 2022 10:57:27 -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=l/RbN3h2FZCYJO8ayeTYwZaePmb6yHBKvz5b0IjCSf4=; b=BT8lR45fFTXcTK7jwyh8ivJUM/DfBw42vpu16NA26Is3Pnlw0caaxB2zSCM1CtHaxe dRiL7Quh6lrgNNXWSQpAgdu5CaDjwIIPQJy/FODMiivF8t+omgYPbJ72qAN7LLueY6pv FKNjkGSyFpMXV0BV9QbMWwQ/mxOzF25XCzCYSgELqysvZAi+GtIpf9YUsYjVJRPyGMr+ GMnnwNTl0z9ZuiQGvbKgtoEuc/2u2+dWj/Uh5EmZE5mdu1iXCcjX1rv2g6/G6DEXlNa+ PG8F5JPotOnrV/ct7HrtPVkkgdlUtLOQU+welQRovGWaXHOxwvVCNmkEfIiFHmZ5mvzM ILMA== 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=l/RbN3h2FZCYJO8ayeTYwZaePmb6yHBKvz5b0IjCSf4=; b=6NbsXQ8j+0NmMzMxLnibi5TZQ9rtNgwjtXB8n2CJQya8K74u7GNAjBd5rafuEkRLOf mGEc+5Sbxu1uCRc1q6ezinWsGyzGSKNT6iKyHkG/SpjnfZPgwoLKlp1TkLeHU35UsxhH oRLjU/uwaU+IzckUcGi0rvkpEgbbfzF+WjVbpVEYIPSKSHtn04YpNrCQH1mz6A2CwUmX 6yZGLy03gdCoqJFm3Ig2ri2OqLSogoay6SzBjJu/8QnZJhLxE8zJGapm3GxaE1ebazRj z5wa6ga+lApg4lXfqcMZSzoVt6gce0mc9XNqzE9IVTVtVqJ5fuTNUG7xSxcC2CCnavlO Ni4g== X-Gm-Message-State: ACrzQf1pdkmZqLc0mAzmX5YZ92JahqOw9rpXOdHaZCBD45nZvvVIUT1F eybfljObz9yW2B1karn0HTq8fz5m6Y4= X-Google-Smtp-Source: AMsMyM4LyCRBgQS846+9nHopfVf4JDRXnVBfWp0qPX1eRuZVNTrnb84A/9khNE4F1Wab7a6Hjiw4Yw== X-Received: by 2002:a05:6870:1607:b0:131:dcec:98f6 with SMTP id b7-20020a056870160700b00131dcec98f6mr34911450oae.162.1668020245143; Wed, 09 Nov 2022 10:57:25 -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 d10-20020a056870e24a00b0010e73e252b8sm6531773oac.6.2022.11.09.10.57.24 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 09 Nov 2022 10:57:24 -0800 (PST) Message-ID: Date: Wed, 9 Nov 2022 12:57:23 -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: <34c71bc8-e4cc-05f8-c0e5-211754c55940@gmail.com> <20221109161759.6bnobepy6cfpl5ig@hjp.at> From: Ron In-Reply-To: <20221109161759.6bnobepy6cfpl5ig@hjp.at> 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/9/22 10:17, Peter J. Holzer wrote: > On 2022-11-07 14:40:40 -0600, Ron wrote: >> On 11/7/22 10:57, Вадим Самохин wrote: >> 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. 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. > If you use insert statements it will be significantly slower (which may > not matter for small files or one-off actions). If you use copy_from() > you don't have to parse it (but then why use Python at all?) If OP does not want to embed psql in his app, then he must find a different solution.  Python is such an option. -- Angular momentum makes the world go 'round.