Обсуждение: Check before INSERT INTO

----- Original Message -----

From: Kopljan Michael

To: 'Shavonne Marietta Wijesinghe'

Sent: Monday, February 11, 2008 1:45 PM

Subject: RE: [SQL] Check before INSERT INTO

Try fierst to add Transaction issolation level to Connection object :

Conn.IsolationLevel   = 1048576  && adXactIsolated

 

Then when user submit form try something like this :

strSQL = ’SELECT * FROM my_shevi ORDER BY n_gen DESC, n_sheet DESC, tot_sheet DESC LIMIT 1’

Set SQLN_GEN = oConn.Execute(strSQL)

SQLN_GEN.AddNew

SQLN_GEN(’id’).Value = ’a’

Conn.BeginTrans()

SQLN_GEN.Update

SQLN_GEN.Requery()

Conn.CommitTrans()

 

Bye Michael

 

---

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Shavonne Marietta Wijesinghe
Sent: Monday, February 11, 2008 1:04 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Check before INSERT INTO

 

My table structure

 

CREATE TABLE my_shevi
(
  id text,
  n_gen serial NOT NULL,
  n_sheet serial NOT NULL,
  tot_sheet serial NOT NULL,
  CONSTRAINT my_shevi_pkey PRIMARY KEY (n_gen, n_sheet, tot_sheet)
)
WITH OIDS;
ALTER TABLE my_shevi OWNER TO postgres;

 

The user updates the DB via ASP. When 2 users click on the submit button at the same time, only 1 record is inserted. (ERROR:  duplicate key violates unique constraint "my_shevi_pkey")

 

For example they both send a string like below.

strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);

 

I thought of adding a test before executing the insert into. 

 

Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")

 

if (not SQLN_GEN.eof) then
     ***** set n_gen + 1  
else
     ***** leave n_gen the way it is
end if

 

conn.Execute strSQL

But how can i ask it to change the n_gen value??? (The part with the *****)

 

Thanks

 

Shavonne Wijesinghe

__________ Information from ESET Smart Security, version of virus signature database 2863 (20080211) __________

The message was checked by ESET Smart Security.

http://www.eset.com

"Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> writes:

> The user updates the DB via ASP. When 2 users click on the submit button at the
> same time, only 1 record is inserted. (ERROR: duplicate key violates unique
> constraint "my_shevi_pkey")
>
> For example they both send a string like below.
> strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
>
> I thought of adding a test before executing the insert into. 

It's not clear to me what you're trying to do. If you're trying to update an
existing record then you might want something like example 37-1 on this page:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html

If you want to pick the first available n_gen then you're going to have to
repeatedly try inserting until you don't get that error. That will perform
quite poorly when you get to large values. You could do a "SELECT max(n_gen)
WHERE..." first but even that will be quite a lot of work for your database.

Perhaps you should rethink n_gen and use a serial column to generate your
primary key instead.

> Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET),
> upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND
> N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")

For what it's worth your script is a security hole. Look into using query
parameters which in ASP will probably be represented by "?". The method above
will allow hackers to get direct access to your database and do nasty things.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!

Thanks for the reply Grogory. I am trying to do a INSERT INTO.

Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key 
(Serial not null)  id     | n_gen     | n_sheet   | tot_n_sheet
----------+-----------+-----------+-------------    a    |      1    |     1     |      1    b    |      2    |     1
 |      2    x    |      2    |     2     |      2    u    |      3    |     1     |      1    r    |      4    |     1
   |      3    a    |      4    |     2     |      3    s    |      4    |     3     |      3
 


So there are 2 users inserting in to the db. In my ASP page i have a field 
that shows the value of n_gen +1. So when the 2 users both login at the same 
time, with different sessions, they both see "7" in the n_gen field. But 
when they click on the sumbit button only one record is inserted and the 
other is lost.

I though it was possible to change the SQL string before it does the 
update.. But i can't seem to find a solution for it.. Any idea ??

Thanks,

Shavonne

----- Original Message ----- 
From: "Gregory Stark" <stark@enterprisedb.com>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, February 11, 2008 5:03 PM
Subject: Re: Check before INSERT INTO


> "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> writes:
>
>> The user updates the DB via ASP. When 2 users click on the submit button 
>> at the
>> same time, only 1 record is inserted. (ERROR: duplicate key violates 
>> unique
>> constraint "my_shevi_pkey")
>>
>> For example they both send a string like below.
>> strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
>>
>> I thought of adding a test before executing the insert into.
>
> It's not clear to me what you're trying to do. If you're trying to update 
> an
> existing record then you might want something like example 37-1 on this 
> page:
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html
>
> If you want to pick the first available n_gen then you're going to have to
> repeatedly try inserting until you don't get that error. That will perform
> quite poorly when you get to large values. You could do a "SELECT 
> max(n_gen)
> WHERE..." first but even that will be quite a lot of work for your 
> database.
>
> Perhaps you should rethink n_gen and use a serial column to generate your
> primary key instead.
>
>> Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET),
>> upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND
>> N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
>
> For what it's worth your script is a security hole. Look into using query
> parameters which in ASP will probably be represented by "?". The method 
> above
> will allow hackers to get direct access to your database and do nasty 
> things.
>
> -- 
>  Gregory Stark
>  EnterpriseDB          http://www.enterprisedb.com
>  Ask me about EnterpriseDB's RemoteDBA services! 

"Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it> writes:

> Thanks for the reply Grogory. I am trying to do a INSERT INTO.
>
> Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key
> (Serial not null)
>   id     | n_gen     | n_sheet   | tot_n_sheet
> ----------+-----------+-----------+-------------
>     a    |      1    |     1     |      1
>     b    |      2    |     1     |      2
>     x    |      2    |     2     |      2
>     u    |      3    |     1     |      1
>     r    |      4    |     1     |      3
>     a    |      4    |     2     |      3
>     s    |      4    |     3     |      3
>
>
> So there are 2 users inserting in to the db. In my ASP page i have a field that
> shows the value of n_gen +1. So when the 2 users both login at the same time,
> with different sessions, they both see "7" in the n_gen field. But when they
> click on the sumbit button only one record is inserted and the other is lost.
>
> I though it was possible to change the SQL string before it does the update..
> But i can't seem to find a solution for it.. Any idea ??

You'll have to explain what you want n_gen to contain.

Then you'll have to decide whether you want to do this in ASP where you can
certainly change the SQL all you like, or on the server where you can have
triggers which change the values being stored or executing additional queries.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!

Shavonne Marietta Wijesinghe wrote:
> Thanks for the reply Grogory. I am trying to do a INSERT INTO.
> 
> Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key
> (Serial not null)

That is sure confusing. What could a DDL saying
  INTEGER n_gen SERIAL NOT NULL;  INTEGER n_sheet SERIAL NOT NULL;  INTEGER tot_n_sheet SERIAL NOT NULL;  PRIMARY KEY
(n_gen,n_sheet, tot_n_sheet)
 

mean? Is this what your DDL says? Do you enter rows of this table specifying
the id and expecting the three serial generators to pick non-null sequential
numbers for the other three fields? I think you are very unclear about what
is going on here. Are you perhaps saying the table has three (distinct)
primary keys? Because if you assign them values, why would they be SERIAL?

>   id     | n_gen     | n_sheet   | tot_n_sheet
> ----------+-----------+-----------+-------------
>     a    |      1    |     1     |      1
>     b    |      2    |     1     |      2
>     x    |      2    |     2     |      2
>     u    |      3    |     1     |      1
>     r    |      4    |     1     |      3
>     a    |      4    |     2     |      3
>     s    |      4    |     3     |      3
> 
> 
> So there are 2 users inserting in to the db. In my ASP page i have a
> field that shows the value of n_gen +1. So when the 2 users both login
> at the same time, with different sessions, they both see "7" in the
> n_gen field. But when they click on the sumbit button only one record is
> inserted and the other is lost.

Whatever you are doing that I do not understand with your keys, if you have
two users doing inserts on the same table, would you not have to run this
with Serializable Isolation Level (12.2.2. in the manual)? Would this not
fix your problem especially if you have a SERIAL as primary key?
> 
> I though it was possible to change the SQL string before it does the
> update.. But i can't seem to find a solution for it.. Any idea ??
> 



--  .~.  Jean-David Beyer          Registered Linux User 85642. /V\  PGP-Key: 9A2FC99A         Registered Machine
241939./()\ Shrewsbury, New Jersey    http://counter.li.org^^-^^ 11:35:01 up 18 days, 1:21, 1 user, load average: 4.22,
4.28,4.27
 

Thanks for the replies.. But my problem still continues even after setting 
the isolation level.

Set oRs = oConn.Execute("SELECT N_GEN FROM MY_TABLE ORDER BY N_GEN::INT 
DESC")

If err <> 0 then 'If table not foundGetFieldValue = "1"WriteToFile logfilepath, date & " " & time & " -- no table
Numero
 
progressivo: 1" & vbcrlf , True
elseBEGINSET TRANSACTION = "ISOLATION LEVEL SERIALIZABLE"if tipo_scheda = "SINGOLA" then GetFieldValue = oRs("N_GEN") +
1elseGetFieldValue = oRs("N_GEN")end if
 
end if
COMMIT

Debugging my code(with logs) i see that still when 2 users login at the same 
time the N_GEN value is the same. (they enter the first if)
In "My_Table" the last record has the value "5" so the next user that logs 
in shoul get the value "6". Sadly both the current users get the value "6".

Have i set the isolation level correctly??

Thanks

Shavonne

----- Original Message ----- 
From: "Jean-David Beyer" <jeandavid8@verizon.net>
To: <pgsql-sql@postgresql.org>
Sent: Monday, February 11, 2008 5:55 PM
Subject: Re: [SQL] Check before INSERT INTO


> Shavonne Marietta Wijesinghe wrote:
>> Thanks for the reply Grogory. I am trying to do a INSERT INTO.
>>
>> Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key
>> (Serial not null)
>
> That is sure confusing. What could a DDL saying
>
>   INTEGER n_gen SERIAL NOT NULL;
>   INTEGER n_sheet SERIAL NOT NULL;
>   INTEGER tot_n_sheet SERIAL NOT NULL;
>   PRIMARY KEY (n_gen, n_sheet, tot_n_sheet)
>
> mean? Is this what your DDL says? Do you enter rows of this table 
> specifying
> the id and expecting the three serial generators to pick non-null 
> sequential
> numbers for the other three fields? I think you are very unclear about 
> what
> is going on here. Are you perhaps saying the table has three (distinct)
> primary keys? Because if you assign them values, why would they be SERIAL?
>
>>   id     | n_gen     | n_sheet   | tot_n_sheet
>> ----------+-----------+-----------+-------------
>>     a    |      1    |     1     |      1
>>     b    |      2    |     1     |      2
>>     x    |      2    |     2     |      2
>>     u    |      3    |     1     |      1
>>     r    |      4    |     1     |      3
>>     a    |      4    |     2     |      3
>>     s    |      4    |     3     |      3
>>
>>
>> So there are 2 users inserting in to the db. In my ASP page i have a
>> field that shows the value of n_gen +1. So when the 2 users both login
>> at the same time, with different sessions, they both see "7" in the
>> n_gen field. But when they click on the sumbit button only one record is
>> inserted and the other is lost.
>
> Whatever you are doing that I do not understand with your keys, if you 
> have
> two users doing inserts on the same table, would you not have to run this
> with Serializable Isolation Level (12.2.2. in the manual)? Would this not
> fix your problem especially if you have a SERIAL as primary key?
>>
>> I though it was possible to change the SQL string before it does the
>> update.. But i can't seem to find a solution for it.. Any idea ??
>>
>
>
>
> -- 
>  .~.  Jean-David Beyer          Registered Linux User 85642.
>  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
> /( )\ Shrewsbury, New Jersey    http://counter.li.org
> ^^-^^ 11:35:01 up 18 days, 1:21, 1 user, load average: 4.22, 4.28, 4.27
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org 

Shavonne Marietta Wijesinghe wrote:
> Thanks for the replies.. But my problem still continues even after 
> setting the isolation level.
> 
> Set oRs = oConn.Execute("SELECT N_GEN FROM MY_TABLE ORDER BY N_GEN::INT 
> DESC")
> 
> If err <> 0 then 'If table not found
> GetFieldValue = "1"
> WriteToFile logfilepath, date & " " & time & " -- no table Numero 
> progressivo: 1" & vbcrlf , True
> else
> BEGIN
> SET TRANSACTION = "ISOLATION LEVEL SERIALIZABLE"
> if tipo_scheda = "SINGOLA" then
>  GetFieldValue = oRs("N_GEN") + 1
> else
>  GetFieldValue = oRs("N_GEN")
> end if
> end if
> COMMIT
> 
> Debugging my code(with logs) i see that still when 2 users login at the 
> same time the N_GEN value is the same. (they enter the first if)

Of course it is.

> In "My_Table" the last record has the value "5" so the next user that 
> logs in shoul get the value "6". Sadly both the current users get the 
> value "6".

Why sadly? What do you think should happen?

> Have i set the isolation level correctly??

I think you are having problems with thinking through the concurrency of 
this problem.

Scenario 1 - will work
==========
User1: Read value 5
User1: new value = 5 + 1
User1: Commit changes
User2: Read value 6
User2: new value = 6 + 1
User2: Commit changes

Scenario 2 - will not work
==========
User1: Read value 5
User1: new value = 5 + 1
User2: Read value 5 (there is no "6" yet, it's not been committed)
User2: new value = 5 + 1
User1: Commit changes
User2: Commit changes - ERROR

There are only two alternatives in scenario #2 - block user 2 from 
reading a value until user1 commits/rolls back or give them a value that 
might be out of date. That's what the isolation level controls.
From your original email you have n_gen defined as a serial. That's 
basically an integer column with a default value from a 
sequence-generator. I'd just let the default value be accepted when you 
want a new number, that guarantees you a different value each time 
(although you can't guarantee you'll get 1,2,3,4,5...)

--   Richard Huxton  Archonet Ltd

Even though n_gen is defined as a serial I can't let it handle the 
progressive key by its self since there is the need that some records should 
have the same value. That's why i use 3 primary keys.
A |  B |  C
---+---+---1  |  1  |  12  |  1  |  32  |  2  |  32  |  3  |  33  |  1  |  23  |  2  |  24  |  1  |  1

The 3 keys A, B, C are defined as Serial and Primay Keys

Anyway the other suggestion, Blocking the second user from reading the db. 
So for the second user I could give A temp key something like 0 and then do 
a select before the submit and change the value.

But how is it possible to know if some other user is reading the db??

Thanks

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
Cc: "Jean-David Beyer" <jeandavid8@verizon.net>; <pgsql-sql@postgresql.org>
Sent: Tuesday, February 12, 2008 11:35 AM
Subject: Re: [SQL] Check before INSERT INTO


> Shavonne Marietta Wijesinghe wrote:
>> Thanks for the replies.. But my problem still continues even after 
>> setting the isolation level.
>>
>> Set oRs = oConn.Execute("SELECT N_GEN FROM MY_TABLE ORDER BY N_GEN::INT 
>> DESC")
>>
>> If err <> 0 then 'If table not found
>> GetFieldValue = "1"
>> WriteToFile logfilepath, date & " " & time & " -- no table Numero 
>> progressivo: 1" & vbcrlf , True
>> else
>> BEGIN
>> SET TRANSACTION = "ISOLATION LEVEL SERIALIZABLE"
>> if tipo_scheda = "SINGOLA" then
>>  GetFieldValue = oRs("N_GEN") + 1
>> else
>>  GetFieldValue = oRs("N_GEN")
>> end if
>> end if
>> COMMIT
>>
>> Debugging my code(with logs) i see that still when 2 users login at the 
>> same time the N_GEN value is the same. (they enter the first if)
>
> Of course it is.
>
>> In "My_Table" the last record has the value "5" so the next user that 
>> logs in shoul get the value "6". Sadly both the current users get the 
>> value "6".
>
> Why sadly? What do you think should happen?
>
>> Have i set the isolation level correctly??
>
> I think you are having problems with thinking through the concurrency of 
> this problem.
>
> Scenario 1 - will work
> ==========
> User1: Read value 5
> User1: new value = 5 + 1
> User1: Commit changes
> User2: Read value 6
> User2: new value = 6 + 1
> User2: Commit changes
>
> Scenario 2 - will not work
> ==========
> User1: Read value 5
> User1: new value = 5 + 1
> User2: Read value 5 (there is no "6" yet, it's not been committed)
> User2: new value = 5 + 1
> User1: Commit changes
> User2: Commit changes - ERROR
>
> There are only two alternatives in scenario #2 - block user 2 from reading 
> a value until user1 commits/rolls back or give them a value that might be 
> out of date. That's what the isolation level controls.
>
> From your original email you have n_gen defined as a serial. That's 
> basically an integer column with a default value from a 
> sequence-generator. I'd just let the default value be accepted when you 
> want a new number, that guarantees you a different value each time 
> (although you can't guarantee you'll get 1,2,3,4,5...)
>
> -- 
>   Richard Huxton
>   Archonet Ltd 

Shavonne Marietta Wijesinghe wrote:
> Even though n_gen is defined as a serial I can't let it handle the 
> progressive key by its self since there is the need that some records 
> should have the same value. 

Of course you can - the default is only provided if you don't provide 
your own.


> That's why i use 3 primary keys.

No, you're not. By definition you can only have one primary key. You are 
using a 3-column primary key.

> A |  B |  C
> ---+---+---
> 1  |  1  |  1
> 2  |  1  |  3
> 2  |  2  |  3
> 2  |  3  |  3
> 3  |  1  |  2
> 3  |  2  |  2
> 4  |  1  |  1
> 
> The 3 keys A, B, C are defined as Serial and Primay Keys

You don't have 3 keys, you have 3 columns and one primary key (A,B,C).

I'm not clear why B and C are serial - I don't see what that gets you.

> Anyway the other suggestion, Blocking the second user from reading the 
> db. So for the second user I could give A temp key something like 0 and 
> then do a select before the submit and change the value.
> 
> But how is it possible to know if some other user is reading the db??

Don't try. If you want to insert some rows with a new value for A do 
something like:

INSERT INTO my_shevi (a,b,c) VALUES (DEFAULT, 1, 1);
or
INSERT INTO my_shevi (a,b,c) VALUES (nextval(<sequence-name-here>, 1, 1);

INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 2);
INSERT INTO my_shevi (a,b,c) VALUES (currval(<sequence-name-here>), 1, 3);
etc.

Relevant parts of the manual (in 8.3 anyway):  9.15. Sequence Manipulation Functions  9.22. System Information
Functions
The second chapter is for pg_get_serial_sequence() which might be useful 
if you need to do this a lot.

Oh - and please try trimming unwanted parts of the message when you 
reply. There was 100 unnecessary lines below here.

--   Richard Huxton  Archonet Ltd

I have a table like this:

usda=# \d nutrient_data           Table "public.nutrient_data"    Column      |         Type          | Modifiers
-----------------+-----------------------+-----------ndb_no          | integer               | not nullnutrient_no
|integer               | not nullnutrient_value  | double precision      | not nulldata_points     | double precision
  | not nullstd_error       | double precision      |src_cd          | integer               | not nullderivation_code
|character varying(5)  |ref_ndb_no      | integer               |add_nutr_mark   | character varying(2)  |num_studies
 | integer               |min             | double precision      |max             | double precision      |df
   | numeric               |low_eb          | double precision      |up_eb           | double precision      |stat_cmt
     | character varying(15) |cc              | character varying(5)  |
 
Indexes:   "nutrient_data_pkey" PRIMARY KEY, btree (ndb_no, nutrient_no)
Foreign-key constraints:   "nutrient_data_derivation_code_fkey" FOREIGN KEY (derivation_code) 
REFERENCES derivation_code(derivation_code) ON UPDATE CASCADE ON DELETE 
CASCADE   "nutrient_data_ndb_no_fkey" FOREIGN KEY (ndb_no) REFERENCES 
food_description(ndb_no) ON UPDATE CASCADE ON DELETE CASCADE   "nutrient_data_nutrient_no_fkey" FOREIGN KEY
(nutrient_no)
 
REFERENCES nutrient_definitions(nutrient_no) ON UPDATE CASCADE ON DELETE 
CASCADE   "nutrient_data_src_cd_fkey" FOREIGN KEY (src_cd) REFERENCES 
source_code(src_cd) ON UPDATE CASCADE ON DELETE CASCADE



when I run this query:select ndb_no, nutrient_no, nutrient_value from nutrient_data where 
ndb_no = 13473;

it produces:
ndb_no | nutrient_no | nutrient_value
--------+-------------+---------------- 13473 |         203 |          24.18 13473 |         204 |          15.93 13473
|        205 |              0 13473 |         207 |            1.1 13473 |         208 |            247 13473 |
221|              0 13473 |         255 |          57.78 13473 |         262 |              0 13473 |         263 |
        0 13473 |         268 |           1033 13473 |         269 |              0 13473 |         291 |
013473 |         301 |              5 13473 |         303 |           3.35 13473 |         304 |             24 13473 |
       305 |            199 13473 |         306 |            302 13473 |         307 |             67 13473 |
309|           4.67 13473 |         312 |          0.131 13473 |         315 |          0.015 13473 |         317 |
     10.9 13473 |         318 |              0 13473 |         319 |              0 13473 |         320 |
013473 |         321 |              0 13473 |         322 |              0 13473 |         323 |           0.18 13473 |
       334 |              0 13473 |         337 |              0 13473 |         338 |              0 13473 |
401|              0 13473 |         404 |          0.101
 


I want only certain nutrient_no (say 8 of them) and the nutrient values 
by ndb_no.

how would I write that query.  BIG THANKS in advance as I'm lost on this 
one.

J

PostgreSQL Admin wrote:
> I have a table ...
>
> when I run this query:
> select ndb_no, nutrient_no, nutrient_value from nutrient_data where 
> ndb_no = 13473;
>
> it produces:
> ndb_no | nutrient_no | nutrient_value
> --------+-------------+----------------
>  13473 |         203 |          24.18
> ...
>
>
> I want only certain nutrient_no (say 8 of them) and the nutrient 
> values by ndb_no.
Not entirely sure I understand the question. Do you mean that for a 
given nutrient_no, you want the complete list of nutrient values? If so, 
it's just:

--Example for nutrient_no 203:
SELECT ndb_no, nutrient_value from nutrient_data where nutrient_no=203;

Cheers,
Steve

Steve Crawford wrote:
> PostgreSQL Admin wrote:
>> I have a table ...
>>
>> when I run this query:
>> select ndb_no, nutrient_no, nutrient_value from nutrient_data where 
>> ndb_no = 13473;
>>
>> it produces:
>> ndb_no | nutrient_no | nutrient_value
>> --------+-------------+----------------
>>  13473 |         203 |          24.18
>> ...
>>
>>
>> I want only certain nutrient_no (say 8 of them) and the nutrient 
>> values by ndb_no.
> Not entirely sure I understand the question. Do you mean that for a 
> given nutrient_no, you want the complete list of nutrient values? If 
> so, it's just:
>
> --Example for nutrient_no 203:
> SELECT ndb_no, nutrient_value from nutrient_data where nutrient_no=203;
>
> Cheers,
> Steve
>
>
I would like to have multiple values nutrient_no:
ndb_no | nutrient_no | nutrient_value
--------+-------------+----------------13473 |         203 |          24.1813473 |         204 |          15.9313473 |
      205 |              013473 |         207 |            1.113473 |         208 |            24713473 |         221 |
            0
 

I'm thinking:
select nutrient_no, nutrient_value from nutrient_data where ndb_no = 
13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 208);


Now is that the most efficient SQL query?

Thanks,
J

On Wed, 2008-06-11 at 14:41 -0400, PostgreSQL Admin wrote:
> I would like to have multiple values nutrient_no:
> ndb_no | nutrient_no | nutrient_value
> --------+-------------+----------------
>  13473 |         203 |          24.18
>  13473 |         204 |          15.93
>  13473 |         205 |              0
>  13473 |         207 |            1.1
>  13473 |         208 |            247
>  13473 |         221 |              0
> 
> I'm thinking:
> select nutrient_no, nutrient_value from nutrient_data where ndb_no = 
> 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no =
> 208);
> 
> 
> Now is that the most efficient SQL query?
> 
> Thanks,
> J

It seems that you'd want to do something like:

select nutrient_no, nutrient_value from nutrient_data where ndb_no =
13473 and nutrient_no in (203, 204, 208..)

You could also grab the most significant 8 nutrients by doing something
like:

select nutrient_no, nutrient_value from nutrient_data where ndb_no =
13473 order by nutrient_value desc limit 8

-Mark

Mark Roberts wrote:
> On Wed, 2008-06-11 at 14:41 -0400, PostgreSQL Admin wrote:
>   
>> I would like to have multiple values nutrient_no:
>> ndb_no | nutrient_no | nutrient_value
>> --------+-------------+----------------
>>  13473 |         203 |          24.18
>>  13473 |         204 |          15.93
>>  13473 |         205 |              0
>>  13473 |         207 |            1.1
>>  13473 |         208 |            247
>>  13473 |         221 |              0
>>
>> I'm thinking:
>> select nutrient_no, nutrient_value from nutrient_data where ndb_no = 
>> 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no =
>> 208);
>>
>>
>> Now is that the most efficient SQL query?
>>
>> Thanks,
>> J
>>     
>
> It seems that you'd want to do something like:
>
> select nutrient_no, nutrient_value from nutrient_data where ndb_no =
> 13473 and nutrient_no in (203, 204, 208..)
>
> You could also grab the most significant 8 nutrients by doing something
> like:
>
> select nutrient_no, nutrient_value from nutrient_data where ndb_no =
> 13473 order by nutrient_value desc limit 8
>
> -Mark
>
>
>   
Thanks Mark!

> I would like to have multiple values nutrient_no:
> ndb_no | nutrient_no | nutrient_value
> --------+-------------+----------------
> 13473 |         203 |          24.18
> 13473 |         204 |          15.93
> 13473 |         205 |              0
> 13473 |         207 |            1.1
> 13473 |         208 |            247
> 13473 |         221 |              0
>
> I'm thinking:
> select nutrient_no, nutrient_value from nutrient_data where ndb_no = 
> 13473 and (nutrient_no = '203' or nutrient_no = '204' or nutrient_no = 
> 208);
>
Depending on what you are trying to achieve:

Particular ndb_no and multiple nutrient_no, note that output of ndb_no 
is superfluous as it is always the same:
select nutrient_no, nutrient_value from nutrient_data where ndb_no = 
13473 and nutrient_no in ('203', '204','208');

Size limited list (say top 5 nutrient values) for a given ndb_no:
select nutrient_no,nutrient_value from nutrient_data where ndb_no = 
13473 order by nutrient_value limit 5;

Cheers,
Steve

this is a small sample of the data:
                       short_desc                        
|                               long_desc                               

----------------------------------------------------------+------------------------------------------------------------------------CHICKEN,BROILERS
ORFRYERS,LEG,MEAT&SKN,CKD,FRIED,BATTER | Chicken, 
 
broilers or fryers, leg, meat and skin, cooked, fried, batter

Is the best method of search through this data full text search via 
tsearch or some other method.  I'm running version 8.3

say I want to search for chicken skin?

Thanks for the advice,
J