We are hard at work building Doltgres, the Postgres-compatible version of Dolt. Dolt is the world's first and only version-controlled SQL database and is MySQL compatible, and most of us at DoltHub are more familiar with MySQL than with Postgres. So we've been learning a lot about Postgres in the process of building a Postgres-compatible version of Dolt.

Postgres is faster than MySQL by a fair margin, and it can do a lot that MySQL can't. This, combined with Oracle's buyout of InnoDB several years ago, has really turned the tide in SQL databases and led us to conclude that Postgres is winning and MySQL is dying. That realization is the main reason we're building Doltgres.

But despite Postgres's growing dominance in free SQL databases, there are some features MySQL has that have no equivalent in Postgres, and we find their absence kind of puzzling. We've been discovering these as we do the long-tail work of getting Dolt's integration tests, written for a MySQL dialect, to run on Doltgres. Let's look at a few of them.

Schema examination queries

We've mentioned this in the past because it was such a sticking point for us getting started on Postgres compatibility, but Postgres lacks syntax to easily examine the schema of tables and other database entities. MySQL has a bunch of convenience queries that do this:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| common             |
| test               |
| test1              |
| world              |
+--------------------+

mysql> show tables;
+---------------------+
| Tables_in_test      |
+---------------------+
| a                   |
| about               |
| ai                  |
| ...                 |
| t1                  |
+---------------------+

mysql> describe t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | int  | NO   |     | NULL    |       |
| b     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `pk` int DEFAULT NULL,
  `val` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------+

There are no tricks here, these are all normal SQL queries that work in any client. The server parses them and sends result rows. By contrast, Postgres makes you query the pg_catalog or information_schema tables to get this information. The psql shell that ships with postgres has special \\ commands that you can issue, but these are intrepreted by the client and transformed into pg_catalog queries on your behalf, so they don't work anywhere but in psql. Here's the Postgres equivalent to SHOW TABLES, invoked with \\d in the psql shell:

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind 
    WHEN 'r' THEN 'table' 
    WHEN 'v' THEN 'view' 
    WHEN 'm' THEN 'materialized view' 
    WHEN 'i' THEN 'index' 
    WHEN 'S' THEN 'sequence' 
    WHEN 't' THEN 'TOAST table' 
    WHEN 'f' THEN 'foreign table' 
    WHEN 'p' THEN 'partitioned table' 
    WHEN 'I' THEN 'partitioned index' 
  END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

That is a lot more complicated than SHOW TABLES.

We think that's a bad usability experience, so Doltgres parses and executes MySQL's special schema inspection syntax statements. SHOW TABLES etc. work as expected in Doltgres.

INSERT IGNORE

What do you do when you want to insert a bunch of rows into a table, but some of them might already exist and you don't care? MySQL has a very easy extension for this: INSERT IGNORE.

INSERT IGNORE INTO mytables VALUES (...);

If one of the rows you're inserting has a primary key that's already present in the table, MySQL just ignores that row (issuing a warning) and continues on inserting the rest.

Postgres does support this behavior via the ON CONFLICT clause, by specifying a DO NOTHING action.

INSERT INTO mytables VALUES (...) ON CONFLICT (myKeyColumn) DO NOTHING;

This achieves the same result, but requires you to name the column with the key you want to ignore duplicates on. Even worse, if you have more than one UNIQUE key on a table, you can't ignore both of them in the same INSERT statement, you can only do one at a time.

By contrast, INSERT IGNORE will ignore every single constraint at once, including NULL violations, CHECK failures, FOREIGN KEY failures, etc. This can be very useful when doing certain mass data import jobs, and there's just no way to do it in Postgres.

We haven't yet committed to extending Doltgres's syntax to support INSERT IGNORE, but it would be easy to do, since it uses the same underlying SQL execution engine as Dolt. Should we do it?

UPDATE IGNORE

Similarly, MySQL allows you to ignore any errors that occur during UPDATE statements with the IGNORE keyword.

UPDATE IGNORE myTable SET myKeyCol = 10 WHERE myKeyCol = 1;

This statement will change all the 1s into 10s, except where doing so would cause a primary key violation, unique violation, check constraint failure, etc. Unlike in the INSERT case, there's no workaround to get this behavior in Postgres. It just doesn't exist.

Once again, it would be easy to support this syntax in Doltgres. Should we do it?

DELETE and UPDATE with a LIMIT clause

In MySQL, you can update or delete a fixed number of rows by including a LIMIT clause in the statement. It looks like this:

DELETE FROM myTable WHERE id > 0 ORDER BY id DESC LIMIT 10;
UPDATE myTable SET column = 'newValues' WHERE id > 0 ORDER BY id DESC LIMIT 10;

This will delete or update the 10 highest rows in the table. This can come in very handy in certain situations, but it's not supported in Postgres. There is a workaround, though. You can move the LIMIT clause into the WHERE condition, like this:

DELETE FROM myTable WHERE id > 0 AND id IN 
    (SELECT id FROM myTable ORDER BY id DESC LIMIT 10);

This works fine, but is obviously more verbose.

Doltgres already supports LIMIT clauses in UPDATE and DELETE statements.

Creating multiple unique keys and constraints in a CREATE TABLE statement

MySQL supports CREATE TABLE statements that allow you to specify every constraint and index on a table in a single statement. This is legal:

CREATE TABLE myTable(
    a int not null,
    b int not null,
    c int null,
    primary key (a),
    unique key (a,b),
    check (b + c > 0)
);

In Postgres, CREATE TABLE statements are limited to constraints and indexes that can be declared inline with the column definition. To create this table, you need to issue several separate statements:

CREATE TABLE myTable(
    a int not null,
    b int not null,
    c int null,
    primary key (a)
);
ALTER TABLE myTable ADD UNIQUE KEY (a,b);
ALTER TABLE myTable ADD CONSTRAINT check (b + c > 0);

Maybe this isn't a huge deal, but it's pretty nice to be able to define a table and all its constraints in a single statement.

Doltgres doesn't support this yet because we're still playing catchup with full postgres type and schema support, but it's definitely something on our radar.

Conclusion

Postgres is the future, and MySQL is dying. But there's a lot of low-hanging quality of life issues with Postgres that MySQL already solved. Why shouldn't Postgres have nice things too?

Agree, disagree? Want to try out a version-controlled Postgres server? Join our Discord server to talk our engineers in real time. We hope to hear from you!