Dicas PostgreSQL / PostGIS

dicas_postgresql_postgis.md

Criar um Índice:

CREATE INDEX ON ctrm_cafirweb (incra);

Resetar uma Sequência:

--TRUNCATE tabela;
ALTER SEQUENCE tabela_id_seq RESTART WITH 1;
UPDATE tabela SET id=nextval('tabela_id_seq');

Adicionar um campo virtual sequencial:

select (row_number() OVER ())::integer AS id ...

Backup de uma tabela no banco de dados:

pg_dump --table=yourTable --data-only --column-inserts yourDataBase > file.sql

Resolver problema com o pid:

rm /Users/marcellodebarrosfilho/Library/Application\ Support/Postgres/var-13/postmaster.pid

Apagar todas as tabelas de um BD:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

Subir vários SQL para a Amazon:

for f in *.sql; do PGPASSWORD=${PASS} psql -v ON_ERROR_STOP=1 -h itrfacil-reserva.c9cdhnwr59jf.us-east-1.rds.amazonaws.com -U itrfacil_user -p 5432 -w itrfacil_db -f $f ; done

Ver o tamanho das tabelas em um banco:

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name ='public'
ORDER BY table_size DESC;