Re: [SQL] Quota query with decent performance?
On Tue, 11 Nov 2003 18:49:31 -0500, Chester Kustarz wrote: [... cut efficient top-3 youngest query wanted ...] > select * > from person > where age <= > (select age from person order by age limit 1 offset 2); Thanks - it works; why didn't I think of that? -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Quota query with decent performance?
On Tue, 11 Nov 2003 18:49:31 -0500, Chester Kustarz wrote: > select * > from person > where age <= > (select age from person order by age limit 1 offset 2); Integrated into http://troels.arvin.dk/db/rdbms/#select-top-n -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] search facilities
Bartunov, Thanks alot for you reply, please clarify for me something; I have a database driven website developed using php and postgresql database, can I use tsearch facility and customize it to search my database driven website like how postgresql.org is search please do advice thanks again Kind regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ From: Oleg Bartunov <[EMAIL PROTECTED]> To: Martin Kuria <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [SQL] search facilities Date: Sat, 8 Nov 2003 10:49:41 +0300 (MSK) Check contrib/tsearch2 and http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2 for documentation Oleg On Fri, 7 Nov 2003, Martin Kuria wrote: > Hi, > I have a content management system, for my database driven website developed > using php and postgresql but I don't know how to develop a search facility > for it. > > How do I go about it?. > > I have seen database driven website developed using php and postgresql and > they have their built search facilities where can I learn to develop my > customized search facility please do advice. > > Kind Regards > > +-+ > | Martin W. Kuria (Mr.) [EMAIL PROTECTED] > ++ > > _ > Tired of spam? Get advanced junk mail protection with MSN 8. > http://join.msn.com/?page=features/junkmail > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL substring
... WHERE field1 LIKE '%' || field2 || '%' or ... WHERE position(field2 in field1)>0 On Wed, 2003-11-12 at 11:07, Guy Korland wrote: Hi, How can I compare two fields if one is a substring of the other? Something like: ... WHERE StringA like '%' + StringB + '%'; Thanks, Guy Korland ---(end of broadcast)--- TIP 8: explain analyze is your friend signature.asc Description: This is a digitally signed message part
Re: [SQL] SQL substring
On Wed, Nov 12, 2003 at 11:38:31 -0300, Franco Bruno Borghesi <[EMAIL PROTECTED]> wrote: > ... WHERE field1 LIKE '%' || field2 || '%' The first way won't work correctly if field2 has %s in it. > or > ... WHERE position(field2 in field1)>0 ---(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
[SQL] SQL substring
Hi, How can I compare two fields if one is a substring of the other? Something like: ... WHERE StringA like '%' + StringB + '%'; Thanks, Guy Korland ---(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
Re: [SQL] How to know column constraints via system catalog tables
Thanks alot, you are right with the table and the -E option. It's very useful.
The only issue I find with your SQL is it relies on "(a.attnum = x.conkey[1] or
a.attnum = x.conkey[2])" which assumes there is two columns forming the primary key of
a table. Perhaps, I should explain what I'm trying to achieve with this SQL.
I'm writing an abstraction layer (abit like persistent but less ambitious). I hope to
make this C++ layer generic for any RDBMS as long as the RDMS allow discovery of a
table's columns name and the columns' data type. The RDBMS should also expose the
field(s) used to form the primary key of a table. This will free the programmer from
coding the class data member to correspond to the underlying table's fields (automate
those tedious tasks of mapping OO classes to database tables).
I'm using libpqxx for postgreSQL, I had thought of a hack which is to strip the
{1,2..} string returned by the conkey of pg_constraint to get the column numbers. It's
not pretty as I have to execute at least two queries but it should be workable.
Thanks.
---snip---
First, do you know the psql -E option which shows you the SQL behind the \d outputs.
You have probably used this films table (there is more than one in the doc):
CREATE TABLE films (
code CHAR(5),
title VARCHAR(40),
did DECIMAL(3),
date_prod DATE,
kind VARCHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
Based on a query I'm using to retrieve column information
(improved by Tom Lane's help), I think I've found something useful. Try
SELECT
upper(u.usename) AS TBL_OWNER, upper(c.relname) AS TBL_NAME,
upper(a.attname) AS COL_NAME, a.atttypid AS COL_TYPE,
int4larger(a.attlen, a.atttypmod - 4) AS COL_LENGTH,
CASE WHEN a.attnotnull=true THEN 0 ELSE 1 END AS COL_NULL, a.attnum AS COL_SEQ,
CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d
WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN
1
ELSE
0
END AS COL_DEFAULT
from pg_attribute a, pg_constraint x,
pg_class c left join pg_user u on (u.usesysid = c.relowner)
where c.oid = a.attrelid and not (c.relname ~* 'pg_') and
c.relkind = 'r' and a.attnum > 0 and
c.relfilenode=x.conrelid and x.contype='p' and c.relname ='films' and
(a.attnum = x.conkey[1] or a.attnum = x.conkey[2]) ;
tbl_owner | tbl_name | col_name | col_type | col_length | col_null | col_seq |
col_default
---+--+--+--++--+-+-
CH | FILMS | CODE | 1042 | 5 | 0 | 1 | 0
CH | FILMS | TITLE | 1043 | 40 | 0 | 2 | 0
(2 rows)
You'll probably want to get rid of some parts (e.g. the upper case conversion),
but basically it's what you were looking for. Right?
___
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] transaction processing after error in statement
Holger Jakobs wrote: Calling a procedure is a statement as well, and it includes all other procedures called from this procedure. So the statement level is always the statements that were carried out directly in the transaction. If anything within one statement fails, the statement was not carried out and must not have any effect. It is not important whether the procedure was fired by a trigger or called by another procedure. So you define the smalles unit being one single statement as issued by the client application and receiving one single returncode over the frontend/backend protocol. That's almost what people think of as subtransactions. I think if we ever implement them, we will have some session setting that lets the backend behave like that and your needs will be satisfied. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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
[SQL] Looks are important
Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad? Thanks in advance, George
Re: [SQL] Looks are important
George, > SELECT RPAD(no,30,' ') || tableb.kind FROM tablea > WHERE tablea.kind = tableb.kind Try SELECT RPAD(no, (35 - LENGTH(tableb.kind)), ' ') -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Looks are important
Title: Message Try the TRIM function or the LTRIM function: SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea WHERE tablea.kind = tableb.kind Louise -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George WeaverSent: Wednesday, November 12, 2003 3:12 PMTo: [EMAIL PROTECTED]Subject: [SQL] Looks are important Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad? Thanks in advance, George
Re: [SQL] Looks are important
Title: Message Hi Louise, Josh, Thanks for the suggestions. What I'm trying to accomplish is to have a space between no and kind. Length(no) can vary. I would like all the kinds to line up evenly when displayed, with a space between no and kind. But when I RPAD no (to try and get an even starting point for kind), the ' 's are not quite the same width as an ordinary number or letter. Thus the physical display length of "30 characters" (padded) can vary from row to row. The result is that the kinds don't necessary line up neatly. I need to concatenate the two as they are being displayed as one column in a drop down combobox. Is what I'm trying to do possible??? George - Original Message - From: Louise Cofield To: 'George Weaver' ; [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 5:19 PM Subject: RE: [SQL] Looks are important Try the TRIM function or the LTRIM function: SELECT RPAD(no,30,' ') || TRIM(tableb.kind) FROM tablea WHERE tablea.kind = tableb.kind Louise -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George WeaverSent: Wednesday, November 12, 2003 3:12 PMTo: [EMAIL PROTECTED]Subject: [SQL] Looks are important Hi Everyone, I am trying to concatenate two fields through a query: SELECT RPAD(no,30,' ') || tableb.kind FROM tablea WHERE tablea.kind = tableb.kind The result gives (for example): 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue What I would like to have is better alignment: 4595448 Green5966 Yellow106-60033 Green15-94-387 Red217-991173 Blue Is there some kind of encoding or other string options that will result in better alignment than what I've tried with Rpad? Thanks in advance, George
Re: [SQL] Looks are important
"George Weaver" <[EMAIL PROTECTED]> writes: > ... the ' 's are not quite the same width as= > an ordinary number or letter. Thus the physical display length of "30 cha= > racters" (padded) can vary from row to row. The result is that the kinds do= > n't necessary line up neatly. I need to concatenate the two as they are be= > ing displayed as one column in a drop down combobox. Use a fixed-width font. > Is what I'm trying to do possible??? Not with a variable-width font that you haven't even told us the exact character widths of ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
