<commit_before># postgres-copy [![Build Status](https://travis-ci.org/diogob/postgres-copy.svg?branch=master)](https://travis-ci.org/diogob/postgres-copy) [![Code Climate](https://codeclimate.com/github/diogob/postgres-copy.svg)](https://codeclimate.com/github/diogob/postgres-copy)

This Gem will enable your AR models to use the PostgreSQL COPY command to import/export data in CSV format.
If you need to tranfer data between a PostgreSQL database and CSV files, the PostgreSQL native CSV parser
will give you a greater performance than using the ruby CSV+INSERT commands.
I have not found time to make accurate benchmarks, but in the use scenario where I have developed the gem
I have had a four-fold performance gain.
This gem was written having the Rails framework in mind, I think it could work only with active-record, 
but I will assume in this README that you are using Rails.

## Install

Put it in your Gemfile

    gem 'postgres-copy'

Run the bundle command

    bundle

## IMPORTANT note about recent versions

* Rails 4 users should use the version 0.7 and onward, while if you use Rails 3.2 stick with the 0.6 versions.
* Since version 0.8 all methods lost the prefix pg_ and they should be included in models thourgh acts_as_copy_target.

## Usage

To enable the copy commands in an ActiveRecord model called User you should use:
```ruby
class User < ActiveRecord::Base
  acts_as_copy_target
end
```

This will add the aditiontal class methods to your model:

* copy_to 
* copy_to_string
* copy_from

### Using copy_to and copy_to_string

You can go to the rails console and try some cool things first.
The first and most basic use case, let's copy the enteire content of a database table to a CSV file on the database server disk.
Assuming we have a users table and a User AR model:

```ruby
User.copy_to '/tmp/users.csv'
```

This will execute in the database the command:

```sql
COPY (SELECT "users".* FROM "users" ) TO '/tmp/users.csv' WITH DELIMITER ',' CSV HEADER
```

Remark that the file will be created in the database server disk.  
But what if you want to write the lines in a file on the server that is running Rails, instead of the database?  
In this case you can pass a block and retrieve the generated lines and then write them to a file:

```ruby
File.open('/tmp/users.csv', 'w') do |f|
  User.copy_to do |line|
    f.write line
  end
end
```

Or, if you have enough memory, you can read all table contents to a string using .copy_to_string

```ruby
puts User.copy_to_string
```

Another insteresting feature of copy_to is that it uses the scoped relation, it means that you can use ARel 
operations to generate different CSV files according to your needs.
Assuming we want to generate a file only with the names of users 1, 2 and 3:

```ruby
User.select("name").where(:id => [1,2,3]).copy_to "/tmp/users.csv"
```

Which will generate the following SQL command:

```sql
COPY (SELECT name FROM "users" WHERE "users"."id" IN (1, 2, 3)) TO '/tmp/users.csv' WITH DELIMITER ',' CSV HEADER
```

The COPY command also supports exporting the data in binary format.

```ruby
User.select("name").where(:id => [1,2,3]).copy_to "/tmp/users.dat", :format => :binary
```

Which will generate the following SQL command:

```sql<commit_msg>Add new copy_to_enumerator to README.<commit_after># postgres-copy [![Build Status](https://travis-ci.org/diogob/postgres-copy.svg?branch=master)](https://travis-ci.org/diogob/postgres-copy) [![Code Climate](https://codeclimate.com/github/diogob/postgres-copy.svg)](https://codeclimate.com/github/diogob/postgres-copy)

This Gem will enable your AR models to use the PostgreSQL COPY command to import/export data in CSV format.
If you need to tranfer data between a PostgreSQL database and CSV files, the PostgreSQL native CSV parser
will give you a greater performance than using the ruby CSV+INSERT commands.
I have not found time to make accurate benchmarks, but in the use scenario where I have developed the gem
I have had a four-fold performance gain.
This gem was written having the Rails framework in mind, I think it could work only with active-record,
but I will assume in this README that you are using Rails.

## Install

Put it in your Gemfile

    gem 'postgres-copy'

Run the bundle command

    bundle

## IMPORTANT note about recent versions

* Rails 4 users should use the version 0.7 and onward, while if you use Rails 3.2 stick with the 0.6 versions.
* Since version 0.8 all methods lost the prefix pg_ and they should be included in models thourgh acts_as_copy_target.

## Usage

To enable the copy commands in an ActiveRecord model called User you should use:
```ruby
class User < ActiveRecord::Base
  acts_as_copy_target
end
```

This will add the aditiontal class methods to your model:

* copy_to
* copy_to_string
* copy_to_enumerator
* copy_from

### Using copy_to and copy_to_string

You can go to the rails console and try some cool things first.
The first and most basic use case, let's copy the enteire content of a database table to a CSV file on the database server disk.
Assuming we have a users table and a User AR model:

```ruby
User.copy_to '/tmp/users.csv'
```

This will execute in the database the command:

```sql
COPY (SELECT "users".* FROM "users" ) TO '/tmp/users.csv' WITH DELIMITER ',' CSV HEADER
```

Remark that the file will be created in the database server disk.  
But what if you want to write the lines in a file on the server that is running Rails, instead of the database?  
In this case you can pass a block and retrieve the generated lines and then write them to a file:

```ruby
File.open('/tmp/users.csv', 'w') do |f|
  User.copy_to do |line|
    f.write line
  end
end
```

Instead of yielding each line, you could return an enumerator with all users:
```ruby
enumerator = User.copy_to_enumerator
```

And for better performance when rendering the result of the enumerator, you can return an enumerator with blocks of 100 lines joined:
```ruby
enumerator = User.copy_to_enumerator(:buffer_lines => 100)
```

Or, if you have enough memory, you can read all table contents to a string using .copy_to_string

```ruby
puts User.copy_to_string
```

Another insteresting feature of copy_to is that it uses the scoped relation, it means that you can use ARel
operations to generate different CSV files according to your needs.
Assuming we want to generate a file only with the names of users 1, 2 and 3:

```ruby
User.select("name").where(:id => [1,2,3]).copy_to "/tmp/users.csv"
```

Which will generate the following SQL command:

```sql
COPY (SELECT name FROM "users" WHERE "users"."id" IN (1, 2, 3)) TO '/tmp/users.csv' WITH DELIMITER ',' CSV HEADER
```

The COPY command also supports exporting the data in binary format.

```ruby
User.select("name").where(:id => [1,2,3]).copy_to "/tmp/users.dat", :format => :binary
```

Which will generate the following SQL command:

```sql
{'fraction_non_alphanumeric': 0.08485639686684072, 'fraction_numerical': 0.006962576153176675, 'mean_word_length': 4.27947932618683, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 10, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1902697', 'n_tokens_mistral': 2137, 'n_tokens_neox': 1998, 'n_words': 978}