I'm doing something wrong with copy_data

[studdugie]

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]

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]

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]

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]

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.