Re: [SQL] Slow performance on MAX(primary_key)

Ludwig Lim wrote: >>I have just been comparing some large table >>performance under 7.1 using the >> >> select max(primary key)from table; >> > > Try using the following as alternative : > > SELECT primary_key > FROM table > ORDER BY primary_key desc > LIMIT 1; > > This shou

Re: [SQL] Slow performance on MAX(primary_key)

On Tuesday 15 Oct 2002 8:47 am, Keith Gray wrote: > Ludwig Lim wrote: > > As of now, Max() doesn't utilizes the indices hence > > it always do a sequential scan. > > Thanks Ludwig, > > That does help performance, but I was using a "standard" > SQL command wrapped in a VB6 ADO ODBC program. > > I

Re: [SQL] Stored procedure returning row or resultset

On Monday 14 Oct 2002 6:17 pm, Tom Lane wrote: > Richard Huxton <[EMAIL PROTECTED]> writes: > > Have you looked at marking f1() etc cachable? This means Postgresql > > will only call the function once for each parameter-set. > > Unfortunately that's not true at all, or at least not helpful for thi

[SQL] how do i insert an empty string ?

FAQ: A search yielded nothing explicit... I have an INSERT statement: INSERT INTO metadata (md5, origin, name, value) VALUES ('fd859f263bd0579935f2146a22d24f32', 'EXIF', 'UserComment', '') but this fails (using Perl DBI, DBD::Pg) because $dbh->quote() returns two single quotes, wh

Re: [SQL] how do i insert an empty string ?

Sorry: 7.3 beta 2 on OpenBSD 3.2 Peter - Original Message - From: "Peter Galbavy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 15, 2002 11:01 AM Subject: [SQL] how do i insert an empty string ? > FAQ: A search yielded nothing explicit... > > I have an INSERT statem

Re: [SQL] IGNORE ME how do i insert an empty string ?

Please ignore me for now. The string is NOT empty, but full of NUL characters. My bad for not using 'less' to view the output... Peter - Original Message - From: "Peter Galbavy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 15, 2002 11:01 AM Subject: [SQL] how do i i

[SQL] Messy Casts, Is there a better way?

I have a table with the following, in part: contract_start date contract_term int (term in MONTHS) I want to calculate the contract end date. I came up with: CREATE FUNCTION "get_contract_end" (integer) RETURNS date AS 'SELECT cast(contract_start + cast(cast(contract_term as text) || '' month'

[SQL] set difference

Hello, I would like to make symmetrical(set) difference in a query. But the simpliest way I could find is select id from a except select id from b union select id from b except select id from a Is there any better solution for this problem? Thanks Laca ---(end of

Re: [SQL] Stored procedure returning row or resultset

Richard Huxton <[EMAIL PROTECTED]> writes: > On Monday 14 Oct 2002 6:17 pm, Tom Lane wrote: >> Unfortunately that's not true at all, or at least not helpful for this >> problem. The cachable attribute was poorly named, because it leads >> people to think that PG *will* cache function results, as

Re: [SQL] SQL function triggers

Brian Blaha <[EMAIL PROTECTED]> writes: > I would like to write a function as a set of SQL statements, and then > use that function > in a trigger. However, since triggers require a return type of opaque, > and SQL functions > cannot return type opaque, this doesn't look possible. You could cal

Re: [SQL] how do i insert an empty string ?

On Tue, 15 Oct 2002, Peter Galbavy wrote: > FAQ: A search yielded nothing explicit... > > I have an INSERT statement: > > INSERT INTO metadata (md5, origin, name, value) > VALUES ('fd859f263bd0579935f2146a22d24f32', 'EXIF', > 'UserComment', '') > > but this fails (using Perl DBI, DB

Re: [SQL] set difference

=?iso-8859-1?Q?Domoszlai_L=E1szl=F3?= <[EMAIL PROTECTED]> writes: > I would like to make symmetrical(set) difference in a query. > But the simpliest way I could find is > select id from a > except > select id from b > union > select id from b > except > select id from a > Is there any better

Re: [SQL] Messy Casts, Is there a better way?

Larry, > contract_start date > contract_term int (term in MONTHS) > > I want to calculate the contract end date. I came up with: > CREATE FUNCTION "get_contract_end" (integer) RETURNS date AS 'SELECT > cast(contract_start + cast(cast(contract_term as text) || '' month'' > as > interval) as dat

Re: [SQL] Messy Casts, Is there a better way?

Larry Rosenman writes: > I have a table with the following, in part: > > contract_start date > contract_term int (term in MONTHS) Store contract_term as interval? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscrib

[SQL] int id's helpful for indexing, or just use text names?

[linux, postgresql 7.2, 500MHz * 4 xeon cpu's, 1GB ram, hardware raid] My current db has serveral instances of something like: table foos(fooid int2, fooname text, foouser text, foobar int2 references bars(barid)) table bars(barid int2, barname text, barcolor text, primary key(barid) ) et

[SQL] ADO with postgreSQL

Hi,   Thank you for the previous help (INNER BETWEN MORE THAN ONE DATABASES).     In the Visual Basic 6.0:     Dim dbConn as Connection, rs as recordset, strSQL as string, strConn as string       strConn = "Provider=MSDASQL.1;Extended Properties=""DRIVER={PostgreSQL};DATABASE=MyDataBase;SERV

Re: [SQL] int id's helpful for indexing, or just use text names?

george young <[EMAIL PROTECTED]> writes: > The question is: would I be better off losing all those integer ids > and just using the text names as primary indices? Is there much > performance lost comparing text strings for every index operation? Strings would be slower, but possibly not by enoug

Re: [SQL] SQL function triggers

Brian Blaha wrote: > > I would like to write a function as a set of SQL statements, and then > use that function > in a trigger. However, since triggers require a return type of opaque, > and SQL functions > cannot return type opaque, this doesn't look possible. Am I missing > something? The SQL