So you're switching to PostgreSQL from MySQL? Here is some help… General hints on PostgreSQL \? opens the command overview \d lists things: \du lists users, \dt lists tables etc...

...Command comparison Description MySQL command PostgreSQL equivalent Connect to the database mysql -u $USERNAME -p sudo -u postgres psql Show databases SHOW DATABASES; \l[ist] Use/Connect to a database named...

When talking to your MySQL server via a mysql shell, you can terminate queries by ; or \G – the latter gives you a vertical output. You know this: mysql> SELECT * FROM...

+----+---------+---------------------+-----------------+ 3 rows in set (0.00 sec) This is how it looks like vertically: mysql> SELECT * FROM users \G *************************** 1. row *************************** id: 1 name: Alice email: alice@example.com greeting: Hello world...

To show the collation of your tables you have to login to the MySQL console and execute SHOW TABLE STATUS FROM database; mysql> SHOW TABLE STATUS FROM test; +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine...

...show the default character set of a table you have to login to the MySQL console and execute SHOW CREATE TABLE table; mysql> SHOW CREATE TABLE foobar; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table...

Note: This applies specifically to MySQL. In PostgreSQL for example, this is not an issue. If you care about performance, never use a query like UPDATE users SET has_message...

...WHERE users.id IN (SELECT user_id FROM messages) MySQL does not optimize this and seems to scan the temporary table, which isn't indexed, for every row in the update...

...and end_date are simple dates. In order to make sense of your query, MySQL will cast your dates to datetimes where the time component is 00:00:00. Because...

...FROM log_items WHERE created_at BETWEEN '2007-05-01' AND '2007-05-31' MySQL sees that you are trying to compare a datetime with a date and silently casts...

A MySQL DECIMAL column is used when it is important to preserve exact precision. It takes two parameters, where precision is the total number of digits and scale the...

...a decimal column definition looks like this: t.decimal :amount, :precision => 6, :scale => 2. Issue MySQL prior to 5.6 stored leading zeros (0003.1) and +/- characters (+2.1) within the column. However, it...

When you do a script/dbconsole -p, your MySQL shell will already be using UTF-8. When you call it yourself using mysql, it may not be enabled.

...of Hlavní město Praha. You need to manually switch on UTF-8, in the MySQL console: SET NAMES 'utf8...

...to use encrypted connections between your application and RDS instances. If you're using MySQL on RDS, here's what to do: Download the AWS CA file and copy it...

...further details of the SSL configuration (such as public key). Try to connect using MySQL client % mysql -uyour_username -p -h rds_hostname_from_management_cockpit.eu-west-1.rds.amazonaws.com --ssl --ssl-ca=/path/to/mysql-ssl-ca-cert.pem Use this statement to...

...but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don't represent the actual database sizes? This is for you!

...a mysql root console: SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema; This will get you...

...databases are actually very complicated, and chances are, your making some incorrect assumptions. The MySQL innodb engine actually has four different modes for transactions: READ UNCOMMITTED READ COMMITTED REPEATABLE READ...

...are consistent nonlocking reads. The problem with consistent nonlocking reads If you open a MySQL transaction, the first SELECT statement will establish a "snapshot" of the database's state. All...

...want to see how long your database queries actually take, you need to disable MySQL's query cache. This can be done globally by logging into a database console, run...

ActiveRecord::Base.uncached do yield end true end Don't forget to re-enable MySQL query caching later with SET GLOBAL query_cache_type=ON...

CONCAT('foo', 'bar', NULL) = NULL the NULL always wins in MySQL. If you would rather treat NULL as an empty string, use CONCAT_WS (concatenation with separator) instead...

...create an int column, which is smaller than a bigint. These are values for MySQL. At least on PostgreSQL you can just use bigint instead of integer: create_table :example...

Usually our mysql queries are not case sensitive. In order to query case sensitive, you can use the mysql COLLATE clause. The collate clause lets you specify a collation, which...

The mysql2 gem in version 0.3.13 might break while compiling on older patch releases of Ruby 1.9.3 within rvm: ** [err :: server] ruby: symbol lookup error: /path/to/deployment/shared/bundle/ruby/1.9.1/gems/mysql2-0.3.13/lib/mysql2/mysql2.so: undefined symbol: rb_wait...

...server] ruby: symbol lookup error: /path/to/deployment/shared/bundle/ruby/1.9.1/gems/mysql2-0.3.13/lib/mysql2/mysql2.so: undefined symbol: rb_wait_for_single_fd Fixating mysql2 to version 0.3.11 helped...

The way MySQL's FULLTEXT tokenizer splits text into word tokens might not always be what you need. E.g. it splits a word at period characters. Since the tokenizer has...

...before it is stored in your FULLTEXT column. E.g. if you don't want MySQL to split at period characters, just strip out period characters. If you apply the same...

This might be due to AppArmor denying the MySQL server access to most of the filesystem. You can instead use LOAD DATA LOCAL INFILE … to pipe data through the MySQL...

...or end of a word: note.title =~ /\bfoo\b/ Unfortunately \b is not available in MySQL. You can use [[:<:]] and [[:>:]] to match the beginning and end of a word instead:

...FROM notes WHERE title REGEXP "[[:<:]]foo[[:>:]]" These markers are unique to MySQL and not available in Ruby regular expressions...