Re: 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

"Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)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(at)enterprisedb(dot)com>
To: "Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)it>
Cc: <pgsql-sql(at)postgresql(dot)org>
Sent: Monday, February 11, 2008 5:03 PM
Subject: Re: Check before INSERT INTO

> "Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)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 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

"Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)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!

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)
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(at)verizon(dot)net>
To: <pgsql-sql(at)postgresql(dot)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 | 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

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(at)archonet(dot)com>
To: "Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)it>
Cc: "Jean-David Beyer" <jeandavid8(at)verizon(dot)net>; <pgsql-sql(at)postgresql(dot)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 null
nutrient_no | integer | not null
nutrient_value | double precision | not null
data_points | double precision | not null
std_error | double precision |
src_cd | integer | not null
derivation_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 | 0
13473 | 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 | 0
13473 | 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.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

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 OR FRYERS,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