Postgresql – error when creating a view with CTE

postgresql

When running the CTE below, it fetchs me the data I want.

WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
          a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

However when I'm trying to create a view on it like below

CREATE VIEW toto AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2013-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Axis_Reference_All"."Site", "Reference_internal", "Customer_code") IN
       ( SELECT d."Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code";

It gives me the following error

ERROR: The column « Site » has been specified more than once

********** Error **********

ERROR: The column « Site » has been specified more than once
WSQL State :42701

I've tried to put the view name before Site, namely a or d or "Axis_Reference_All" but none of it works.

Questions are the following:

Is CTE allowed with view creation on postgres? I guess no but it is unclear.
What would be the best way to create a view with my CTE? That CTE has been built that way because it is improving performance.

Best Answer

There are two possible solutions

Do a USING join like below

CREATE VIEW report_view
AS 
WITH d as(
SELECT *
FROM report.get_sa001('2013-01-01'::date", TO_CHAR(NOW()", 'YYYY-MM-DD')::date", 32)
    )",
     a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site"", "Reference_internal"", "Customer_code") IN
       ( SELECT "Site"",
                    "Internal_reference"", "Customer_code"
        FROM d ) )
SELECT *
FROM d
LEFT JOIN a USING ("Site","Internal_reference","Customer_code")
-- here is the USING. Courtesy of Jack Douglas for the tip

However as I'm retrieving datas from 5 years ago, it takes more than 5 minutes if I'm using a USING.

The best way is to call each columns separately like below and be sure to name the origin of the column, here a for Site and Customer_code.

CREATE VIEW report_view AS WITH d as
  ( SELECT *
   FROM report.get_sa001('2015-01-01'::date, TO_CHAR(NOW(), 'YYYY-MM-DD')::date, 32) ),
                           a as
  ( SELECT *
   FROM report."Axis_Reference_All"
   WHERE ("Site", "Reference_internal", "Customer_code") IN
       ( SELECT "Site", "Internal_reference", "Customer_code"
        FROM d ) )
SELECT "Period_date",
       a."Site",
       a."Customer_code",
       "Internal_reference",
       "InvoiceNumber",
       "Value_in_currency",
       "Value_in_EUR",
       "Value_Budget_in_EUR",
       "Selling_price_CUR",
       "Selling_price_EUR",
       "Currency_code",
       "Selling_quantity",
       "Variance_price_CUR",
       "Variance_price_EUR",
       "Variance_value_CUR",
       "Variance_value_EUR",
       "Selling_date",
       "Reference",
       "Reference_internal",
       "Reference_customer",
       "Reference_supplier",
       "Reference_description",
       "Reference_workshop",
       "Reference_line",
       "Reference_segment",
       "Reference_purchasingfamily",
       "Reference_motorapplication",
       "Reference_interco",
       "Reference_trading",
       "Cogs_rm",
       "Cogs_dl",
       "Cogs_voh",
       "Supplier_global",
       "Supplier_code",
       "Supplier_name",
       "Supplier_account_manager",
       "Supplier_interco",
       "Supplier_incoterm",
       "Supplier_incoterm_location",
       "Supplier_incoterm_via",
       "Supplier_continent",
       "Supplier_country",
       "Supplier_city",
       "Supplier_zipcode",
       "Purchasing_unit",
       "Purchasing_price",
       "Purchasing_currency",
       "Purchasing_payment_term_days",
       "Purchasing_payment_term_type",
       "Purchasing_consigned",
       "Purchasing_grossweight",
       "Purchasing_grosscube",
       "Purchasing_eco_order_qty",
       "Purchasing_pack_order_qty",
       "Purchasing_moq",
       "Purchasing_mov",
       "Purchasing_leadtime_days",
       "Customer_global",
       "Customer_name",
       "Customer_account_manager",
       "Customer_interco",
       "Customer_incoterm",
       "Customer_incoterm_location",
       "Customer_incoterm_via",
       "Customer_continent",
       "Customer_country",
       "Customer_city",
       "Customer_zipcode",
       "Selling_unit",
       "Selling_price",
       "Selling_currency",
       "Selling_payment_term_days",
       "Selling_payment_term_type",
       "Selling_consigned",
       "Selling_grossweight",
       "Selling_grosscube",
       "Selling_eco_order_qty",
       "Selling_pack_order_qty",
       "Selling_moq",
       "Selling_mov",
       "Selling_leadtime_days",
       "Reference_netweight",
       "Sc_storage_unit",
       "Sc_production_unit",
       "Sc_inventory_status",
       "Sc_inventory_price",
       "Sc_inventory_type"
FROM d
LEFT JOIN a ON d."Site" = a."Site"
AND d."Internal_reference" = a."Reference_internal"
AND d."Customer_code" = a."Customer_code"

This will give the required speed in term of data retrieval.

Cheers

Related Solutions

PostgreSQL – Joining a Function and a View

Have you tried using CTEs? I've found several times that reducing the size of my data sets before attempting the joins can lead to a big improvement in query times.

with
    __d as(
        select
            "Selling_date",
            "Value_in_EUR",
            "Value_in_currency",
            "Site",
            "Internal_reference",
            "Customer_code"
        from
            report.get_sa001('2016-01-01'::date, '2017-03-31'::date, 32)
    ),
    __a as(
        select
            "Selling_currency",
            "Site",
            "Reference_internal" as "Internal_reference",
            "Customer_code"
        from
            report."Axis_RefCustomer"
        where
            ("Site", "Reference_internal", "Customer_code") in(
                select
                    "Site",
                    "Internal_reference",
                    "Customer_code"
                from
                    __d
            )
    )
select
    __d."Selling_date",
    __a."Selling_currency", 
    __d."Value_in_EUR", 
    __d."Value_in_currency", 
    __d."Site"
from
    __d
    left join __a using("Site", "Internal_reference", "Customer_code")

Conditional INSERT with Nested CTE in PostgreSQL

For the purpose of this question, I'll assume employee_details.name to be defined UNIQUE. Else, the whole operation wouldn't make sense.

You cannot nest a data-modifying CTE like you tried (as you already found out the hard way) - and you don't need to. This query would achieve your objective:

WITH e AS (
   SELECT name, employee_id
   FROM   employee_details
   WHERE  name = 'jack bauer'
   )
 , i1 AS (
   INSERT INTO employee             -- no target columns!
   SELECT                           -- empty SELECT list!
   WHERE NOT EXISTS (SELECT FROM e)
   RETURNING id
   )
 , i2 AS (
   INSERT INTO employee_details (name, employee_id) 
   SELECT 'jack bauer', id
   FROM   i1
   RETURNING name, employee_id
   )
SELECT employee_id, name FROM e
UNION ALL 
SELECT employee_id, name FROM i2;

The core feature is the INSERT with no target columns and an empty SELECT. Postgres fills all columns not listed in the SELECT with default values. This way we can replace the unconditional VALUES (default) with a conditional INSERT. The CTE i1 only inserts a row if the given name was not found.

The manual:

If no list of [target] column names is given at all, the default is all the columns of the table in their declared order; [...]

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

This is a Postgres specific extension of the standard:

Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard.

The final CTE i2 only inserts a row if i1 returned a row. Voilá.

This is subject to race conditions under concurrent write load to the same tables. If you need to rule that out, you need to do more. Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?

Without the complications from the conditional INSERT in the 2nd table, this would boil down to a common case of SELECT or INSERT:

Is SELECT or INSERT in a function prone to race conditions?

Aside

"id" text DEFAULT gen_random_uuid()

I'd strongly advise to use the data type uuid to store UUIDs.

Best Answer

Related Solutions

PostgreSQL – Joining a Function and a View

Conditional INSERT with Nested CTE in PostgreSQL

Aside

Related Question