This notebook contain my cheat sheet for working with databases. It’s is hiding here while it’s in draft.

Table of Contents

Getting Started Once the Database Is Set Up

You can do this either in a jupyter notebook or in a shell.

In a shell, enter psql

Note that the user names are case sensitive. So if you set up an account with your username Julius it will be julius. So you have to log in as follows:

psql -d postgres -U julius

You can also log in to the admin account like so:

psql -d postgres -U postgres

Creating a Table

Let’s create a table with PostGIS geometries in it.

CREATE TABLE IF NOT EXISTS geom_table (id serial primary key, polygon_geom geometry(Polygon, 4326), geometry_geom geometry(Geometry, 4326));

Administering the Database

One way to administer your database is with pdAdmin. You’ll probably find that for most things you prefer the command line, but for some things it’s nice to have a UI.


You can see the users here: julius and postgres. postgres is the admin account.


Here’s how to update permissions.


Again, you can do this in the pgAdmin.


You’ll also have to give permission to sequences: GRANT UPDATE, USAGE, SELECT ON SEQUENCE public.geom_table_id_seq TO julius;

Looking Around



Query a table

select * from geom_table limit 10

Entering into database

Now let’s add some stuff to our table

insert into geom_table (polygon_geom, geometry_geom) values ('0103000020E61000000100000005000000EF30A5DE4B855EC0C3308F769AB347406ED22F8149855EC0DD60A6D99AB347404AB842264A855EC035C13C23A2B34740CD16B8834C855EC01B9125C0A1B34740EF30A5DE4B855EC0C3308F769AB34740', '0103000020E61000000100000005000000EF30A5DE4B855EC0C3308F769AB347406ED22F8149855EC0DD60A6D99AB347404AB842264A855EC035C13C23A2B34740CD16B8834C855EC01B9125C0A1B34740EF30A5DE4B855EC0C3308F769AB34740')

I don’t like doing a lot from the command line tools. It doesn’t give you enough feedback if you make a mistake. For example, if you try to insert into a table that doesn’t exist (perhaps you had a typo in the name) it doesn’t give you an error.

INSERT INTO geom_table (polygon_geom, geometry_geom) VALUES (GeomFromText('POLYGON ((4 5, 5 5, 5 4, 4 4, 4 5))'), GeomFromText('POLYGON ((4 5, 5 5, 5 4, 4 4, 4 5))'))

Note that you can also get weird errors. For example, when I run this:

INSERT INTO Customers(name, email, entry_num, age) VALUES ("John Smith", "", 100, 25);

The error is weird


The solution is to change your single quotes to double quotes.

Some samples

CREATE TABLE Customers (
  name text,
  email text,
  entry_num int,
  age int

INSERT INTO Customers(name, email, entry_num, age) VALUES ("John Smith", "", 100, 25);
INSERT INTO Customers(name, email, entry_num, age) VALUES ("Karen Smith", "", 101, 42);
INSERT INTO Customers(name, email, entry_num, age) VALUES ("John Smith", "", 102, 25);

select entry_num, count(*) from customers
group by entry_num
having count(*)>1

Return all the names that are duplicated and the number of entries

select name, email, count(*)
from Customers
group by name, email
having count(*) > 1

Another way

with subquery as (
select name, email, entry_num, row_number() over(partition by email) as rk
from Customers
select name, email, entry_num 
from subquery
where rk = 1

Select all rows that are part of a duplicate

select t.* from Customers as t
inner join
( select name, email, age, count(*) from Customers
group by email, name, age
having count(*) >= 2) as tt
on and and t.age=tt.age

Get the first entry of every person

select name, email, age, min(entry_num) as entry_num from Customers group by email, name, age

Using pandas

You can load results into a DataFrame like so:

with db_session(engine=my_engine) as sess:
    records = sess.execute(query_string)
df = pd.DataFrame(records)

But you can also do it directly using read_sql:

with db_session(engine=my_engine) as sess:
    df = pd.read_sql(query_string, sess.bind)

Useful commands

update my_table set deleted=now() where ...


If you’re coming from a Python world, remember, single quotes and double quotes are not interchangeable!


The ->> operator gets the JSONB array element as a text, while the -> operator gets it as JSONB. If you want to process the element as JSONB, use ->; if you need it as text for comparison or other operations, use ->>.