时间戳

首页 > 解决方案 > PostgreSQL - 将输出导出到 CSV

postgresql - PostgreSQL - 将输出导出到 CSV

问题描述

我想将查询的表导出到 CSV 文件。这里写的查询语法不是我的代码。我从我的同事那里得到的。我对 PostgreSQL 的了解非常少，也不知道如何修改代码。请给我一个可以将查询表导出为 CSV 的工作代码。只要是 CSV 格式，格式并不重要，因为我可以使用 Python 重新格式化表格。

WITH es_events AS
  (SELECT *
   FROM dblink('foreign_rs_server', $EXT$
           -- Note that since device_id and register_id are known and singular, 
        -- they are replaced as constant values rather than retrieved.
        -- As a distributed columnar store, Redshift is most performant when fewer columns are retrieved.
               SELECT DATE("dt")::timestamp as dt, value, 2777 as device_id, 1343 as register_id
               FROM "es_events" AS "events"
                             WHERE "events"."event_type" = 0
                             AND register_id = 1343
                             AND device_id=2777 
                             AND dt>='2018-12-01T00:00:00' 
                             AND dt<'2018-12-02T00:00:00'
               ORDER BY DATE("dt") ASC; $EXT$) AS es_events (dt TIMESTAMP WITH TIME ZONE, value DOUBLE PRECISION, device_id INTEGER, register_id INTEGER)),
register_versions AS
(
    SELECT 
        r.*,
        r.valid_at as valid_start,
        CASE
        WHEN r2.valid_at IS NULL THEN '2020-01-01'
        ELSE r2.valid_at
        END as valid_end
    FROM registers r
    LEFT JOIN registers r2 ON r2.id=r.id AND r2.valid_at>r.valid_at
    ORDER BY id ASC 
)

SELECT "dt",
             r.name,
             "events"."value"*r.scaling as scaled_value,
             r.units_of_measure
FROM "es_events" AS "events"
JOIN register_versions r ON r.id = events.register_id AND (r.valid_start <= dt AND r.valid_end > dt)
JOIN device_asset_mapping dam ON dam.device_id=events.device_id AND dam.active
ORDER BY dam.asset_id, dt ASC

标签： postgresql

解决方案

你可以像这样使用终端命令：

COPY ( your query inside ) TO 'C:\tmp\persons_email_db.csv' DELIMITER ',' CSV;

或者您可以简单地使用一些数据库工具（例如 navicat 或 dbeaver）在上面运行您的查询并将结果导出到 csv

推荐阅读