Importing CSV file - PostgreSQL 9.2

Question

I'm trying to import data from a CSV file on a PostgreSQL 9.2 server, but I get errors when doing that.

Pgadmin:

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

• Why am I getting that error if the DB is not read-only?

psql console:

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

Command used:

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

• How can I import the file?

UPDATE:

table 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 file:

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

• Vérace: Please provide your table structure (\d+ table_name IIRC) and a few lines of the .csv (with title line if present).
• user83914: @Vérace Question updated

Answer 1

Your table is like this:

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...>

The fields you want to insert are

(code, info, unit_cost, unit_price)

For starters, you don't provide values for the field account_id which is NOT NULL

Your insert is like this:

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

I ran this SQL (on a slightly modified CREATE TABLE - don't have your other tables for FOREIGN KEYs - don't know what "measurement_unit" is (user-defined data type?)). New CREATE TABLE below.

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);

And it worked - notice the single-quotes (').

Hopefully this will help to get you started.

New 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]

Start simple and then bring in complexity. Post your new .csv with the account_id.

Or, write an SQL statement like I did. See what errors occur (you should get none).

Start with no CONSTRAINTs - then add them gradually and see where your COPY starts to fail - it's debugging database style :-)

• user83914: So I have to create a temp table and then import it to the table that already exists ? Can't I import it to the current working table?
• Vérace: "Temporary" tables normally have another meaning - in this context they are more often called "Staging" tables are a good way of data cleansing - the reason I snipped parts of your original table were because of FKs for other tables which I didn't have and a sequence I also didn't have (why not use serial?). If you can get the correct data in your .csv then you won't need a staging table. But there's nothing wrong with using them.
• user83914: I've added the account_id - But still getting the error ERROR: invalid input syntax for type numeric: "unit_cost" You have answered but still can't understand because I'm getting the error