101–110 of 168 posts
Is it just me, or are the code examples not monospaced ? This doesn't help when talking about alignment.
(Quite liked SQL Prompt's auto formatter, but a shame so hideously expensive).
Is there a good sql autoformatter? For cleaning up ORM-generated queries so I can read them. I've used python's sqlparse but it produces output that's often still unreadable.
Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…
You didn't mentioned the most important thing to make readable SQL and that is demonstrated in your clause: use the "join" keyword instead of doing the join in the "where" clause. I can always auto-reformat a complex SQL in a IDE, but if it isn't using join clauses, it will stink.
Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…
That matches what I do pretty much with the exception of capitalization. I agree, it's not totally necessary, but it does provide a visual delineation of each section/component of the the statement, which, for large statements, can be very helpful in quickly scanning what it does: SELECT t1.col1, t2.col2, t3.col3 FROM table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = some…
select
columnA
,columnB
,columnC
from
tableName
select
columnA,
columnB,
columnC
from
tableName
Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…
That matches what I do pretty much with the exception of capitalization. I agree, it's not totally necessary, but it does provide a visual delineation of each section/component of the the statement, which, for large statements, can be very helpful in quickly scanning what it does: SELECT t1.col1, t2.col2, t3.col3 FROM table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = some…
My pet hate, directly out of SQL Server Management Studio, is how their code generation places commas. It generates code like this: select columnA ,columnB ,columnC from tableName Whereas I want my commas AFTER the column names on the same line: select columnA, columnB, columnC from tableName Or just looks so much neater.
SELECT
first
, second
, third
...
SELECT
first,
second,
third
...
SELECT
first,
second,
...Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…
Yep. Same here. I find screaming one's reserved words far more distracting than the highlighting helps. Another SQL habit that seems to far more annoying than helpful is putting commas at the start of next line. Great, they line up. Did you know that publishers have a term for vertical patterns in blocks of text? They're called 'rivers', and one generally attempts to avoid them, because they're visually distracting.
In short, it has nothing to do with purposeful perfect vertical alignment in code.
My pet hate, directly out of SQL Server Management Studio, is how their code generation places commas. It generates code like this: select columnA ,columnB ,columnC from tableName Whereas I want my commas AFTER the column names on the same line: select columnA, columnB, columnC from tableName Or just looks so much neater.
Leading with commas makes it easier to refactor if you're prototyping a query. You're less likely to cause an error when removing a column from: SELECT first , second , third ... than from SELECT first, second, third ... Which becomes: SELECT first, second, ...
The trade-off is code that is harder to mentally parse, because we are used to trailing commas, not leading commas.
If you spend more time reading code than writing it (which I assume applies to the majority of development), then the trailing comma is a much, much better choice of style.
Earlier quoted context omitted.
I've wondered about this too. I think ORMs serve effectively the same purpose as Javascript transpilers (which gives you 1 & 2) You can also write stored procedures in Postgres in other languages.
ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators. Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. Fabian Pascal (who has made a career out of…
PostgreSQL distributions come with support for PL/Python, PL/Tcl, PL/Perl and PL/pgSQL (a PostgreSQL specific PL/SQL clone). Other languages exist, but aren't included. See https://www.postgresql.org/docs/9.6/static/external-pl.html