Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (
  gid int,
  idx_pt int,
  x numeric,
  y numeric,
  geom geometry(point,4326) GENERATED ALWAYS AS (ST_MakePoint(x,y)) STORED
);

INSERT INTO mytable VALUES
(1,1,65250.78,700000.52),
(1,2,65250.78,700000.52),
(1,3,65250.78,700000.52),
(1,4,65999.00,700555.00),
(1,5,65999.00,700555.00),
(1,6,65999.00,700555.00);

CREATE TABLE



INSERT 0 6


CREATE INDEX idx_t_gid ON mytable (gid);

CREATE INDEX


SELECT 
  gid,idx_pt,
  FIRST_VALUE(idx_pt) OVER w AS id_unique,
  x,y,geom
FROM mytable
WHERE gid = 1
WINDOW w AS (PARTITION BY gid,geom ORDER BY idx_pt
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY gid,idx_pt;


gid
idx_pt
id_unique
x
y
geom


1
1
1
65250.78
700000.52
0101000020E61000005C8FC2F558DCEF40A4703D0AC15C2541

1
2
1
65250.78
700000.52
0101000020E61000005C8FC2F558DCEF40A4703D0AC15C2541

1
3
1
65250.78
700000.52
0101000020E61000005C8FC2F558DCEF40A4703D0AC15C2541

1
4
4
65999.00
700555.00
0101000020E610000000000000F01CF0400000000016612541

1
5
4
65999.00
700555.00
0101000020E610000000000000F01CF0400000000016612541

1
6
4
65999.00
700555.00
0101000020E610000000000000F01CF0400000000016612541



SELECT 6

gid	idx_pt	id_unique	x	y	geom
1	1	1	65250.78	700000.52	0101000020E61000005C8FC2F558DCEF40A4703D0AC15C2541
1	2	1	65250.78	700000.52	0101000020E61000005C8FC2F558DCEF40A4703D0AC15C2541
1	3	1	65250.78	700000.52	0101000020E61000005C8FC2F558DCEF40A4703D0AC15C2541
1	4	4	65999.00	700555.00	0101000020E610000000000000F01CF0400000000016612541
1	5	4	65999.00	700555.00	0101000020E610000000000000F01CF0400000000016612541
1	6	4	65999.00	700555.00	0101000020E610000000000000F01CF0400000000016612541