[SQL] Last serial number inserted
How can I get the last id ( serial number ) inserted. My C code is :
res = PQexec(conn,"insert into table1 (field1) values ('value1')");
table1:
id SERIAL,
field1 VARCHAR(10)
Thanks
Eduardo K
Re: [SQL] Last serial number inserted
Eduardo, Use the curval(serial) function. For more information, look in the online docs under the name of that function. nextval(), curval() are wonderful things! -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Variable-length Types
> It takes a string and returns something that looks like Varchar(22); > If someone types in 10 digits, it returns output like (###) ###- > If a user enters 7 digits, it picks a default area code and returns > (415) ###-, > and if they type more than 10 digits or enter any digits after an "x", > they get (###) ###- x### Why not treat each group of ###s as its own variable? You would then have a lot of freedom to insert defaults if null and the like. -C. Currie
[SQL] nested transactions
Hi, Just out of curiousity, does Postgres support nested transactions? - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
[SQL] Big Sub-select statement
Hi,
Let's say if I have a log table with (log_id, emp_id, book_id,
author_id), and there are foreign tables related to each id except the
log_id. Is it possible to write up a big sub-select statement to
retrieve all possible info?
eg1; (big sub_select)
select ltb.log_id, ltb.emp_id, ltb.book_id, ltb.author_id
from log_tb ltb
where ltb.emp_id in
(select etb.name
etb.phone
etb.email
from emp_tb etb
where etb.emp_id=ltb.emp_id)
and ltb.book_id in
(select btb.name
...
)
and ... [same for author_id];
So, is it possible and efficient to retrieve all info in one big
statement? or is there other better ways to do it?
Right now, here's what I do,
select log_id, emp_id, ...
from log_tb;
while(not end of rows)
{
select name, phone, email from emp_tb where emp_id=log_tb.emp_id;
... [same for book_tb and author_tb]
}
I am thinking if it's in one big statement, then I don't need to look
around in the script to change the select statements. Thank you. =)
- Bernie
begin:vcard
n:Huang;Bernie
tel;fax:(604)664-9195
tel;work:(604)664-9172
x-mozilla-html:TRUE
org:Environment Canada;Standards and Technology Services
adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:Programmer
x-mozilla-cpt:;0
fn:Bernie Huang
end:vcard
[SQL] problem with select where like ']'
hi, i have a table (view actually) which contains field "article_name" which is written like: [AGD]|[Kuchenki Mikrofalowe]|[Samsung AKMS1] i.e. some string within "[]" delimited by "|" i want to select all record that have "[AGD]|[" in front of them so i tried select * from my_view where article_name like '[AGD]|['; this doesn't work. no errors, but no tuples returned. i checked that the problem is character ']'. '[' works o.k. '|' - i guess works too. but inserting "]" makes the whole expression doesn't work i tried escaping of it like \], \\] or even \\\] but none of these worked. right now i'm using comparison: where substring (...) = '...' and it works even with those "]" signs. but i'm just wondering why like ']' doesn't work. any ideas? depesz p.s. sorry for my english. -- hubert depesz lubaczewski <=--=> adres www usuniêty na pro¶bê Asi ¦l. najwspanialsz± rzecz± jak± da³o nam nowoczesne spo³eczeñstwo, jest niesamowita wrêcz ³atwo¶æ unikania kontaktów z nim ...
Re: [SQL] problem with select where like ']'
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > but i'm just wondering why like ']' doesn't work. What LOCALE setting are you running the postmaster in? ']' is not a special character as far as LIKE is concerned, but I suspect you may be seeing another variant of the problems that LIKE index optimization has with peculiar collation rules. You can find plenty of discussion of this in the mailing list archives :-( If there is an index on the field you are doing LIKE on, try dropping the index to see if that makes the problem go away. Another possibility is that you have been careless about always starting the postmaster with the same LOCALE setting, in which case the index may actually be corrupt (out of order) due to different records having been inserted with different ideas about what the sort ordering should be. In that case, dropping and recreating the index should help. regards, tom lane
Re: [SQL] problem with select where like ']'
> hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > > but i'm just wondering why like ']' doesn't work. > > What LOCALE setting are you running the postmaster in? > > ']' is not a special character as far as LIKE is concerned, but > I suspect you may be seeing another variant of the problems that > LIKE index optimization has with peculiar collation rules. OTOH, it seems like it shouldn't return any rows, as the original statement has no metacharachters, % or _ -- Well I tried to be meek And I have tried to be mild But I spat like a woman And I sulked like a child I have lived behind the walls That have made me alone Striven for peace Which I never have known Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)
Re: [SQL] nested transactions
Net yet. [ Charset ISO-8859-15 unsupported, converting... ] > Hi, > > Just out of curiousity, does Postgres support nested transactions? > > > - Bernie Content-Description: Card for Bernie Huang [ Attachment, skipping... ] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] nested transactions
Bernie Huang writes: > Just out of curiousity, does Postgres support nested transactions? I'd like to know too, and not just out of curiousity. I have a use for that. -- John Hasler [EMAIL PROTECTED] (John Hasler) Dancing Horse Hill Elmwood, WI
Re: [SQL] problem with select where like ']'
Tom Lane wrote: > > hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > > but i'm just wondering why like ']' doesn't work. > > What LOCALE setting are you running the postmaster in? > > ']' is not a special character as far as LIKE is concerned, but > I suspect you may be seeing another variant of the problems that > LIKE index optimization has with peculiar collation rules. > You can find plenty of discussion of this in the mailing list archives > :-( > WHAT mailing list archives? They aren't linked to anywhere on www.postgresql.org that I can find. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] problem with select where like ']'
Joseph Shraibman <[EMAIL PROTECTED]> writes: > WHAT mailing list archives? > They aren't linked to anywhere on www.postgresql.org that I can find. Hmm. My bookmark is http://www.postgresql.org/lists/mailing-list.html Dunno how to get there from the site toplevel... regards, tom lane
Re: [SQL] problem with select where like ']'
The fact is, I can't figure out how to get there without the URL. > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > WHAT mailing list archives? > > They aren't linked to anywhere on www.postgresql.org that I can find. > > Hmm. My bookmark is > > http://www.postgresql.org/lists/mailing-list.html > > Dunno how to get there from the site toplevel... > > regards, tom lane > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] problem with select where like ']'
The only way I can find is to do a search on something, and select to search in mailing lists. Then after the search returns click on a link, and trucate the url to http://www.postgresql.org/mhonarc/ Bruce Momjian wrote: > > The fact is, I can't figure out how to get there without the URL. > > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > > WHAT mailing list archives? > > > They aren't linked to anywhere on www.postgresql.org that I can find. > > > > Hmm. My bookmark is > > > > http://www.postgresql.org/lists/mailing-list.html > > > > Dunno how to get there from the site toplevel... > > > > regards, tom lane > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [SQL] problem with select where like ']'
> > http://www.postgresql.org/users-lounge/index.html has most of them. > Actually it's under General Info from the user's lounge. > > It was brought to my attention today that the list of archives was > incomplete so I'll be adding to it and adding another one (developer > and user). Don't get too attached to the url below, it will be going > away very soon. Oh, I see it now. Thanks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] problem with select where like ']'
http://www.postgresql.org/users-lounge/index.html has most of them. Actually it's under General Info from the user's lounge. It was brought to my attention today that the list of archives was incomplete so I'll be adding to it and adding another one (developer and user). Don't get too attached to the url below, it will be going away very soon. Vince. On Tue, 17 Oct 2000, Bruce Momjian wrote: > The fact is, I can't figure out how to get there without the URL. > > > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > > WHAT mailing list archives? > > > They aren't linked to anywhere on www.postgresql.org that I can find. > > > > Hmm. My bookmark is > > > > http://www.postgresql.org/lists/mailing-list.html > > > > Dunno how to get there from the site toplevel... > > > > regards, tom lane > > > > > -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
RE: [SQL] nested transactions
> > Just out of curiousity, does Postgres support nested transactions? > > I'd like to know too, and not just out of curiousity. I have > a use for that. Hopefully, savepoints will be available in 7.2 and give required functionality. Vadim
Re: [SQL] JDBC Performance
On Mon, 16 Oct 2000, Josh Berkus wrote: > Mr. May, > > For discussions of JDBC, please subscribe to the pgsql-interfaces > list. You will find many JDBC users on that list. > > -Josh Berkus > > P.S. PGSQL folks, is there any way we can clarify this on the web page? > The JDBC users seem to keep ending up on this list. I think it needs to be mentioned somewhere on the postgresql site. I've had this on my site for the last 3 years ;-) Also, I only get the sql & general lists here at home (retep.org.uk), but get interfaces at work as well so I can sometimes answer it faster if it's posted to interfaces. Peter -- Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/ Java PDF Generator http://www.retep.org.uk/pdf/
