Skip to content

Instantly share code, notes, and snippets.

@cecepm
Last active March 28, 2016 15:40
Show Gist options
  • Save cecepm/6349572 to your computer and use it in GitHub Desktop.
Save cecepm/6349572 to your computer and use it in GitHub Desktop.
PostgreSQL cheatsheet

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

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 Command Line

List all commands

\?

List Database

\l

Connect to Database

\connect mydatabasename

or short version

\c mydatabasename

List Relations (tables, views, sequences)

\d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment