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.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState