導入 CSV 文件 - PostgreSQL 9.2

我正在嘗試從 PostgreSQL 9.2 伺服器上的 CSV 文件導入數據，但這樣做時出現錯誤。

管理員：

ERROR: Cannot execute COPY FROM in a read-only transaction

• 如果數據庫不是只讀的，為什麼會出現該錯誤？

psql 控制台：

ERROR:  invalid input syntax for type numeric: "unit_cost"
CONTEXT:  COPY billables, line 1, column unit_cost: "unit_cost"

使用的命令：

COPY dm.billables(code, info, unit_cost, unit_price) FROM '/var/lib/pgsql/sql/charge.csv' DELIMITER ',' QUOTE '"' csv;

• 如何導入文件？

更新：

表 dm.billables：

   CREATE TABLE dm.billables
   (
     billable_id bigint NOT NULL DEFAULT "nextval"('"dm"."billables_billable_id_seq"'::"regclass"),
     acco

unt_id bigint NOT NULL,
 code character varying(64) NOT NULL,
 info "text",
 m_unit "measurement_unit", 
 m_unit_custom character varying(64),
 unit_cost numeric(16,4), 
 tax_aggregate_id_cost bigint,
 unit_price numeric(16,4), 
 tax_enabled_price boolean DEFAULT true,
 tax_aggregate_id_price bigint, 
 ts_created timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
 ts_modified timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
 ts_last_used timestamp with time zone,
 is_demo boolean NOT NULL DEFAULT false,
 CONSTRAINT pk_billables PRIMARY KEY ("billable_id"),
 CONSTRAINT fk_cost_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_cost)
     REFERENCES dm.tax_aggregates (tax_aggregate_id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT fk_price_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_price)
     REFERENCES dm.tax_aggregates (tax_aggregate_id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION,
 CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
 CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY["m_unit" IS NOT NULL, "m_unit_custom" IS NOT NULL] <> ARRAY[true, true])
)

CSV 文件：

Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) non-SLIAN,41,Alisha Davidson]",46.5,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) SLIANZ,92,Angela Murray]",59,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) non-SLIANZ,60,Anthony Swindale]",56.5,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) SLIANZ,142,Bernadette  Cutelli]",49,95
Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (5+) SLIANZ,11,Beryl Harrison(Harri)]",59,95

你的表是這樣的：

CREATE TABLE billables
(
 billable_id "nextval"('"dm"."billables_billable_id_seq"'::"regclass"),
 account_id bigint NOT NULL,           <<<<---- Can't be NULL...
 code character varying(64) NOT NULL,
 info "text",
 m_unit "measurement_unit", 
 m_unit_custom character varying(64),
 unit_cost numeric(16,4), 
 tax_aggregate_id_cost bigint,
 unit_price numeric(16,4), 
 <rest snipped...>

您要插入的欄位是

(code, info, unit_cost, unit_price)

對於初學者，您不為以下欄位提供account_id值NOT NULL

你的插入是這樣的：

Interpreting Normal/AH,"DO NOT CHANGE [OTH-INTERPSERV (2-5) non-SLIAN,41,Alisha Davidson]",46.5,95,Alisha Davidson

我執行了這個 SQL（稍作修改CREATE TABLE- 沒有其他表用於FOREIGN KEYs - 不知道“measurement_unit”是什麼（使用者定義的數據類型？））。下面新建CREATE TABLE。

INSERT INTO billables (account_id, code, info, unit_cost, unit_price)
VALUES (45, 'Interpreting Normal/AH','DO NOT CHANGE [OTH-INTERPSERV (2-5)
non-SLIAN,41,Alisha Davidson]',46.5,95);

它奏效了——注意單引號（’）。

希望這將有助於您入門。

新的CREATE TABLE.

CREATE TABLE billables
(
 billable_id serial,  <<-- why not user serial here?
 account_id bigint NOT NULL,
 code character varying(64) NOT NULL,
 info "text",
 m_unit bigint, 
 m_unit_custom character varying(64),
 unit_cost numeric(16,4), 
 tax_aggregate_id_cost bigint,
 unit_price numeric(16,4), 
 tax_enabled_price boolean DEFAULT true,
 tax_aggregate_id_price bigint, 
 ts_created timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
 ts_modified timestamp with time zone NOT NULL DEFAULT "transaction_timestamp"(),
 ts_last_used timestamp with time zone,
 is_demo boolean NOT NULL DEFAULT false,
 CONSTRAINT pk_billables PRIMARY KEY ("billable_id"),
 CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
 CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY["m_unit" IS NOT NULL, "m_unit_custom" IS NOT NULL] <> ARRAY[true, true])
);

$$ EDIT in response to OP’s comments $$ 從簡單開始，然後引入複雜性。.csv使用 account_id發布您的新產品。

或者，像我一樣編寫一條 SQL 語句。查看發生了什麼錯誤（您應該沒有錯誤）。

從沒有CONSTRAINTs 開始 - 然後逐漸添加它們，看看你COPY開始失敗的地方 - 這是調試數據庫風格:-)

引用自：https://dba.stackexchange.com/questions/132131