Re: [SQL] SELECT very slow
On 09.06.2005 03:13 Alain wrote: > > > Tom Lane escreveu: > >> Thomas Kellerer <[EMAIL PROTECTED]> writes: >> >>> Is there anything I can do, to convince PG to return the first row >>> more quickly? > > > Are you now looking for the LIMIT ? > > SELECT * FROM table LIMIT 1; > > and when when you wnat the rest of it: > > SELECT * FROM table OFFSET 1; > No I want the whole result. Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] rejecting characters in a field
add check constraint with following pattern check(name !~ '[*/^#]') with regards, S.Gnanavel > -Original Message- > From: [EMAIL PROTECTED] > Sent: Thu, 9 Jun 2005 12:36:31 +0530 > To: pgsql-sql@postgresql.org > Subject: [SQL] rejecting characters in a field > > hi > i have a table with a varchar field called 'name'. I want postgres to > reject any insert with characters defined as illegal characters, for > example '*/^#'. How do i do this? > -- > regards > kg > > http://www.livejournal.com/users/lawgon > tally ho! http://avsap.sourceforge.net > ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] rejecting characters in a field
hi i have a table with a varchar field called 'name'. I want postgres to reject any insert with characters defined as illegal characters, for example '*/^#'. How do i do this? -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [despammed] [SQL] rejecting characters in a field
On Jun 9, 2005, at 4:18 PM, Andreas Kretschmer wrote: am 09.06.2005, um 12:36:31 +0530 mailte Kenneth Gonsalves folgendes: hi i have a table with a varchar field called 'name'. I want postgres to reject any insert with characters defined as illegal characters, for example '*/^#'. How do i do this? With a RULE or a TRIGGER. Or a CHECK constraint on the column. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [despammed] [SQL] rejecting characters in a field
am 09.06.2005, um 12:36:31 +0530 mailte Kenneth Gonsalves folgendes: > hi > i have a table with a varchar field called 'name'. I want postgres to > reject any insert with characters defined as illegal characters, for > example '*/^#'. How do i do this? With a RULE or a TRIGGER. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Indices and user defined operators
Tom Lane wrote: I wrote: "Dmitri Bichko" <[EMAIL PROTECTED]> writes: So, is there any way to make these operators use an index defined as above? If you've set things up so that the operators are defined by inline-able SQL functions, I'd sort of expect it to fall out for free ... Now *that* is very nice. Thanks for the example Tom - nice to know the sql list is still teaching me things within the first 5 messages I read. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT very slow
Hi, On 6/9/05, Thomas Kellerer <[EMAIL PROTECTED]> wrote: > No I want the whole result. As Tom underlined: On 6/9/05, Tom Lane <[EMAIL PROTECTED]> wrote: > The solution is to use a cursor and FETCH a reasonably > small number of rows at a time. AFAIC, query results are stored as arrays in PGresult structures. Thus, storing huge result sets in a single struct is not very feasible; although, you can face with theoretical limits like MAX_INT in the long run. Moreover, it's so rare to see any practical use of retrieving thousands of rows. If you're getting quite huge sets of data, you should try grouping them with suitable statements. IMHO, you should use cursors to fetch a suitable amount of row from related table and forward it recursively. (Furthermore, I think this is one of the design goals of FETCH mechanism.) Although, as I see from most API implementations (like C++, Perl, PHP, Python and etc.), they use libpq as layer between API and server. Therefore, you'll probably encounter with every limitation of libpq with other programming languages out of C too. Regards. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SELECT very slow
On 09.06.2005 02:06 Tom Lane wrote: > Thomas Kellerer <[EMAIL PROTECTED]> writes: > >>Is there anything I can do, to convince PG to return the first row more >>quickly? > > > libpq's API for PQresult is such that it really doesn't have any choice > but to collect the full result set before it hands you back the > PQresult. I don't know JDBC very well but I think it has similar > semantic constraints. > > The solution is to use a cursor and FETCH a reasonably small number of > rows at a time. > My understanding was that setting the fetchSize() to 1 in JDBC would force the use of a cursor. I'll have to go through the JDBC docs again to check how I can enforce this. Thanks for the answer though Thomas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [despammed] rejecting characters in a field
On Thu, Jun 09, 2005 at 09:18:09 +0200, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > am 09.06.2005, um 12:36:31 +0530 mailte Kenneth Gonsalves folgendes: > > hi > > i have a table with a varchar field called 'name'. I want postgres to > > reject any insert with characters defined as illegal characters, for > > example '*/^#'. How do i do this? > > With a RULE or a TRIGGER. Using a check constraint would be better a better way to do this. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [despammed] rejecting characters in a field
am 09.06.2005, um 3:29:15 -0500 mailte Bruno Wolff III folgendes: > On Thu, Jun 09, 2005 at 09:18:09 +0200, > Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > > am 09.06.2005, um 12:36:31 +0530 mailte Kenneth Gonsalves folgendes: > > > hi > > > i have a table with a varchar field called 'name'. I want postgres to > > > reject any insert with characters defined as illegal characters, for > > > example '*/^#'. How do i do this? > > > > With a RULE or a TRIGGER. > > Using a check constraint would be better a better way to do this. Yes, okay. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SELECT very slow
On 09.06.2005 02:06 Tom Lane wrote: > Thomas Kellerer <[EMAIL PROTECTED]> writes: > >>Is there anything I can do, to convince PG to return the first row more >>quickly? > > The solution is to use a cursor and FETCH a reasonably small number of > rows at a time. Thanks for all your answers. I turned out that I did not read the JDBC documentation closely enough (blush) Setting the fetchSize to 1 (or something >0) only uses a cursor if autocommit is disabled (due to the annoying "transaction is aborted" when running DDL scripts that have errors, I usually turn autocommit on). With autocommit off, the rows will be returned "immediately" (so the driver is using a cursor to fetch the data) Regards Thomas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
