Postgresql
From WeWeWeb Wiki
Jump to navigationJump to searchTo add a user:
create user <name> sysid <uid> encrypted password 'password' createdb;
To convert to another encoding system:
did a pg_dump iconv -f 8859_1 -t UTF-8 created new db with encoding UNICODE and reloaded- no errors upon reload
To dump a database:
pg_dump dbname > outfile
To reload a datbase:
psql dbname < infile or psql -d database -f db.out or pg_restore -d newdb db.tar
To add the language 'plpgsql', login as postgres:
createlang plpgsql <database>
To set trigger to update last_update column when modified:
CREATE OR REPLACE FUNCTION update_lastupdate_column() RETURNS TRIGGER AS $$ BEGIN NEW.last_update = now(); RETURN NEW; END; $$ language 'plpgsql';
CREATE TRIGGER update_customer_lastupdate BEFORE UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
Goto Linux