CODEWITHSUNDEEP
ruby-on-railsarrayspostgresqlrails-activerecord
Donald
Donald

Reputation: 176

Rails and PSQL arrays, the difference between {} and []?

I recently converted a column in my rails psql database to a PSQL array.

When trying to query against the column using psql's contains operator (<@) I'm getting an error that says

PG::InvalidTextRepresentation: ERROR:  array value must start with "{" or dimension information

The column format looks something like this in the rails console:

["string1",
 "string2",
 "string3"]

During my migration the column was set up like this:

add_column :table, :column, :text, :array => true, null: false, :default => []

and I then copied the contents of a different column into it.

So two questions:

1) Is my migration set up correctly? I've seen some people default their arrays to {} what is the difference?

2) Is the issue my data formatting and if so what can I do to fix it?

Upvotes: 0

Views: 1406

Answers (1)

mu is too short
mu is too short

Reputation: 434665

The standard string representation of an array within PostgreSQL uses { and }, for example:

=> select array[11, 23];
  array  
---------
 {11,23}
(1 row)

So if someone is using '{}' (note the quotes to make it a string) then they're presumably using the string representation of an empty array. When you use:

:default => [ ]

you're using an empty Ruby array as the default and letting ActiveRecord convert that to what PostgreSQL understands.

If your migration ran successfully then presumably it is correct. You can verify that by looking at the table's structure from inside the psql CLI tool, if you say:

\d table_name

you should see something like this:

column | text[] | default '{}'::text[]

in the output.

Upvotes: 5

Related Questions