diogob / postgres-copy Goto Github PK

I'm using postgres-copy for importing CSV files in tempory tables that are later used for parsing line by line(record by record).

That's why such ActiveRecord models & tables could not be created upfront.
I believe that might be not such a rare use case.

That's the workaround that I'm currently using:

    table_name = "upload_#{SecureRandom.hex}"
    ActiveRecord::Base.connection.create_table table_name, id: :integer do |t|
      columns.each do |column_name|
        t.string column_name.to_sym
      end
    end

    klass = Class.new(ActiveRecord::Base) do
      acts_as_copy_target
      self.table_name = table_name
    end
    klass.copy_from file_path.to_s, columns: columns

Please let me know if you may need this as an example in README, so I'll prepare a PR.
HTH

When copying from CSV to DB, would there be any way of skipping a column if it doesn't map to any table row in the DB?

Already asked here:#14 but I wonder if this is something you'd welcome in the gem? I will submit a PR for this if so.

I would suggest that we add an option to the pg_copy_from command called :through or similar. When present, the command would copy into an intermediary or temp table, and then joining on the model's primary key, it would insert or update the records into the model's table. I.e. (pardon my v0.6 syntax):

MyModel.pg_copy_from csv, :through => "my_model_temp" or
MyModel.pg_copy_from csv, :temp_table => true

It seems this is an important features for copy_from, since in most usecases, running the copy_from command more than once on the same table will result in an error violating primary key constraint.

👍? 👎? 😐 ?

Hi,

First, thanks for your greate work! I must deal with a pgsql batch copy but I need to use converters from CSV for hash for examples. I see that you use File.open why not use CSV.open? Is it faster with File.open?
(I use v 0.6 because I'm in rails 3.2)

I need to do this trick for example :

CSV::Converters[:hash] = lambda do |string|
        hash = {}
        begin
          string.gsub!(/[{}]/, '')
          string.split(',').each do |pair|
            key,value = pair.split(/=>/)
            hash[key] = value
          end
        rescue ArgumentError
          string
        end
      end

ActiveRoad::StreetNumber.pg_copy_from "/tmp/street_numbers.csv", :converters => converters

Is there a way I could set encoding like this - :encoding => 'windows-1251'

I am getting "invalid byte sequence in UTF-8" error

Hi, i'm using copy_from to import data from a csv file, it works great.
I was wondering if there is a way to remove duplicate records, to be more accurate I don't want to import record wuth a key-value already present in my db.
For example, if I had write a record with name='foo' i want to avoid to import another foo.
With is the best strategy to approach this?
Thank you

I didn't mess around with this, so maybe you can map this in some way, but it would be nice if you can import with no headers.

First of all, @diogob thanks for this fantastic and very helpful gem!

I have an idea on how this could be for new users because there is a minor issue that I faced myself.

I have pretty messy CSV files that I can not change, so I'm trying various configuration options(mapping, quotes & other configurations).

Eventually, I came up with proper config but each iteration was rather painful because I couldn't understand what's the actual SQL command that's being generated.

I haven't checked the source code but all exceptions were rather cryptic, short/truncated and without full stack trace.

A few examples:

        1: from app/models/public_model.rb:12:in `import'
PG::SyntaxError (ERROR:  zero-length delimited identifier at or near """")
LINE 1: COPY "public_models" ("","","","","","","","","","","","",""...
                              ^

        1: from app/models/public_model.rb:12:in `import'
ArgumentError (invalid byte sequence in UTF-8)

        1: from app/models/public_model.rb:12:in `import'
NoMethodError (private method `gets' called for #<Pathname:/app/dumps/models.csv>)

Hi

Rails show a warning message in console

.../shared/bundle/ruby/1.9.1/gems/actionpack-3.2.8/lib/action_dispatch/http/mime_type.rb:102: warning: already initialized constant ZIP

Since the use of a postgres database is implicit I believe the "pg_" prefixes on methods are unnecessary. Furthermore, it could be unified with other copy libraries for other databases, so when you do a "copy" the appropriate SQL would be used, just like save or destroy. In that scenario, to have "pg_copy_to", "mysql_copy_to" and so on would look sooooo PHPish.

When I'm trying to copy_from a CSV, I'm getting an error when it's this row's turn during iteration:

310114,"07UT","small_airport","ATK/Thiokol Airport",41.7166667,-112.4472222,4480,"NA","US","US-UT","Howell","no","07UT",,"07UT",,,"UT23, Air Force Plant 78 Airfield"

The error is:

PG::BadCopyFileFormat: ERROR:  extra data after last expected column
CONTEXT:  COPY airports, line 3063: "310114,"07UT","small_airport","ATK/Thiokol Airport",41.7166667,-112.4472222,4480,"NA","US","US-UT","..."

Seems like the comma which exists at the last column confuses the gem.

My Rails 4 app is using version 0.9.2 perfectly, no issues whatsoever.

However, I just added this to my Rails 3 app (specifying version 0.6), and it crashes like so upon class loading:

undefined local variable or method `acts_as_copy_target' for #<Class:0x007fae10b7f2f8> (NameError)`

Any suggestions would be appreciated.

Hi there,

I created a rake task to insert data into my postgres DB with the following code:

VehicleData.copy_from "lib/tasks/xxx.csv", :delimiter => "\t", :format => :csv, :table => "vehicle_data",
:map => { 'VehicleData_ID' => 'id'}
end

It goes through without errors but the DB is empty, any idea?

Thanks
L

I am working through your readme.md to see if this is a good solution for me. From the rails console I am attempting
Customer.copy_to '/tmp/customers.csv'
but getting
PG::InsufficientPrivilege: ERROR: must be superuser to COPY to or from a file

Is this an issue I can have a workaround for or am I just showing my inexperience?

Appreciate your help. Looks like this might be a really useful Gem that others in the community have directed me to.

Hi there

I am using postgres-copy gem to export User Account based data in CSV i.e; I want to export all the data of a particular user as his data backup. But as per the documentation, I can export only a single table's data.

Once exported, I would like to import the data exported for the particular user from same CSV to populate all the tables from which this data is exported previously.

Is something that is possible with this gem?

Thanks

When I tried to use copy_from, I got this error:

Error: ERROR: null value in column "created_at" of relation "items" violates not-null constraint

Is it possible to ignore constraints on these auto generated columns? I think not wanting to import these values is a common scenario? I tried using columns, but didn't work.

I'm seeing an issue where column names are:

A) converted to lowercase (they are uppercase in the SQL)
B) cannot be remapped in ruby code (without hacking around in the enumerator).

Would a PR to add the map: {} syntax to CSV exports be mergeable?

When I tried to use this gem on Jruby 1.7.10 I got the following error, basically it looks like this gem has a hard requirement on the MRI / cruby 'pg' gem at the moment. Could this be fixed to support running on Jruby?

Gem::Installer::ExtensionBuildError: ERROR: Failed to build gem native extension.

    /Users/me/.rbenv/versions/jruby-1.7.10/bin/jruby extconf.rb 
NotImplementedError: C extension support is not enabled. Pass -Xcext.enabled=true to JRuby or set JRUBY_OPTS.

   (root) at /Users/me/.rbenv/versions/jruby-1.7.10/lib/ruby/shared/mkmf.rb:8
  require at org/jruby/RubyKernel.java:1083
   (root) at /Users/me/.rbenv/versions/jruby-1.7.10/lib/ruby/shared/rubygems/core_ext/kernel_require.rb:1
   (root) at extconf.rb:2


Gem files will remain installed in /Users/me/.rbenv/versions/jruby-1.7.10/lib/ruby/gems/shared/gems/pg-0.17.1 for inspection.
Results logged to /Users/me/.rbenv/versions/jruby-1.7.10/lib/ruby/gems/shared/gems/pg-0.17.1/ext/gem_make.out
An error occurred while installing pg (0.17.1), and Bundler cannot continue.
Make sure that `gem install pg -v '0.17.1'` succeeds before bundling.

Hi,

There are 2 Vulnerabilities discovered here:

GHSA-ggxm-pgc9-g7fp
https://security.snyk.io/vuln/SNYK-RUBY-RAKE-552000

These requires rdoc version to be updated to 6.3.1 whereas rake version to be updated to 12.3.3 in order to resolve these High severity vulnerabilities.

I was hoping if it would be possible to bump the versions of these gems in this rubygem ?

Couldn't it bring in only active_support and active_record?

i've a csv file, when i check using unix file command, for example :

file -b --mime data.csv

its return text/plain; charset=us-ascii

But when i try to import using postgres-copy i've an exception :

ActiveRecord::StatementInvalid: PG::CharacterNotInRepertoire: ERROR:  invalid byte sequence for encoding "UTF8": 0x92 

how do i set a character encoding before import a csv to the database ?

A new responder might be needed.

Howdy,

The equivalent COPY command:

copy public.import_customers FROM '/Volumes/MainHD/Users/billy/Projects/standard_supply/db/import_files/Customer.csv' DELIMITER ',' CSV HEADER;

works well to populate the target table, but the console postgres-copy command:

ImportCustomer.copy_from "/Volumes/MainHD/Users/billy/Projects/standard_supply/db/import_files/Customer.csv"

generates the following error:

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "customer_id" of relation "import_customers" does not exist

There is definitely a "customer_id" column in the table, which BTW has 230 columns and a header row that matches the schema of the csv file that I am importing.

It acts as if it is not reading the correct schema, or perhaps the 230 columns is too many columns???

psql query of the first few rows of the target table after the COPY command was run:

$ ~/Projects/ss_project: psql -U billy -d ss_development
psql (9.3.1)
Type "help" for help.

ss_development=# select customer_id, company_id, taxable_flag from import_customers;
customer_id | company_id | taxable_flag
-------------+------------+--------------
100014 | SSD | Y
1 | SSD | N
100987 | SSD | Y
100988 | SSD | Y
100989 | SSD | Y
100990 | SSD | Y
100991 | SSD | N

Rails model:

class ImportCustomer < ActiveRecord::Base
acts_as_copy_target
end

Appendix A

rails migration file contents:

create_table :import_customers, :id => false do |t|
t.integer :customer_id
t.string :company_id, :limit => 8
t.string :taxable_flag, :limit => 10
t.string :resale_certificate, :limit => 40
t.decimal :credit_limit, precision: 22, scale: 4
t.decimal :credit_limit_used, precision: 22, scale: 4
t.string :terms_id, :limit => 10
t.string :salesrep_id, :limit => 16
t.string :ar_account_no, :limit => 32
t.string :revenue_account_no, :limit => 32
t.string :cos_account_no, :limit => 32
t.string :allowed_account_no, :limit => 32
t.string :terms_account_no, :limit => 32
t.string :delete_flag, :limit => 10
t.datetime :date_created
t.datetime :date_last_modified
t.string :last_maintained_by, :limit => 30
t.string :customer_id_string, :limit => 22
t.string :freight_account_no, :limit => 32
t.string :brokerage_account_no, :limit => 32
t.string :class_1id, :limit => 8
t.string :class_2id, :limit => 8
t.string :class_3id, :limit => 8
t.string :class_4id, :limit => 8
t.string :class_5id, :limit => 8
t.string :trade_percent_disc
t.string :accept_partial_orders
t.decimal :acceptable_wait_time, precision: 22, scale: 4
t.string :price_file_id
t.string :edi_or_paper, :limit => 10
t.string :security_info, :limit => 10
t.string :interchg_receiver_id, :limit => 15
t.string :intl_san, :limit => 15
t.string :credit_card_no, :limit => 24
t.string :credit_card_type, :limit => 10
t.string :credit_card_name, :limit => 41
t.string :credit_card_expiration_date
t.string :last_check_number, :limit => 40
t.string :last_check_amount
t.string :last_check_date
t.string :receivable_group_id, :limit => 8
t.decimal :credit_limit_per_order, precision: 19, scale: 4
t.string :open_item_balance_forward, :limit => 10
t.string :order_acknowledgments, :limit => 10
t.string :accept_interchangeable_items, :limit => 10
t.string :bill_to_contact_id, :limit => 16
t.string :generate_finance_charges, :limit => 10
t.decimal :limit_max_shipments_per_order, precision: 19, scale: 4
t.string :credit_limit_check_at_shipment, :limit => 10
t.string :generate_customer_statements, :limit => 10
t.string :sic_code
t.decimal :minimum_order_dollar_amount, precision: 19, scale: 4
t.string :federal_exemption_number, :limit => 40
t.string :other_exemption_number, :limit => 40
t.string :floor_plan_account, :limit => 10
t.string :default_disposition, :limit => 10
t.decimal :fc_percentage, precision: 19, scale: 4
t.decimal :fc_grace_days, precision: 19, scale: 0
t.decimal :minimum_finance_charge, precision: 19, scale: 4
t.string :fc_cycle, :limit => 10
t.string :last_fc_date
t.string :statement_frequency_id, :limit => 8
t.string :highest_credit_limit_used
t.string :billed_on_gross_net_qty, :limit => 10
t.string :state_excise_tax_exemption_no, :limit => 40
t.string :credit_status, :limit => 8
t.string :deferred_revenue_account_no, :limit => 32
t.string :override_revenue_by_item, :limit => 10
t.string :pick_ticket_type, :limit => 10
t.string :finance_chg_revenue_account_no, :limit => 32
t.string :finance_charge_ship_to_id
t.string :customer_name
t.string :use_consolidated_invoicing, :limit => 10
t.string :ci_for_complete_orders_only, :limit => 10
t.string :ci_print_detail, :limit => 10
t.decimal :invoice_print_qty, precision: 5, scale: 0
t.string :fob_required_flag, :limit => 10
t.string :cod_required_flag, :limit => 10
t.integer :pricing_method_cd
t.string :source_price_cd
t.string :multiplier
t.string :po_no_required, :limit => 10
t.string :consolidated_ship_to_id
t.integer :invoice_batch_uid
t.string :customer_statement_history_uid
t.string :trading_partner_name
t.string :default_rebate_location_id
t.string :lot_bill_summary_on_invoices, :limit => 10
t.string :print_zero_dollar_customers, :limit => 10
t.integer :generate_statements_by
t.string :pending_payment_account_no, :limit => 32
t.string :print_prices_on_packinglist, :limit => 10
t.string :job_pricing, :limit => 10
t.string :record_type
t.string :record_source
t.string :slx_contactid
t.integer :statement_batch_uid
t.string :allow_advance_billing, :limit => 10
t.string :advance_bill_account_no, :limit => 32
t.integer :include_non_alloc_on_pick_tix
t.string :exclude_canceld_from_pick_tix, :limit => 10
t.string :exclude_hold_from_pick_tix, :limit => 10
t.string :exclude_canceld_from_pack_list, :limit => 10
t.string :exclude_hold_from_pack_list, :limit => 10
t.string :exclude_canceld_from_order_ack, :limit => 10
t.string :exclude_hold_from_order_ack, :limit => 10
t.string :disp_addl_info_on_invc_flag, :limit => 10
t.string :remit_to_address_id
t.string :default_branch_id, :limit => 8
t.string :cust_part_no_group_hdr_uid
t.decimal :currency_id, precision: 19, scale: 0
t.string :override_profit_limit, :limit => 10
t.string :minimum_order_line_profit
t.string :maximum_order_line_profit
t.string :minimum_order_profit
t.string :maximum_order_profit
t.string :date_acct_opened
t.string :created_by
t.integer :include_non_alloc_on_pack_list
t.string :print_packinglist_in_shipping, :limit => 10
t.string :source_type_cd
t.string :pricing_method_cd_web
t.string :source_price_cd_web
t.string :multiplier_web
t.string :default_orders_to_will_call, :limit => 10
t.string :send_ucc128info, :limit => 10
t.string :mfr_no
t.string :always_use_job_price, :limit => 10
t.string :allow_non_job_item, :limit => 10
t.string :prompt_for_non_job_item, :limit => 10
t.string :allow_exceed_job_qty, :limit => 10
t.string :print_lot_attrib_on_invoice, :limit => 10
t.string :print_lot_attrib_on_packlist, :limit => 10
t.string :over_tolerance_percentage
t.string :under_tolerance_percentage
t.integer :customer_type_cd
t.string :lead_source_id, :limit => 8
t.string :data_identifier_group_uid
t.string :passport_customer_id
t.string :cost_center_tracking_option
t.integer :fascor_wms_pricing_option
t.string :allow_item_level_contract_flag, :limit => 10
t.decimal :ship_to_credit_limit, precision: 19, scale: 9
t.string :legacy_id
t.string :parker_customer_cd, :limit => 10
t.string :web_enabled_flag, :limit => 10
t.string :allow_line_item_freight_flag, :limit => 10
t.string :freight_charge_uid
t.string :send_dsc856_flag, :limit => 10
t.string :pass_through_option_flag, :limit => 10
t.string :enable_budget_codes_flag, :limit => 10
t.string :enable_aggregate_budgets_flag, :limit => 10
t.string :max_orders_per_budget
t.string :service_terms_id, :limit => 10
t.decimal :downpayment_percentage, precision: 19, scale: 2
t.string :req_pymt_upon_release_of_items, :limit => 10
t.string :include_dp_summary_on_invoices, :limit => 10
t.string :job_number_required_flag, :limit => 10
t.string :district_id
t.string :preferred_flag, :limit => 10
t.string :use_last_margin_pricing_flag, :limit => 10
t.integer :invoice_comp_cost_cd_tier1
t.string :apply_fuel_surcharges_cd
t.string :applied_fuelcharges_to_ds_flag, :limit => 10
t.string :suppress_zero_dollar_flag, :limit => 10
t.integer :invoice_comp_cost_cd_tier2
t.integer :invoice_comp_cost_cd_tier3
t.string :signature_required_flag, :limit => 10
t.integer :days_until_quote_expires
t.string :reclaim_discount_on_memos_flag, :limit => 10
t.string :promise_date_buffer
t.string :order_surcharge_uid
t.string :salesrep_assigned_date
t.string :clock_cell_tracking_flag, :limit => 10
t.string :environmental_fee_flag, :limit => 10
t.string :admin_fee_flag, :limit => 10
t.string :epa_cert_on_file_flag, :limit => 10
t.string :order_disc_type
t.string :order_disc_factor
t.string :split_cores_flag, :limit => 10
t.decimal :freight_markup_multiplier, precision: 19, scale: 9
t.string :floor_plan_taxable_flag, :limit => 10
t.string :use_vendor_item_terms_flag, :limit => 10
t.string :rental_update_flag, :limit => 10
t.string :use_vendor_contracts_flag, :limit => 10
t.string :pedigree_customer, :limit => 10
t.string :national_account_flag, :limit => 10
t.string :order_priority_uid
t.string :manufacturer_distributor_no
t.string :manufacturer_program_type_pct
t.string :manufacturer_rebate_loc
t.string :foreign_currency_guarantee_type, :limit => 10
t.string :sales_market_group_uid
t.string :special_labeling_flag, :limit => 10
t.string :special_packaging_flag, :limit => 10
t.string :sfdc_account_id
t.string :sfdc_create_date
t.string :sfdc_update_date
t.string :use_sys_ups_handling_chrg_flag, :limit => 10
t.decimal :ups_handling_charge, precision: 19, scale: 2
t.string :zoom360_grade
t.string :zoom360_grade_expiration_date
t.string :price_label_flag, :limit => 10
t.string :print_complete_orders_flag, :limit => 10
t.string :buy_list_hdr_uid
t.string :days_overdue_for_credit_hold
t.string :print_pick_fee_flag, :limit => 10
t.string :gl_code_list_hdr_uid
t.string :cmi_customer_note, :limit => 8000
t.string :gl_code_override_flag, :limit => 10
t.string :rma_revenue_account_no, :limit => 32
t.string :so_po_no_required_flag, :limit => 10
t.string :ar_batch_type, :limit => 10
t.string :use_int_address_format_flag, :limit => 10
t.string :ucc128_form_filename
t.string :lowest_across_libraries_flag, :limit => 10
t.string :price_rounding_flag, :limit => 10
t.string :dealer_wrrty_claims_account_no, :limit => 32
t.string :include_aging_info_on_invoice_flag, :limit => 10
t.string :decimal_precision_price
t.string :private_label_flag, :limit => 10
t.string :cfn_cost_goods_sold_account, :limit => 32
t.string :cfn_receivable_account, :limit => 32
t.string :cfn_revenue_account, :limit => 32
t.string :servicebench_servicer_no
t.string :apply_convenience_fee_flag, :limit => 10
t.string :default_kit_markup_percent
t.string :fiscal_year_start_month
t.string :sales_tax_payable_account_no
t.string :customer_tax_class

Thanks for the great work.

copy_to_string is nice, but how about adding a method to do the reverse?

/usr/local/Cellar/ruby/1.9.3-p194/lib/ruby/gems/1.9.1/gems/postgres-copy-0.5.4/lib/postgres-copy/csv_responder.rb:1:in `<top (required)>': uninitialized constant Responders (NameError)

I was poking around this gem but ultimately was unable to use it because I need to do some upserting, which is a newish feature as of postgres 9.5.

https://www.postgresql.org/docs/current/static/sql-insert.html#SQL-ON-CONFLICT

It's definitely a bit tricky to implement - any copy_from would need to know the indices that could conflict and then how to update the rows with DO UPDATE SET - but it would be a great feature to have.

In my solution I ultimately had to resort to this methodology:

csv_data = CSV.parse(csv_string, headers: true)
csv_file = Tempfile.new([client.name, '_client_data', 'csv'])
tmp_table_name = "tmp_#{table_name}"

ActiveRecord::Base.connection.execute("
  CREATE TEMP TABLE #{tmp_table_name} AS SELECT * FROM #{table_name} WITH NO DATA;
  COPY #{tmp_table_name} (#{csv_data.headers.join(',')}) FROM '#{csv_file.path}' CSV HEADER;

  INSERT INTO #{table_name}
    SELECT *
    FROM #{tmp_table_name}
    ON CONFLICT (#{unique_key_columns.join(',')})
    DO UPDATE SET
      (updated_at, #{cols_to_update.join(',')}) =
      (NOW(), #{cols_to_update.map { |c| "EXCLUDED.#{c}" }.join(',')});

  DISCARD TEMP;
")

find_executable: checking for pg_config... -------------------- yes

" -o conftest -I/include/universal-java1.8 -I/usr/local/Cellar/rbenv/1.1.0/versions/jruby-9.1.6.0/lib/ruby/include/ruby/backward -I/usr/local/Cellar/rbenv/1.1.0/versions/jruby-9.1.6.0/lib/ruby/include -I. -I/usr/local/Cellar/postgresql/9.6.1/include -D_XOPEN_SOURCE -D_DARWIN_C_SOURCE -fPIC -DTARGET_RT_MAC_CFM=0 -fno-omit-frame-pointer -fno-strict-aliasing -fexceptions conftest.c -L. -L/usr/local/Cellar/rbenv/1.1.0/versions/jruby-9.1.6.0/lib -L/usr/local/lib -arch x86_64 "
checked program was:
/* begin */
1: #include "ruby.h"
2:
3: #include <ruby.h>
4: int main(int argc, char *argv)
5: {
6: return 0;
7: }
/ end */

Currently there's a CSV responder for those using inherited_resources.

I'd like to avoid that dependency, so I've just rolled the code into my project, but I think render_to_enumerator would be a sensible addition alongside (e.g.) render_to_string.

Would a PR be welcome?

Does the existing version (1.5.0) of postgres-copy work on Ruby 3.0? I didn't see anything in the docs confirming that it does. Also the CI only tests for ruby 2.7.

I would be happy to submit a PR to help if it needs work.

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

Is generating the following SQL command:
COPY (SELECT name FROM "users" WHERE "users"."id" IN (1, 2, 3)) TO '/tmp/users.csv' WITH DELIMITER ',' CSV HEADER
※Mentioed column is not present in SQL command

Thank you.

Hey Diogo,

$ bundle 
Fetching gem metadata from http://rubygems.org/.
Unfortunately, the gem postgres-copy (0.3.5) has an invalid gemspec. As a result, Bundler cannot install this Gemfile. Please ask the gem author to yank the bad version to fix this issue. For more information, see http://bit.ly/syck-defaultkey.

I'm not pretty sure why it's happening, but it's begin a pain install any version of postgres-copy just because the error above, even when I force it to use 0.3.6.

Maybe would be the case to yank version 0.3.5.