indexes and relationships - Mailing list pgsql-novice

Hello,

I am new to sql of any sort. I have selected postgresql to learn and am
in the process of trying to train myself.  I am using 8.3 on linux.

My first self assignment is to get the data from the nutritional
database published by USDA into a postgresql database.

This database is available at

    http://www.nal.usda.gov/fnic/foodcomp/search/

at a button at the bottom when it isn't busy.

Based on their documentation I have created the tables, converted the
text files to csv files and inserted the data.

Now I am in the process of setting up the relationships.

Most of the relationships are relatively straightforward but one is more
challenging, at least to me.

The data in one file uses two fields to refer to the data in another
file which will use one field or two fields as the case may be.

When I searched the novice archives, the search tool referred to a
manual page (paragraph 11.5) which seemed to be on point and discussed
multiple indexes.

Unfortunately, I did not see any example of usage in the manual.

Would someone please provide an example of the implementation of this
capability/technique?

Actual data and notes follow:

-------------------------------

PROBLEM:
data in one file contains empty fields or
one line referrs to all items with a part of key value
data is:

table d06_nut_dat.csv
   field1, field2, field3, field4, ...
   "02047","645",0.000,0,,"4","","","",,,,,,,""
   "02047","646",0.000,0,,"4","","","",,,,,,,""
->"02048","203",0.00,3,0.000,"1","A","","",1,0.00,0.00,,,,"2- 3"
   "02048","204",0.00,3,0.000,"1","A","","",1,0.00,0.00,,,,"1- 2- 3"
   "02048","205",0.93,0,,"4","NR","","",,,,,,,""
   "02048","207",0.17,3,0.003,"1","A","","",1,0.17,0.18,2,0.160,0.189,
    "2-3"
=>"02048","208",21,0,,"4","NR","","",,,,,,,""
   "02048","210",0.00,1,,"12","MA","","",,,,,,,"1"
   "02048","211",0.10,1,,"12","MA","","",,,,,,,""
   "02048","212",0.30,1,,"12","MA","","",,,,,,,""
   "02048","213",0.00,1,,"12","MA","","",,,,,,,"1"
   "02048","214",0.00,1,,"12","MA","","",,,,,,,"1"
   "02048","221",0.0,0,,"4","","","",,,,,,,""
   "02048","255",93.81,3,0.092,"1","A","","",1,93.65,93.97,2,93.414,
    94.204,"2-3"
   "02048","262",0,0,,"7","Z","","",,,,,,,""
   "02048","263",0,0,,"7","Z","","",,,,,,,""
   "02048","268",90,0,,"4","NR","","",,,,,,,""
   "02048","269",0.40,0,,"4","NC","","",,,,,,,""
   "02048","287",0.00,0,,"7","Z","","",,,,,,,""

table d11_footnote.csv
   field1, field2, field3, field4, ...
   "02034","01","N","204","Total proximates do not equal 100% because
                  piperine was subtracted from libid value."
->"02048","01","D","","Contains 5% acetic acid.  Total proximates do
not     equal 100%"
=>"02048","02","N","208","Acetic acid is included in energy
calculation."
   "02053","01","D","","Contains 5% acetic acid.  Total proximates do
not     equal 100%"

--------------------------------------

Thanks in advance,

Bill House