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

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