How to insert array of strings in postgres using raw query in elixir

Question

I am trying to insert into the postgres using Elixir Ecto.Adapters.SQL.query, the query consists of array of tags which are strings. However it does inset into postgres, but not as a list of strings, but a single string. i.e. if tags contains ["abc","xyz"], it inserts in postgres as abcdef and not as array.

Important to mention, i am not using schema or migrations. Simple raw queries.

Map that needs to be inserted in postgres

  response_map=  %{"description" => "des33", "id" => 158,
  "inserted_at" => "2017-06-09T10:19:03.904572", "tags" => ["abc","xyz"],
  "title" => "test 1", "updated_at" => "2017-06-09T10:19:03.904578"}

Code that i use

%{"description" => desc, "id" => id, "title" => title, "tags" =>  tags}=response_map
Ecto.Adapters.SQL.query!(Repo,"insert into test values ('#{id}','#{title}','#{desc}','#{tags}')")

Does Ecto.Adapters.SQL.query know about Postgres array types? This is a Postgres specific feature, and if the query builder / SQL client doesn't know about this, it won't know how to treat it correctly. — Horia Coman
– Horia Coman, Commented Jun 9, 2017 at 10:25
@HoriaComan so what do you think, how should i insert an array, — Zubair Nabi
– Zubair Nabi, Commented Jun 9, 2017 at 10:26
IDK, that's up to you. You could make tags be a json/jsonb type, of the form { "tags": [ list of tags ] }. If you send it a serialized JSON object postgres will be smart enough to transform it into a JSON field, rather than leave it as a string. — Horia Coman
– Horia Coman, Commented Jun 9, 2017 at 10:28

Dogbert · Accepted Answer · 2017-06-09 10:27:15Z

0

You should never use string interpolation in queries. You're opening yourself to SQL injection attacks. Use parameterized queries and you'll fix the SQL injection as well as make this work with arrays:

Ecto.Adapters.SQL.query!(Repo, "insert into test values ($1, $2, $3, $4)", [id, title, desc, tags])

answered Jun 9, 2017 at 10:27

Dogbert

224k43 gold badges419 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Zubair Nabi Over a year ago

Thank you, but this is what i am getting after the above code (ArgumentError) Postgrex expected a binary, got ["abc", "xyz"]. Please make sure the value you are passing matches the definition in your t able or in your query or convert the value accordingly.

Dogbert Over a year ago

What is the type of the fourth column in PostgreSQL? It should be an Array.

Dogbert Over a year ago

Can you post the output of running \d test from psql connected to your database? (That should print the schema of the table.) I just tested this and it works for me when the column is of type text[].

Zubair Nabi Over a year ago

tags | character varying(255)[]

Dogbert Over a year ago

Can you add the complete output to the question?

|

Collectives™ on Stack Overflow

How to insert array of strings in postgres using raw query in elixir

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related