Find missing data in a column

[john polo]

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?


Cheers,

John

--
Enlightenment is ego's ultimate disappointment.
-Chogyam Trungpa

Re: Find missing data in a column

[john polo]

On 12/29/2021 1:16 PM, Adrian Klaver wrote:

On 12/29/21 10:10, Rob Sargent wrote:






If you want to use psql then:

pg_dump.exe" 
--file="C:\Users\Nightrunner\DOCUMENTS\programming\pg_out\ebird_sptl_all.txt" 
--host="localhost" --port="5432" --username="postgres" --password 
--no-owner --no-privileges --dbname="ebird_work" 
--table="p_loc.ebd_sptl" --data-only


psql ebirds


\i C:\Users\Nightrunner\DOCUMENTS\programming\pg_out\ebird_sptl_all.txt



Doesn’t that presume OP can connect to linux db from Windows machine? 
  Seems the best plan would be copy to; file-tranfer; copy from


Yeah that should been the file location from the OP's COPY command:

'/nt-d/projects_and_data/ebd_OR/ebird_sptl_all.txt'


Thanks, everyone, for the replies.

Thanks, Adrian, for the helpful commands you provided. They worked up to 
a point that gave me a different error as the data were loading. I will 
spend some time trying to work that new error out.


Cheers,

John