pgloader
pgloader copied to clipboard
dimitri
csv load error in postgres v12 using pgload (v 3.6.1) on Ubuntu 20.04 on Windows 10 WSL2
-
[ ] pgloader --version
# Ubuntu 20.04 on Windows 10 WSL2 pgloader version "3.6.1" compiled with SBCL 1.4.15.debian -
[ ] did you test a fresh compile from the source tree?
I am learning PostgreSQL. Don't know to compile the source yet. No, installed in Ubuntu 20.04 using "sudo apt install pgloader".
-
[ ] did you search for other similar issues?
-
[ ] how can I reproduce the bug?
# Below docker command installs Postgres version 12 container.
docker run \
--name pgloader \
-d -p 5432:5432 \
-e POSTGRES_PASSWORD=pg1234 \
--mount type=bind,source="$(pwd)",target=/src \
postgres
docker exec -it pgloader bash
createdb -U postgres --encoding=utf-8 pgloader
psql -U postgres
\l
\c pgloader
LOAD CSV
FROM INLINE with encoding 'ascii'
INTO postgresql:///pgloader
TARGET TABLE jordane
WITH truncate,
fields terminated by '|',
fields not enclosed,
fields escaped by backslash-quote
SET work_mem to '128MB',
standard_conforming_strings to 'on'
BEFORE LOAD DO
$$ drop table if exists jordane; $$,
$$ CREATE TABLE jordane
(
"NOM" character(20),
"PRENOM" character(20)
)
$$;
BORDET|Jordane
BORDET|Audrey
LASTNAME|"opening quote
BONNIER|testprenombe~aucouptroplong
JOURDAIN|héhé¶
- [ ] pgloader output you obtain
00:38 $ pgloader csv-test.load
2020-07-12T19:11:42.006000Z LOG pgloader version "3.6.1"
2020-07-12T19:11:42.008000Z LOG Parsing commands from file #P"/mnt/d/ajit-code/gh/mindmap/postgres/pgloader/pgloader-csv/csv-test.load"
2020-07-12T19:11:42.114000Z ERROR Database error 42703: column "adsrc" does not exist
QUERY: -- params: table-type-name
-- including
-- filter-list-to-where-clause for including
-- excluding
-- filter-list-to-where-clause for excluding
with seqattr as
(
select adrelid,
adnum,
adsrc,
case when adsrc ~ 'nextval'
then substring(pg_get_expr(d.adbin, d.adrelid)
from '''([^'']+)'''
)
else null
end as seqname
from pg_attrdef d
)
select nspname, relname, c.oid, attname,
t.oid::regtype as type,
case when atttypmod > 0
then substring(format_type(t.oid, atttypmod) from '\d+(?:,\d+)?')
else null
end as typmod,
attnotnull,
case when atthasdef then def.adsrc end as default,
case when s.seqname is not null then 'auto_increment' end as extra
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_attribute a on c.oid = a.attrelid
join pg_type t on t.oid = a.atttypid and attnum > 0
left join pg_attrdef def on a.attrelid = def.adrelid
and a.attnum = def.adnum
and a.atthasdef
left join seqattr s on def.adrelid = s.adrelid
and def.adnum = s.adnum
where nspname !~ '^pg_' and n.nspname <> 'information_schema'
and relkind in ('r', 'f', 'p')
and ((n.nspname = 'public' and c.relname = 'jordane'))
order by nspname, relname, attnum;
2020-07-12T19:11:42.114000Z FATAL Failed to prepare target PostgreSQL table.
2020-07-12T19:11:42.114000Z FATAL Database error 42703: column "adsrc" does not exist
QUERY: -- params: table-type-name
-- including
-- filter-list-to-where-clause for including
-- excluding
-- filter-list-to-where-clause for excluding
with seqattr as
(
select adrelid,
adnum,
adsrc,
case when adsrc ~ 'nextval'
then substring(pg_get_expr(d.adbin, d.adrelid)
from '''([^'']+)'''
)
else null
end as seqname
from pg_attrdef d
)
select nspname, relname, c.oid, attname,
t.oid::regtype as type,
case when atttypmod > 0
then substring(format_type(t.oid, atttypmod) from '\d+(?:,\d+)?')
else null
end as typmod,
attnotnull,
case when atthasdef then def.adsrc end as default,
case when s.seqname is not null then 'auto_increment' end as extra
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
left join pg_attribute a on c.oid = a.attrelid
join pg_type t on t.oid = a.atttypid and attnum > 0
left join pg_attrdef def on a.attrelid = def.adrelid
and a.attnum = def.adnum
and a.atthasdef
left join seqattr s on def.adrelid = s.adrelid
and def.adnum = s.adnum
where nspname !~ '^pg_' and n.nspname <> 'information_schema'
and relkind in ('r', 'f', 'p')
and ((n.nspname = 'public' and c.relname = 'jordane'))
order by nspname, relname, attnum;
2020-07-12T19:11:42.114000Z LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
fetch 0 0 0.004s
before load 0 2 0.021s
----------------- --------- --------- --------- --------------
----------------- --------- --------- --------- --------------
- [ ] data that is being loaded, if relevant
SELECT * FROM public.jordane
No records
Compiled the pgloader from source code.
$ pgloader --version
pgloader version "3.6.2"
compiled with SBCL 2.0.1.debian
After load, the data is loaded as below:
pgloader=# select * from jordane;
NOM | PRENOM
----------------------+----------------------
BORDET | Jordane
BORDET | Audrey
LASTNAME | "opening quote
(3 rows)
This issue should already be fixed by this commit: https://github.com/dimitri/pgloader/commit/8a13c02561fe030301045f9f50d94f523dd61b2c
