DataBase Build Notes

• Database Management
• Data Manipulation
• Table Management

Procedure:

1. Database Management

   Procedure	Postgres Command	Notes
   Delete Database	dropdb databasename	Run as postres user
   Create Database	createdb databasename	Must have postgres installed and setup.
   Start db	psql databasename
   Restore Database	psql -f dumpfilename databasename	Must create table before restoring.
   Backup Database	pg_dump databasename > dumpfilename
   List Databases	psql -l	List local databases
   Create User	createuser username	Must be the postgres user
   Delete User	dropuser username	Must be the postgres user

   Return to top of page
2. Data Manipulation

   Procedure	Postgres Command	Notes
   Change a Data Value in a Tuple	UPDATE cpu SET tag = 'SI10599' WHERE name = 'Das104';
   Enter Data	INSERT INTO cpu VALUES ('Adcs206', 206, 'Ultra10', 9, 320, 'SG23763', 'FW81926808');
   Delete a Tuple	DELETE FROM monitor WHERE name = 'Das210';

   Return to top of page
3. Table Management

   Procedure	Postgres Command	Notes
   Change Column Type	Copy to a new column, add new column contraints, copy table to remove old column
   Copy Table	CREATE TABLE monitor2 AS SELECT name,istc,type,model,hd,tag,serail,remark FROM monitor;
   Add Table	CREATE TABLE monitor(name text, tag text, serial text);
   Add a counting column	Add colomn call id with integer. Create a sequence Update current values of id, UPDATE CPU SET id=NEXTVAL('cpu_id_seq'); Set id column to auto update, ALTER TABLE cpu ALTER id SET DEFAULT NEXTVAL('cpu_id_seq');
   Add Tuple	ALTER TABLE cpu ADD remarks text;
   Make two Colums unique	ALTER TABLE cpu ADD unique(tag, serial);
   Create column	ALTER TABLE cpu ADD COLUMN model2 text;
   Copy Column	UPDATE TABLE cpu SET newcol=oldcol;	Use copy table to remove old columns and reorder colomns.
   Create a sequence	CREATE SEQUENCE cpu_id_seq;