creando una postgres db en ramdisk

#ramdisk de 5GB
mount -o size=5G -t tmpfs tmpfs /mnt/ramdb
chown -R postgres.postgres /mnt/ramdb

#en postgres ahora
CREATE TABLESPACE ramdb LOCATION '/mnt/ramdb';
CREATE DATABASE db WITH TABLESPACE ramdb;

Plantilla de base de datos con PostGIS incluida

http://geospatial.nomad-labs.com/2006/12/24/postgis-template-database/


# Set postgis-1.5 path.
$ POSTGIS_SQL_PATH=`pg_config --sharedir`/contrib/postgis-1.5

# Creating the template spatial database
$ createdb -E UTF8 -T template0 template_postgis
# and add PLPGSQL language support.
$ createlang -d template_postgis plpgsql

# Loading the PostGIS SQL routines.
$ psql -d template_postgis -f $POSTGIS_SQL_PATH/postgis.sql
$ psql -d template_postgis -f $POSTGIS_SQL_PATH/spatial_ref_sys.sql

# Enabling users to alter spatial tables.
$ psql -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
$ psql -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"

# Garbage-collect and freeze.
$ psql -d template_postgis -c "VACUUM FULL;"
$ psql -d template_postgis -c "VACUUM FREEZE;"

# Allows non-superusers the ability to create from this template.
$ psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';"
$ psql -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='template_postgis';"


Mostrando nulo cuadn NULL en postgres

\pset null nulo

Get list tables from postgres database

If we need to iterate over all tables of a certain schema, we can get the list of schema, table, type and user using psql:

$ psql -qAtF, -c "\dt da_schema.*"
da_schema,segments,table,jacen
da_schema,speeds,table,jacen
$ _

You can also use "*" in the schema name, for example:

$ psql -qAtF, -c "\dt da_schema*.*"
da_schema,segments,table,jacen
da_schema,speeds,table,jacen
da_schema_2,labels,table,jacen
da_schema_test,positions,table,jacen
$ _

Work with Data/Time in Postgres

A common scenario in Postgres (at least for me) is do conversions between different time formats. For example

* How to transform 'time' from epoch to any human readable format?

  SELECT to_char(to_timestamp(1239148800) at TIME ZONE 'UTC', 'YYYY/MM/DD HH24:MI:SS')

For any other format, you can see the table "Template Patterns for Date/Time Formatting" on the documentation of your version of Postgres.


* How to transform from timestamp to epoch ?

  SELECT EXTRACT (EPOCH FROM timestamp '20090101T041000' at time zone 'UTC') ;

Other way to do the last transformation is set the time zone on the environment

  SET TIMEZONE TO 'UTC'
  SELECT EXTRACT(EPOCH FROM timestamp '20090506T041000');