I've recently started experimenting with setting up a new local PostgreSQL server, hooked up to an existing Rails app. I have a table I am trying to insert to:
postgres=# \d+ events.t_sales_events
Table "events.t_sales_events"
Column | Type | Modifiers | Storage | Stats target | Description
------------+-----------------------------+---------------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('events.t_sales_events_id_seq'::regclass) | plain | |
event_name | character varying(45) | not null | extended | |
actor_id | integer | | plain | |
actor_type | character varying(45) | | extended | |
json | jsonb | | extended | |
created | timestamp without time zone | not null | plain | |
modified | timestamp without time zone | not null | plain | |
deleted | timestamp without time zone | | plain | |
timestamp | timestamp without time zone | not null | plain | |
context | text | | extended | |
Indexes:
"t_sales_events_pkey" PRIMARY KEY, btree (id)
This is an example of an event that I'm trying to insert into t_sales_events:
event =
{
:event_name => "lead_created",
:context => nil,
:actor_type => "sales_user",
:timestamp => "2013-03-18T07:13:42.000+0000",
:json => {
:dc_id => "00AA000000AAaaAAaa1",
:name => "John Doe",
:title => "CEO",
:role => nil,
:phone => nil,
:company => "Does Does",
:email => "jdoe@doesdoes.com",
:dc_source => nil,
:lead_id => nil
},
:actor_id => nil,
:created => 2015-09-15 18:32:25 -0700,
:modified => 2015-09-15 18:32:33 -0700
}
And this is the error I get when I run SalesEvent.create(event):(0.2ms)
BEGIN
SQL (4.0ms) INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id" [["actor_id", nil], ["actor_type", "sales_user"], ["context", nil], ["created", Wed, 16 Sep 2015 01:32:25 UTC +00:00], ["deleted", nil], ["event_name", "lead_created"], ["json", "---\n:sfdc_id: 00AA000000AAaaAAaa1\n:name: John Doe\n:title: CEO\n:role: \n:phone: \n:company: Does Does\n:email: jdoe@doesdoes.com\n:lead_id: \n"], ["modified", Wed, 16 Sep 2015 01:32:33 UTC +00:00], ["timestamp", Mon, 18 Mar 2013 07:13:42 UTC +00:00]]
PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json
DETAIL: Token "-" is invalid.
CONTEXT: JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"
(0.1ms) ROLLBACK
*** ActiveRecord::StatementInvalid Exception: PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json
DETAIL: Token "-" is invalid.
CONTEXT: JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"
It seems to me that the error arises because "---" is added to the beginning of the field labelled 'json', but I'm at a loss as to why.
EDIT: I've added the model for SalesEvent below. I did indeed have serialize :json in there. Apologies - I forgot that I'd added that in a somewhat blind attempt to try and resolve the same error.
class SalesEvent < ActiveRecord::Base
establish_connection :warehouse_development
self.table_name = "events.t_sales_events"
serialize :json
def self.get_all
SalesEvent.all
end
end
Removing that particular line returns the same 'Token "-" is invalid' error, albeit due to different input:
(0.1ms) BEGIN
SQL (4.2ms) INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id" [["actor_id", nil], ["actor_type", "sales_user"], ["context", nil], ["created", Wed, 16 Sep 2015 06:09:00 UTC +00:00], ["deleted", nil], ["event_name", "lead_created"], ["json", {:sfdc_id=>"00AA000000AAaaAAaa1", :name=>"John Doe", :title=>"CEO", :role=>nil, :phone=>nil, :company=>"Does Does", :email=>"jdoe@doesdoes.com", :dc_source=>nil, :lead_id=>nil}], ["modified", Wed, 16 Sep 2015 06:09:07 UTC +00:00], ["timestamp", Mon, 18 Mar 2013 07:13:42 UTC +00:00]]
PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json
DETAIL: Token "-" is invalid.
CONTEXT: JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"
(0.1ms) ROLLBACK
*** ActiveRecord::StatementInvalid Exception: PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json
DETAIL: Token "-" is invalid.
CONTEXT: JSON data, line 1: -...
: INSERT INTO "events"."t_sales_events" ("actor_id", "actor_type", "context", "created", "deleted", "event_name", "json", "modified", "timestamp") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING "id"
Turns out it was the incompatibility of the postgres connector in rails 3.2.17 with jsonb objects. After upgrading to 4.2.4, the error stopped cropping up.