[Tile-serving] [osm2pgsql-dev/osm2pgsql] Proposal for new data types (Issue #2274)

[Hidde Wieringa]

hiddewie left a comment (osm2pgsql-dev/osm2pgsql#2274)

**Arrays**

Array datatypes would be useful and reduces boilerplate code in Lua Osm2Psql import scripts.

Both numeric and string array content is useful. Numeric for referencing collections of OSM IDs, and string arrays for `;`-split OSM tag values.

For example I use
```lua
osm2pgsql.define_table({
  # ...
  columns = {
    # ...
    { column = 'operator', sql_type = 'text[]' },
  }
})
```
with pre-generated SQL array syntax to fill the column.

Having specific `int[]` and `text[]` types that automatically convert Lua tables to valid (escaped!) SQL in the correct type would be useful. I use a conversion function like
```lua
function to_sql_array(items)
  -- Put the items in a table into a raw SQL array string (quoted and comma-delimited)
  if not items then
    return nil
  end

  local result = '{'

  for index, item in ipairs(items) do
    if index > 1 then
      result = result .. ','
    end

    -- Raw SQL array syntax
    result = result .. "\"" .. item:gsub("\\", "\\\\"):gsub("\"", "\\\"") .. "\""
  end

  return result .. '}'
end
```

For the proposal on the implementation variants: Variant D sounds most general and easy to use. The implementation could accept only tables, and perform the same conversion for each item into the target SQL type. This would be obvious to Osm2Psql users because the array types would have the same conversion behaviour as singular types.

I expect integrating array-type-support into Osm2Psql would also be more performant, to avoid allocating many strings just to convert a Lua table into an SQL string. Streaming this directly from the input table to Postgres should (theoretically) lower allocations.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/issues/2274#issuecomment-3124451105
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/issues/2274/3124451105 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20250727/07eef5d1/attachment.htm>