Skip site navigation (1) Skip section navigation (2)

indexes and relationships

From: bill house <wchouse(at)bellsouth(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: indexes and relationships
Date: 2009-12-27 19:06:31
Message-ID: 4B37B037.8050607@bellsouth.net (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-novice by date

Next:From: Bob McConnellDate: 2009-12-27 22:35:31
Subject: Re: indexes and relationships
Previous:From: sfarley1Date: 2009-12-25 17:42:56
Subject: PostgreSQL 8.3 and Windows 7

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group