Creating a primary key on the id columns does not work

[MarcErdmann]

What version of osm2pgsql are you using?

osm2pgsql version 2.1.1
Build: Release
Compiled using the following library versions:
Libosmium 2.22.0
Proj 9.6.2
Lua 5.1.4 (LuaJIT 2.1.1744318430)

What operating system and PostgreSQL/PostGIS version are you using?

Database version: 17.5 (Ubuntu 17.5-1.pgdg22.04+1) running in Docker
PostGIS version: 3.5

Tell us something about your system

32 GB RAM, Apple M4

What did you do exactly?

osm2pgsql --output=flex --style=./osm-flex.lua --create --database=<connectionString> <pathToOsmFile>

-- https://osm2pgsql.org/doc/manual.html

-- OSM Flex Import Script for German Geodata
-- Extracts zip code areas, places, and streets with multilingual names

-- No need for manual JSON encoding - osm2pgsql handles jsonb conversion automatically

-- Helper function to extract multilingual names following OSM standards
-- Returns: default_name (string), language_names (table)
local function extract_names(object)
    local default_name = object.tags.name  -- Common default name in local language
    local language_names = {}
    
    -- Extract language-specific names (name:lang=*)
    for key, value in pairs(object.tags) do
        if key:match('^name:') then
            local lang = key:sub(6) -- Remove 'name:' prefix
            language_names[lang] = value
        end
    end
    
    -- Skip alt_name, prefix, etc. to maintain consistent language-only structure
    -- These could be stored in separate fields if needed for the application
    
    return default_name, language_names
end


-- Define tables with correct osm2pgsql flex syntax
local zip_areas_table = osm2pgsql.define_table({
    name = 'zip_areas',
    ids = { type = 'area', id_column = 'area_id', index = 'primary_key' },
    columns = {
        { column = 'postal_code', type = 'text' },
        { column = 'name', type = 'text' },
        { column = 'names', type = 'jsonb' },
        { column = 'population', type = 'int' },
        { column = 'admin_level', type = 'int' },
        { column = 'area_sqkm', type = 'real' },
        { column = 'geom', type = 'multipolygon', srid = 4326, not_null = true }
    }
})

local places_table = osm2pgsql.define_table({
    name = 'places',
    ids = { type = 'node', id_column = 'node_id', index = 'primary_key' },
    columns = {
        { column = 'name', type = 'text' },
        { column = 'names', type = 'jsonb' },
        { column = 'place_type', type = 'text', not_null = true },
        { column = 'postal_codes', type = 'text' },
        { column = 'population', type = 'int' },
        { column = 'admin_level', type = 'int' },
        { column = 'geom', type = 'point', srid = 4326, not_null = true }
    }
})

local streets_table = osm2pgsql.define_table({
    name = 'streets',
    ids = { type = 'way', id_column = 'way_id', index = 'primary_key' },
    columns = {
        { column = 'name', type = 'text' },
        { column = 'postal_code', type = 'text' },
        { column = 'highway_type', type = 'text', not_null = true },
        { column = 'city', type = 'text' },
        { column = 'housenumber_start', type = 'text' },
        { column = 'housenumber_end', type = 'text' },
        { column = 'geom', type = 'linestring', srid = 4326, not_null = true }
    }
})

-- Process relations (for postal code boundaries only - matches https://github.com/yetzt/postleitzahlen)
function osm2pgsql.process_relation(object)
    local postal_code = object.tags.postal_code or object.tags['addr:postcode']
    local boundary = object.tags.boundary
    local relation_type = object.tags.type
    local admin_level = tonumber(object.tags.admin_level)
    
    -- Process only dedicated postal code boundary relations (exact match to https://github.com/yetzt/postleitzahlen)
    -- Query: relation["type"="boundary"]["boundary"="postal_code"]
    if boundary == 'postal_code' and relation_type == 'boundary' and postal_code then
        local default_name, language_names = extract_names(object)
        local population = tonumber(object.tags.population)
        
        -- Calculate area in square kilometers if possible
        local area_sqkm = nil
        if object.tags['area:km2'] then
            area_sqkm = tonumber(object.tags['area:km2'])
        end
        
        -- Create multipolygon geometry
        local geom = object:as_multipolygon()
        if geom then
            local inserted, message = zip_areas_table:insert({
                postal_code = postal_code,
                name = default_name,
                names = language_names,  -- osm2pgsql will convert table to jsonb automatically
                population = population,
                admin_level = admin_level,
                area_sqkm = area_sqkm,
                geom = geom
            })
            -- Debug failed inserts only
            if not inserted and message then
                print("Failed to insert postal code boundary " .. postal_code .. ": " .. message)
            end
        end
    end
end

-- Process nodes (for places like cities, towns, villages)
function osm2pgsql.process_node(object)
    local place_type = object.tags.place
    
    -- Only process settlements
    if place_type and (place_type == 'city' or place_type == 'town' or 
                      place_type == 'village' or place_type == 'hamlet' or
                      place_type == 'suburb' or place_type == 'neighbourhood') then
        
        local default_name, language_names = extract_names(object)
        local postal_codes = object.tags.postal_code or object.tags['addr:postcode'] or ''
        local population = tonumber(object.tags.population)
        local admin_level = tonumber(object.tags.admin_level)
        
        -- Create point geometry
        local geom = object:as_point()
        if geom then
            local inserted, message = places_table:insert({
                name = default_name,
                names = language_names,  -- osm2pgsql will convert table to jsonb automatically
                place_type = place_type,
                postal_codes = postal_codes,
                population = population,
                admin_level = admin_level,
                geom = geom
            })
            -- Debug failed inserts
            if not inserted and message then
                print("Failed to insert place " .. (object.tags.name or "unnamed") .. ": " .. message)
            end
        end
    end
end

-- Process ways (for streets)
function osm2pgsql.process_way(object)
    local highway = object.tags.highway
    
    -- Only process streets/roads
    if highway and (highway == 'primary' or highway == 'secondary' or 
                   highway == 'tertiary' or highway == 'residential' or
                   highway == 'living_street' or highway == 'service' or
                   highway == 'unclassified' or highway == 'trunk' or
                   highway == 'motorway') then
        
        local name = object.tags.name
        local postal_code = object.tags.postal_code or object.tags['addr:postcode']
        local city = object.tags['addr:city'] or object.tags['addr:municipality']
        
        -- Extract house number ranges if available
        local housenumber_start = object.tags['addr:housenumber:start']
        local housenumber_end = object.tags['addr:housenumber:end']
        
        -- Only insert if we have a name or postal code and highway type
        if (name or postal_code) and highway then
            -- Create linestring geometry
            local geom = object:as_linestring()
            if geom then
                local inserted, message = streets_table:insert({
                    name = name,
                    postal_code = postal_code,
                    highway_type = highway,
                    city = city,
                    housenumber_start = housenumber_start,
                    housenumber_end = housenumber_end,
                    geom = geom
                })
                -- Debug failed inserts
                if not inserted and message then
                    print("Failed to insert street " .. (name or "unnamed") .. ": " .. message)
                end
            end
        end
    end
    
    -- Note: Postal code boundaries are handled as relations only (matching https://github.com/yetzt/postleitzahlen)
    -- Ways are processed only for street data, not postal code boundaries
end

What did you expect to happen?

I expected my tables to be created including the primary key.

What did happen instead?

My tables were created without the primary key. Data was loaded as expected still.

What did you do to try analyzing the problem?

I have tried multiple variants of my lua script with no success.
The PR #2289 added tests that use index.

[MarcErdmann]

Update: Using create_index instead of index works. There seems to be an inconsistency with the test cases which use index

[joto]

I am reopening this because there is clearly a failure in out testing here that needs to be addressed. The test in tests/bdd/flex/lua-table-definitions.feature uses index instead of create_index and still does not fail.

I think there are two problems with this tests:

1. The tests do not check that the indexes are actually created in the right way. We could check this by accessing pg_indexes but we currently don't have a way of checking that in our arsenal of tests. I think we need some way of checking table/view contents against regular expression. In this case we might want to check that SELECT indexdef FROM pg_indexes WHERE tablename='foo' contains UNIQUE or something like that.
2. We did not notice that we were not using the correct create_index and used index instead. osm2pgsql doesn't check for settings it doesn't know about so that we can introduce them later without old versions of osm2pgsql breaking. But maybe we should at least check and print a warning? And then have a mechanism that turns those warnings into errors for tests?

[lonvia]

I'll have a look into (1). I think we can actually do this now with what we have, just couldn't at the time of writing the tests.

Regarding (2), I like that idea. Some kind of strict_mode variable you can turn on. (Parameter would be easier for the tests because it is easier to inject, but I'm not sure we like new parameters.) Shall we move that part into a new issue?

[joto]

Shall we move that part into a new issue?

Created at #2381