search

mplourde / rpostgresql

Automatically exported from code.google.com/p/rpostgresql
0 stars 1 forks source link

Empty result when query a column which name contains "." #70

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
I install the package from cran.

Database structure:
CREATE UNLOGGED TABLE "stock.history.ticks"
(
  "stock.code" character(9) NOT NULL,
  "deal.date" date NOT NULL, 
  "deal.ticks" json, -- ticks details collapsed as json, contains: stock.code, deal.time, deal.action, deal.price, deal.volume
  CONSTRAINT "PK_stock.history.ticks" PRIMARY KEY ("stock.code", "deal.date")
)

When I call following statement, return 0-rows result:

dbGetQuery(conn, 'select * from "stock.history.ticks" where "stock.code" = 
\'600690.SH\'')

When I call following statement, return as expected:
dbGetQuery(conn, 'select * from "stock.history.ticks" limit 3')

the first SQL statement works in pgadmin, so i guess rpostgresql wrongly 
handles the query statement with quotes ?

Original issue reported on code.google.com by U2US...@gmail.com on 25 Nov 2014 at 7:46

GoogleCodeExporter commented 9 years ago 
Not really enough information, but  I tested this on the following and it 
worked fine.  Marking this as closed as it does not appear to be a problem with 
RPostgreSQL.

R 3.1.1
PostgreSQL 9.4 rc1
Package: RPostgreSQL
    Version: 0.5-1
    Packaged: NA
   Built: R 3.1.1; x86_64-apple-darwin13.1.0; 2014-07-23 15:23:33 UTC; unix

Using the following data inserted into your table definition using psql.

INSERT INTO "stock.history.ticks" ("stock.code", "deal.date") VALUES 
('600690.SH', now());
INSERT INTO "stock.history.ticks" ("stock.code", "deal.date") VALUES 
('600690.SH', '1/1/2014');
INSERT INTO "stock.history.ticks" ("stock.code", "deal.date") VALUES 
('600691.SH', '1/2/2014');

And I get the following back:

> dbGetQuery(db, 'select * from "stock.history.ticks" where "stock.code" = 
\'600690.SH\'')
  stock.code  deal.date deal.ticks
1  600690.SH 2014-11-25       <NA>
2  600690.SH 2014-01-01       <NA>
Warning message:
In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type json (id:114) in column 2)

And

> dbGetQuery(db, 'select * from "stock.history.ticks"')
  stock.code  deal.date deal.ticks
1  600690.SH 2014-11-25       <NA>
2  600690.SH 2014-01-01       <NA>
3  600691.SH 2014-01-02       <NA>
Warning message:
In postgresqlExecStatement(conn, statement, ...) :
  RS-DBI driver warning: (unrecognized PostgreSQL field type json (id:114) in column 2)

Original comment by ne...@neiltiffin.com on 25 Nov 2014 at 4:01