RDBMS on Stack Overflow trends

Stack Overflow provided a great tool for creating charts. This is mine for “PostgreSQL vs MySQL vs Oracle vs MSSQL”.


PostgreSQL vs MySQL vs Oracle vs MSSQL on Stack Overflow trends

PS Oracle and MSSQL have some additional tags depending on version. However, I’m sure we may not include them into statistics because the main tag is always used IMO.

Easy PostgreSQL docs for Sublime Text

I’m using this perfect tool called Sublime Text 3 for a bunch of tasks. One of them is viewing SQL scripts from time to time. ST3 has perfect SQL highlighting, but what I miss the most is the context help functionality, e.g. I select “LEFT JOIN” and hit F1 hot key. But that’s not problem since ST3 has a lot of packages. To solve my problem I need GotoDocumentation package and some tuning. Here is my settings for GotoDocumentation:

"docs": {
// obj containing the docs for each scope
// these are merged with the default ones
// the key value pair represent scope -> doc url
// supported placeholders:
// - %(query)s the selected text/word
// - %(scope)s the current scope
"sql": "http://www.postgresql.org/search/?q=%(query)s"

"pascal": "http://docwiki.embarcadero.com/Libraries/Seattle/en/Special:Search/%(query)s",
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
"fallback_scope": "google"

ST3 context help  for pg sql

ST3 context help for pg sql

Creating titles from text (SQL)

Task: get title (or excerpt) from given text (body)

left(body, 29) ||
COALESCE( substring( substr( body, 30 ) from '(.+?)(\,| )' ), '' ) as title,

Details: Take the first 29 characters, concatenate them with substring started from the 30-th character till the first occurrence of comma or space. Or concatenate with empty string, if pattern doesn’t match (COALESCE).

MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

  2. MERGE INTO acme_obj_value d
  3. USING ( SELECT object_id
  4.         FROM   acme_state_tmp
  5.       ) s
  6. ON (d.object_id = s.object_id)
  7.   WHEN matched THEN
  8.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  9.   WHEN NOT matched THEN
  10.     INSERT (d.id, d.object_id, d.date_value)
  11.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)

PostgreSQL statement:

  2. WITH s AS (
  3.      SELECT object_id
  4.      FROM   acme_state_tmp
  5. ),
  6. upd AS (
  7.      UPDATE acme_obj_value
  8.      SET    date_value = LEAST(l_dt, d.date_value)
  9.      FROM   s
  10.      WHERE  acme_obj_value.object_id = s.object_id
  11.      RETURNING acme_obj_value.object_id
  12. )
  13. INSERT INTO acme_obj_value(id, object_id,  date_value)
  14. SELECT NEXTVAL('acme_param_sequence'), s.object_id, l_dt
  15. FROM   s
  16. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)

Delphi: Warnigs → Errors

Want to force your dev team to address Compiler Warnings rather than ignoring them?

Elevate Warnings to Errors:

1) Go to Project Options
2) Under Delphi Compiler, select Hints and Warnings
3) Expand the Output warnings item
4) For each important Warning you wish for your developers to address rather than ignore, select the drop-down list and select Error.


Thanks Simon Stuart for hint!