0

I'm going to port my data from sqlite3 to PostgreSQL (version 10.1) but some query statements look like not compatible with the originals. for example, the statement:

SELECT date+300 AS date_norm, close FROM History WHERE date_norm>1502681700;

when run in sqlite3, I'm getting:

ERROR: column "date_norm" does not exist

LINE 1: ...T date+300 AS date_norm, close FROM History WHERE date_norm>...

I have no idea about the error and any hints are appreciated. thanks in advance.

Community
  • 1
  • 1
lzwujun
  • 1

1 Answers1

2

There is a problem with your existing query that you will need to change.

SELECT "date"+300 AS date_norm, close 
FROM History
WHERE "date" > (1502681700-300);
  • You cannot refer to a column alias formed in the select clause inside the where clause. The actual execution sequence of SQL is NOT the same as the way you write an SQL query. In fact the where clause is performed before the select clause, so the alias "date_norm" simply isn't understood by the where clause. Instead refer to the source column "date" and amend the way you filter accordingly, here I just deducted 300 from the constant value you used.

Moreover, date is a "reserved word" in standard SQL (refer). You should not use reserved words as column names OR you should quote that column name as "date" (such as select "date" from History). While it will not pose a problem in PostgreSQL, it is a potential portability problem.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • Your point 1 is wrong - the very page you link to says that `DATE` is reserved in standard SQL, but *not* in PostgreSQL. – Laurenz Albe Mar 13 '18 at 09:02
  • The SQL standards mentioned may be the origin but it is Postgres that implements the restrictions. To be honest I feel you have been overly harsh suggesting it is wrong. To be fully wrong "date" would not be a reserverd word st all, but we know it is. – Paul Maxwell Mar 13 '18 at 09:57
  • PostgreSQL does *not* restrict the usage of `date` in any way. I don't mean to be rude or harsh, but there is no problem with using unquoted `date` as a name in PostgreSQL. At best, you should advise that it is a good idea to avoid standard SQL keywords because they might be a porting problem. – Laurenz Albe Mar 13 '18 at 10:31
  • Very well, I have toned down the advice. Thanks. – Paul Maxwell Mar 13 '18 at 10:59
  • Thanks, everyone, all your advice are appreciated – lzwujun Mar 14 '18 at 01:40