Forums PostgreSQL.fr

\d ps_to_remove
                      Table « pg_temp_3.ps_to_remove »
Colonne |          Type                   | Collationnement | NULL-able | Par défaut
    ---------+-----------------------------+----------------------+---------------+------------
   ps_id   | character varying(255) |                          |                   |
  ps_gs   | character varying(255) |                          |                   |

CREATE TEMPORARY TABLE ps_to_remove AS (
  SELECT ps.topiaid ps_id, gs.topiaid ps_gs
  FROM practicedsystem ps
  INNER JOIN growingsystem gs ON ps.growingsystem = gs.topiaid
  INNER JOIN growingplan gp ON gp.topiaid = gs.growingplan
  INNER JOIN domain d ON d.topiaid = gp.domain
  LEFT JOIN plot p ON p.growingsystem = gs.topiaid
  LEFT JOIN zone z ON z.plot = p.topiaid
  WHERE d.campaign < 2000 OR d.campaign >= 2025
  AND d.active IS false
  AND ps.active IS false
  GROUP BY ps_id, ps_gs
);

SELECT growingsystem FROM ps_to_remove;
ERROR:  column "growingsystem" does not exist
LIGNE 1 : SELECT growingsystem FROM ps_to_remove;

inra-agrosyst-latest=# SELECT COUNT(*) FROM growingsystem_networks WHERE growingsystem IN (SELECT growingsystem FROM ps_to_remove);
count
-------
29365
(1 ligne)

inra-agrosyst-latest=# SELECT COUNT(*) FROM growingsystem_networks WHERE growingsystem IN (SELECT ps_gs FROM ps_to_remove);
count
-------
     7
(1 ligne)

DELETE FROM growingsystem_networks WHERE growingsystem IN (
  SELECT growingsystem FROM ps_to_remove
);