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 => "[email protected]",
: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: [email protected]\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=>"[email protected]", :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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With