DROP TABLE IF EXISTS :schema.nodes CASCADE;

CREATE TABLE :schema.nodes AS
WITH all_nodes AS (
  SELECT ST_StartPoint(geom) AS geom FROM :schema.links
  UNION
  SELECT ST_EndPoint(geom) AS geom FROM :schema.links
),
distinct_nodes AS (
  SELECT DISTINCT geom FROM all_nodes
)
SELECT ROW_NUMBER() OVER () AS id, geom FROM distinct_nodes;

CREATE INDEX IF NOT EXISTS nodes_geom_idx ON :schema.nodes USING GIST (geom);
ALTER TABLE :schema.nodes ADD PRIMARY KEY (id);