Category Archives: Databases - Page 2

My PostgreSQL cheat sheet

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

How to drop a column in SQLite 3

As you might know, SQLite 3 does not support dropping columns from tables with ALTER TABLE command so we have to do a workaround instead. The idea is to create a new table with all the columns (and data) that we want to keep and then drop the old table and rename the new table with the old name.
Here is an example:

First we create a new table with all the columns and data we want to save

CREATE TABLE my_table_temp AS (SELECT id, user_id, latitude, longitude FROM my_table);

Then we drop the old table

DROP my_table;

And finally we rename our new table with the old name

ALTER TABLE my_table_temp RENAME TO my_table

Done!

Tested on OSX 10.7.5 and SQLite v3.7.11

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

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