SQL and R - Download data from a table with space in names and integer filter

Asked

Viewed 104 times

1

I have a database in Postgresql and would like to download a table, but with some filters.

The column 'CNAE 2.0 Class' is an integer. 

library(RPostgreSQL) 

PIA_Estab <- dbGetQuery(con,"
                   SELECT *
                   FROM rais.rais_estabelecimento 
                   WHERE 'CNAE 2.0 Classe' BETWEEN 5.000 AND 33.000 
                   ")

However, I am receiving the following message:

Error in postgresqlExecStatement(Conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: input syntax unusual for Numeric type: "CNAE 2.0 Class" LINE 4:
WHERE 'CNAE 2.0 Class' BETWEEN 5.000 AND... ^ )

Warning message: In postgresqlQuickSQL(Conn, statement, ...) : Could not create execute: SELECT * FROM Rais.rais_establishment WHERE 'CNAE 2.0 Class' BETWEEN 5.000 AND 33.000

I’ve tried to use LIKE (ex: "5%") and operators (ex: >=5000)

EDIT:

I put the values between ' ' and solved the error.

However, the table is coming empty. If you have left 'CNAE 2.0 Classe' >= '5000' the table comes complete, including values less than 5000. What may be happening ?

sql r postgresql
  • 2

    In terms of Postgresql syntax a delimited identifier (or quoted Identifier), as is the field "CNAE 2.0 Class" needs to be in quotes("). When you put between apostrophes (') you are setting a string. By the second message (Could not create execute: SELECT * FROM Rais.rais_establishment WHERE 'CNAE 2.0 Class' BETWEEN 5,000 AND 33,000) it seems to me that it is considering a string and not a delimited identifier. Another thing is that if the field is integer I see no sense in specifying values with decimal places.

  • I put the values between ' ' and solved the error. However, the table is coming empty. Left 'CNAE 2.0 Classe' >= '5000' the table comes complete, including values less than 5000. What can be happening ?

  • 3

    You did it wrong. The way you are comparing two strings: the string 'CNAE 2.0 Class' and the string '5000'. You must put the name of the field in quotation marks: "CNAE 2.0 Classe".

  • 1

    @anonimo No R this will be the instruction SELECT between ' ' and the column name between " ". Or you can even define a string with the SELECT thus: sql <- 'SELECT etc "CNAE 2.0 Classe" BETWEEN 5 AND 33' followed by dbGetQuery(con, sql).

  • @Rui Barradas: I don’t work with R and therefore couldn’t say how to mount the SQL expression in R. What I put above is that the SQL expression that was coming to Postgresql was wrong, according to the second error message. I believe your comment should be addressed to Ricardo.

  • @anonimo Sorry, I hadn’t noticed your profile. But despite this, the comment also reaches the AP.

  • @Noisy thanks ! Now it worked ! I reversed the single quotes with double quotes and pulled the right result. Thanks !

Show 2 more comments

1 answer

2


I reversed the single quotes ' with the doubles " and it worked.

PIA_Estab <- dbGetQuery(con,'
                   SELECT *
                   FROM rais.rais_estabelecimento 
                   WHERE "CNAE 2.0 Classe" BETWEEN 5000 AND 34000
                   ')

Browser other questions tagged sql r postgresql

You are not signed in. Login or sign up in order to post.