[postgis-users] SRID in geometry_columns view

[Mike Toews]

On 25 July 2012 13:48, Richard Greenwood <richard.greenwood at gmail.com> wrote:
> Okay, but there is an example in the doc's showing just geometry(srid)
> which doesn't work for me. Guess that's what you're saying?

I now see what you are looking at, and it's a typo in the docs.

> I'm dealing with a view, not a table. My table shows the correct srid
> in geometry_columns but the view which is based upon the table shows a
> srid of 0. I don't want or need to transform the geometry. I just need
> for its srid to be correctly reflected in the geometry_columns view.

OK, I follow you correctly now. You are using the older-style
constraints on your table, which looks something like:

ALTER TABLE my_table
  ADD CONSTRAINT enforce_srid_wkb_geometry
  CHECK (st_srid(wkb_geometry) = 3739);

Although this shows the correct SRID for the table in the
geometry_columns view, it doesn't propagate further to derived views.
The simplest way to get this to work is to drop the older style
constraint, and use the new 2.0 typmod syntax, described above.

ALTER TABLE my_table DROP CONSTRAINT enforce_srid_wkb_geometry;
ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_wkb_geometry;

-- you'll also need to temporarily drop your view; now, e.g. set as Point

ALTER TABLE my_table
    ALTER COLUMN wkb_geometry TYPE geometry(Point,3739)
    USING ST_SetSRID(wkb_geometry,3739);

After restoring your view, you should see the correct geometry type
and SRID for the source table, and all derived views.

-Mike