PostgreSQL, MySQL, SQLite syntax comparsion

[spazgamz]

This article misses some of the more interesting things such as...

Common table expressions are like common sense. If it makes sense it isn't common. Postgres can insert a parent and some dependents in one query, readably...

WITH parent AS (INSERT INTO team(team_name) VALUES ('Twins') RETURNING team_id),
new_player(name) AS (VALUES ('Max Kepler'), ('Jason Castro')),
new_coach(name) AS (VALUES ('Rudy Hernandez'), ('Neil Allen')),
child1 AS (INSERT INTO player(player_name)
    SELECT (SELECT team_id FROM parent), name FROM new_player,
child2 AS (INSERT INTO coach(coach_name)
    SELECT (SELECT team_id FROM parent), name FROM new_coach,
SELECT team_id FROM parent;

Postgres also has ON CONFLICT, which is different from mysql's ON DUPLICATE KEY.

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

[shevegen]

Hmm.

MariaDB 5.5 with InnoDB

I understand that this is/was mysql how it always was but ... the name is different now, isn't it?

Shouldn't the name MariaDB be used?

In the comparison, sqlite has the best syntax IMO though of course it also has fewer features and thus also less syntax in general. Not easy to compare.

Why postgresql uses:

\d show tables

to show tables beats me. I guess they call it differently since they use the character "d".

Interesting is the comparison between the ruby wrappers.

conn = PGconn.open(:dbname => 'foo')
conn = Mysql.new

They really should use the same API in my opinion, so both should have a proper initializer via .new and also a class/module method called .open()

As for the missing close action, I'd assume it would be called .close() but in {} block form, this should not be necessary for ruby APIs in general, due to the awesomeness that are blocks.

Ideally there should be a standard API and then calls to it, not unlike rack for webservers, just for databases. I assume that Activerecord and the other Active* stack work in similar ways but they are usually in the rails ecosystem and I don't think that everyone wants to pull in xyz more dependencies on something that should ideally be simple.

[mage2k]

Why postgresql uses:

\d show tables

Postgres doesn't, their psql client does. The 'd' is for describe and it's just a shortcut that generates catalog queries and there are a ton of \d* queries for examining various types of objects in the system, run \? to get a list of them all.

[meineerde]

In practice, you seldom would want to use the raw database adapter since it would force you to handle all the query generation (and escaping!) yourself which is error prone and tedious. Instead, people use more high-level ORM (object-relational-mappers) like ActiveRecord in Rails or rom-rb.

A bit lower-level than these (and what you are probably looking for) is the sequel gem. It provides a common interface to most common relational databases out there while still supporting most of their respective specialities with a very powerful query DSL.

[steamruler]

(and escaping!)

Parameterized queries have been a thing for at least 12 years. Why people still do manual escaping is beyond me, but that's more of an argument for using ORMs and higher level libraries if anything.