这个错误对Rails 4应用程序中这个庞大的postgresql语句意味着什么？

Question

我有以下大量的SQL语句通过常用的ActiveRecord查询助手（其中，连接）在Rails 4应用程序中执行。产品通过唯一的数据库ID进行区分。

SELECT 
    subscriptions.braintree_account_id as braintree_account_id, 
    subscriptions.braintree_subscription_id as braintree_subscription_id, 
    format('%s %s', addresses.first_name, addresses.last_name) as shipping_address_full_name, 
    users.email as email, 
    addresses.line_1 as shipping_address_line_1, 
    addresses.line_2 as shipping_address_line_2, 
    addresses.city as shipping_address_city, 
    addresses.state as shipping_address_state, 
    addresses.zip as shipping_address_zip_code, 
    addresses.country as shipping_address_country, 
    CASE WHEN tiger_shirt IS NULL THEN '' ELSE tiger_shirt END, 
    CASE WHEN tiger_socks IS NULL THEN '' ELSE tiger_socks END, 
    CASE WHEN tiger_accessories IS NULL THEN '' ELSE tiger_accessories END, 
    CASE WHEN tiger_waist IS NULL THEN '' ELSE tiger_waist END, 
    CASE WHEN tiger_tail_shirt IS NULL THEN '' ELSE tiger_tail_shirt END, 
    CASE WHEN tiger_tail_socks IS NULL THEN '' ELSE tiger_tail_socks END, 
    CASE WHEN tiger_tail_waist IS NULL THEN '' ELSE tiger_tail_waist END, 
    CASE WHEN tiger_tshirt IS NULL THEN '' ELSE tiger_tshirt END, 
    CASE WHEN tiger_accessories_waist IS NULL THEN '' ELSE tiger_accessories_waist END, 
    CASE WHEN tiger_ta_tail_shirt IS NULL THEN '' ELSE tiger_ta_tail_shirt END, 
    CASE WHEN tiger_ta_tail_waist IS NULL THEN '' ELSE tiger_ta_tail_waist END, 
    plans.name as plan_name, 
    products.sku as sku, 
    to_char(subscriptions.created_at, 'MM/DD/YYYY HH24:MM:SS') as subscription_created_at, 
    to_char(subscriptions.next_assessment_at, 'MM/DD/YYYY HH24:MM:SS') as subscription_next_assessment_at, 
    subscriptions.subscription_status as subscription_status 
FROM 
    "subscriptions" 
INNER JOIN 
    "addresses" 
    ON "addresses"."id" = "subscriptions"."shipping_address_id" 
    AND "addresses"."type" IN ('ShippingAddress') 
LEFT OUTER JOIN 
    shipping_methods 
    ON shipping_methods.subscription_id = subscriptions.id 
LEFT OUTER JOIN 
    plans 
    ON subscriptions.plan_id = plans.id 
LEFT OUTER JOIN 
    users 
    ON subscriptions.user_id = users.id 
LEFT OUTER JOIN 
    products 
    ON plans.product_id = products.id 
LEFT OUTER JOIN 
    (SELECT * FROM crosstab('SELECT 
           subscriptions.id, 
           choice_types.id, 
           value_choices.presentation 
          FROM 
           subscriptions 
          LEFT JOIN 
           subscription_variables 
           ON subscriptions.id = subscription_variables.subscription_id 
          LEFT JOIN 
           variables 
           ON subscription_variables.variable_id=variables.id 
          LEFT JOIN 
           value_choice_variables 
           ON variables.id=value_choice_variables.variable_id 
          LEFT JOIN 
           value_choices 
           ON value_choice_variables.value_choice_id=value_choices.id 
          LEFT JOIN 
           choice_types 
           ON value_choices.choice_type_id=choice_types.id 
          LEFT JOIN 
           choice_type_products 
           ON choice_type_products.choice_type_id=choice_types.id 
          LEFT JOIN 
           products 
           ON choice_type_products.product_id = products.id 
          WHERE 
           products.id IN (2, 3, 4, 11, 12, 15) 
          ORDER BY 1,2', 
          'SELECT 
           choice_types.id 
          FROM 
           choice_types 
          JOIN 
           choice_type_products 
           ON choice_type_products.choice_type_id = choice_types.id 
          JOIN 
           products 
           ON choice_type_products.product_id = products.id 
          WHERE 
           products.id IN (2, 3, 4, 11, 12, 15) 
          ORDER BY 
           choice_types.id ASC') 
          AS (
           subscription_id int, 
           tiger_shirt VARCHAR, 
           tiger_socks VARCHAR, 
           tiger_accessories VARCHAR, 
           tiger_waist VARCHAR, 
           tiger_tail_shirt VARCHAR, 
           tiger_tail_waist VARCHAR, 
           tiger_tail_socks VARCHAR, 
           tiger_tshirt VARCHAR, 
           tiger_accessories_waist VARCHAR, 
           tiger_ta_tail_shirt VARCHAR, 
           tiger_ta_tail_waist VARCHAR 
           ) 
    ) subscription_variable_view 
    ON subscriptions.id=subscription_variable_view.subscription_id 
    WHERE 
     "subscriptions"."subscription_status" = $1 AND 
     "addresses"."flagged_invalid_at" IS NULL AND 
     "products"."id" IN (2, 3, 4, 11, 12, 15) AND 
     (NOT EXISTS (
      SELECT 
       "subscription_hunted_months".* 
      FROM 
       "subscription_hunted_months" 
      WHERE 
       "subscription_hunted_months"."month_year" = 'JUN2016' AND 
       subscription_hunted_months.subscription_id = subscriptions.id)) 

当它试图运行时出现以下错误......这是什么意思？什么可能导致它？

ActiveRecord::StatementInvalid: PG::DuplicateObject: ERROR: duplicate category name 

Answer 1

这可能是因为你的CATEGORY SQL参数返回重复值：

SELECT 
    choice_types.id 
FROM 
    choice_types 
JOIN 
    choice_type_products 
    ON choice_type_products.choice_type_id = choice_types.id 
JOIN 
    products 
    ON choice_type_products.product_id = products.id 
WHERE 
    products.id IN (2, 3, 4, 11, 12, 15) 
ORDER BY 
    choice_types.id ASC 

如果你只是使它DISTINCT并重新运行您查询 - 它可能解决问题。 -

SELECT DISTINCT 
    choice_types.id 
    ...