PostgreSQL 错误 : Column "Income" specified more than once

Question

我正在尝试构建一个基本数据库来帮助筛选许多 CSV 银行交易。

我已经将我所有的交易导入到多个表格中，这些表格来自不同的账户，这些账户的“扣除”列我已经勾选了“假”或“真”

我正在尝试执行“创建 View ”以从多个表中获取一个表以进行“扣除”。

基本上；来自收入列的多个表的银行交易显示为“TRUE”

The Error is - Query 1 ERROR: ERROR: column "Income" specified more than once

我已经尝试了大约一天的各种代码行，但似乎无法让它工作。

CREATE VIEW "Deductions" AS
SELECT
    "EverydaySTG"."Income",
    "EverydaySTG"."Deduction",
    "EverydaySTG"."Date",
    "EverydaySTG"."Amount",
    "EverydaySTG"."Payee",
    "CC WTPC"."Income",
    "CC WTPC"."Deduction",
    "CC WTPC"."Date",
    "CC WTPC"."Amount"
FROM
    "EverydaySTG",
    "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or(
        "CC WTPC"."Deduction" = 'TRUE' ::text
);

Answer 1

您的报表中有多个收入列:"EverydaySTG"."Income" 和 "CC WTPC"."Income"。替换..:

CREATE VIEW "Deductions"
AS SELECT
    "EverydaySTG"."Income",
    "EverydaySTG"."Deduction",
    "EverydaySTG"."Date",
    "EverydaySTG"."Amount", 
    "EverydaySTG"."Payee",
    "CC WTPC"."Income",
    "CC WTPC"."Deduction",
    "CC WTPC"."Date",
    "CC WTPC"."Amount"
FROM
    "EverydaySTG", "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or ( "CC WTPC"."Deduction" = 'TRUE' ::text );

... 通过:

CREATE VIEW "Deductions"
AS SELECT
    "EverydaySTG"."Income" AS income_a,
    "EverydaySTG"."Deduction" AS deduction_a,
    "EverydaySTG"."Date" AS date_a,
    "EverydaySTG"."Amount" AS amount_a,
    "EverydaySTG"."Payee",
    "CC WTPC"."Income" AS income_b,
    "CC WTPC"."Deduction" AS deduction_b,
    "CC WTPC"."Date" AS date_b,
    "CC WTPC"."Amount" AS amount_b
FROM "EverydaySTG", "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or( "CC WTPC"."Deduction" = 'TRUE' ::text );

---

OP 评论“结果是交易水平填充。而不是收入、扣除、日期、金额等干净的单行。有没有办法保持原始行的完整，所以交易垂直堆叠？” .

我知道他们想要一个 UNION 查询:

CREATE TABLE "EverydaySTG" (
    "Income" INTEGER
    "Deduction" CHARACTER VARYING
    "Date" DATE
    "Amount" INTEGER
    "Payee" CHARACTER VARYING
);

CREATE TABLE "CC WTPC" (
    "Income" INTEGER
    "Deduction" CHARACTER VARYING
    "Date" DATE
    "Amount" INTEGER
);

CREATE VIEW "Deductions_horizontal"
AS SELECT
    "EverydaySTG"."Income" AS income_a,
    "EverydaySTG"."Deduction" AS deduction_a,
    "EverydaySTG"."Date" AS date_a,
    "EverydaySTG"."Amount" AS amount_a,
    "EverydaySTG"."Payee",
    "CC WTPC"."Income" AS income_b,
    "CC WTPC"."Deduction" AS deduction_b,
    "CC WTPC"."Date" AS date_b,
    "CC WTPC"."Amount" AS amount_b
FROM "EverydaySTG", "CC WTPC"
WHERE (
    "EverydaySTG"."Deduction" = 'TRUE' ::text)
    Or( "CC WTPC"."Deduction" = 'TRUE' ::text );

CREATE VIEW "Deductions_vertical" AS
    SELECT
        "EverydaySTG"."Income",
        "EverydaySTG"."Deduction",
        "EverydaySTG"."Date",
        "EverydaySTG"."Amount", 
        "EverydaySTG"."Payee",
    FROM "EverydaySTG"
    WHERE "EverydaySTG"."Deduction" = 'TRUE'
    UNION
    SELECT 
        "CC WTPC"."Income",
        "CC WTPC"."Deduction",
        "CC WTPC"."Date",
        "CC WTPC"."Amount",
        NULL AS "Payee"
    FROM "CC WTPC"
    WHERE "CC WTPC"."Deduction" = 'TRUE';