Postgresql

From WeWeWeb Wiki
Jump to: navigation, search

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