Ronald Pringadi's Technical Notes

Add an Existing User to an Existing Group on Linux

Posted on August 24, 2011 by ronaldpringadi

1	usermod -a -G GROUPNAME USERNAME

Posted in Linux | Comments Off

Backup and Restore Postgres Using pg_dump and psql

Posted on August 24, 2011 by ronaldpringadi

This is how you backup your Postgres Database

To Backup

1	pg_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:

1	pg_dump --host=localhost --port=5432 --username=postgres --schema-only --format=p --create --inserts --file=YOUR_FILENAME.sql YOUR_DB_NAME

To Restore

1	psql -Upostgres -hlocalhost YOUR_DB_NAME < YOUR_FILENAME.sql

Posted in Database, PostgreSQL | Comments Off

Setting Session Authorization and Search Path

Posted on August 24, 2011 by ronaldpringadi

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

Posted in Database, PostgreSQL | Comments Off

Implicit Casting in PostgreSQL

Posted on August 24, 2011 by ronaldpringadi

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

Posted in Database, PostgreSQL | Comments Off

Integer Array Casting in PostgreSQL

Posted on August 24, 2011 by ronaldpringadi

1
2
3
4
5
6

-- 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 get_visible_domains)::integer[])));

Posted in Database, PostgreSQL | Comments Off