7 ms·

While postgres is indeed case sensitive usually writing queries with keywords in all caps is an effort to increase legibility for visual pattern matching. It a

by munk-a 2y ago

While postgres is indeed case sensitive usually writing queries with keywords in all caps is an effort to increase legibility for visual pattern matching. It absolutely isn't needed but if I'm debugging a query of yours I will send it through my prettifier so that I can breeze through your definitions without getting hung up on minor weird syntax things. It's like prettification in any other language - visual structures that we can quickly recognize (like consistent indentation levels) make us waste less time on comprehension of the obvious so we can focus on what's important. The only thing I really object to is "actuallyUsingCaseInIdentifiers" I never want to see columns that require double quotes for me to inspect on cli.

archsurface 2y ago
My understanding is that the caps were syntax highlighting on monochrome screens; no longer needed with colour. Can't provide a reference, it's an old memory.
- _Wintermute 2y ago
  Most of the SQL I write is within a string of another programming language, so it's essentially monochrome unless there's some really fancy syntax highlighting going on.
  - jaredklewis 2y ago
    Aside, but jetbrains IDEs seem to have some way to detect embedded sql and highlight it. I don’t remember configuring anything to get this feature.
    - wmfiv 2y ago
      More than highlight they'll do schema validation against inline SQL strings also.
    - formerly_proven 2y ago
      If you're working with some established framework and project structure their IDEs pull that information out of that, otherwise you'll need to at least tell it the dialect, but if you e.g. configure the database as a data source in the IDE you'll get full schema xref.
    - tomjen3 2y ago
      Another aside: that is true for a huge range of programming languages as well as things like HTML. I believe it can automatically add \ to " in strings when those strings are marked to the IDE as HTML.
    - TRiG_Ireland 2y ago
      VS Code does (did?) detect embedded SQL in PHP and correctly colour it, but only if it's on a single line. Any linebreaks and the colour is turned off. Also, if you're using prepared statements and have an @label, and that label is at the end of the string (so immediately followed by a closing quote), the SQL colouring continues into the rest of the PHP beyond the string. So it's important that single-line SQL statements ending in a @label be edited into multi-line statements to turn off the broken SQL colouring. Odd.
      - munk-a 2y ago
        PHP strings tend to have better syntax highlighting with here/now docs (i.e. starting with `<<<TOKEN`). I've found SublimeText to have excellent SQL detection when using these tokens to delineate queries (and the syntax lends itself well to block strings anyways).
    - yen223 2y ago
      This is Jetbrain's "language injection" feature if you want to look it up. It works with any languages that the IDE supports, and like a sibling comment mentioned it does more than syntax highlighting. https://www.jetbrains.com/help/idea/using-language-injections.html https://www.jetbrains.com/help/idea/using-language-injection...
    - wruza 2y ago
      Vimers can adapt https://vim.fandom.com/wiki/Different_syntax_highlighting_within_regions_of_a_file https://vim.fandom.com/wiki/Different_syntax_highlighting_wi... for a similar thing (but you have to wrap a string into some regular syntax).
  - WorldMaker 2y ago
    I find that in worst cases I can always copy and paste to a quick temporary buffer that is highlighted. I might be doing that naturally anyway if I'm trying to debug it, just to run it in a Data IDE of my choice, but sometimes even just using a scratch VS Code "Untitled" file can be useful (it's SQL auto-detect is usually good enough, but switching to SQL is easy enough if it doesn't auto-detect).
  - somat 2y ago
    Same, I am a bit conflicted, I enjoy the sql and don't really like the ORM's but I hate seeing the big blocks of SQL in my code. So I wrote a thing that lets me use sql text as a function, it is several sorts of terrible, in that way that you should never write clever code. but I am not really a programmer, most of my code is for myself. I keep using it more and more. I dread the day Someone else needs to look at my code. http://nl1.outband.net/extra/query.txt http://nl1.outband.net/extra/query.txt
- CoastalCoder 2y ago
  I think the "color is all we need" idea makes sense in proportion to how many of our tools actually support colorization. E.g., the last time I used the psql program, I don't think it had colorization of the SQL, despite running in a color-capable terminal emulator. It probably doesn't help that terminal colors are a bit of mess. E.g., piping colored output through 'less' can result in some messy control-character rendering rather than having the desired effect.
  - mixmastamyk 2y ago
    Try pgcli for color and completion.
    - renewiltord 2y ago
      Good tip, thank you.
  - dllthomas 2y ago
    > piping colored output through 'less' can result in some messy control-character rendering rather than having the desired effect. It can, but -G or -R can fix that.
- vundercind 2y ago
  Like sigils in that regard. Perl-type sigils are extremely nice... if you're editing in Notepad or some ancient vi without syntax highlighting and the ability to ID references on request. Little point to them, if you've got more-capable tools.
avg_dev 2y ago
i agree; but i use caps in my codebase, and lowercase when testing things out manually, just for ease of typing.
- munk-a 2y ago
  Ditto - if I'm throwing out an inspection query just to get a sense of what kind of data is in a column I won't bother with proper readable syntax (i.e. a `select distinct status from widgets`). I only really care about code I'll need to reread.
emmanuel_1234 2y ago
Any recommendation for a prettifier / SQL linter?
- gnulinux 2y ago
  I'm curious about this for DuckDB [1]. In the last couple months or so I've been using DuckDB as a one-step solution to all problems I solve. In fact my development environment rarely requires anything other than Python and DuckDB (and some Rust if native code is necessary). DuckDB is an insanely fast and featureful analytic db. It'd be nice to have a linter, formatter etc specifically for DuckDB. There is sqlfluff etc but I'm curious what people use. [1] DuckDB SQL dialect is very close to Postgres, it's compatible in many ways but has some extra QOL features related to analytics, and lacks a few features like `vacuum full`;
  - ptman 2y ago
    sqlfluff is better than https://github.com/darold/pgFormatter https://github.com/darold/pgFormatter , but it can get confused at times.
  - chrisjc 2y ago
    Since you're using python, have you looked into sqlglot? I think it has some pretty-print options. https://github.com/tobymao/sqlglot https://github.com/tobymao/sqlglot
- deleted 2y ago
  [deleted]
- homebrewer 2y ago
  IDEA if you want to use it for other things (or any other JetBrains IDE). Nothing comes close feature-wise. If you don't: - https://www.depesz.com/2022/09/21/prettify-sql-queries-from-command-line/#more-4112 https://www.depesz.com/2022/09/21/prettify-sql-queries-from-... - https://gitlab.com/depesz/pg-sql-prettyprinter https://gitlab.com/depesz/pg-sql-prettyprinter Or https://paste.depesz.com https://paste.depesz.com for one-off use.
wiredfool 2y ago
I find all caps identifiers wind up just looking like interchangeable blocks, where lowercase have word shapes. So all caps just slows down my reading.
- WorldMaker 2y ago
  I feel similarly and I also have friends with Dyslexia with even stronger opinions on it. All caps in addition to being "shouting" to my ancient internet-using brain (and thus rude in most cases), creates big similar rectangular blocks as word shapes and is such a big speed bump to reading speed for everyone (whether or not they notice it). For some of my friends with Dyslexia that have a huge tough time with word shapes at the best of times, all caps can be a hard stop "cannot read" blocker for them. They say it is like trying to read a redacted document where someone just made rectangular black marker cross outs. Personally, given SQL's intended similarity to English, I find that I like English "sentence case" for it, with the opening keyword starting with a capital letter and nearly every remaining letter lower case (except for Proper Nouns, the truly case-sensitive parts of SQL like table names). Sentence case has been helpful to me in the past in spotting things like missing semicolons in dialects like Postgres' that require them, and/or near keywords like `Merge` that require them or helping to visually make sure the `select` under a `Merge` is intended as a clause rather than starting a new "sentence".
  - akira2501 2y ago
    > I find that I like English "sentence case" for it, I could go either way, but if you want to go back and modify a query, this makes it more difficult for me. I just use a block syntax for my queries: SELECT * FROM the_table WHERE some_column = 12 AND other_column IS NOT NULL ORDER BY order_column;
    - cwbriscoe 2y ago
      SELECT * FROM the_table WHERE some_column = 12 AND other_column IS NOT NULL ORDER BY order_column; I usually don't bother with ALL-CAP keywords.
marcosdumay 2y ago
Well, as long as you aren't imposing the noisy syntax into everybody by pushing the case-change back into the code... But getting some editor that highlights the SQL will completely solve your issue.
- munk-a 2y ago
  I think fighting in PRs over syntax preferences is pretty useless so dev shops should generally have code style guidelines to help keep things consistent. In my company we use all caps casing since we have momentum in that direction but I think that decision can be reasonable in either direction as long as it's consistent - it's like tabs vs. spaces... I've worked in companies with both preferences, I just configure my editor to auto-pretty code coming out and auto-lint code going in and never worry about it.
o11c 2y ago
Note that case handling is a place where postgres (which folds to lowercase) violates the standard (which folds to uppercase). This is mostly irrelevant since you really shouldn't be mixing quoted with unquoted identifiers, and introspection largely isn't standardized.
- yen223 2y ago
  Given that other mainstream RDBMSes lets you configure how case handling should happen, Postgres is arguably the closest to the standard. Usual caveat of how nobody sticks to the ANSI standard anyway applies.
gwbas1c 2y ago
It's really useful to know this when working with SQL interactively. Specifically, if I'm banging out an ad-hoc query that no one will ever see, and I'm going to throw away, I don't worry about casing. Otherwise, for me, all SQL that's checked in gets the commands in ALL CAPS.
jillyboel 2y ago
what is your prettifier of choice for postgres?
zusammen 2y ago
[dead]
grahamplace 2y ago
For checked-in SQL queries, we follow: https://www.sqlstyle.guide/ https://www.sqlstyle.guide/ The combination of all caps keywords + following "the river" whitespace pattern dramatically improves readability in my opinion
MetaWhirledPeas 2y ago
> writing queries with keywords in all caps is an effort to increase legibility for visual pattern matching Considering most programming languages do just fine without ALL CAPS KEYWORDS I'd say it's a strange effort. I wish SQL didn't insist on being different this way. I agree with you on prettification though. As long as the repository chooses a prettifier you can view it your way then commit it their way. So that's my advice: always demand prettification for pull requests.
- sensanaty 2y ago
  I don't write or read SQL too often, but I prefer the ALL_CAPS because it usually lets me know if something is part of the SQL syntax itself or a function or whatever, or if it's referencing a table/column/etc. Obviously not very foolproof, but automated linters/prettifiers like the one in DataGrip do a good job with this for every query I've ever thrown at it.
mannyv 2y ago
The uppercase is usually there to show people what's SQL vs what's custom to your database. In books it's usually set in courier. I thought he was talking about psql's case sensitivity with table names, which is incredibly aggravating.
troyvit 2y ago
> no longer needed with colour. I think the increased legibility for visual pattern matching also makes SQL easier to read for many of the 350 million color blind people in the world.
neves 2y ago
You can convey more info with color. Any half decent editor can color your SQL. All caps letters are more similar and harder to read.

by munk-a 2y ago

archsurface 2y ago

My understanding is that the caps were syntax highlighting on monochrome screens; no longer needed with colour. Can't provide a reference, it's an old memory.

_Wintermute 2y ago
Most of the SQL I write is within a string of another programming language, so it's essentially monochrome unless there's some really fancy syntax highlighting going on.
- jaredklewis 2y ago
  Aside, but jetbrains IDEs seem to have some way to detect embedded sql and highlight it. I don’t remember configuring anything to get this feature.
  - wmfiv 2y ago
    More than highlight they'll do schema validation against inline SQL strings also.
  - formerly_proven 2y ago
    If you're working with some established framework and project structure their IDEs pull that information out of that, otherwise you'll need to at least tell it the dialect, but if you e.g. configure the database as a data source in the IDE you'll get full schema xref.
  - tomjen3 2y ago
    Another aside: that is true for a huge range of programming languages as well as things like HTML. I believe it can automatically add \ to " in strings when those strings are marked to the IDE as HTML.
  - TRiG_Ireland 2y ago
    VS Code does (did?) detect embedded SQL in PHP and correctly colour it, but only if it's on a single line. Any linebreaks and the colour is turned off. Also, if you're using prepared statements and have an @label, and that label is at the end of the string (so immediately followed by a closing quote), the SQL colouring continues into the rest of the PHP beyond the string. So it's important that single-line SQL statements ending in a @label be edited into multi-line statements to turn off the broken SQL colouring. Odd.
    - munk-a 2y ago
      PHP strings tend to have better syntax highlighting with here/now docs (i.e. starting with `<<<TOKEN`). I've found SublimeText to have excellent SQL detection when using these tokens to delineate queries (and the syntax lends itself well to block strings anyways).
  - yen223 2y ago
    This is Jetbrain's "language injection" feature if you want to look it up. It works with any languages that the IDE supports, and like a sibling comment mentioned it does more than syntax highlighting. https://www.jetbrains.com/help/idea/using-language-injections.html https://www.jetbrains.com/help/idea/using-language-injection...
  - wruza 2y ago
    Vimers can adapt https://vim.fandom.com/wiki/Different_syntax_highlighting_within_regions_of_a_file https://vim.fandom.com/wiki/Different_syntax_highlighting_wi... for a similar thing (but you have to wrap a string into some regular syntax).
- WorldMaker 2y ago
  I find that in worst cases I can always copy and paste to a quick temporary buffer that is highlighted. I might be doing that naturally anyway if I'm trying to debug it, just to run it in a Data IDE of my choice, but sometimes even just using a scratch VS Code "Untitled" file can be useful (it's SQL auto-detect is usually good enough, but switching to SQL is easy enough if it doesn't auto-detect).
- somat 2y ago
  Same, I am a bit conflicted, I enjoy the sql and don't really like the ORM's but I hate seeing the big blocks of SQL in my code. So I wrote a thing that lets me use sql text as a function, it is several sorts of terrible, in that way that you should never write clever code. but I am not really a programmer, most of my code is for myself. I keep using it more and more. I dread the day Someone else needs to look at my code. http://nl1.outband.net/extra/query.txt http://nl1.outband.net/extra/query.txt
CoastalCoder 2y ago
I think the "color is all we need" idea makes sense in proportion to how many of our tools actually support colorization. E.g., the last time I used the psql program, I don't think it had colorization of the SQL, despite running in a color-capable terminal emulator. It probably doesn't help that terminal colors are a bit of mess. E.g., piping colored output through 'less' can result in some messy control-character rendering rather than having the desired effect.
- mixmastamyk 2y ago
  Try pgcli for color and completion.
  - renewiltord 2y ago
    Good tip, thank you.
- dllthomas 2y ago
  > piping colored output through 'less' can result in some messy control-character rendering rather than having the desired effect. It can, but -G or -R can fix that.
vundercind 2y ago
Like sigils in that regard. Perl-type sigils are extremely nice... if you're editing in Notepad or some ancient vi without syntax highlighting and the ability to ID references on request. Little point to them, if you've got more-capable tools.

avg_dev 2y ago

i agree; but i use caps in my codebase, and lowercase when testing things out manually, just for ease of typing.

munk-a 2y ago
Ditto - if I'm throwing out an inspection query just to get a sense of what kind of data is in a column I won't bother with proper readable syntax (i.e. a `select distinct status from widgets`). I only really care about code I'll need to reread.

emmanuel_1234 2y ago

Any recommendation for a prettifier / SQL linter?

gnulinux 2y ago
I'm curious about this for DuckDB [1]. In the last couple months or so I've been using DuckDB as a one-step solution to all problems I solve. In fact my development environment rarely requires anything other than Python and DuckDB (and some Rust if native code is necessary). DuckDB is an insanely fast and featureful analytic db. It'd be nice to have a linter, formatter etc specifically for DuckDB. There is sqlfluff etc but I'm curious what people use. [1] DuckDB SQL dialect is very close to Postgres, it's compatible in many ways but has some extra QOL features related to analytics, and lacks a few features like `vacuum full`;
- ptman 2y ago
  sqlfluff is better than https://github.com/darold/pgFormatter https://github.com/darold/pgFormatter , but it can get confused at times.
- chrisjc 2y ago
  Since you're using python, have you looked into sqlglot? I think it has some pretty-print options. https://github.com/tobymao/sqlglot https://github.com/tobymao/sqlglot
deleted 2y ago
[deleted]
homebrewer 2y ago
IDEA if you want to use it for other things (or any other JetBrains IDE). Nothing comes close feature-wise. If you don't: - https://www.depesz.com/2022/09/21/prettify-sql-queries-from-command-line/#more-4112 https://www.depesz.com/2022/09/21/prettify-sql-queries-from-... - https://gitlab.com/depesz/pg-sql-prettyprinter https://gitlab.com/depesz/pg-sql-prettyprinter Or https://paste.depesz.com https://paste.depesz.com for one-off use.

wiredfool 2y ago

I find all caps identifiers wind up just looking like interchangeable blocks, where lowercase have word shapes. So all caps just slows down my reading.

WorldMaker 2y ago
I feel similarly and I also have friends with Dyslexia with even stronger opinions on it. All caps in addition to being "shouting" to my ancient internet-using brain (and thus rude in most cases), creates big similar rectangular blocks as word shapes and is such a big speed bump to reading speed for everyone (whether or not they notice it). For some of my friends with Dyslexia that have a huge tough time with word shapes at the best of times, all caps can be a hard stop "cannot read" blocker for them. They say it is like trying to read a redacted document where someone just made rectangular black marker cross outs. Personally, given SQL's intended similarity to English, I find that I like English "sentence case" for it, with the opening keyword starting with a capital letter and nearly every remaining letter lower case (except for Proper Nouns, the truly case-sensitive parts of SQL like table names). Sentence case has been helpful to me in the past in spotting things like missing semicolons in dialects like Postgres' that require them, and/or near keywords like `Merge` that require them or helping to visually make sure the `select` under a `Merge` is intended as a clause rather than starting a new "sentence".
- akira2501 2y ago
  > I find that I like English "sentence case" for it, I could go either way, but if you want to go back and modify a query, this makes it more difficult for me. I just use a block syntax for my queries: SELECT * FROM the_table WHERE some_column = 12 AND other_column IS NOT NULL ORDER BY order_column;
  - cwbriscoe 2y ago
    SELECT * FROM the_table WHERE some_column = 12 AND other_column IS NOT NULL ORDER BY order_column; I usually don't bother with ALL-CAP keywords.

marcosdumay 2y ago

Well, as long as you aren't imposing the noisy syntax into everybody by pushing the case-change back into the code... But getting some editor that highlights the SQL will completely solve your issue.

munk-a 2y ago
I think fighting in PRs over syntax preferences is pretty useless so dev shops should generally have code style guidelines to help keep things consistent. In my company we use all caps casing since we have momentum in that direction but I think that decision can be reasonable in either direction as long as it's consistent - it's like tabs vs. spaces... I've worked in companies with both preferences, I just configure my editor to auto-pretty code coming out and auto-lint code going in and never worry about it.

o11c 2y ago

Note that case handling is a place where postgres (which folds to lowercase) violates the standard (which folds to uppercase). This is mostly irrelevant since you really shouldn't be mixing quoted with unquoted identifiers, and introspection largely isn't standardized.

yen223 2y ago
Given that other mainstream RDBMSes lets you configure how case handling should happen, Postgres is arguably the closest to the standard. Usual caveat of how nobody sticks to the ANSI standard anyway applies.

gwbas1c 2y ago

It's really useful to know this when working with SQL interactively. Specifically, if I'm banging out an ad-hoc query that no one will ever see, and I'm going to throw away, I don't worry about casing. Otherwise, for me, all SQL that's checked in gets the commands in ALL CAPS.

jillyboel 2y ago

what is your prettifier of choice for postgres?

zusammen 2y ago

[dead]

grahamplace 2y ago

For checked-in SQL queries, we follow: https://www.sqlstyle.guide/ https://www.sqlstyle.guide/ The combination of all caps keywords + following "the river" whitespace pattern dramatically improves readability in my opinion

MetaWhirledPeas 2y ago

> writing queries with keywords in all caps is an effort to increase legibility for visual pattern matching Considering most programming languages do just fine without ALL CAPS KEYWORDS I'd say it's a strange effort. I wish SQL didn't insist on being different this way. I agree with you on prettification though. As long as the repository chooses a prettifier you can view it your way then commit it their way. So that's my advice: always demand prettification for pull requests.

sensanaty 2y ago
I don't write or read SQL too often, but I prefer the ALL_CAPS because it usually lets me know if something is part of the SQL syntax itself or a function or whatever, or if it's referencing a table/column/etc. Obviously not very foolproof, but automated linters/prettifiers like the one in DataGrip do a good job with this for every query I've ever thrown at it.

mannyv 2y ago

The uppercase is usually there to show people what's SQL vs what's custom to your database. In books it's usually set in courier. I thought he was talking about psql's case sensitivity with table names, which is incredibly aggravating.

troyvit 2y ago

> no longer needed with colour. I think the increased legibility for visual pattern matching also makes SQL easier to read for many of the 350 million color blind people in the world.

neves 2y ago

You can convey more info with color. Any half decent editor can color your SQL. All caps letters are more similar and harder to read.