Unable to insert data into postgresql table using flat file with copy command

Question

My table structure is

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               |

and i have following data in the flat file(*.txt file).

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);

Now I am trying load into my table 'address' using following query i psql shell.

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

Error is:

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'..."

Please suggest modifications to be done in the above query

Gordon Linoff · Accepted Answer · 2017-11-05 02:47:08Z

1

You don't have a data file. You have a file with a set of commands.

You can use the psql command to execute the inserts.

A data file would look more like this:

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

answered Nov 5, 2017 at 2:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

prvreddy Over a year ago

Thank you Gordon, I have copied above lines in txt file and run following query. It worked copy address from 'C:/P Files/address_data.txt' ( DELIMITER(',') ); Thank you

Collectives™ on Stack Overflow

Unable to insert data into postgresql table using flat file with copy command

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related