Difference between pages "File:X-35 p6.jpg" and "Postgresql"
From WeWeWeb Wiki
(Difference between pages)
Jump to navigationJump to search (Created page with "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...") |
|||
Line 1: | Line 1: | ||
+ | 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]] |
Latest revision as of 12:14, 27 January 2022
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
File history
Click on a date/time to view the file as it appeared at that time.
Date/Time | Thumbnail | Dimensions | User | Comment | |
---|---|---|---|---|---|
current | 11:08, 27 January 2022 | 423 × 232 (12 KB) | Willy (talk | contribs) |
You cannot overwrite this file.
File usage
The following page uses this file: