FAIL: TestUpsert | Unable to upsert XxxxxxxDatum

[saulortega]

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v2.6.0

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

go test -v ./models/

If this happened at runtime what code produced the issue? (if not applicable leave blank)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

(«go test» dont have -d flag)

=== RUN   TestUpsert
=== RUN   TestUpsert/DeviceData
=== RUN   TestUpsert/Devices
=== RUN   TestUpsert/Vehicles
--- FAIL: TestUpsert (0.00s)
    --- FAIL: TestUpsert/DeviceData (0.00s)
        device_data_test.go:650: Unable to upsert DeviceDatum: models: unable to upsert device_data: pq: syntax error at or near "ON"
        device_data_test.go:655: models: failed to count device_data rows: pq: current transaction is aborted, commands ignored until end of transaction block
        device_data_test.go:658: want one record, got: 0
        device_data_test.go:667: Unable to upsert DeviceDatum: models: unable to upsert device_data: pq: syntax error at or near "ON"
        device_data_test.go:672: models: failed to count device_data rows: pq: current transaction is aborted, commands ignored until end of transaction block
        device_data_test.go:675: want one record, got: 0
    --- FAIL: TestUpsert/Devices (0.00s)
        devices_test.go:972: Unable to upsert Device: models: unable to upsert devices: pq: syntax error at or near "ON"
        devices_test.go:977: models: failed to count devices rows: pq: current transaction is aborted, commands ignored until end of transaction block
        devices_test.go:980: want one record, got: 0
        devices_test.go:989: Unable to upsert Device: models: unable to upsert devices: pq: syntax error at or near "ON"
        devices_test.go:994: models: failed to count devices rows: pq: current transaction is aborted, commands ignored until end of transaction block
        devices_test.go:997: want one record, got: 0
    --- FAIL: TestUpsert/Vehicles (0.01s)
        vehicles_test.go:809: Unable to upsert Vehicle: models: unable to upsert vehicles: pq: syntax error at or near "ON"
        vehicles_test.go:814: models: failed to count vehicles rows: pq: current transaction is aborted, commands ignored until end of transaction block
        vehicles_test.go:817: want one record, got: 0
        vehicles_test.go:826: Unable to upsert Vehicle: models: unable to upsert vehicles: pq: syntax error at or near "ON"
        vehicles_test.go:831: models: failed to count vehicles rows: pq: current transaction is aborted, commands ignored until end of transaction block
        vehicles_test.go:834: want one record, got: 0
FAIL
exit status 1

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE public.devices
(
  id integer NOT NULL,
  unit_id integer NOT NULL,
 [... more fields...]
  rpm_sensor boolean,
  ignition boolean,
  speed boolean,
  battery boolean,
  disconnect boolean,
  CONSTRAINT "device_id_PK" PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.devices
  OWNER TO postgres;

CREATE TABLE public.device_data
(
  id bigint NOT NULL DEFAULT nextval('device_data_id_seq'::regclass),
  device_unit_id bigint NOT NULL,
  device_manufacturer character varying(20) NOT NULL,
  longitude double precision,
  latitude double precision,
  altitude double precision, -- msnm
  speed double precision, -- km/h
  direction double precision, -- grados
  CONSTRAINT device_data_pkey PRIMARY KEY (id),
  CONSTRAINT "unit_id_manufacturer_UQ" UNIQUE (device_unit_id, device_manufacturer)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.device_data
  OWNER TO postgres;

---

This is my first attemp to implement SQLBoiler, and I get this error.

My sqlboiler.toml:

whitelist=["vehicles", "devices", "device_data"] blacklist=["migrations", "other"] debug=true schema="public" [postgres] ...

[saulortega]

Well, I'm trying to do a debug and I see SQLBoiler is running this:

INSERT INTO "device_data" ("id", "device_unit_id", "device_manufacturer", "longitude", "latitude", "altitude", "speed", "direction") VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT DO NOTHING

And that syntaxis is wrong for Postgres.

ERROR:  syntax error at or near "ON"
LINE 2:  VALUES (1516629669,1516629671,5,74,4,2600,0,20) ON CONFLICT...
                                                         ^

********** Error **********

ERROR: syntax error at or near "ON"
SQL state: 42601
Character: 183

EDIT: Ok, my Postgres version is old. ON CONFLICT is supported only from 9.5. :(