Follow

Software micro-rant, PostgreSQL 

TIL that:

select * from user;

and

select * from "user";

CAN yield different results. I had assumed that the quotes were simply to keep uppercase characters from turning into lowercase or something.

In my case, the first one gives me results from the internal PG user table, and the second gives me results from... my project's "user" table. Yikes.

· · Web · 4 · 6 · 7

Software micro-rant, PostgreSQL 

@kensanata I have NO IDEA how this sorcery works, or what is the logic behind it... but it. blew. my. mind.

For some reason whoever created these tables used camelCase for the names, and I thought I was so clever because I knew to use double quotes to be able to access them...

...but this "user" shit was next level. I was NOT prepared for that, and no one told me! I don't even know _why_ I decided to try, I assumed the result would be the same 😄

Software micro-rant, PostgreSQL 

@estebanm I need to watch out for similar effects. I've noticed in DBeaver that when I use completion to complete the table name 'text' it gets quoted as "text" where as other table names are not. I always wondered about that.

Software micro-rant, PostgreSQL 

@kensanata That's probably because "text" is a type, so a reserved word?

...and I might have just realised why "user" needs to be quoted 😂😅

Although, I still wonder how come that "select * from user" is valid and gives different results, so... I'm still confused, really.

Also, DBeaver looks nice! I'll give it a try 😄

Software micro-rant, PostgreSQL 

@estebanm We've been using DBeaver ever since we switched from Oracle to PostgreSQL. I used to like Toad a lot. But once you leave the Oracle world, there's really no point. DBeaver is great. And if you've been Eclipse on the side, you'll know a lot of the keyboard shortcuts. If you haven't… then, uh, I guess not so great. But I love it. Too bad the company switched from Eclipse to IntelliJ…

@Esteban Manchado Velázquez That's because user is a reserved keyword in SQL. Afaict this is the correct behaviour and you should avoid using reserved keywords as column- or table names.

https://www.postgresql.org/docs/current/sql-keywords-appendix.html

@harald Yeah, I realised that later, but, how come that "select * from user" *is* valid? That seems very surprising.

If "select * from user" had been a syntax error, everything would have been evident.

Software micro-rant, PostgreSQL 

@estebanm Been there. I renamed the table to account 😁

Software micro-rant, PostgreSQL 

@estebanm
I was fully expecting you to say select * from "user" yields:
u
s
e
r

different table, that's unexpected...

Sign in to participate in the conversation
Mastodon

Server run by the main developers of the project 🐘 It is not focused on any particular niche interest - everyone is welcome as long as you follow our code of conduct!