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

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Very useful article

marksen's picture

That was exactly what I needed to clear my ideas about these 3 db sw.

small error

Anonymous's picture

Hi,

small error in your delete and explanation. According to the text, you want to delete book with bookid = 8, but the code says bookid = 7.

Nice comparisation of the command line of some of the biggest/most used open source and/or free DBs.

SQLite3 allows multi insert

A.Bouchez's picture

As of version 3.7.11 (the latest one) SQLite does support multi-row-insert.
The new multi-valued insert is merely syntactic suger (sic) for the compound insert.
There is no performance advantage one way or the other: an explicit transaction is needed to make insertion fast (due to the ACID feature of SQLite, outside a transaction each INSERT will wait for the data to be flushed on disk).

NICE !!

biohazd's picture

Very nice article - its exactly what I needed....

Good Article!

Anonymous's picture

Good article. I've only used MySQL before, so I find this to be refreshing about other database management systems.

A bit of clarity

Anonymous's picture

Please note that PostgreSQL is not "pickier" about the use of single/double quotes. The ANSI SQL standard is. PostgreSQL is merely following that standard, perhaps closer than the other DBs you reviewed. They're quite open about this decision. Good on them for that, it has helped produce a powerful tool.

Regards,

Just A. User

Thanks for the comparison

Anonymous's picture

Thanks for the article, quite useful as a quick comparison between those three databases command-line interfaces.

Thanks for the comparison

Anonymous's picture

Thanks for the article, quite useful as a quick comparison between those three databases command-line interfaces.

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix