0

I'm having a really weird issue running a timestamp comparison query in my postgresql database.

I have a table called user (I know it's a terrible name) and we have a column named createdAt that's the type of timestamp with timezone. I should be able to run a query comparing against this column no problem. Yet, the WHERE in this clause (and the AND) don't seem to have any effect. I get back every single user from this table including ones with a null value in the createdAt column. I'm perplexed.

select * from "user"
where 'createdAt' >= '2017-08-01 23:25:53+00'
and 'createdAt' is not null
order by id desc;

EDIT:

relevant table structure and sample data 

*** table columns ***

id serial primary key
createdAt timestamp with time zone
first_name text
last_name text

** sample data ***

id first_name last_name createdAt

1  john       smith     2018-09-21 02:53:42+00
2  james      smith     2018-09-19 00:27:14+00

EDIT: SOLVED

This was a weird issue but I wasn't getting any feedback from my where clause. The problem is the original devs named the columns camel cased which makes it necessary that any future queries referencing this column use the double quotes. Ended up having to say "user"."createdAt". That was a syntax issue on my part since I only ever name my columns snake case.

Are PostgreSQL column names case-sensitive?

Zachary Springer
  • 63
  • 8
  • 1
    `'created_at'` is a string literal value, not a reference to a column. Try `user.createdAt` – MatBailie Jul 30 '19 at 15:45
  • ERROR: column user.createdat does not exist LINE 3: and "user".createdAt is not null ^ HINT: Perhaps you meant to reference the column "user.createdAt". – Zachary Springer Jul 30 '19 at 15:45
  • That's what I get thrown unfortunately :/ – Zachary Springer Jul 30 '19 at 15:46
  • please share the table structure with sample data. – dassum Jul 30 '19 at 15:47
  • 1
    Then try what the error message says to try!? – MatBailie Jul 30 '19 at 15:47
  • I did. Multiple, multiple times. – Zachary Springer Jul 30 '19 at 15:48
  • No, you didn't. The error message shows you used `"` in one place, but not the other. Use `user.createdAt` in all cases, not just half of them. – MatBailie Jul 30 '19 at 15:49
  • Ok, before I asked this question I did exactly that. Here, I'll show you the message. – Zachary Springer Jul 30 '19 at 15:55
  • select * from "user" where "user.createdAt" >= '2017-08-01 23:25:53+00' and "user.createdAt" is not null order by id desc; ERROR: column "user.createdAt" does not exist LINE 2: where "user.createdAt" >= '2017-08-01 23:25:53+00' – Zachary Springer Jul 30 '19 at 15:55
  • select * from "user" where "user".createdAt >= '2017-08-01 23:25:53+00' and "user".createdAt is not null order by id desc; ERROR: column user.createdat does not exist LINE 2: where "user".createdAt >= '2017-08-01 23:25:53+00' ^ HINT: Perhaps you meant to reference the column "user.createdAt". – Zachary Springer Jul 30 '19 at 15:56
  • is the where clause on other columns working? – dassum Jul 30 '19 at 16:04
  • So I figured it out and I'll edit the question to show the problem. It was a silly mistake on my part. (it ended up being "user"."createdAt"). I was able to figure it out by this stack overflow question https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive – Zachary Springer Jul 30 '19 at 16:06

0 Answers0