|
Building a test data base in PostgreSQL
Here is a file that will
generate a dummy data base (all data is random in this
file). Down load the file to the cygwin directory you
will use for testing (make a special directory
for data base work, lets not mush it up with your
YouTube collection).
Next, run psql on this file. You tell psql to
take its commands from a file with the -f switch.
Assumming you have named the file from above maketest,
type the following:
psql -d medical -f maketest
where -d medical indicates you are working with the
medical data base (created above) and that the command will
come from file maketest.
You should see a large number of lines appear that more or less look like:
<![CDATA[
INSERT 17561 1
INSERT 17562 1
INSERT 17563 1
INSERT 17564 1
INSERT 17565 1
ERROR: table "meds" does not exist
psql:maketest:394: ERROR: table "meds" does not exist
CREATE TABLE
INSERT 17571 1
INSERT 17572 1
INSERT 17573 1
INSERT 17574 1
INSERT 17575 1
INSERT 17576 1
INSERT 17577 1
INSERT 17578 1
INSERT 17579 1
INSERT 17580 1
INSERT 17581 1
INSERT 17582 1
INSERT 17583 1
INSERT 17584 1
INSERT 17585 1
INSERT 17586 1
INSERT 17587 1
INSERT 17588 1
INSERT 17589 1
INSERT 17590 1
]]>
The error message is ok. In the file, before it creates
each table, it deletes any old version of the table.
Since you have no old version, you get an error message.
Should you run this a second time, the error messages
will not appear since you now do have these tables.
Examine a sample of maketest:
<![CDATA[
drop table demographic;
create table demographic (ptid text, DOB text, Gender text, Race text, Marital text,
PriPhys text, PriPhysTel text, PriPhysAddr text,
EContact text, ERelat text,
EAddr text, ETel text, PriPharm text, PriPharmAddr text,
PriPharmTel text);
insert into demographic values ('1001', '1952-Mar-28','Male','White','Single',
'Dr. J. Snerd','617 266 1234','123 Elm St, Waltham, MA 01234',
'Mike Jones','Brother',
'987 Elk St, Dover, MA','781 123 4567',
'Walgreens', '123 Jet St, Hudson, MA','508 123 4567');
insert into demographic values ('1002', '1962-Jan-18','Male','Black','Married',
'Dr. X. Grinch','617 123 9876','123 Green St, Dedham, MA 09876',
'Jane Smith','Sister',
'987 Moose St, Sekonk, MA','508 123 4567',
'Osco', '776 Nahatan St, Norwood, MA', '781 987 6543');
insert into demographic values ('1003', '1932-Dec-08','Female','White','Divorced',
'Dr. A. Claus','319 123 9876','123 Corn St, Hudson, IA 50611',
'Jane Smith','Sister',
'987 Main St, Attleboro, MA','508 999 4567',
'Osco', '776 Nahatan St, Norwood, MA', '781 987 6543');
insert into demographic values ('1004', '1922-Nov-02','Female','White','Single',
'Dr. A. Claus','319 123 9876','123 Apple St, Grundy, IA 50611',
'Mike Jones','Brother',
'987 Main St, Attleboro, MA','508 999 4567',
'Walmart', '776 Boston Post Rd, Walpole, MA', '781 987 6543');
insert into demographic values ('1005', '1942-Feb-02','Male','White','Married',
'Dr. W. Strange','319 123 9876','123 Hog St, Waterloo, IA 50611',
'Nancy Adams','Wife',
'987 Main St, Attleboro, MA','508 999 4567',
'Osco', '345 School St, Dedham, MA', '781 326 6543');
drop table temperature;
create table temperature (ptid text, date text, time text, temperature text);
insert into temperature values ('1001', '1999-12-01', '12:05', '98.6');
insert into temperature values ('1001', '1999-12-02', '13:05', '99.1');
insert into temperature values ('1001', '1999-12-03', '14:05', '100.0');
insert into temperature values ('1001', '1999-12-04', '11:05', '99.0');
insert into temperature values ('1001', '1999-12-05', '10:05', '99.9');
insert into temperature values ('1001', '1999-12-01', '13:05', '99.1');
insert into temperature values ('1001', '1999-12-02', '14:05', '98.6');
insert into temperature values ('1001', '1999-12-03', '15:05', '98.5');
]]>
This is the beginning of the file. First, it attempts to delete the
table demographic (which will probably give an error message).
Then it creates the table. When it creates a table, you are labeling
the columns and describing the nature of the data that will
appear in this column (all text here).
Next, several rows are inserted into the table. Each successive value goes in
a successive column as per the table creation. These generate the "INSERT" messages
each time one is successful.
Next, the process is repeated for a table named temperature and so
on for several other tables.
Accessing and manipulating the data base:
psql -d medical
or,
psql -d medical -U username
By default, a user id was created for the user who created the
database.
You can explicitly specify a user name with the -U switch.
This would be the username you created with the createuser command
above.
Once in psql, you can see, display and manipulate the tables:
<![CDATA[
\d shows all table names
\d demographic shows details of demographic table
select namelast, namefirst, ptid from ptname;
yields:
namelast | namefirst | ptid
----------+-----------+------
Jones | John | 1001
Smith | Charles | 1002
Smith | Sara | 1003
Jones | Jane | 1004
Adams | William | 1005
(5 rows)
\q exits psql
]]>
|