PostgreSQL’s COPY FROM STDIN and Ruby
I stumbled over this and could not find a good example for it so here is my solution.
If you want to import a lot of data into a PostgreSQL database then most propably you want to use one COPY … FROM statement instead of a INSERT statement for every record to create. Let’s assume we want to import some data from Ruby using the raw postgres library.
You can COPY from file which is propably the simplest way of using it:
COPY articles FROM FILE 'articles.csv' WITH DELIMITER AS ',';
However, this only works if you are a PostgreSQL super user and the file is on the server. So: How do we solve it? We use COPY FROM STDIN! But, wait… we are inside our Ruby script - what is STDIN? Reading PostgreSQL’s documentation yields that it is the connection from our client library to the server. How can we write to this stream?
The solution is to send the COPY … FROM STDIN statement to the server. The server will then wait for the data. We send the CVS data with PGconn#putline and close the data submission with PGConn#endcopy. That’s it.
Let’s look at an example:
require 'postgres'
conn = PGconn.open('dbname' => 'my_database')
res = conn.exec(%Q{COPY articles FROM STDIN WITH DELIMITER AS ','})
file_contents = File.open('articles.csv', 'r') { |f| f.read }
file_contents.each_line { |line| conn.putline(line) }
conn.endcopy
conn.close
March 27th, 2007 at 7:08 am
[...] I admit it. I am bad. I run raw SQL in my migrations. The dataset I am working with requires it of me. It must be punished. To aid in its punishment, I found a post explaining how to get at PostgreSQL’s COPY from within the Postgres Ruby driver. [...]
June 5th, 2010 at 9:26 pm
Please tell me it worked right? I dont want to sumit it again if i do not have to! Either the blog glitced out or i am an idiot, the second option doesnt surprise me lol. thanks for a great blog!
August 12th, 2010 at 8:58 am
Cool post! How much stuff did you have to look up in order to write this one? I can tell you put some work in.
September 15th, 2010 at 12:43 pm
Hello dude,i liking Your New site very much. do u have suggestion being my blog? thanks as A New attention
April 4th, 2011 at 1:30 pm
This is very useful suggestions. I have to say I enjoy reading this article lots. It aids me to become better knowledge of on the subject. It is all well and good produced. I am going to definitely get this specific satisfied pretty intriguing. I hope you can supply more one day.
April 7th, 2011 at 12:31 am
wau super
April 9th, 2011 at 10:04 am
I don’t normally comment but I gotta say thankyou for the post on this one : D.
April 16th, 2011 at 5:20 pm
What do you mean whit this ? can u give me an example? Thanks and hope to hear from you…
April 23rd, 2011 at 2:39 am
hello there and thanks for your info - I’ve certainly picked up anything new from right here. I did however expertise a few technical issues using this web site, as I experienced to reload the site many times previous to I could get it to load correctly. I had been wondering if your web hosting is OK? Not that I am complaining, but sluggish loading instances times will often affect your placement in google and could damage your quality score if advertising and marketing with Adwords. Anyway I’m adding this RSS to my email (Darnstaedt@beautytime1.com.pl) and could look out for a lot more of your respective intriguing content. Ensure that you update this again soon.. Bezpieczny motocylista
April 24th, 2011 at 9:14 pm
Pretty section of content. I just stumbled upon your web site and in accession capital to assert that I acquire in fact enjoyed account your blog site articles. Anyway I’ll be subscribing to your feeds and even I achievement you access consistently fast. Sherry Eberling
May 26th, 2011 at 2:34 am
I have a slightly different frame of mind, but I deeply respect you for sharing this news.
May 27th, 2011 at 9:53 am
It’s a fabulous pity you actually don’t enjoy a donate switch! I’d most likely donate to this exceptional web site! Document suppose that for the time being i’ll be happy with book-marking and additionally including any Rss feed to make sure you great Google and bing membership. Document check forth to make sure you new posts all of which show this approach site by means of great Youtube group: )
July 11th, 2011 at 7:17 pm
Hi, I couldn’t find a contact form and it’s really important that I reach you so I hope you don’t mind me posting here. WOW, what an interesting little blog you have here :)! I also run one similar to Thoughtlessly Thought Thoughts » Blog Archive » PostgreSQL’s COPY FROM STDIN and Ruby, I guess great haha. I’ve been following your site for a while now and I’ve got some bad news - you’re wasting your time if you’re only making a couple hundred bucks or even a few thousand a month. You can be doing SO much more. There are a lot of tips and tricks that will help you improve your google and yahoo ranking, just some stuff I’ve learned over the ages. It’s easy! It shouldn’t take you more than 5-10 minutes. Please reach me today at BigBloggerJake [at] gmail.com. I’ll check my spam folder and you should too, I know email filters are crazy these days. Looking forward to boucing ideas with you!
August 17th, 2011 at 2:12 pm
I needed to thanks for this great learn!! I undoubtedly having fun with every little bit of it I’ve you bookmarked to check out new stuff you publish
August 22nd, 2011 at 3:48 pm
Good site! I truly did enjoy it. Thank you for sharing your info.
August 25th, 2011 at 11:31 pm
59. Thank you for another informative blog. Where else could I get that kind of info written in such an ideal way? I’ve a project that I am just now working on, and I’ve been on the look out for such information.
October 6th, 2011 at 9:48 am
Хотите поднять настроение окружающим? Удивить их своим необычным вкусом?
Сделать незабываемым праздник, день рождения, юбилей? Все очень просто.
Вручите Вашему близкому человеку, другу, партнеру по бизнесу оригинальные подарки,
которые не только удивят, порадуют и развеселят, но и расскажут получателю
подарка об искренности намерений. Ведь такая сувенирная продукция всегда запоминаема
и желанна. Ждем Вас в нашем интернет-магазине http://podarok-hobby.ru тел. 8(495)769-47-31, 8(916)988-33-32