ERROR: type modifier is not allowed for type "geometry" in PostGIS 2.1

0
votes

On Debian Wheezy I had postgresql 9.1 installed and installed the postGIS 1.5 from the standard apt repo. This didn't have the geometry type however so I added the postgresql apt repo and installed postgis 2.1:

sudo apt-get install postgis-2.1

the last output of which related to postgis was:

Setting up postgresql-9.4-postgis-2.2 (2.2.1+dfsg-2.pgdg70+1)

All appeared good but when I run the following sql the same error as in postGIS 1.5 results:

ALTER TABLE bolls ADD COLUMN location geometry(POINT,-1);

Edit: After instruction from Nick Barnes in comment below I tried ALTER EXTENSION postgis update to "2.1"; and got 

ERROR:  extension "postgis" does not exist

If I try CREATE EXTENSION postgis it tells me PostGIS is already installed.

1
postgresqldebianpostgispostgresql-9.1
Did you run ALTER EXTENSION ... UPDATE? Installing the package makes the new version available, but it won't modify your databases. - Nick Barnes
updated question accordingly - user1561108
Apparently 1.5->2.0+ upgrades are not possible via a simple extension update; a dump/restore of the database is required (see here). This isn't the error message I'd expect, though, so there may be other issues... - Nick Barnes
By the way, as long as you're doing a dump/restore of the database, you might want to consider upgrading from Postgres 9.1 (which will be EOL in September). - Nick Barnes

1 Answers

1
votes

To go from version 1.5 to 2.1, follow the hard upgrade instructions in the manual. These steps, in brief, are:

  1. Dump the old database to a file
  2. Spatially enable a new empty database (you've already done this with CREATE EXTENSION postgis)
  3. Use a special utility postgis_restore.pl that came with your new version of PostGIS to translate the old format to the new format.

Also geometry(POINT,-1) is invalid. If you don't have an SRID, it's just geometry(POINT).