PostGIS, Postgre

postgis.md

101

• http://postgis.net/workshops/postgis-intro/geometries.html

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

postgre.md

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;

• source: http://www.postgresqltutorial.com/postgresql-list-users/

text_column.md

For text column

Symptom. When we annonate text field @Lob, the hibernate will save the text on the postgre as long type. To workaround it, see the following:

• https://shred.zone/cilla/page/299/string-lobs-on-postgresql-with-hibernate-36.html
• https://stackoverflow.com/questions/4488693/hibernate-postgresql-column-x-is-of-type-oid-but-expression-is-of-type-byte

update_sequence.md

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));