MariaDB/MySQL, PostgreSQL and SQLite3 - Comparing Command-Line Interfaces

The UPDATE Statement

Remember that typo? Well, it's time to fix it. This UPDATE line works for all three:

UPDATE books SET title = 'The Hobbit' WHERE title = 'The Hobbbit';

The DELETE Statement

Deleting rows also is the same across all three:

DELETE FROM books WHERE bookid = 7;

The above will delete the row in the books table that has a bookid of 8. If you've been following along, there should not be an entry with that bookid, so nothing will happen.

The ALTER Statement

Suppose I decide to remove the seriesid column from the books table. In MariaDB and PostgreSQL, the following statement will do it:

ALTER TABLE books DROP seriesid;

SQLite3, on the other hand, does not support the removal of columns from tables. You can add columns to a table, or modify columns, but the only way to remove a column is to create a new table without a seriesid column, transfer the data from the old table to the new table, drop the old table, and then rename the new table to the original name. It's not as annoying as you might think, thanks to some SQL INSERT trickery (well, I thought it was tricky the first time I saw it in action). The basic idea is to use the output of a SELECT statement as the input to an INSERT statement, like so:

CREATE TABLE books2 (
  bookid integer PRIMARY KEY NOT NULL,
  title varchar(100) NOT NULL,
  authorid integer
);
INSERT INTO books2 (bookid, title, authorid)
  SELECT bookid, title, authorid FROM books;
DROP TABLE books;
ALTER TABLE books2 RENAME TO books;

The above trick also works as written in MariaDB and PostgreSQL as long as you change the bookid line of the CREATE TABLE statement to the following:

bookid serial PRIMARY KEY,

But, that's an awful lot of work if you just want to drop a column from a table.

These examples should be enough SQL to give you a picture of how the three compare to each other.

SQLite Output

When trying the SQL examples, you will notice the SQLite output is not nearly as pretty as the output from MariaDB/MySQL or PostgreSQL. By default, SQLite doesn't print column names or try to pad columns so that they line up nice and fancy like the others do. To make SQLite do so for the SELECT ... JOIN statement, enter the the following commands before the statement:

.explain ON
.mode column
.width 30 10 10

The .explain command instructs SQLite to display column headers; .mode sets the output to display in columns, and the .width command sets the width of the columns. The only issue with doing this is that it will mess up the output of future queries (unless they happen to look fine with the .width values you specified). To reset things back to the default, set the output mode back to the default "list" with .mode list. Doing this also turns off explain and resets the column widths back to their defaults.

Single vs. Double Quotes

In the SQL examples I use single quotes (') for most things and double quotes (") sparingly. MariaDB and SQLite allow you to use single or double quotes interchangeably for most quoted text in queries. PostgreSQL is pickier, because it tries to stay closer to the ANSI SQL standard, which says single quotes should be used for values (for example: title = 'The Hobbbit'), and double quotes should be used for system identifiers (field names, table names and so on—for example: SELECT title AS "Book Title"...). You can force MariaDB to obey the standard—and reject double-quoted values—with the command SET sql_mode='ANSI_QUOTES'.

Conclusion

It is not hard to interact with databases on the command line. In my opinion, doing the tasks listed above is much easier on the command line than through a graphical database program.

Of course, manipulating your database by hand, whether on the command line or with a graphical program, probably should be avoided in many cases in favor of using an automated front end—for example, a PHP content management front end for the database that contains the content for your company Web site. However, for those times when you do need to dive in and tweak something manually, or for small projects that don't justify the time or expense of a custom front end, there is no need to be afraid of using the command-line client of your chosen database.

Resources

MariaDB Web Site: http://mariadb.org

MariaDB Documentation: http://kb.askmonty.org

MariaDB Downloads: http://downloads.askmonty.org

PostgreSQL Web Site: http://www.postgresql.org

PostgreSQL Documentation: http://www.postgresql.org/docs

PostgreSQL Downloads: http://www.postgresql.org/download

SQLite Web Site: http://www.sqlite.org

SQLite Documentation: http://www.sqlite.org/docs.html

SQLite Downloads: http://www.sqlite.org/download.html

SQLite SQL Syntax Diagrams: http://www.sqlite.org/syntaxdiagrams.html

Wikipedia Article on SQL: http://en.wikipedia.org/wiki/SQL

Wikibooks Article on Moving between MySQL and PostgreSQL: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL