PostgreSQL cheatsheet

postgis_administration.md

PostGIS Administration

Create Database with Spatial Support (PostGIS v2.0)

Connect to your database, example using psql command line

\connect mydb

then install extensions

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

List of extension installed

mydb=# \dx
                                         List of installed extensions
       Name       | Version |   Schema   |                             Description                             
------------------+---------+------------+---------------------------------------------------------------------
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis          | 2.0.1   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_topology | 2.0.1   | topology   | PostGIS topology spatial types and functions
(3 rows)

mydb=# 

List of tables/view after extension installed

mydb=# \d
                List of relations
  Schema  |       Name        |   Type   | Owner 
----------+-------------------+----------+-------
 public   | geography_columns | view     | cecep
 public   | geometry_columns  | view     | cecep
 public   | raster_columns    | view     | cecep
 public   | raster_overviews  | view     | cecep
 public   | spatial_ref_sys   | table    | cecep
 topology | layer             | table    | cecep
 topology | topology          | table    | cecep
 topology | topology_id_seq   | sequence | cecep
(8 rows)

mydb=# 

Create Database with Spatial Support (PostGIS v2.1)

Connect to your database, then install extensions

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_tiger_geocoder;

postgresql_administration.md

PostgreSQL Administration

SQL Query to Manage your Databases

All of this command is executing on your sql client (like psql command line)

Create Username

CREATE USER mydbusername PASSWORD 'mypassword';

Create Database (with specific owner)

CREATE DATABASE mydb OWNER mydbusername ENCODING 'utf8';

Command Line Utility

Run this command directly from your shell

psql.md

PSQL Command Line

List all commands

\?

List Database

\l

Connect to Database

\connect mydatabasename

or short version

\c mydatabasename

List Relations (tables, views, sequences)

\d