Thread: Find missing data in a column

Find missing data in a column

From

john polo

Date:

29 December 2021, 20:43:55

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

Find missing data in a column

From

"David G. Johnston"

Date:

29 December 2021, 20:54:49

On Wednesday, December 29, 2021, john polo <jpolo@mail.usf.edu> wrote:

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?

It doesn’t actually mean anything…you cannot use the copy command to import the contents of a dump file. Especially not one dumped using the custom format.

David J.

Re: Find missing data in a column

From

Adrian Klaver

Date:

29 December 2021, 20:56:04

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.klaver@aklaver.com

Re: Find missing data in a column

From

Adrian Klaver

Date:

29 December 2021, 21:07:21

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?


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



> 
> Cheers,
> 
> John
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Find missing data in a column

From

Rob Sargent

Date:

29 December 2021, 21:10:48

On Dec 29, 2021, at 10:07 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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?

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

Cheers,
John

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Find missing data in a column

From

Adrian Klaver

Date:

29 December 2021, 21:16:35

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'

>>
>>
>>> Cheers,
>>> John
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Find missing data in a column

From

Alan Hodgson

Date:

29 December 2021, 21:36:43

On Wed, 2021-12-29 at 12:43 -0500, john polo wrote:

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?

You're going to want to look into the pg_restore command to restore a custom format dump file.

If you wanted something to read with COPY FROM you would first dump it with COPY TO.

Re: Find missing data in a column

From

john polo

Date:

30 December 2021, 05:09:24

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