Skip to content

Instantly share code, notes, and snippets.

@appkr
Last active Nov 5, 2020
Embed
What would you like to do?
PostGIS, Postgre

101

Create table

CREATE DATABASE db_name ENCODING=utf8 lc_collate="C" template=template0;

-- Reconnect to `db_name` database
-- @see https://stackoverflow.com/questions/3949876/how-to-switch-databases-in-psql
CREATE EXTENSION postgis;

Run postgresql in local

$ mkdir $HOME/pg_data
$ docker run -d \
      --name=postgis \
      -e POSTGRES_USER=postgres \
      -e POSTGRES_PASS=secret \
      -e POSTGRES_DBNAME=region \
      -e ALLOW_IP_RANGE=0.0.0.0/0 \
      -p 5432:5432 \
      -v $HOME/pg_data:/var/lib/postgresql \
      --restart=always \
      kartoza/postgis:10.0-2.4

List users

SELECT u.usename AS "Role name",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
    WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
    WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
    ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;

Update hibernate sequence

When we insert a record manully through a postgre client, the hibernate sequence will not be updated... so...

SELECT setval('hibernate_sequence', (SELECT max(id) FROM table_name));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment