Ecto Schema for a postgres varchar array

Questions / Help
1

I have a table with an varchar array in Postgres called paramaters, defined as create table ... parameters varchar[]

I have defined an Ecto Schema with a field like this field :parameters, {:array, :string}

Inserting the string "{a,b,c}" like this works using the Postgres console directly, but fails silently using a create in Ecto.

What am I missing in this whole thing?

2

It should be {:array, :string}, not {:list, :string}.

3

Sorry, that is what I had, I just forgot to update the post.

4

I ended up doing this, but I was hoping for a more elegant solution.

"{a,b,c}" 
          |> String.replace("{", "")
          |> String.replace("}", "")
          |> String.split(",")
5

Postgres will do certain type casting and conversions with literals in SQL that it won’t do for parameters.

Can you show more complete code? Where is this input coming from, and how are you using it?

6

The input is coming from an api endpoint, but it is just a string with the form "{a,b,c}"

I created this snippet here with most of the relevant code, Ecto schema for a postgres table ($2385678) · Snippets · Snippets · GitLab

The data is coming from here, https://api.ust.is/aq/a/getStations which gives information about air quality measurement stations in Iceland. I wanted to do something with the data since we have yet another volcanic eruption going on at the moment.

7

If I’m reading you correctly, you’re having trouble inserting the data into the database from Ecto, but having not trouble doing so directly into PostgreSQL tools (psql, etc)? If so you might be having trouble because you’re going straight to the syntax that PostgreSQL expects for array literals and getting gummed up by the abstraction that Ecto puts over such fields.

I’ve seen your snippet, but it only has the code that builds the PostgreSQL literal string and the Ecto schema definition. What’s really at stake is the insert and that’s not in the snippets.

If I’ve understood the problem correctly; pretty sure Ecto is expecting you to give it a list of strings from which it will handle dealing with the conversion to the PostgreSQL specific representation. You shouldn’t need to convert it to the PostgreSQL literal syntax yourself.

EDIT: I’ve re-read the thread and I might have misunderstood. Is the problem reading it from the database? If so, my comment isn’t really changed much. You shouldn’t have to do the parsing on the way out either; pretty sure you’d get an Elixir list of strings based on the Ecto definition: Postgrex — Postgrex v0.17.4

1 Like
8

The problem is, best I can tell, that the remote API is returning a list of things in the format "{a,b,c}" which is weird. They’re trying to insert this into postgres. They’re not trying to build that string, they’re being given that string by the remote API.

@arni1981 if the remote API is giving you literally "{a,b,c}" then I think the code you have is basically fine, but that’s a very weird string for them to give you.

1 Like
9

I’d still convert that code to a list of strings and just feed it to Ecto. Should be easy unless I am misunderstanding OP.

value =
  string_from_api
  |> String.trim_leading("{")
  |> String.trim_trailing("}")
  |> String.split(",")

changeset = YourSchema.changeset(%YourSchema{}, %{parameters: value, ...})

This should work, shouldn’t it? Unless the changeset function itself doesn’t include the field, or transforms it somehow.

1 Like
10

It does work as I said, I was just hoping for a more elegant solution. If one is not available I’m fine with this solution.

11

Yes, I guess this string replacing and splitting is necessary because of the way the api returns its result.