PostgreSQL cheat sheet for MySQL lamers

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 'some_database'	USE some_database;	\c some_database
Show tables/relations within one database	SHOW TABLES;	\dt
Show table details (columns, types)	DESCRIBE some_table;	\d+ some_table
Show indices of some_table (in case of MySQL) and all indices of database (PostgreSQL)	SHOW INDEX FROM some_table;	\di
Create user that can create databases	CREATE USER harry IDENTIFIED BY 'foo';	CREATE ROLE username WITH createdb LOGIN [PASSWORD 'password'];
Change password of an existing user	...	ALTER ROLE username WITH PASSWORD 'password';
Grants access to a database.	GRANT ALL PRIVILEGES ON database.* TO username@localhost;	GRANT ALL PRIVILEGES ON DATABASE database TO username;
Grants access to create databases.	?	ALTER USER username CREATEDB;
Expanded table formatting mode (pretty print)	YOUR QUERY\G;	\x on

How to create Excel sheets with spreadsheet gem and use number formats for cells like money or date

The following snippet demonstrates how you could create excel files (with spreadsheet gem) and format columns so that they follow a specific number format like currencies or dates do.

require 'rubygems...

Fix for mysql2 error "Incorrect MySQL client library version! This gem was compiled for x.x.x but the client library is y.y.y."

This should be fixed in the latest LTS-branches of our mysql2 fork, 0.2.x-lts and 0.3.x-lts.

Use

gem 'mysql2', git: 'https://github.com/makandra/mysql2', branch: '0.2.x-lts' # for Rails 2.x
gem 'mys...

Read your mail in networks that forbid e-mail traffic

If you are connected with a network that forbids e-mail traffic but allows SSH, you can tunnel your e-mail connection through a trusted, intermediary server:

 sudo ssh -i /home/local-user/.ssh/local-user.key -L 143:mail-server.tld:143 remote-...

Posted by Thomas Eisenbarth to makandra dev (2013-09-26 15:35)

PostgreSQL cheat sheet for MySQL lamers

General hints on PostgreSQL

Command comparison

Further reading

Related cards:

Be careful! The following solution will delete files on a volume. If you don't know exactly what you're doing, you can run into big trouble with your computer.

General hints on the DOM

Be careful!
The following solution will delete files on a volume. If you don't know exactly what you're doing, you can run into big trouble with your computer.