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?

user330315 · Accepted Answer · 2014-12-15 20:32:33Z

3

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
);

answered Dec 15, 2014 at 20:32

user330315

Sign up to request clarification or add additional context in comments.

Comments

obchardon · Accepted Answer · 2014-12-15 20:49:43Z

1

Try this:

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

edited Dec 15, 2014 at 20:49

obchardon

10.8k1 gold badge19 silver badges36 bronze badges

answered Dec 15, 2014 at 20:33

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL : cannot cast type record to integer, PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related