Skip to main content
added 348 characters in body
Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84

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

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

Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84

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