Clojurians Log v2

Is there any built-in way to double quote table names? With Postgres if you want to create a table named user, you have to wrap it in double quotes. This is because the user table is used internally by Postgres. Currently I'm quoting tables myself before running the execution command

@kevin.van.rooijen did you try? (sql/format :quoting :ansi)

don't create a table called user 😉

in a similiar vein, don't create a table called group

Those two are about the only exceptions I make when keeping table names singular. I have a users table and a groups table. All my other tables are singluar.

It will help in the long term, for you won't have to remember to quote your queries every single time.

Sadly, that doesn't work

(-> (q/select :*)
    (q/from :user)
    (sql/format {:quoting :ansi}))
;; => ["SELECT * FROM user"]

I don't have to remember, since all my queries go through a single pipeline. My setup currently works fine, but if there's a built-in way to manage it in honeysql then I'd rather have that

sure, whatever works for you 🙂 But, you're forgetting you may not only be the only user of that database in the future....

That's true, guess I'm just selfish like that 😛

But you make a valid point

Maybe I can solve this in a more practical way, thanks

🙂

oh wow, format doesn't take a map 🙂

(-> (q/select :*)
    (q/from :user)
    (sql/format :quoting :ansi))
;; => ["SELECT * FROM \"user\""]

Apparently :quoting ansi works fine if you don't mess it up like I did haha

Thanks

In some databases, those plural names will collides with system variables etc. You're better off sticking to singular everywhere and using quoting as appropriate.

With respect, I disagree - user and group are reserved words and thus should be avoided (in fact, I came across a table today that had a column named desc). Why make lives difficult? Perhaps finding another name would be appropriate, that may be related to the problem domain, like shopper or customer or buyer - trying to avoid reserved words is a good thing, imho 🙂

The inconsistency of two possible plural tables when everything else is singular is bad -- especially since users will bite you on some databases, so your advice isn't even universal.

Instead, with the approach you advocate, you are pushing the problem further down the line, for every single integrator to the database will have to know to quote the tables - and where does it stop? Why not call columns desc and quote them too?

Why not consistently quoted everywhere? Which is essentially what happens for next.jdbc.sql functions (and their equivalents in c.j.j) and if you're using HoneySQL, you just add :quoting :ansi to the format calls (or whatever DB you're using).

It's far better to be consistent in your data model than arbitrarily bend your data model to fit whatever weird rules your particular database has. Especially since different DBs have different rules 😐