Re: Find missing data in a column

On 12/29/21 09:43, john polo wrote:
> Hi,
>
> I have a database in PostgreSQL 12 on Windows. It has > 8,000,000 rows.
> I want to copy this database to PostgreSQL 10 on Slackware Linux. I used
> this command to get the data out of the Windows database:
>
> "C:\Program Files\PostgreSQL\12\bin\pg_dump.exe"
> --file="C:\Users\Nightrunner\DOCUMENTS\programming\pg_out\ebird_sptl_all.dump"
> --host="localhost" --port="5432" --username="postgres" --password
> --verbose --format=c --no-owner --no-privileges --dbname="ebird_work"
> --table="p_loc.ebd_sptl"
>
> On Slackware, I first:
>
> su postgres
>
> Then try:
>
> psql ebirds
>
> SET SEARCH_PATH TO p_loc;
>
> COPY p_loc.ebird_sptl FROM
> '/nt-d/projects_and_data/ebd_OR/ebird_sptl_all.dump';
>
> That fails with
>
> missing data for column "COMMON_NAME"
>
> I understand this means there is a problem with one or more rows in the
> column referenced. This is a column of text. How do I find the error(s)
> in question?

How did you think this would work at all?

--format=c is compressed custom binary format

COPY p_loc.ebird_sptl FROM
'/nt-d/projects_and_data/ebd_OR/ebird_sptl_all.dump';

Is looking for separated value text format file.

Look at pg_restore:

https://www.postgresql.org/docs/current/app-pgrestore.html

>
> Cheers,
>
> John
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com