Postgres varchar column giving error "invalid input syntax for integer"

Question

I'm trying to use a INSERT INTO ... SELECT statement to copy columns from one table into another table but I am getting an error message:

gis=> INSERT INTO places (SELECT 0 AS osm_id, 0 AS code, 'country' AS fclass, pop_est::numeric(10,0) AS population, name, geom FROM countries);
ERROR:  invalid input syntax for integer: "country"
LINE 1: ...NSERT INTO places (SELECT 0 AS osm_id, 0 AS code, 'country' ...

The SELECT statement by itself is giving a result like I expect:

gis=> SELECT 0 AS osm_id, 0 AS code, 'country' AS fclass, pop_est::numeric(10,0) AS population, name, geom FROM countries LIMIT  1;
 osm_id | code | fclass  | population | name  |                                                                                                                                                                                     geom                                                                                                                                                                                     
--------+------+---------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      0 |    0 | country |     103065 | Aruba | 0106000000010000000103000000010000000A000000333333338B7951C0C8CCCCCC6CE7284033333333537951C03033333393D82840CCCCCCCC4C7C51C06066666686E0284000000000448051C00000000040002940333333333B8451C0C8CCCCCC0C18294099999999418351C030333333B3312940333333333F8251C0C8CCCCCC6C3A294000000000487E51C000000000A0222940333333335B7A51C00000000000F62840333333338B7951C0C8CCCCCC6CE72840
(1 row)

But somehow it looks like it's getting confused thinking that the fclass column should be an integer when, in fact, it is actually a character varying(20)

gis=> \d+ places
                                                   Unlogged table "public.places"
   Column   |          Type          |                      Modifiers                       | Storage  | Stats target | Description 
------------+------------------------+------------------------------------------------------+----------+--------------+-------------
 gid        | integer                | not null default nextval('places_gid_seq'::regclass) | plain    |              | 
 osm_id     | bigint                 |                                                      | plain    |              | 
 code       | smallint               |                                                      | plain    |              | 
 fclass     | character varying(20)  |                                                      | extended |              | 
 population | numeric(10,0)          |                                                      | main     |              | 
 name       | character varying(100) |                                                      | extended |              | 
 geom       | geometry               |                                                      | main     |              | 
Indexes:
    "places_pkey" PRIMARY KEY, btree (gid)
    "places_geom" gist (geom)

I've tried casting all of the columns to their exact types they need to be for the destination table but that doesn't seem to have any effect.

All of the other instances of this error message I can find online appear to be people trying to use empty strings as an integer which isn't relevant here because I'm selecting a constant string as fclass.

Nick Nick · Accepted Answer · 2016-10-17T23:44:36

You need to specify the column names you are inserting into:

INSERT INTO places (osm_id, code, fclass, population, name, geom) SELECT ...

Without specifying them individually, it is assumed that all columns are to be inserted into - including gid, which you want to have auto-populate. So, 'country' is actually being inserted into code by your current INSERT statement.

Postgres varchar column giving error "invalid input syntax for integer"

1 Answers