SQL : cannot cast type record to integer, PostgreSQL

Question

I have a problem with the following query (using PostgreSQL)

DELETE FROM node
WHERE node.node_id IN
(
SELECT nbr
FROM 
(
SELECT node.node_id 
FROM node, edge_data
WHERE ST_intersects(node.geom, edge_data.geom)
) as nbr
GROUP BY nbr
HAVING COUNT(*) = 2) ;

But unfortunately I receive this error message:

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
********** Erreur **********
ERROR: operator does not exist: integer = record

I understand that nbr is a "record" and my node_id an integer. But if a try to cast nbr in integer (with nbr::integer) the following message appear :

ERROR: cannot cast type record to integer

Does someone know how to resolve this problem?

Answer 1

This part:

SELECT nbr
FROM 
(
  SELECT node.node_id 
  FROM node, edge_data
  WHERE ST_intersects(node.geom, edge_data.geom)
) as nbr

Selects the actual alias name from the derived table, not the column that is returned from the inner query. When using the alias name, you are select a record not a single column. You need to change that to:

SELECT node_id
FROM (
  SELECT node.node_id 
  FROM node, edge_data
  WHERE ST_intersects(node.geom, edge_data.geom)
) as nbr

So the whole statement is:

DELETE FROM node
WHERE node.node_id IN
(
  SELECT nbr
  FROM (
    SELECT node.node_id 
    FROM node, edge_data
    WHERE ST_intersects(node.geom, edge_data.geom)
  ) as nbr
  GROUP BY nbr
  HAVING COUNT(*) = 2
);