Home
%3CLINGO-SUB%20id%3D%22lingo-sub-805801%22%20slang%3D%22en-US%22%3EUsing%20COPY%20command%20in%20Azure%20Database%20for%20PostgreSQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EPostgres%20%E2%80%98COPY%E2%80%99%20command%20can%20be%20used%20to%20transfer%20data%20between%20text%20files%20and%20database%20tables%2C%20in%20this%20article%20I%20will%20discuss%20how%20to%20avoid%20%22ERROR%3A%20must%20be%20superuser%20to%20COPY%20to%20or%20from%20a%20file%22%20while%20using%20Azure%20Database%20for%20PostgreSQL.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECOPY%20command%20needs%20superuser%20and%20Azure%20database%20for%20PostgreSQL%20is%20a%20managed%20PaaS%20database%20server%20that%20doesn't%20give%20the%20server%20admin%20superuser%20privileges%2C%20interested%20to%20know%20why%3F%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fhowto-create-users%23the-server-admin-account%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Eread%20this%3C%2FA%3E.%20so%20if%20you%20execute%26nbsp%3BCOPY%20command%20from%20PgAdmin%20or%20psql%20utility%20you%20will%20run%20into%20the%20prementioned%20error%20as%20in%20the%20following%20screenshot%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20769px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127058iCF58879FE9520D2B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22copy%20error.jpg%22%20title%3D%22copy%20error.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20786px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127059i1D5C988B462F9E18%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22copyerror2.jpg%22%20title%3D%22copyerror2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESolution%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3Euse%20psql%20command%20%3CSPAN%3E%3CEM%3E%3CSTRONG%3E%5Ccopy%3C%2FSTRONG%3E%3C%2FEM%3E(as%20the%20error%20message%20suggests)%20rather%20than%20copy%2C%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3E%3CSTRONG%3E%3CEM%3E%5CCOPY%20my_table%20FROM%20'C%3A%2FUsers%2Fbahusse%2FDesktop%2Ffile2.txt'%20DELIMITERS%20'%2C'%20CSV%20QUOTE%20''''%3B%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127060i9E8460E55BB5F1A9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22copysoln.jpg%22%20title%3D%22copysoln.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

Postgres ‘COPY’ command can be used to transfer data between text files and database tables, in this article I will discuss how to avoid "ERROR: must be superuser to COPY to or from a file" while using Azure Database for PostgreSQL.

 

COPY command needs superuser and Azure database for PostgreSQL is a managed PaaS database server that doesn't give the server admin superuser privileges, interested to know why? read this. so if you execute COPY command from PgAdmin or psql utility you will run into the prementioned error as in the following screenshot:

 

copy error.jpg

copyerror2.jpg

 

Solution:

use psql command \copy (as the error message suggests) rather than copy,
\COPY my_table FROM 'C:/Users/bahusse/Desktop/file2.txt' DELIMITERS ',' CSV QUOTE '''';

copysoln.jpg