Ruby/Rails Array of Strings to Postgresql Insert

Ruby / Rails array of strings to PostgreSQL insert

For something like this I'd ignore the ActiveRecord quoting and escaping stuff and go straight to ActiveRecord::Base.connection.quote.

The SQL you're trying to end up with is something like this:

INSERT INTO TABLE (column)
SELECT column FROM TABLE
UNION VALUES ('example one''s value'), ('example (2) value')
EXCEPT SELECT column FROM TABLE

You can use quote to convert ' to '' (note that SQL doubles single quotes to escape them) but you'll have to add the appropriate parentheses in the VALUES yourself. The connection's quote method will also add the outer single quotes.

Something like this:

values = array_of_strings.map { |s| "(#{ActiveRecord::Base.connection.quote(s)})" }.join(',')

followed by:

sql = %Q{
    INSERT INTO TABLE (column)
    SELECT column FROM TABLE
    UNION VALUES #{values}
    EXCEPT SELECT column FROM TABLE
}

should do the trick and be safe.

How to I insert an array into a Postgresql table using ActiveRecord?

I was able to insert using the following Ruby code:

alternatenames = '{' + s[3].split(/,/).map {|n| '"' + n + '"'}.join(",") + '}'

adding array as parameter to sql query in ruby

As others said, you can't parametrise a whole array. Use this instead:

ziparray = ["95626", "95645", "95837"]
zip_placeholders = ziparray.map.with_index(1) { |_, i| "$#{i}" }.join(', ')
sql = "SELECT * from table_name WHERE code in (#{zip_placeholders});"
# => "SELECT * from table_name WHERE code in ($1, $2, $3)"

Then you can use the normal parameter binding.

insert array into postgresql map prepared statement

The exec_params method doesn't take a prepared statement name, that's not a supported argument.

You're probably intending to use the send_query_prepared method which does have that argument.

Here's a refactored version of your code:

a.each_with_index do |r, i|
  con.prepare("statement_#{i}","INSERT INTO keywords (keyword, created_at, updated_at) VALUES ($1, $2, $3)")
  con.exec_prepared("statement_#{i}", [r, '2017-01-01 07:29:33.096192', '2017-01-01 07:29:33.096192' ])
end

You should use each instead of map if you're not concerned with the results, as map creates a temporary array of the rewritten entries. Additionally each_with_index avoids manually manipulating i.

how to insert an array of strings to sql query in ruby

Don't do that. Bobby Tables is watching. Instead, provide the adequate number of placeholders:

sql = "SELECT variants.id,
                      code,
                      regular_price,
                      price_before_sale
                FROM  variants
                WHERE variants.code IN (#{context.codes.map { "?" }.join(",")})"

and then provide *context.codes in statement parameters.

Add an array column to a resource

You have to create new migration so rails g migration change_column_type_of_ids_and_length . Then edit generated migration file.

1. First try to use change_column method. If this works, your data will be preserved. Else, try step 2

   change_column :streams , :ids , :string , array: true , default: []
   change_column :streams , :lengths, :integer ,array: true , default: []
   

2. Here we are removing the column so the data , then creating new one.

   remove_column :streams, :ids
   remove_column :streams, :lengths
   add_column :streams , :ids , :string ,array: true , default: []
   add_column :streams , :lengths , :integer ,array:  true , default: []
   

Rails t.array vs t.string array:true, default:[]

This creates an array of strings whose default is an empty array:

t.string :A_options, array: true, default: []

This creates an array of integers whose default is an empty array:

t.integer :A_options, array: true, default: []

This should be a NoMethodError:

t.array :A_options

There is no "array" type in PostgreSQL, only "array of X" where "X" is some other column type. PostgreSQL arrays aren't generic containers like Ruby arrays, they're more like arrays in C, C++, Go, ...

If you need a generic container that's more like a Ruby array then perhaps you want jsonb instead. A jsonb array could hold a collection of numbers, strings, booleans, arrays, hashes, ... at the same time.

---

As far as the generator goes, you can't specify default: [] because you can't specify the default at all:

3.5 Column Modifiers
[...]

null and default cannot be specified via command line.

Also see Can I pass default value to rails generate migration?.

Rails string array and PostgreSQL

This issue is actually arising out of the use of <<. Rails does not track in place modifications of attributes (see this issue). I outline in the usage notes that you want to avoid the << operator when using arrays, as rails will not see this change, it will actually cause issues with default values.

This can also be seen by checking the products_changed? state. It will be false when you use <<.

---

Related Topics