Find missing data in a column

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.

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

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

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

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

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.

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