Category Archives: Database

Postgres comes with a functionality called vacuum. Vacuum is intended to cleanup dead tuples or rows. This is how you cleanup the entire DB in PostgreSQL 1/usr/pg9/bin/vacuumdb –full –port=5433 –username=YOUR_USERNAME –password DB_NAME

This is how you backup your Postgres Database To Backup 1pg_dump –host=localhost –port=5432 –username=postgres –file=YOUR_FILENAME.sql YOUR_DB_NAME There are other options such as if you’re interested only on the structure then you can add: 1pg_dump –host=localhost –port=5432 –username=postgres –schema-only –format=p –create … Continue reading →

SET SESSION AUTHORIZATION jane; SET search_path = schema1,schema2, schema3; SELECT * FROM any_table_in_schema_1_2_or_3;

12CREATE CAST(integer AS character varying) WITH INOUT AS IMPLICIT; CREATE CAST (character varying AS integer) WITH INOUT AS IMPLICIT;

123456– Rule: "_DELETE" ON sometable — DROP RULE "_DELETE" ON sometable; CREATE OR REPLACE RULE "_DELETE" AS     ON DELETE TO sometable DO INSTEAD  DELETE FROM _sometable  WHERE _sometable.account_id = old.account_id AND (old.domain_id = ANY ((( SELECT get_visible_domains(‘DELETE’::text) AS … Continue reading →

1SELECT setval(‘YOUR_SEQUENCE_NAME’, (SELECT MAX(id) FROM YOUR_TABLE_NAME));