Thread: select vs varchar
How do I do selects against varchars? A "select * from <table> where <field>=<value>" doesn't seem to work. The same select against a char field works.
hey.
i'm using dbi in perl to access a table whos primary key is a serialized
variable. i want to preform an insert on the table and i want the value of
the key associated with that insert.
this is what i'm doing:
# i get the new value like this:
select nextval('testtable_the_key_seq');
#and then i preforme the insert like this:
insert into testtable (an_id, timestamp) values (2, 2);
what results.
say the last value of the sequence is 'n-1' so the select nextval
statement above will return 'n' but when i preform the insert the value of
testtable.the_key is actually 'n+1'. should i wrap the insert up into a
transaction? any help would be most appreciated.
i dont know if this is relevent but in perl i'm preparing the statement by
using (?,?) and place holders for the (2,2).
thanks
john
On Sun, 5 Aug 2001, Allan Engelhardt wrote:
> Francisco Reyes wrote:
>
> > How do I do selects against varchars?
> > A "select * from <table> where <field>=<value>" doesn't seem to work.
> >
> > The same select against a char field works.
>
> What problem are you experiencing?
> test=# create table test (text varchar);
> CREATE
> test=# insert into test values ('foo');
> INSERT 12734264 1
> test=# insert into test values ('bar');
> INSERT 12734265 1
> test=# select * from test where text = 'foo';
> text
> ------
> foo
> (1 row)
>
User error. :-(
The value I was using on the where clause was different from the value on
the field. After your example I took another look and found my typing
error.
thanks.
On Monday 06 August 2001 06:55, you wrote:
> this is what i'm doing:
> # i get the new value like this:
> select nextval('testtable_the_key_seq');
you are not assigning it to any variable?
> #and then i preforme the insert like this:
> insert into testtable (an_id, timestamp) values (2, 2);
I assume that an_id is not your primary key?
> say the last value of the sequence is 'n-1' so the select nextval
> statement above will return 'n' but when i preform the insert the value of
> testtable.the_key is actually 'n+1'. should i wrap the insert up into a
> transaction? any help would be most appreciated.
>
> i dont know if this is relevent but in perl i'm preparing the statement by
> using (?,?) and place holders for the (2,2).
Why do you call nextval? If your key attribute is "serial", postgres calls
nextval for you autiomatically. Thus, you call it once and postgres calls it
once, hence an increment of 2
Horst
Sometime in August Horst Herb assaulted keyboard and produced...
|On Monday 06 August 2001 06:55, you wrote:
|
|> this is what i'm doing:
|> # i get the new value like this:
|> select nextval('testtable_the_key_seq');
|
|you are not assigning it to any variable?
|
|> #and then i preforme the insert like this:
|> insert into testtable (an_id, timestamp) values (2, 2);
|
|I assume that an_id is not your primary key?
|
|> say the last value of the sequence is 'n-1' so the select nextval
|> statement above will return 'n' but when i preform the insert the value of
|> testtable.the_key is actually 'n+1'. should i wrap the insert up into a
|> transaction? any help would be most appreciated.
|>
|> i dont know if this is relevent but in perl i'm preparing the statement by
|> using (?,?) and place holders for the (2,2).
|
|Why do you call nextval? If your key attribute is "serial", postgres calls
|nextval for you autiomatically. Thus, you call it once and postgres calls it
|once, hence an increment of 2
|
|Horst
this is what i want to do:
i want to insert data into a table that has a serialized key. then i want
the value of the key for the row i just inserted. from the stuff i read
on the net i though i was supposed to run the nextval command first then
preform the insert. referring specifically to the url below:
http://www.mail-archive.com/dbi-users@isc.org/msg15723.html
i was under the impression that nextval would give what the nextval would
be. i didnt think it was suppose to increment it. after reading your
response and someone else's it seems i need to get the next value of the
key and insert it explicitly is that correct?
for what it's worth the sql to create the table:
CREATE TABLE testtable(
the_key serial,
an_id int4,
timestamp int4,
PRIMARY KEY(the_key));
thanks
john
On Monday 06 August 2001 08:47, harrold@sage.che.pitt.edu wrote: > i was under the impression that nextval would give what the nextval would > be. i didnt think it was suppose to increment it. after reading your > response and someone else's it seems i need to get the next value of the > key and insert it explicitly is that correct? Yes. Alternatively, you can let Postgres use nextval implicitly, and call currval afterwards to get the value of the inserted "nextval" Horst
harrold@sage.che.pitt.edu writes:
> i want to insert data into a table that has a serialized key. then i want
> the value of the key for the row i just inserted. from the stuff i read
> on the net i though i was supposed to run the nextval command first then
> preform the insert.
That is about the simplest and most obvious way to do it, IMHO. What
you're missing is that you have to explicitly insert the value you got
from nextval() into the key column. If you allow the key column to be
defaulted in the INSERT, then nextval() gets run again.
regards, tom lane
john There is no operating system but linux and linus is its kernel maintainer. Sometime in August Horst Herb assaulted keyboard and produced... |On Monday 06 August 2001 08:47, harrold@sage.che.pitt.edu wrote: | |> i was under the impression that nextval would give what the nextval would |> be. i didnt think it was suppose to increment it. after reading your |> response and someone else's it seems i need to get the next value of the |> key and insert it explicitly is that correct? | |Yes. Alternatively, you can let Postgres use nextval implicitly, and call |currval afterwards to get the value of the inserted "nextval" | thanks. one more question. what can i do to prevent another insert from taking place before the currval request is made? is this even an issue aslong as the currval query is executed using the same connection? john
hey. that's just dandy-it works fine. thanks everyone for helping me sort out my misunderstanings. -- john There is no operating system but linux and linus is its kernel maintainer. Sometime in August Tom Lane assaulted keyboard and produced... |harrold@sage.che.pitt.edu writes: |> i want to insert data into a table that has a serialized key. then i want |> the value of the key for the row i just inserted. from the stuff i read |> on the net i though i was supposed to run the nextval command first then |> preform the insert. | |That is about the simplest and most obvious way to do it, IMHO. What |you're missing is that you have to explicitly insert the value you got |from nextval() into the key column. If you allow the key column to be |defaulted in the INSERT, then nextval() gets run again. | | regards, tom lane |