On Sunday, December 20, 2015 at 3:24:00 PM UTC-8, Kematzy wrote: > > > Hi Jeremy, > > Thanks for your reply. > > Actually, I was too rushed yesterday morning to fully understand the > problem and therefore blamed the wrong thing. My bad & my sincere > apologies. > > I now believe the problem seems to occur when collecting records via a > *one_to_many* association. > > You can see the entire codebase here [ > https://github.com/kematzy/sequel-audited/tree/postgres-errors-example ] > with the results fully outlined here [ > https://github.com/kematzy/sequel-audited/blob/postgres-errors-example/Error-Output.md > > ]. > > I thought it was better to see the problem within the context rather than > isolated, but if this is not suitable, please let me know and I will try to > compress the code to minimum required. >
The problem here is having a string foreign key referencing a integer primary key. PostgreSQL doesn't have implicit casts of integer to string, so it doesn't like that. If the primary key is an integer, the foreign key should be an integer, and vice-versa. There are probably ways to work around the problem in Sequel using association options that will do the necessary casts (http://sequel.jeremyevans.net/rdoc/files/doc/association_basics_rdoc.html#label-Associations+Based+on+SQL+Expressions+Options), but my recommendation would be better to fix the design. I see three possible approaches: 1) If you must have a single audit table that can handle both string primary keys and integer primary keys, you could have two foreign key columns, one for a string primary key and one for an integer primary key. This would have issues if the main table has a composite primary key, or a non-string/integer primary key. 2) You could switch to a more relational approach of a separate audit table per main table, with a proper foreign key 3) You could go the schemaless route and store the reference information in a json column Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
