Date: Thu, 25 Sep 2025 20:10:39 +0000 (UTC)
From: Brent Wood <pcreso@yahoo.com>
To: 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>, 
	Bryan Sayer <brysayer@gmail.com>
Message-ID: <1729237457.837174.1758831039332@mail.yahoo.com>
In-Reply-To: <80c44b4d-1185-478b-939c-62e0d4377106@gmail.com>
References: <80c44b4d-1185-478b-939c-62e0d4377106@gmail.com>
Subject: Re: Additional options for COPY from
MIME-Version: 1.0
Content-Type: multipart/alternative; 
	boundary="----=_Part_837173_1925973751.1758831039330"
Content-Length: 5679
Archived-At: <https://www.postgresql.org/message-id/1729237457.837174.1758831039332%40mail.yahoo.com>
Precedence: bulk

------=_Part_837173_1925973751.1758831039330
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

As I understand it, COPY is intended as a simple & fast way to get clean da=
ta into Postgres.
It is not intended to groom data during the process. There are a variety of=
 ETL tools that can apply filters & rules to data streams to do what you ar=
e asking, so no need to invent another Postgres specific wheel.
That said, there are some ways to do this without a formal ETL application.
If the data rules you want to apply can be provided in a script, an approac=
h I use is to pipe the data file through a set of checks & filters into a C=
OPY command reading from STDIN. Very effective on Linux, Windows doesn't re=
ally do this very well.
eg: to change any instance of Grent in your data to great, use:
cat <file> | sed 's/Grent/great/' | psql <db> -c "copy <table> from STDIN w=
ith delimiter ',' null '';"=C2=A0
You can chain as many awk, sed, tr, grep, etc commands as you need together=
 to transform the data as required on the way to COPY
A Postgres only approach that=C2=A0I've also used is to create a loading ta=
ble with columns to match the incoming data, but each column is an unconstr=
ained text or varchar. Then modify these data to remove any errors using SQ=
L, finishing up with a select to insert the cleaned data into the final tab=
le. If the final table has appropriate constraints to validate the data, on=
ly valid data will get there. As you find errors, you fix them & rerun the =
insert until it works.
HTH,
Brent Wood

=20
=20
 On Friday, September 26, 2025 at 07:49:09 AM GMT+12, Bryan Sayer <brysayer=
@gmail.com> wrote:=20


=20
=20
Hi,

I'm not sure if this is the best list to ask this (and I am very new to Pos=
tgreSQL) but I think more options are needed in the COPY from command, in o=
rder to better deal with exceptions. By exceptions I mean data not consiste=
nt with the format, empty rows, extra delimiters at the end of rows, etc.

Is there someone or a party that deals with the details of the COPY command=
 that I could discuss this with?

Just for context, I am dealing with delimited data of millions of rows and =
perhaps 60 columns or so that I wish to read into tables in a Postgres 17 d=
atabase under Windows 11, with everything on local drives.

--=20
Bryan Sayer
Retired Demographer/Statistician
In a world in which you can be anything, be kind=20


------=_Part_837173_1925973751.1758831039330
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><div class=3D"yahoo-style-wrap" style=3D"font-fami=
ly:verdana, helvetica, sans-serif;font-size:16px;"><div>As I understand it,=
 COPY is intended as a simple &amp; fast way to get clean data into Postgre=
s.</div><div><br></div><div>It is not intended to groom data during the pro=
cess. There are a variety of ETL tools that can apply filters &amp; rules t=
o data streams to do what you are asking, so no need to invent another Post=
gres specific wheel.</div><div><br></div><div dir=3D"ltr" data-setdir=3D"fa=
lse">That said, there are some ways to do this without a formal ETL applica=
tion.</div><div><br></div><div>If the data rules you want to apply can be p=
rovided in a script, an approach I use is to pipe the data file through a s=
et of checks &amp; filters into a COPY command reading from STDIN. Very eff=
ective on Linux, Windows doesn't really do this very well.</div><div><br></=
div><div>eg: to change any instance of Grent in your data to great, use:</d=
iv><div><br></div><div><i>cat &lt;file&gt; | sed 's/Grent/great/' | psql &l=
t;db&gt; -c "copy &lt;table&gt; from STDIN with delimiter ',' null '';"&nbs=
p;</i></div><br><div>You can chain as many awk, sed, tr, grep, etc commands=
 as you need together to transform the data as required on the way to COPY<=
/div><div dir=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" data=
-setdir=3D"false">A Postgres only approach that&nbsp;<span>I've also used i=
s to create a loading table with columns to match the incoming data, but ea=
ch column is an unconstrained text or varchar. Then modify these data to re=
move any errors using SQL, finishing up with a select to insert the cleaned=
 data into the final table. If the final table has appropriate constraints =
to validate the data, only valid data will get there. As you find errors, y=
ou fix them &amp; rerun the insert until it works.</span></div><div><br></d=
iv><div dir=3D"ltr" data-setdir=3D"false">HTH,</div><div dir=3D"ltr" data-s=
etdir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"false">Brent Wood=
</div><br><br> <br> <br> On Friday, September 26, 2025 at 07:49:09 AM GMT+1=
2, Bryan Sayer &lt;brysayer@gmail.com&gt; wrote: <br><br><br><br><br><br> <=
br> <br>Hi,<br><br>I'm not sure if this is the best list to ask this (and I=
 am very new to PostgreSQL) but I think more options are needed in the COPY=
 from command, in order to better deal with exceptions. By exceptions I mea=
n data not consistent with the format, empty rows, extra delimiters at the =
end of rows, etc.<br><br>Is there someone or a party that deals with the de=
tails of the COPY command that I could discuss this with?<br><br>Just for c=
ontext, I am dealing with delimited data of millions of rows and perhaps 60=
 columns or so that I wish to read into tables in a Postgres 17 database un=
der Windows 11, with everything on local drives.<br><br>-- <br>Bryan Sayer<=
br>Retired Demographer/Statistician<br>In a world in which you can be anyth=
ing, be kind <br><br><br><br><br></div></body></html>
------=_Part_837173_1925973751.1758831039330--