Postgresql
From WeWeWeb Wiki
Jump to navigationJump to searchThe printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
To 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