I'm doing something wrong with copy_data

18 views
Skip to first unread message

studdugie

unread,
Sep 15, 2020, 1:34:07 PM9/15/20
to ruby-pg
Hello,

I'm trying to copy some data from DynamoDB to PostgreSQL and I can't get it to work. Full disclosure, I'm new to Ruby and thus new to the gem. The error I'm getting is:
  /usr/lib/ruby/gems/2.7.0/gems/pg-1.2.3/lib/pg/connection.rb:174:in `get_last_result': ERROR:  missing data for column "line_number" (PG::BadCopyFileFormat)

Here is some code. There is a break statement in there because I just wanted to try inserting a few rows to make sure everything worked.

sql = "COPY raw_dialog_lines FROM STDIN WITH DELIMITER AS '|'"
enco = PG::TextEncoder::CopyRow.new
pgconn.copy_data sql, enco do
  transcript.each do |hash|
    id = hash[key_name]
    next if invalid_keys.key? id

    id_opt[':id'] = id
    resp = dyndb.query qry
    resp.items.each do |item|
      match_stat = item['match_status'].nil? ? 'NULL' : item['match_status']
      topic = item['topic'].nil? ? 'NULL' : item['topic']
      udb_script = item['udb_script'].nil? ? 'NULL' : item['udb_script']
      rubric_keys = if item['rubric_keys'].nil?
                      'NULL'
                    else
                      item['rubric_keys'].join(',')
                    end
      data = [
        id,
        item['dialogue_line_position'].to_i,
        item['eventtype'],
        item['listener'],
        item['said_at'],
        item['speaker'],
        item['meaning'],
        match_stat,
        topic,
        udb_script,
        rubric_keys
      ]
      puts data.join('|') + "\n"
      pgconn.put_copy_data(data.join('|') + "\n")
    end
  break if true
  end
end

Any insights would be appreciated. Thanks

Lars Kanis

unread,
Sep 15, 2020, 1:49:41 PM9/15/20
to ruby-pg
Hi,

you're using a CopyRow encoder, so you shouldn't specify a delimiter and you have to pass an array of columns to put_copy_data.

Regards, Lars

studdugie

unread,
Sep 15, 2020, 5:21:44 PM9/15/20
to ruby-pg
This is a continuation from my previous message about passing an array.

As I was saying before I was initially passing an array. As you can see from the code an array is being created (i.e., data = [ ...). When I couldn't get it to work by passing the array I modified the code to generate a string by calling join on the array.

studdugie

unread,
Sep 15, 2020, 5:21:44 PM9/15/20
to ruby-pg
I initially tried it that way and it didn't work either. The error I got then was "no data for rubric_keys column".

On Tuesday, September 15, 2020 at 1:49:41 PM UTC-4 Lars Kanis wrote:

Lars Kanis

unread,
Sep 16, 2020, 1:17:20 AM9/16/20
to ruby-pg
The columns sent have to exactly match the table columns, if you send a COPY command without column definition. Otherwise it fails with an error like you've got. Also you have to send NULL values as nil in the Ruby array.

--
You received this message because you are subscribed to the Google Groups "ruby-pg" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ruby-pg+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ruby-pg/8d60172f-7d0a-49a3-a685-02d635b34b62n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages