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 No 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

Niklas Tech Blog
Powered by  GDPR Cookie Compliance
Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.