cursor.copy_from(
    file=location_file,
    table='quote_location',
    columns=('name', 'country', 'source', 'type'),
    sep=',',
    null=''
psycopg2.DataError: extra data after last expected column
CONTEXT:  COPY quote_location, line 3092: "3249,"Al-Dhabbaya transmitter, Tower North",ae,geo,other,0101000020E610000097FF907EFB3A384016359886E..."
Reason is comma(,) in name "Al-Dhabbaya transmitter, Tower North". While the csv file is working properly without any error with other csv readers.
          
If you have a comma in your values you cannot use the comma as a separator. Use a different character, as a tab. Or use CSV and use copy expert to tell so, and quote the values.
This is not a psycopg matter: psycopg just forwards the data to postgres. Please refer to the copy documentation and provide postgres valid data.
          
@dvarrazzo Thanks for your replay. If you can observe the value is already quoted when there is comma in that. I believe it should work when value is quoted and comma is there inside it. The same data works fine while dealing with COPY utility of postgresql but in psycopg2 it is creating error.
You referenced CSV and copy expert. Will it be possible to give any reference on this? I am unable to identify exactly which utilities are you talking about.
          
@ultimatecoder you should use cursor.copy_expert() and use any feature Postgres offers, e.g. (untested):
cursor.copy_expert("copy quote_location from stdin (format csv)", location_file)
          
@ultimatecoder, Postgres developers continuously add new features to COPY: copy_from and copy_to were maybe complete at the time when they were introduced, some 15 years ago, but with more features added every year it doesn't make sense try to duplicate all of them into a different parameter (Alan Perlis said If you have a procedure with ten parameters, you probably missed some).
It's not dissimilar from normal SQL: execute() doesn't know every Postgres SQL feature and doesn't try to wrap them into objects or parameters: it allows sending a command as a string, letting the developer and the database interacting using all the features and without getting in the way, the only responsibility being marshalling Python objects into a format the database knows. IMO copy_from and copy_to could be dropped, leaving only copy_expert there (maybe renamed as copy()). But we are sentimental and don't get rid of methods which has been around probably longer than our girlfriends. copy_from() was actually at dinner with me yesterday, and I ensured him we won't fire him, no, just he won't have more parameters added. ;)
          
I am having the same issue.  the straight copy works with "quoted,comma,works" a string field with comma inside.  The \COPY ... with (format 'csv', delimiter ',', quote '"')  In the past I have also written parsers to work with quoted comma.  It is only a few extra lines of code (this is in C++); in python it should be even simpler.  Asking the user to user alternative is not as good as adding more functionality to the implementation.
          
@kemin711 this is a bug tracker, you are using it as a rubber duck. You have added, changed, removed comments which are just your toying with the library.
If you have observations or questions write the mailing list.
Locking the issue because I've had enough email about new comments just to see they were rewritten or discarded and this is not even a bug.