Solved: The integer column was set on auto increment, just left it empty on the import...
we have a lot of data, that is currently in excel. I made a VBA skript, that builds me a CSV to import into our Database / PostgreSQL Table. I'm trying to import with the import/export feature of PgAdmin.
The table has columns of type ([PK] Integer, string, string, JSON). When I try to import it throws me an error, right at the beginning, saying that »2« is no valid integer.
The file is UTF-8 encoded.
This is the command PgAdmin generates:
--command " "\copy public.stocknew (stockid, stockname, stockbarcode, stockjson) FROM '//DESKTOP-G86U473/temp/Test.csv' DELIMITER ',' CSV ENCODING 'UTF8' QUOTE '"' ESCAPE '''';""
Not a regular question asker, so please comment, if anything needs clarification.
Here is the first entry of the CSV file.
2,"W12345","35","{
'"Manufacturer'":'"ExampleValue'",
'"Supplier'":'"ExampleValue'",
'"SupplierName'":'"ExampleValue'",
'"Category'":'"ExampleValue'",
'"SubCategory'":'"ExampleValue'",
'"Partvalue'":'"868MHz - 928MHz, 2.400MHz - 2.500MHz'",
'"Tolerance'":'"ExampleValue'",
'"Dimension'":'"10,4 x 49,6mm'",
'"Temperature'":'"-34°C + 76°C'",
...*This keeps going for a while*...
'"Example'":2,
'"Example'":3,
'"Example'":4
}"
The following data successfully loads
"2","W12345","35","{\"Manufacturer\":\"ExampleValue\",\"Supplier\":\"ExampleValue\",\"SupplierName\":\"ExampleValue\",\"Category\":\"ExampleValue\",\"SubCategory\":\"ExampleValue\",\"Partvalue\":\"868MHz - 928MHz, 2.400MHz - 2.500MHz\",\"Tolerance\":\"ExampleValue\",\"Dimension\":\"10,4 x 49,6mm\",\"Temperature\":\"-34°C + 76°C\"}"
postgres=# create table pew (i1 int, s1 varchar(30), s2 varchar(30), j1 jsonb);
postgres=# copy pew from '/tmp/somedata.csv' with (format CSV, quote '"', escape '\');
COPY 1
postgres=# select * from pew;
i1 | s1 | s2 |
j1
----+--------+----+-----------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 | W12345 | 35 | {"Category": "ExampleValue", "Supplier": "ExampleValue", "Dimension": "10,4 x 49,6mm", "Partvalue": "868MHz - 92
8MHz, 2.400MHz - 2.500MHz", "Tolerance": "ExampleValue", "SubCategory": "ExampleValue", "Temperature": "-34°C + 76°C", "Manufacturer
": "ExampleValue", "SupplierName": "ExampleValue"}
(1 row)
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
Looks like extraneous ' and missing escaping ".
Pardon my ugly SQL