Postgresql错误插入值

Question

我想为gtfs的某些数据添加一个非规范化表。为此，我创建了一个新表：

CREATE TABLE denormalized_trips (
  stops_coords json NOT NULL,
  stops_object json NOT NULL,
  agency_key text NOT NULL,
  trip_id text NOT NULL,
  route_id text NOT NULL,
  service_id text NOT NULL,
  shape_id text,
  route_color text,
  route_long_name text,
  route_desc text,
  direction_id text
);

CREATE INDEX denormalized_trips_index ON denormalized_trips (agency_key, trip_id);
CREATE UNIQUE INDEX denormalized_trips_index ON denormalized_trips (agency_key, route_id);

现在，我希望通过insert语句将数据从一个表传输到另一个表。这句话相当复杂。

INSERT INTO denormalized_trips
SELECT
    trps.stops_coords,
    trps.stops_object,
    trps.trip_id,
    trps.service_id,
    trps.route_id,
    trps.direction_id,
    trps.agency_key,
    trps.shape_id,
    trps.route_color,
    trps.route_long_name,
    trps.route_desc
FROM (
SELECT
    array_to_json(ARRAY_AGG(array[stop_lat, stop_lon])) AS stops_coords,
    array_to_json(ARRAY_AGG(array[
            stops.stop_id,
            CAST ( stop_times.stop_sequence AS TEXT ),
            stops.stop_name,
            stop_times.departure_time,
            CAST ( stop_times.departure_time_seconds AS TEXT ),
            stop_times.arrival_time,
            CAST ( stop_times.arrival_time_seconds AS TEXT )
        ])) AS stops_object,
    trips.trip_id,
    trips.service_id,
    trips.direction_id,
    trips.agency_key,
    trips.shape_id,
    routes.route_id,
    routes.route_color,
    routes.route_long_name,
    routes.route_desc
FROM gtfs_stop_times AS stop_times

INNER JOIN gtfs_trips AS trips
    ON trips.trip_id = stop_times.trip_id AND trips.agency_key = stop_times.agency_key

INNER JOIN gtfs_routes AS routes ON trips.agency_key = routes.agency_key AND routes.route_id = trips.route_id

INNER JOIN gtfs_stops AS stops
    ON stops.stop_id = stop_times.stop_id
    AND stops.agency_key = stop_times.agency_key
    AND NOT EXISTS (
      SELECT 0
      FROM denormalized_max_stop_sequence AS max
      WHERE max.agency_key = stop_times.agency_key
      AND max.trip_id = stop_times.trip_id
      AND max.trip_max = stop_times.stop_sequence
    )
GROUP BY
    trips.trip_id,
    trips.service_id,
    trips.direction_id,
    trips.agency_key,
    trips.shape_id,
    routes.route_id,
    routes.route_color,
    routes.route_long_name,
    routes.route_desc
) as trps

如果我只运行内部select语句，我将得到正确的结果。它们看起来像这样：(屏幕截图并不显示所有的表，因为它太长了)

​

​

但是，如果我执行insert语句并显示表的内容，我将得到如下内容：

​

您可能会注意到，内容没有插入到表的正确列中。agency_key现在有trip_id的值，direction_id现在是service_id (还有更多的表被搞砸了)。

因此，我的问题是，insert语句将内容插入新创建的表的错误列中，我做错了什么？

谢谢你的帮助。

Answer 1

默认情况下，Postgres将按照表中列的声明顺序插入值；它与查询中列的名称无关。

https://www.postgresql.org/docs/9.5/static/sql-insert.html

如果根本没有给出列名列表，则默认为表的所有列按声明的顺序排列；或者，如果只有值子句或查询提供的N列，则为第一个N列名。

可以更改insert以声明要插入的列的顺序，也可以更改select的顺序以与表中的列顺序匹配。