Changing the data type of a column in PostgreSQL 9.1

Question

I'm learning PostgreSQL and PostGIS through "PostGIS in Action" book. My problem comes from Pg. 66 in Listing 3.4.

My code is as follows:

CREATE TABLE ch03.paris_polygons(tags hstore, CONSTRAINT paris_polygons_pk PRIMARY KEY (gid)
)
INHERITS (ch03.paris);

ALTER TABLE ch03.paris_polygons NO INHERIT ch03.paris;
INSERT INTO ch03.paris_polygons(osm_id, ar_num, geom, tags, feature_name, feature_type)
SELECT osm_id, ar_num, ST_Multi(geom) As geom, tags, tags->'name',
COALESCE(tags->'tourism', tags->'railway','other')::varchar(50) As feature_type
FROM ch03.paris_hetero
WHERE ST_GeometryType(geom) LIKE '%Polygon';
SELECT populate_geometry_columns('ch03.paris_polygons'::regclass);
ALTER TABLE ch03.paris_polygons INHERIT ch03.paris;

I received this error after running the code:

ERROR: child table "paris_polygons" has different type for column "geom"
SQL state: 42804

I Googled this to find this:

-204 (ECPG_INT_FORMAT)

The host variable is of type int and the datum in the database is of a different type and contains a value that cannot be interpreted as an int. The library uses strtol() for this conversion. (SQLSTATE 42804)

What would psql command(s) would help me to know how to and merge these?

Thanks again for all the help!

What's the deal with inheriting `paris` then immediately `ALTER`ing it to be `NO INHERIT`? That's just weird. — Craig Ringer, Jan 02 '13 at 00:06
I'm not sure as I'm extremely new to PostgreSQL. The code was taken from the book. — Zach, Jan 02 '13 at 00:14
The search result references ECPG. Unless you're using ECPG, it's irrelevant. At what line in the code did you get the error? What's the definition of `paris`? Show `\d paris` output from `psql`. — Craig Ringer, Jan 02 '13 at 02:05
Does the parent table have a typmod like `geometry(Polygon,4326)`? — Mike T, Jan 02 '13 at 21:45
@Craig Ringer Unfortunately with PostgreSQL 9.1, I can't use \d. Is this the same as [http://stackoverflow.com/a/109337/1777654] or is there another way to check? — Zach, Jan 03 '13 at 00:59
@MikeToews I generated a properties table report on the parent table `paris_polygons` specifically on the `geom` column and I think my problem might be that I'm trying to modify a column that can't be modified as it's inherited. This is a very simple question but I'm Google searched out: how can I check the typmod of the parent table? — Zach, Jan 03 '13 at 01:36
The typmod is viewed with either `\d ch03.paris` in psql (in the "Type" column), or is visible in SQL pane of pgAdminIII. If there is no typmod set, it will look like `geometry` with nothing afterwards. — Mike T, Jan 03 '13 at 01:57
@MikeToews `geom` column in `ch03.paris` is `geometry`, so there's no typmod set. — Zach, Jan 03 '13 at 02:08

score 2 · Accepted Answer · answered Jun 13 '13 at 20:23

2

I just ran into this. The book most likely forgot this step in the snippet of code you (and I) were looking at. But, if you read on to the following page, Where they show just the construction of the LINESTRING-partitioned table, there's a key line there that they forgot in the POLYGON-partitioned table construction:

ALTER TABLE ch03.paris_polygons
ADD CONSTRAINT enforce_geotype_geom
CHECK (geometrytype(geom) = 'POLYGON'::TEXT);

Once I added that constraint, the error went away, and basically what it causes to happen is to allow the geom column to inherit the parent geom column type, rather than the geometry('polygon',SRID) type that it assumes based off the data load, and leaves the constraint to manage the type restriction.

answered Jun 13 '13 at 20:23

Hassan Shahid

309
2
6

Also, just to address @Craig Ringer's comment about enabling INHERIT on the child table, and then immediately altering table to NO INHERIT: The book tries to follow best practices in working with Postgres Table-Inheritance. They first created the child table with inheritance so the table receives all the parent's properties, and then they turn off inheritance so they can do bulk inserts to the child table without impacting the parent or the parent's other child tables. Surely, they didn't have to do it in their contrived example, but in an effort to promote best practice, it makes sense. – Hassan Shahid Jun 14 '13 at 03:09
This doesn't work anymore. Now you get an error while inserting a new row: 'ERROR: new row for relation "paris_polygons" violates check constraint "enforce_geotype_geom"' – picardo Aug 20 '14 at 01:05

score 0 · Answer 2 · edited Jan 19 '15 at 09:32

0

I encountered the same error. I tried to use 'MULTIPOLYGON' instead:

ALTER TABLE ch03.paris_polygons
ADD CONSTRAINT enforce_geotype_geom
CHECK (geometrytype(geom) = 'MULTIPOLYGON'::TEXT);

edited Jan 19 '15 at 09:32

Zero Piraeus

56,143
27
150
160

answered Jan 19 '15 at 09:24

Su Hnin

1

Is this an answer or a question with a failed attempt at solving the problem ? If the latter, you need ask a new question. – Jan 19 '15 at 09:51

Changing the data type of a column in PostgreSQL 9.1

2 Answers2