The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,730 other followers

Archive for the ‘PostgreSQL’ Category

« Previous Entries

SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

Posted by jpluimers on 2019/08/29

Via [WayBack] SQL select only rows with max value on a column, I bumped into http://sqlfiddle.com/#!9/a6c585/1:

Application for testing and sharing SQL queries.

Source: [WayBack] SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.

It is a cool site, currently supporting these SQL back-ends:

  • MySQL 5.6
  • Oracle 11g R2
  • PostgreSQL 9.6
  • PostgreSQL 9.3
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2014

You can host it yourself using [WayBack] GitHub – zzzprojects/sqlfiddle2: New version of SQL Fiddle based on OpenIDM (in the past it was [WayBack] GitHub – zzzprojects/sqlfiddle)

Other resources for learning and playing around with SQL:

–jeroen

Posted in Database Development, Development, MySQL, OracleDB, PostgreSQL, Software Development, SQL, SQL Server | Leave a Comment »

What RDBMS do you use when doing location-aware things? 

Posted by jpluimers on 2019/01/08

There was a nice discussion on [WayBack] What RDBMS do you use when doing location-aware things?More specifically, I am looking into a simple app with use of GPS… – Andrea Raimondi – Google+

TL;DR: use PostGIS on top of PostgresSQL.

–jeroen

Posted in Database Development, Development, PostGIS | Leave a Comment »

SQL: “where not exists … having” formulation; anti-join alternative

Posted by jpluimers on 2018/06/26

I need to write up some notes, but there are some links that will help me:

It’s a question of readability. There is no difference in performance.
Old versions of SQL Server were silly enough to look up meta data, but not any more.

SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id);
SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);

I am not considering NULL or “fun variants” which don’t seem intuitive to me.

SELECT foo FROM bar WHERE EXISTS (SELECT NULL FROM baz WHERE baz.id = bar.id);

SELECT foo FROM bar WHERE EXISTS (SELECT 1/0 FROM baz WHERE baz.id = bar.id);

The question popped up in comments just now. I researched the manuals of the most popular RDBMS:

A search on SO for code:"EXISTS (SELECT 1" yields 5,048 results.
A search on SO for code:"EXISTS (SELECT *" yields 5,154 results.
Updated links and counts 07.2015.

So SELECT * has the popular vote and the big commercial RDBMS on its side.
I find SELECT 1 more intuitive. It’s like saying “if at least one exists”.
Is SELECT * more intuitive?

–jeroen

 

 

Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »

The ##Netherlands saw a big I.T. project meant to deliver a new citizen regis…

Posted by jpluimers on 2017/11/30

Interesting: https://github.com/MinBZK/OperatieBRP

[WayBack] The Netherlands saw a big I.T. project meant to deliver a new citizen registry system fail. So they did one thing right. All the source code is now on github… – Jan Wildeboer – Google+

It includes both documentation and source code.

A bit of browsing reveals at least some of the technologies used:

Most of the SQL code seems to be generated.

There are truckloads of .XML configuration files, typical for a Java environment. Some configuration is in .properties files.

–jeroen

Posted in ActiveMQ, Development, Java, Java Platform, MQ Message Queueing/Queuing, Open Source, PostgreSQL, Software Development | Leave a Comment »

Visual Representation of SQL Joins – CodeProject

Posted by jpluimers on 2017/08/02

I thought I posted a reference to this a long time ago, but didn’t.

It’s one of the things I show when explaining joins to people. Sometimes I need it myself too (:

The article explains these in greater detail:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • OUTER JOIN
  • LEFT JOIN EXCLUDING INNER JOIN
  • RIGHT JOIN EXCLUDING INNER JOIN
  • OUTER JOIN EXCLUDING INNER JOIN

Note:

  • the opposite of INNER JOIN is not OUTER JOIN. It’s OUTERJOIN EXCLUDING INNER JOIN
  • the opposite of OUTER JOIN is empty set.

But the diagram is usually speaks for itself.

–jeroen

Source: Visual Representation of SQL Joins – CodeProject

Read the rest of this entry »

Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server | Leave a Comment »

« Previous Entries
 
%d bloggers like this: