Finding line segment of point on linestring using PostGIS

This probably has to be done by iterating over the line segments and finding the index of the one with minimum distance to the point.

This would be a nice addition to the ST_(3D)ClosestPoint functions. Or perhaps more appropriately it could be an extension to one or more of the Linear Referencing functions

If you want the segment index in order to add a vertex to the LineString at the closest point, then ST_Snap can be used to add the point in one step:

SELECT ST_AsText( ST_Snap('LINESTRING (0 0, 9 9)', 'POINT(1 1.1)', 0.2));

Here is SQL to find the index of the segment which is closest to a point:

WITH data(id, geom) AS (VALUES
    ( 1, 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry )
)
SELECT i FROM (
SELECT i, ST_Distance(
   ST_MakeLine( ST_PointN( data.geom, s.i ), ST_PointN( data.geom, s.i+1 ) ),
   'POINT(15 15.1)'::geometry ) AS dist
   FROM data JOIN LATERAL (
        SELECT i FROM generate_series(1, ST_NumPoints( data.geom )-1) AS gs(i)
     ) AS s(i) ON true
   ORDER BY dist
) AS t LIMIT 1;

Not the simplest, but it could be wrapped up in a function.

Here's a SQL function to compute the segment index of the LineString segment closest to a given point:

CREATE OR REPLACE FUNCTION ST_LineLocateN( line geometry, pt geometry )
RETURNS integer
AS $$
    SELECT i FROM (
    SELECT i, ST_Distance(
        ST_MakeLine( ST_PointN( line, s.i ), ST_PointN( line, s.i+1 ) ),
        pt) AS dist
      FROM generate_series(1, ST_NumPoints( line )-1) AS s(i)
      ORDER BY dist
    ) AS t LIMIT 1;
$$
LANGUAGE sql STABLE STRICT;

Example:

SELECT ST_LineLocateN( 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry, 
  'POINT(15 15.1)'::geometry);
==> 2