一些常用的数据库指令

约 297 字小于 1 分钟

2026-08-16

从远端数据库获取指定Trail并生成插入指令

主要用于从 GhostTrail 表获取路线然后来插入到其他数据库中的 GhostTrail 表，在调试的时候很有用。

WITH config AS (
    SELECT 1 AS target_car_id -- 这里自定义插入指令的carId
)
SELECT
    'INSERT INTO public."GhostTrail" (' ||
    '"dbId", "carId", "area", "ramp", "path", "trail", "time", "driveData", ' ||
    '"driveDMergeSerial", "trendBinaryByUser", "byUserMergeSerial", ' ||
    '"trendBinaryByArea", "byAreaMergeSerial", "trendBinaryByCar", ' ||
    '"byCarMergeSerial", "playedAt", "tunePower", "tuneHandling", ' ||
    '"crownBattle", "vsorgBattle"' ||
    ') VALUES (' ||

    COALESCE("dbId"::text, 'NULL') || ', ' ||

    -- 这里使用自定义 carId
    config.target_car_id::text || ', ' ||

    COALESCE("area"::text, 'NULL') || ', ' ||
    COALESCE("ramp"::text, 'NULL') || ', ' ||
    COALESCE("path"::text, 'NULL') || ', ' ||

    CASE
        WHEN "trail" IS NULL THEN 'NULL'
        ELSE 'decode(' || quote_literal(encode("trail", 'hex')) || ', ''hex'')'
    END || ', ' ||

    COALESCE("time"::text, 'NULL') || ', ' ||

    CASE
        WHEN "driveData" IS NULL THEN 'NULL'
        ELSE 'decode(' || quote_literal(encode("driveData", 'hex')) || ', ''hex'')'
    END || ', ' ||

    COALESCE("driveDMergeSerial"::text, 'NULL') || ', ' ||

    CASE
        WHEN "trendBinaryByUser" IS NULL THEN 'NULL'
        ELSE 'decode(' || quote_literal(encode("trendBinaryByUser", 'hex')) || ', ''hex'')'
    END || ', ' ||

    COALESCE("byUserMergeSerial"::text, 'NULL') || ', ' ||

    CASE
        WHEN "trendBinaryByArea" IS NULL THEN 'NULL'
        ELSE 'decode(' || quote_literal(encode("trendBinaryByArea", 'hex')) || ', ''hex'')'
    END || ', ' ||

    COALESCE("byAreaMergeSerial"::text, 'NULL') || ', ' ||

    CASE
        WHEN "trendBinaryByCar" IS NULL THEN 'NULL'
        ELSE 'decode(' || quote_literal(encode("trendBinaryByCar", 'hex')) || ', ''hex'')'
    END || ', ' ||

    COALESCE("byCarMergeSerial"::text, 'NULL') || ', ' ||
    COALESCE("playedAt"::text, 'NULL') || ', ' ||
    COALESCE("tunePower"::text, 'NULL') || ', ' ||
    COALESCE("tuneHandling"::text, 'NULL') || ', ' ||

    CASE
        WHEN "crownBattle" IS NULL THEN 'NULL'
        WHEN "crownBattle" THEN 'TRUE'
        ELSE 'FALSE'
    END || ', ' ||

    CASE
        WHEN "vsorgBattle" IS NULL THEN 'NULL'
        WHEN "vsorgBattle" THEN 'TRUE'
        ELSE 'FALSE'
    END ||

    ');' AS insert_sql

FROM public."GhostTrail"
CROSS JOIN config
WHERE "dbId" = 1; -- 这里是指定的Trail的dbId