Обсуждение: Find missing data in a column
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
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
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 usedthis 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 postgresThen try:psql ebirdsSET SEARCH_PATH TO p_loc;COPY p_loc.ebird_sptl FROM'/nt-d/projects_and_data/ebd_OR/ebird_sptl_all.dump';That fails withmissing data for column "COMMON_NAME"I understand this means there is a problem with one or more rows in thecolumn 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