Tag Archives: PostgreSQL

My PostgreSQL cheat sheet

Posted by on December 21, 2012 No comments

Here I have collected all common commands and SQL for PostgreSQL that I use in my work

Help on psql slash commands
\?

Help on SQL commands
\h

List databases
\l

Connect to different database
\c database

Eqivavent to DESCRIBE table
\d+ tablename

List database settings
\set

List all tables
\dt

List users
\du

Quit psql command line
\q

Create user

CREATE USER niklas WITH PASSWORD 'myPassword';

Grant all privilegies on database to a user

GRANT ALL PRIVILEGES ON DATABASE myDatabase to niklas;

Grant alla privilegies on table to a user

GRANT ALL PRIVILEGES ON TABLE myTable to niklas;

Create table syntax (standard)

CREATE TABLE myTable (id serial, time integer);

Insert statement

INSERT INTO public.user (id, login, password) VALUES (1, 'test','test');# NOTE the single quotes!

Update statement

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';# NOTE the single quotes!

Delete statement

DELETE FROM films WHERE producer_id = 1;

I hope to come back to this sheet to add stuff as time goes by

OS X Lion, PostgreSQL and MacPorts install and administration cheat sheet

Posted by on May 31, 2012 2 comments

Since PostgreSQL is a nightmare to administer I have to put these hints down here so I never have to deal with it again 🙂

Install PostgreSQL with Macports:

sudo port install postgresql90-server

Create the default database environment

sudo mkdir -p /opt/local/var/db/postgresql90/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql90/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql90/bin/initdb -D /opt/local/var/db/postgresql90/defaultdb'

Start the database environment

sudo su postgres -c '/opt/local/lib/postgresql90/bin/postgres -D /opt/local/var/db/postgresql90/defaultdb'

or

sudo su postgres -c '/opt/local/lib/postgresql90/bin/pg_ctl -D /opt/local/var/db/postgresql90/defaultdb -l /tmp/pg.log start'

Now lets create the database we are going to use

sudo su postgres -c '/opt/local/lib/postgresql90/bin/createdb test'

and now we want to connect to the newly created database with psql

sudo su postgres -c '/opt/local/lib/postgresql90/bin/psql test'

To add a new user to the database and give him full privileges. Open psql (like above) and type:

CREATE USER niklas WITH PASSWORD 'myPassword';
GRANT ALL PRIVILEGES ON DATABASE myDatabase to niklas;

After this the user ‘niklas’ can logon to the database with this:

/opt/local/lib/postgresql90/bin/psql test

Tested on OS X Lion (10.7.3), PostgreSQL 9.0 and MacPorts 2.0.4