Dependent Origination

COPY command of Postgres

Posted on: January 24, 2014

COPY command of Postgres is very useful, convenient and super fast! However, in using it, I have discovered a few considerations that have to go in when you actually write the code.

“COPY table_name FROM /localdisk/path_to_file”

You are going to get an error message saying only superuse can copy from a local file. However, any user can copy from stdin, or copy to stdout. Now you have four workarounds.

1. Use “\COPY” instead. “\”, in postgres, tells the command to treat the “FROM” as if from stdin. That way, any user can copy from stdin, and (educated guess starts:) the implementation probably redirected the give file to stdin. So COPY FROM a local file can work for you, even if you are not a super user of the database.

2. However, that ‘\’ cannot really work in Python. So in Python, you can use psycopg2’s cursor class to do the copy from. A sample code is like this:

import psycopg2

conn = psycopg2.connect(“dbname=db user=user password=password host=host port=port”)

cursor = conn.cursor()

cursor.copy_from(file, “table_name)

conn.commit()

3. You can also use psql command line to copy from.

psql -c “COPY table FROM stdin” and redirect your file into the stdin. Or use subprocess in Python to do that. See here for example.

4. Should you have permission to change the database permissions, you can make yourself a superuser.

ALTER USER myuser WITH SUPERUSER

See here for more details.

I have personally tried #1 and #2 and they all worked. #3 and #4 I haven’t tried but they are listed here for future reference.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

January 2014
M T W T F S S
« Dec   Feb »
 12345
6789101112
13141516171819
20212223242526
2728293031  

Twitter

  • Style Transfer is fun! TensorFlow rocks! #WTM17 https://t.co/zYP0IFIDfp 1 month ago
  • couldn't get over the jetlag, sleeping during the day from seven to four, for days. 3 months ago
  • is emptying trash and happily discovering the available disk space now ranks at 100G+. 4 months ago
  • is looking at other people's intentions, not their capabilities, and feeling much happier every day :) 1 year ago
  • is planning on how to spend the next two weeks until the new year, at home. 1 year ago

Flickr Photos

IMG_3517

IMG_3515

IMG_3505

IMG_3497

IMG_3261

IMG_3260

IMG_3255

IMG_2736

IMG_2733

IMG_2629

More Photos
%d bloggers like this: