postgresql - 无法使用带有复制命令的平面文件将数据插入到 postgresql 表中

Question

我的表结构是

company=# \d address
            Table "public.address"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 name     | character varying(80) |
 age      | integer               |
 dob      | date                  |
 village  | character varying(8)  |
 locality | character varying(80) |
 district | character varying(80) |
 state    | character varying(80) |
 pin      | integer               |

我在平面文件(*.txt 文件)中有以下数据。

insert into address(name,age,dob,village,locality,district,state,pin) 
values('David',43,'1972-10-23','Elchuru','Addanki','Prakasam','AP',544421);
insert into address(name,age,dob,village,locality,district,state,pin) 
values('George',53,'1962-10-23','London','London','LN','LN',544421);
insert into address(name,age,dob,village,locality,district,state,pin) 
values('David',28,'1982-10-23','Ongole','Ongole','Prakasam','AP',520421);

现在我正在尝试使用以下查询在 psql shell 中加载到我的表“地址”。

copy address from 'C:/P Files/address_data.txt';

错误是:

company=# copy address from 'C:/P Files/address_data.txt';

ERROR: value too long for type character varying(80) CONTEXT: COPY address, line 1, column name: "insert into address(name,age,dob,village,locality,district,state,pin) values('David',43,'1972-10-23'..."

请建议对上述查询进行修改

Answer 1

您没有数据 文件。您有一个包含一组命令的文件。

您可以使用 psql command执行插入。

数据文件看起来更像这样:

David,43,1972-10-23,Elchuru,Addanki,Prakasam,AP,544421
George,53,1962-10-23,London,London,LN,LN,544421
David,28,1982-10-23,Ongole,Ongole,Prakasam,AP,520421