ruby-on-rails - postgres - rails inet type

Distinto en la columna de datos JSON Postgresql

Question

Intentando hacer distinciones en un modo con rieles.

2.1.1 :450 > u.profiles.select("profiles.*").distinct


Profile Load (0.9ms)  SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" WHERE "integrations"."user_id" = $1  [["user_id", 2]]
PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json
LINE 1: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integ...
                        ^
: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" WHERE "integrations"."user_id" = $1
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json
LINE 1: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integ...
                        ^
: SELECT DISTINCT profiles.* FROM "profiles" INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" WHERE "integrations"."user_id" = $1
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/rack-mini-profiler-0.9.1/lib/patches/sql_patches.rb:109:in `prepare''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/rack-mini-profiler-0.9.1/lib/patches/sql_patches.rb:109:in `prepare''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:834:in `prepare_statement''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:795:in `exec_cache''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:139:in `block in exec_query''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract_adapter.rb:442:in `block in log''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activesupport-4.0.4/lib/active_support/notifications/instrumenter.rb:20:in `instrument''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract_adapter.rb:437:in `log''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:908:in `select''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/querying.rb:36:in `find_by_sql''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:585:in `exec_queries''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/association_relation.rb:15:in `exec_queries''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:471:in `load''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:220:in `to_a''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/activerecord-4.0.4/lib/active_record/relation.rb:573:in `inspect''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/railties-4.0.4/lib/rails/commands/console.rb:90:in `start''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/railties-4.0.4/lib/rails/commands/console.rb:9:in `start''
    from /Users/mmahalwy/.rvm/gems/ruby-2.1.1/gems/railties-4.0.4/lib/rails/commands.rb:62:in `<top (required)>''
    from bin/rails:4:in `require''
    from bin/rails:4:in `<main>''2.1.1 :451 > 

Obteniendo un error PG::UndefinedFunction: ERROR: could not identify an equality operator for type json

Convertirme en Hstore no es una opción para mí en este caso. Cualquier trabajo alrededor?

Answer 1

La razón detrás de esto, es que en PostgreSQL (hasta 9.3) no hay un operador de igualdad definido para json (es decir, val1::json = val2::json siempre arrojará esta excepción) - en 9.4 habrá uno para jsonb tipo.

Una solución alternativa es, puede convertir su campo json en text . Pero eso no cubrirá todas las json igualaciones. f.ex. {"a":1,"b":2} debe ser igual a {"b":2,"a":1} , pero no será igual si se convierte en text .

Otra solución es (si tiene una clave principal para esa tabla, que debería ser) puede usar el formulario DISTINCT ON (<expressions>) :

u.profiles.select("DISTINCT ON (profiles.id) profiles.*")

Nota : Una advertencia conocida para DISTINCT ON :

Las expresiones DISTINCT ON deben coincidir con la (s) expresión (es) ORDER BY más a la izquierda. La cláusula ORDER BY normalmente contendrá expresiones adicionales que determinan la precedencia deseada de las filas dentro de cada grupo DISTINCT ON.

Answer 2

Lamento llegar tarde a esta respuesta, pero podría ayudar a otros.

Según entiendo su consulta, solo está obteniendo posibles duplicados en los profiles debido a la combinación muchos a muchos de las integrations (que está utilizando para determinar a qué profiles acceder).

Por eso, puede usar una nueva función GROUP BY partir de 9.1 :

Cuando GROUP BY está presente, no es válido para las expresiones SELECT list referirse a columnas desagrupadas excepto dentro de funciones agregadas o si la columna desagrupada es funcionalmente dependiente de las columnas agrupadas, ya que de lo contrario habría más de un valor posible para devolver por una columna desagrupada. Existe una dependencia funcional si las columnas agrupadas (o un subconjunto de las mismas) son la clave principal de la tabla que contiene la columna desagrupada.

Entonces, en tu caso, podrías hacer que Ruby cree la consulta (lo siento, no sé la sintaxis de Ruby que estás usando) ...

SELECT profiles.* 
FROM "profiles" 
  INNER JOIN "integration_profiles" ON "profiles"."id" = "integration_profiles"."profile_id" 
  INNER JOIN "integrations" ON "integration_profiles"."integration_id" = "integrations"."id" 
WHERE "integrations"."user_id" = $1
GROUP BY "profiles"."id"

Solo eliminé el DISTINCT de su cláusula SELECT y agregué el GROUP BY .

Al referirse ÚNICAMENTE a la id en GROUP BY , aprovechará esa nueva característica porque todas las columnas restantes de los profiles son "funcionalmente dependientes" de esa clave principal de id.

De alguna manera, maravillosamente eso evita la necesidad de que Postgres haga verificaciones de igualdad en las columnas dependientes (es decir, su columna json en este caso).

La solución DISTINCT ON también es excelente, y claramente suficiente en su caso, pero no puede usar funciones agregadas como array_agg con ella. PUEDES con este enfoque GROUP BY . ¡Días felices! :)

Answer 3

Si usa PG 9.4, el uso de JSONB en lugar de JSON resuelve este problema Ejemplo:

-- JSON datatype test 

create table t1 (id int, val json);
insert into t1 (id,val) values (1,''{"name":"value"}'');
insert into t1 (id,val) values (1,''{"name":"value"}'');
insert into t1 (id,val) values (2,''{"key":"value"}'');
select * from t1 order by id;
select distinct * from t1 order by id;

-- JSONB datatype test 

create table t2 (id int, val jsonb);
insert into t2 (id,val) values (1,''{"name":"value"}'');
insert into t2 (id,val) values (1,''{"name":"value"}'');
insert into t2 (id,val) values (2,''{"key":"value"}'');

select * from t2 order by id;

select distinct * from t2 order by id;

Result of running the above script :

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
1 | {"name":"value"}
1 | {"name":"value"}
2 | {"key":"value"}

ERROR:  could not identify an equality operator for type json
LINE 1: select distinct * from t1 order by id;
                    ^
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
1 | {"name": "value"}
1 | {"name": "value"}
2 | {"key": "value"}

1 | {"name": "value"}
2 | {"key": "value"}

Como puede ver, PG logró implicar DISTINCT en una columna JSONB mientras falla en una columna JSON.

Intente también lo siguiente para ver que en realidad las claves en el JSONB están ordenadas:

insert into t2 values (3, ''{"a":"1", "b":"2"}'');
insert into t2 values (3, ''{"b":"2", "a":"1"}'');
select * from t2;

1 | {"name": "value"}
1 | {"name": "value"}
2 | {"key": "value"}
3 | {"a": "1", "b": "2"}
3 | {"a": "1", "b": "2"}

tenga en cuenta que ''{"b": "2", "a": "1"}'' se insertó como ''{"a": "1", "b": "2"}'' por lo tanto, PG identifica eso como el mismo registro:

select distinct * from t2;
3 | {"a": "1", "b": "2"}
2 | {"key": "value"}
1 | {"name": "value"}