Updating the Entry in geometry_columns for Spatial Table Created from a Query

Question

I created a spatial table from a query. Now, I notice in geometry_columns, the following entry for the table:

 f_table_catalog | f_table_schema |    f_table_name    | f_geometry_column | coord_dimension |  srid  |     type     
      my_db      | public         | my_table           | geom              |               2 |      0 | GEOMETRY

I want the srid to be 4326 (this is the srid of the original table) and the type to be Linestring (currently Geometry). Running the following,

SELECT Populate_Geometry_Columns('public.my_table'::regclass, false);

updated the srid in geometry_columns to 4326. However the type is still Geometry. How can I make sure that the geom column in my_table is properly recognized in the geometry_columns and its type is Linestring?

Answer 1

With PostGIS 2.x, geometry_columns is a VIEW, not a TABLE.

Try altering my_table.geom to your specifications using:

ALTER TABLE my_table
    ALTER COLUMN geom TYPE geometry(LineString, 4326) USING ST_SetSRID(geom, 4326);

depending on the contents of geom, you may need to modify the USING <expression> part.