Ошибка неверное значение для целого числа

При попытке использовать команду COPY через SQL в Postgres 9.5.1 в простой примерной базе данных…

Я получаю эту ошибку:

ERROR:  invalid input syntax for integer: "Sally"
CONTEXT:  COPY customer_, line 2, column id_: "Sally"

********** Error **********

ERROR: invalid input syntax for integer: "Sally"
SQL state: 22P02
Context: COPY customer_, line 2, column id_: "Sally"

… При импорте этих данных в CSV (значения, разделенные запятыми):

"first_name_","last_name_","phone_","email_"
"Sally","Jones","425.555.1324","s.jones@acme.com"
"Jarrod","Barkley","206.555.3454","j.barkley@example.com"
"Wendy","Melvin","415.555.2343","wendy@wendyandlisa.com"
"Lisa","Coleman","425.555.7282","lisa@wendyandlisa.com"
"Jesse","Johnson","507.555.7865","j.j@guitar.com"
"Jean-Luc","Martin","212.555.2244","jean-luc.martin@example.com"

… Импортируется через следующий SQL, выполняемый в pgAdmin:

COPY customer_
FROM '/home/parallels/Downloads/customer_.csv'
CSV 
HEADER
;

… В эту таблицу:

-- Table: public.customer_

-- DROP TABLE public.customer_;

CREATE TABLE public.customer_
(
  id_ integer NOT NULL DEFAULT nextval('customer__id__seq'::regclass),
  first_name_ text NOT NULL,
  last_name_ text NOT NULL,
  phone_ text NOT NULL DEFAULT ''::text,
  email_ text NOT NULL DEFAULT ''::text,
  CONSTRAINT pkey_customer_ PRIMARY KEY (id_)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.customer_
  OWNER TO postgres;
COMMENT ON TABLE public.customer_
  IS 'Represents a person  whose pets visit our clinic.';

Кажется, первая строка, содержащая имена столбцов, обрабатывается успешно. Точкой отказа является первое значение данных в первой строке данных CSV. Ни один из моих импортированных данных не относится к целочисленному типу, поэтому сообщение об ошибке сбивает меня с толку. Единственное целое число — это первичный ключ id_, автоматически увеличивающийся SERIAL.

Я прочитал страницу вопроса на странице Ошибка PG COPY: недопустимый синтаксис ввода для целого числа. Но этот вопрос действительно касался целочисленных значений, а их отсутствие в пустой строке в кавычках интерпретировалось как NULL. В моем случае здесь у нас нет целых значений в данных; единственное целое число — это столбец первичного ключа SERIAL со сгенерированным значением DEFAULT (не в импортируемых данных).

Я также нашел вопрос, ОШИБКА PostgreSQL: недопустимый синтаксис ввода для целого числа. Но это кажется неуместным.

When trying to use the COPY command via SQL in Postgres 9.5.1 in a simple example database…

I am getting this error:

ERROR:  invalid input syntax for integer: "Sally"
CONTEXT:  COPY customer_, line 2, column id_: "Sally"

********** Error **********

ERROR: invalid input syntax for integer: "Sally"
SQL state: 22P02
Context: COPY customer_, line 2, column id_: "Sally"

…when importing this data in CSV (comma-separated value):

"first_name_","last_name_","phone_","email_"
"Sally","Jones","425.555.1324","s.jones@acme.com"
"Jarrod","Barkley","206.555.3454","j.barkley@example.com"
"Wendy","Melvin","415.555.2343","wendy@wendyandlisa.com"
"Lisa","Coleman","425.555.7282","lisa@wendyandlisa.com"
"Jesse","Johnson","507.555.7865","j.j@guitar.com"
"Jean-Luc","Martin","212.555.2244","jean-luc.martin@example.com"

…being imported via the following SQL executed in pgAdmin:

COPY customer_
FROM '/home/parallels/Downloads/customer_.csv'
CSV 
HEADER
;

…into this table:

-- Table: public.customer_

-- DROP TABLE public.customer_;

CREATE TABLE public.customer_
(
  id_ integer NOT NULL DEFAULT nextval('customer__id__seq'::regclass),
  first_name_ text NOT NULL,
  last_name_ text NOT NULL,
  phone_ text NOT NULL DEFAULT ''::text,
  email_ text NOT NULL DEFAULT ''::text,
  CONSTRAINT pkey_customer_ PRIMARY KEY (id_)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.customer_
  OWNER TO postgres;
COMMENT ON TABLE public.customer_
  IS 'Represents a person  whose pets visit our clinic.';

So it seems the first row containing the names of the columns is being processed successfully. The failure point is with the first data value in the first data line of the CSV. None of my imported data is of integer type, so the I am befuddled by the error message. The only integer is the id_ primary key, auto-incrementing SERIAL.

I did read the Question page on PG COPY error: invalid input syntax for integer. But that question did involve integer values, and the lack thereof in an empty quoted string being interpreted as a NULL. In my case here we have no integer values in the data; the only integer is the primary key SERIAL column with a DEFAULT generated value (not in the data being imported).

I also found the Question, PostgreSQL ERROR: invalid input syntax for integer. But it seems irrelevant.

When trying to use the COPY command via SQL in Postgres 9.5.1 in a simple example database…

I am getting this error:

ERROR:  invalid input syntax for integer: "Sally"
CONTEXT:  COPY customer_, line 2, column id_: "Sally"

********** Error **********

ERROR: invalid input syntax for integer: "Sally"
SQL state: 22P02
Context: COPY customer_, line 2, column id_: "Sally"

…when importing this data in CSV (comma-separated value):

"first_name_","last_name_","phone_","email_"
"Sally","Jones","425.555.1324","s.jones@acme.com"
"Jarrod","Barkley","206.555.3454","j.barkley@example.com"
"Wendy","Melvin","415.555.2343","wendy@wendyandlisa.com"
"Lisa","Coleman","425.555.7282","lisa@wendyandlisa.com"
"Jesse","Johnson","507.555.7865","j.j@guitar.com"
"Jean-Luc","Martin","212.555.2244","jean-luc.martin@example.com"

…being imported via the following SQL executed in pgAdmin:

COPY customer_
FROM '/home/parallels/Downloads/customer_.csv'
CSV 
HEADER
;

…into this table:

-- Table: public.customer_

-- DROP TABLE public.customer_;

CREATE TABLE public.customer_
(
  id_ integer NOT NULL DEFAULT nextval('customer__id__seq'::regclass),
  first_name_ text NOT NULL,
  last_name_ text NOT NULL,
  phone_ text NOT NULL DEFAULT ''::text,
  email_ text NOT NULL DEFAULT ''::text,
  CONSTRAINT pkey_customer_ PRIMARY KEY (id_)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.customer_
  OWNER TO postgres;
COMMENT ON TABLE public.customer_
  IS 'Represents a person  whose pets visit our clinic.';

So it seems the first row containing the names of the columns is being processed successfully. The failure point is with the first data value in the first data line of the CSV. None of my imported data is of integer type, so the I am befuddled by the error message. The only integer is the id_ primary key, auto-incrementing SERIAL.

I did read the Question page on PG COPY error: invalid input syntax for integer. But that question did involve integer values, and the lack thereof in an empty quoted string being interpreted as a NULL. In my case here we have no integer values in the data; the only integer is the primary key SERIAL column with a DEFAULT generated value (not in the data being imported).

I also found the Question, PostgreSQL ERROR: invalid input syntax for integer. But it seems irrelevant.

Это мой код

select * from crosstab($$

**select code_r, code_o,case when tab3.count is null then 0 else tab3.count::bigint end from (
select r.code as code_r,o.id as organ_id_o,o.code as code_o from ref_crime_code r**

inner join ref_location_organ o on 1=1
where r.level =0 and r.type_id in (970)  order by code_o,code_r ) as tab1

left join (
select code_s,organ_id, count(*) from (
select  distinct on (aud.id)
case when c.level = 0 then c.code  when c1.level = 0 then c1.code else c2.code end as code_s,
organ_id

from erpp_aud aud
 inner join m2m_erpp_ref_crime_code mc on mc.erpp_id = aud.id
 inner join ref_crime_code c on c.id = mc.ref_crime_code_id and c.type_id in (970)

    left join ref_crime_code c1 on c1.id = c.parent_id
        left join ref_crime_code c2 on c2.id = c1.parent_id
where movement_point_id <> 399 and uid_suffix is null and movement_reason_id = 2010

order by aud.id,rev  desc, c.id
) as tab2 group by code_s,organ_id
) tab3 on tab1.code_r = tab3.code_s and tab1.organ_id_o = tab3.organ_id order by code_r::int,2
$$)

[22P02] ОШИБКА: неверное значение для целого числа: «119-1″ Где: SQL-оператор:
» select code_r, code_o,case when tab3.count is null then 0 else tab3.count::bigint end from ( select r.code as code_r,o.id as organ_id_o,o.code as code_o from ref_crime_code …

$sql = <<<SQL
INSERT INTO karakter (KullanıcıAdı,uid,Isim,Cinsiyet,Irk,Boy,Kilo,Gün,Ay,Yıl,Güç,Bünye,Dayanıklılık,Çeviklik,Karizma,Zeka,İrade,Odaklanma,Ustalık,Ziraat,İşçilik,Aşçılık,Botanik,Balıkçılık,Dericilik,Marangozluk,Hekimlik,VahşiYaşam,DuvarUstalığı,Camcılık,Demircilik,Avcılık,Şarapçılık,Çekilme,Kalkan,Hançer,Kılıç,Balta,Gürz,Mızrak,Yay,Arbalet)
VALUES ("$user","$uid","$isim","$cinsiyet","$irk","$boy","$kilo","$gun","$ay","$yil","$Güç","$Bünye","$Dayanıklılık","$Çeviklik","$Karizma","$Zeka","$İrade","$Odaklanma","$Ustalık",'$Ziraat',"$İşçilik","$Aşçılık","$Botanik","$Balıkçılık","$Dericilik","$Marangozluk","$Hekimlik","$VahşiYaşam","$DuvarUstalığı","$Camcılık","$Demircilik","$Avcılık","$Şarapçılık","$Çekilme","$Kalkan","$Hançer","$Kılıç","$Balta","$Gürz","$Mızrak","$Yay","$Arbalet")
ON DUPLICATE KEY UPDATE
KullanıcıAdı=VALUES(KullanıcıAdı),
uid=VALUES(uid),
Isim=VALUES(Isim),
Cinsiyet=VALUES(Cinsiyet),
Irk=VALUES(Irk),
Boy=VALUES(Boy),
Kilo=VALUES(Kilo),
Gün=VALUES(Gün),
Ay=VALUES(Ay),
Yıl=VALUES(Yıl),
Güç=VALUES(Güç),
Bünye=VALUES(Bünye),
Dayanıklılık=VALUES(Dayanıklılık),
Çeviklik=VALUES(Çeviklik),
Karizma=VALUES(Karizma),
Zeka=VALUES(Zeka),
İrade=VALUES(İrade),
Odaklanma=VALUES(Odaklanma),
Ustalık=VALUES(Ustalık),
Ziraat=VALUES(Ziraat),
İşçilik=VALUES(İşçilik),
Aşçılık=VALUES(Aşçılık),
Botanik=VALUES(Botanik)
SQL;

Решение

Другие решения