Postgresql – Problem with How to join with subquery

join;postgresqlsubquery

I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get notice like ERROR: column foo.[column] does not exist.

This is the simply what I've done. sqlfiddle.com

Maybe someone can help, thanks!

Table definitions and data:

CREATE TABLE public.tprog (
    progId integer NOT NULL,
    codeProg character varying(20) NOT NULL,
    nmProg character varying(100)
);
INSERT INTO tprog VALUES (1, '01PROG', 'Prog One');

CREATE TABLE public.tkeg (
    kegId integer NOT NULL,
    codeKeg character varying(20) NOT NULL,
    nmKeg character varying(100),
    progId integer NOT NULL
);
INSERT INTO tkeg VALUES (1, '01KEG', 'Keg One', 1);

CREATE TABLE public.mDonation (
    mDonationId integer NOT NULL,
    nmDonation character varying(100)
);
INSERT INTO mDonation VALUES 
(1, 'Flood donation'),
(2, 'Tsunami donation');

CREATE TABLE public.tDonation (
    tDonationId integer NOT NULL,
    customerId integer,
    mDonatioanId integer,
    month character varying(10)
);
INSERT INTO tDonation VALUES 
(1, 1, 1, '2019-01'), (1, 2, 1, '2019-02'), (1, 3, 2, '2019-03');

CREATE TABLE public.tmonth (
    tmonthId integer NOT NULL,
    progId integer NOT NULL,
    kegId integer NOT NULL,
    tDonationId integer
);
INSERT INTO tmonth VALUES (1, 1, 1, 1);

CREATE TABLE public.tCustomer (
    tCustomerId integer NOT NULL,
    nmCustomer character varying(30),
    loc3Id integer NOT NULL
);

INSERT INTO tCustomer VALUES 
(1, 'Dani', 1), 
(2, 'Sarah', 2), 
(3, 'Jin', 3);

CREATE TABLE public.loc1 (
    loc1Id integer NOT NULL,
    nmloc1 character varying(50)
);
INSERT INTO loc1 VALUES (1, 'Country1');

CREATE TABLE public.loc2 (
    loc2Id integer NOT NULL,
    nmloc2 character varying(100) DEFAULT NULL::character varying,
    loc1Id integer
);
INSERT INTO loc2 VALUES 
(1, 'SubCountry1 A', 1),
(2, 'SubCountry1 B', 1);

CREATE TABLE public.loc3 (
    loc3Id integer NOT NULL,
    nmloc3 character varying(100) DEFAULT NULL::character varying,
    loc2Id integer
);
INSERT INTO loc3 VALUES 
(1, 'SubCountry1 A1', 1),
(2, 'SubCountry1 A2', 1),
(3, 'SubCountry1 B1', 2);

Query that results in error:

SELECT a.nmprog, b.nmkeg, f.nmDonation, foo.nmloc1, foo.nmloc2, foo.nmloc3, foo.nmCustomer
FROM tprog a  
LEFT JOIN tkeg b ON a.progId=b.progId
LEFT JOIN tmonth d ON d.kegId=b.kegId
LEFT JOIN tDonation e ON e.tDonationId = d.tDonationId
LEFT JOIN mDonation f ON f.mDonationId = e.mDonatioanId
LEFT JOIN (
  SELECT z.nmloc1, y.nmloc2, x.nmloc3, w.nmCustomer FROM tCustomer w 
  LEFT JOIN loc3 x ON x.loc3Id=w.loc3Id
  LEFT JOIN loc2 y ON y.loc2Id=x.loc2Id
  LEFT JOIN loc1 z ON z.loc1Id=y.loc2Id
) AS foo
ON foo.tCustomerId=e.customerId
WHERE e.month LIKE '%2019-01%'
;

ERROR: column foo.tcustomerid does not exist Position: 508

Best Answer

The problem is:

Solution:

Result:

Thanks!

Related Solutions

Sql-server – Insert query with a subquery

If the (subquery) has SELECT (whatever expression) AS col FROM ..., then you can do:

INSERT INTO mytable 
  (col1, col2, col3, col4) 
SELECT 
  val1, s.col, val2, val3
FROM 
  (subquery) AS s ;

or:

WITH s (col) AS
  (subquery)
INSERT INTO mytable 
  (col1, col2, col3, col4) 
SELECT 
  val1, s.col, val2, val3
FROM 
  s ;

PostgreSQL – How to Delete Duplicate Records Efficiently

Core feature is the window function lag().
Also pay special attention to avoid deadlocks and race conditions with concurrent deletes and inserts (which can affect which rows to delete!):

CREATE OR REPLACE FUNCTION remove_vendor_price_dupes(_vendor int)
  RETURNS integer AS
$func$
DECLARE
   del_ct int;
BEGIN
   -- this may or may not be necessary:
   -- lock rows to avoid race conditions with concurrent deletes
   PERFORM 1
   FROM   vendor_prices
   WHERE  vendor = _vendor
   ORDER  BY sku, effective_date, id  -- guarantee row locks in consistent order
   FOR    UPDATE;

   -- delete redundant prices
   DELETE FROM vendor_prices v
   USING (
      SELECT id
           , price = lag(price) OVER w  -- same as last row
             AND (lead(id) OVER w) IS NOT NULL AS del  -- not last row
      FROM   vendor_prices
      WHERE  vendor = _vendor
      WINDOW w AS (PARTITION BY sku ORDER BY effective_date, id)
      ) d
   WHERE v.id = d.id
   AND   d.del;

   GET DIAGNOSTICS del_ct = ROW_COUNT;  -- optional:
   RETURN del_ct;  -- return number of deleted rows
END
$func$  LANGUAGE plpgsql;

Call:

SELECT remove_vendor_price_dupes(1);

Notes

The current version of the 9.3 major release is 9.3.6. The project recommends that ...

all users run the latest available minor release for whatever major version is in use.
A multicolumn index on (vendor, sku, effective_date, id) would be perfect for this - in this particular order. But Postgres can combine indexes rather efficiently, too.
It might pay to add the otherwise irrelevant price as last item ot the index to get index-only scans out of this. You'll have to test.
Since you have concurrent deletes it may be a good idea to run a separate delete per vendor to reduce the potential for race conditions and deadlocks. Since there are only a few vendors, this seems like a reasonable partitioning. (Many tiny calls would be comparatively slow.)
I am running a separate SELECT (PERFORM in plpgsql, since we do not use the result) because the row locking clause FOR UPDATE cannot be used together with window functions. Don't let the keyword mislead you, this is not just for updates. I am locking all rows for the given vendor, since the result depends on all rows. Concurrent reads are not impaired, only concurrent writes have to wait until we are done. That's another reason why deleting rows for one vendor at a time in a separate transaction should be best.
sku is unique per product, so we can PARTITION BY it.
ORDER BY effective_date, id: your first version of the question included code for duplicate rows, so I added id to ORDER BY as additional tie breaker. This way it works for duplicates on (sku, effective_date) as well.
To preserve the last row for each set: AND (lead(id) OVER w) IS NOT NULL. Reusing the same window for lead() is cheap - independent of the added explicit WINDOW clause - that's just syntax shorthand for convenience.
I am locking rows in the same order: ORDER BY sku, effective_date, id. Make sure that concurrent DELETEs operate in the same order to avoid deadlocks. If all other transactions delete no more than a single row within the same transaction, there cannot be deadlocks and you don't need the row locking at all.
If concurrent INSERTs could lead to a different result (make different rows obsolete), you have to lock the whole table in EXCLUSIVE mode instead to avoid race conditions:
```
LOCK TABLE vendor_prices IN EXCLUSIVE MODE;
```
Do that only if it's necessary. It blocks all concurrent write access.
I am returning the number of rows deleted, but that's totally optional. You might as well return nothing and declare the function as RETURNS void.

Best Answer

Related Solutions

Sql-server – Insert query with a subquery

PostgreSQL – How to Delete Duplicate Records Efficiently

Notes

Related Question