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:
On Linux, you log in as the newly created postgres user (after installing postgresql).
Now where is the
? This is a normal shell command
. Log out from the console with Ctrl-D or
Fine, let’s log back in and
. But there is no
On OS X there is no postgres user created, so you just run
from the terminal.
Creating a new user
See also: Practical PostgreSQL. MySQL equivalent of this section:
The syntax obviously differs,
\h CREATE USER
tells us how.
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:
Great! So let’s log in as this user.
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
. Either change this line
or add the following line if you only want it to hold for the milestone user:
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:
Finally the PostgreSQL service needs to be restarted. Pick one of (if it does not work, see below for
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)
So our milestone user needs access to
, both for all tables as well as sequences. The sequence is used (and changed upon inserts) for the
data type from above.
obviously does not exist, otherwise there would not be a section about this keyword. But there is a data type
The MySQL equivalent is
SHOW TABLES and Co.
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.
To find out on which directory a postgres instance is running, you can use something like
ps aux |grep postgres
Socket file errors
/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
Or you change the
to use another directory like
as socket directory:
then complains that it cannot connect to the server since it cannot connect to something like
. So we reveal it the new path to the socket directory as well:
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 …
This code is stored into a file and loaded in the psql terminal with
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
, or you cannot access the fields with e.g. getInt(1) or getInt("columnName").
Changing the language of error messages to English: