PostgreSQL from a MySQL user's perspective

I know MySQL, so PostgreSQL should be no problem? False.

Those are the solutions to the problems I encountered, for me («don’t look up the same thing twice») and for anyone else having the same problems.

Creating the first DB

MySQL equivalent of the following section:

$ mysql -u root -p
mysql> CREATE DATABASE dbTest;
mysql> USE dbTest;

On Linux, you log in as the newly created postgres user (after installing postgresql).

$ su - postgres
postgres@x220:~$ psql # Start the console
postgres=# \h # Gives you a list of available SQL commands.
postgres=# \h [command] # Display [command] usage

Now where is the CREATE DATABASE ? This is a normal shell command createdb . Log out from the console with Ctrl-D or \q and type:

postgres@x220:~$ createdb dbTest

Fine, let’s log back in and USE dbTest . But there is no USE …

postgres=# \? # List of internal commands
postgres=# \c dbTest
You are now connected to database "dbTest" as user "postgres".
dbTest=#

OS X

On OS X there is no postgres user created, so you just run psql from the terminal.

Creating a new user

See also: Practical PostgreSQL. MySQL equivalent of this section:

mysql> CREATE USER milestone IDENTIFIED BY 'milestone';
mysql> GRANT ALL ON dbTest.* TO milestone;

The syntax obviously differs, \h CREATE USER tells us how.

dbTest=# CREATE USER milestone WITH PASSWORD "milestone";
ERROR: syntax error at or near ""milestone""

dbTest=# CREATE USER milestone WITH PASSWORD 'milestone';
postgres=# GRANT ALL ON DATABASE dbTest TO milestone;
ERROR: database "dbtest" does not exist

Oh what the. Problem is that dbtest really does not exist since the DB name is converted to lowercase in this statement (and executed afterwards). Solution is to wrap it in double quotation marks like this:

postgres=# GRANT ALL ON DATABASE "dbTest" TO milestone;
GRANT

Great! So let’s log in as this user.

postgres=# \c - milestone
FATAL: Peer authentication failed for user "milestone"

Suuure, would have been too easy otherwise. Turns out that you have to tell PostgreSQL: milestone is allowed to log in via password. This happens in the file /etc/postgresql/9.1/main/pg_hba.conf . Either change this line

local all all peer

to

local all all md5

or add the following line if you only want it to hold for the milestone user:

local all milestone md5

Obviously (isn’t it?) this line has to be placed before the rule that is valid for all users or it will not work. Like this:

local all milestone md5
local all all md5

Finally the PostgreSQL service needs to be restarted. Pick one of (if it does not work, see below for pg_ctl ):

# service postgresql restart
# /etc/init.d/postgresql restart

Permissions on tables

Unfortunately all attempts of reading or writing to a table in the DB fails. IRC helps:

<RhodiumToad> the spec defines a fixed hierarchy of catalog.schema.table
<RhodiumToad> in pg, "catalog" is a database (whereas in, say, mysql, databases correspond to schemas)
dbTest=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)

So our milestone user needs access to dbTest.public.* , both for all tables as well as sequences. The sequence is used (and changed upon inserts) for the serial data type from above.

dbTest=# GRANT ALL ON ALL TABLES IN SCHEMA public TO milestone;
dbTest=# GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO milestone;

AUTO_INCREMENT

obviously does not exist, otherwise there would not be a section about this keyword. But there is a data type serial for this.

dbTest=# CREATE TABLE foo (id serial PRIMARY KEY, bar text);

The MySQL equivalent is

mysql> CREATE TABLE foo (id int PRIMARY KEY AUTO_INCREMENT, bar text);

SHOW TABLES and Co.

postgres=# \l # SHOW DATABASES
postgres=# \dt # SHOW TABLES
postgres=# \d myTable # DESCRIBE myTable

Config files and testing setups

A PostgreSQL instance has (and maintains) its own data area. This directory (also called datadir) contains configuration files and the database cluster, i.e. a collection of data bases. This allows to have multiple instances of PostgreSQL accepting connections on multiple ports, but it also means that for starting and stopping postgres you always have to pass the path to the data area as well.

postgres@x220:~$ mkdir db13 # Create new data area
postgres@x220:~$ initdb -D db13 # Initialize the data area
postgres@x220:~$ postgres -D db13 & # Run the PostgreSQL server in background
postgres@x220:~$ pg_ctl status -D db13 # Check if it is running
postgres@x220:~$ createdb dbTest # Create a new DB in db13
postgres@x220:~$ psql # Connect to the PostgreSQL running on the default port (which is 5432)
postgres@x220:~$ pg_ctl stop -D db13 # Stop the server instance running on the db13 data area

postgres@x220:~$ postgres -D db12 -p 6543 & # Run another server instance, but with a different data area, on port 6543
postgres@x220:~$ createdb dbTest -p 6543 # Create a new DB in the data area db12 (maintained by the second server instance listening on port 6543)
postgres@x220:~$ psql -p 6543 # Connect using the port for db12

To find out on which directory a postgres instance is running, you can use something like ps aux |grep postgres .

Socket file errors

Errors like /var/run/postgresql/.s.PGSQL.5432.lock not found on startup happen if automatic startup of PostgreSQL is disabled on Debian systems, e.g. with rcconf --off postgresql . There are two solutions for this, either you create /var/run/postgresql :

# mkdir /var/run/postgresql # chmod a+w /var/run/postgresql # Make the socket directory writable

Or you change the postgresql.conf to use another directory like /tmp as socket directory:

unix_socket_directory = '/tmp' # (change requires restart)

However, psql then complains that it cannot connect to the server since it cannot connect to something like /var/run/postgresql/.s.PGSQL.5432 . So we reveal it the new path to the socket directory as well:

postgres@x220:~$ psql -h /tmp # Our socket directory is now /tmp

PostgreSQL, pl/pgSQL, and JDBC

Good luck with debugging.

Delete and return a row in a stored procedure

For PostgreSQL 9.2 and above you want to take a look at the DELETE … RETURNING … statement.

DROP FUNCTION IF EXISTS getTheRow(int);
CREATE OR REPLACE FUNCTION getTheRow(rowID int)
RETURNS YourTable AS $$
DECLARE
    result YourTable;
BEGIN
    SELECT * INTO result
    FROM YourTable
    WHERE id = rowID
    LIMIT 1;
    
    DELETE FROM YourTable WHERE id = result.id;
    
    RETURN result;
END;
$$ LANGUAGE plpgsql;

This code is stored into a file and loaded in the psql terminal with \i /path/to/your/file . The DROP FUNCTION is necessary e.g. if you change parameter names in a second run; This does not work with CREATE OR REPLACE .

In JDBC, take care to not forget the * FROM , or you cannot access the fields with e.g. getInt(1) or getInt("columnName").

CallableStatement cs = connection.prepareCall(
    "SELECT * FROM receiveMessageFromSender(?)" );
csReceiveMessageFromSender.setInt(1, desiredRowID);

ResultSet rs = cs.executeQuery();

if (rs.next()) {
    Message m = new Message();
    
    int id = rs.getInt("id");
    System.out.println("Fetched row with ID " + id);
}

Other stuff

Changing the language of error messages to English:

dbTest=# SET lc_messages TO 'en_US.UTF-8';

http://www.postgresql.org/docs/9.2/static/sql-do.html

DO $_$ DECLARE i INTEGER; BEGIN FOR i IN 51..60 LOOP INSERT INTO client VALUES (i); END LOOP; END $_$;
insert into client(id) select generate_series(10,50); \timing on # More accurate than explain analyze for smaller queries due to overhead