Unable to edit SRID in PostgreSQL pgAdmin

Question

I am a new user to storing things GIS in PostgreSQL. My experience comes wholly from the QGIS training manual for 3.28.3 and some limited experience with QGIS.

I added the PostGIS extension to my database (address per the instructions). I then added geometry to the table called people. I started looking for where the CRS was stored and found a field in the VIEW called 'geometry_columns' called SRID. I looked at the data in this view for the people table. I discovered that the field is locked and uneditable. Further I discovered that the SQL attached to it did not allow inserts or updates. At this point I was quite confused.

I persisted and discovered that I could import a shapefile and did so, not knowing the CRS or SRID was set to 0. Again I tried to find a way to edit the entry in the database to no avail. I did load it as a new user I created.

I reimported when I discovered I had to assign a CRS even though the shapefile had an entry in it for that CRS. I then used the processing tools 'export to PostGreSQL' and it did store the SRID properly. I did the same with the layer created from the shapefile with 0 or the srid to a new layer in postgreSQL. Both the latter correctly stored the SRID.

Is there anyway to edit, after the fact, the srid stored in the geometry_columns view manually within pgAdmin? I am really unclear why the field was locked! And as a result was totally surprised to learn that I could send it to the database from QGIS. Perhaps the only way is to get it right the first time.

But there has got to be a way to unlock the srid even though it is in a view....I just don't know how to do it.

Answer 1

The CRS of your data is stored as a typemod of the column, postGIS keeps track of this value in the geometry_columns view which as you have discovered you can't edit (changing this would confuse postGIS).

To set this typemod you need to use UpdateGeometrySRID

SELECT UpdateGeometrySRID('roads','geom',4326);

or if you prefer you can set the CRS of your geometry column which can be done using ST_SetSRID

ALTER TABLE roads
  ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326)
  USING ST_SetSRID(geom,4326);

Note Neither of these operations will change the coordinates of your data, they simply update the metadata to be correct (hopefully). If you want to change your projection then you need to use ST_Transform.