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 should work if primary_key is indexes. > > 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. Is this likely to be sorted in 7.2 ? Is anyone looking at this? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
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. > > Is this likely to be sorted in 7.2 ? > Is anyone looking at this? As I understand, the problem is that the optimisation only applies for simple cases, and for certain aggregate functions (e.g. not sum()). This means that the parser would need special-case code to spot these cases, along with tags for those functions that can be optimised. Thinking further, it might also vary from type to type. Given that there is a simple workaround and the need for the optimisation to be added cleanly to the code I believe this has a fairly low priority. There is a todo list on the developers' side of the website which has a list of changes in upcoming releases, you could check there for details. -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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 this > problem. The cachable attribute was poorly named, because it leads > people to think that PG *will* cache function results, as opposed to > *could* cache function results. I must admit, that was my impression. Are there simple rules for if/when PG will cache function results? -- Richard Huxton ---(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] 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, which fails because something along the way thinks this is a
single quote. I do NOT want to insert a NULL but an empty string...
(This is either doing a $dbh->do(...) or a prepare ... execute without
$dbh->quote())
Peter
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 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, which fails because something along the way thinks this is
a
> single quote. I do NOT want to insert a NULL but an empty string...
>
> (This is either doing a $dbh->do(...) or a prepare ... execute without
> $dbh->quote())
>
> Peter
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
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 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, which fails because something along the way thinks this is
a
> single quote. I do NOT want to insert a NULL but an empty string...
>
> (This is either doing a $dbh->do(...) or a prepare ... execute without
> $dbh->quote())
>
> Peter
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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'' as interval) as date) FROM circuit WHERE internal_id = $1;' LANGUAGE 'sql'; Is there a better way? -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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 broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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 opposed to >> *could* cache function results. > I must admit, that was my impression. Are there simple rules for if/when PG > will cache function results? It won't; there is no function cache. What there is is a pass of constant-folding before a query is run. For example, if you write select * from foo where x > sqrt(4); then the function call "sqrt(4)" will be folded down to a constant "2" before planning and execution starts, rather than evaluating it again at each row of foo. (This also improves the system's ability to use indexes, etc, so it's a pretty essential thing.) The point of the poorly-named isCachable attribute is to tell the constant-folding pass whether it's safe to apply the function in advance of execution --- ie, does it always return the same output, given constant inputs? An example of a non-cachable function is now(). In 7.3 isCachable has been split into two attributes "immutable" and "stable", distinguishing functions that are constant for all time from those whose outputs are constant during any single query. (sqrt() is immutable, now() is stable, random() is neither.) These names perhaps will be less likely to mislead people into thinking that some kind of caching goes on while a query runs. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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 call a SQL function from a trigger, but it can't be a trigger itself; at present triggers have to be in C, plpgsql, or pltcl (maybe plpython? Not sure about that one). For what you are doing (transferring info from one table to another) I suspect a rule might work better than a trigger anyway. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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, DBD::Pg) because $dbh->quote() returns two
Since you are using DBI, why not bind the variables and be done with it?
this would become
$query = $db->prepare("INSERT INTO metadata (md5, origin, name, value)
VALUES (?, ?, ?, ?)");
$query->execute($md5, $origin, $name, $value);
This way you don't have to deal with double quoting and all that. (You can
also call bind_param, but I find it easier to just pass args into execute)
--
Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/
Ronald McDonald, with the help of cheese soup,
controls America from a secret volkswagon hidden in the past
---
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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 solution for this problem? One thing you should definitely do is change "union" to "union all". "union" implies a pass of duplicate removal, which shouldn't be necessary here (unless a or b individually contain duplicates and you want to get rid of those too). Another thing to try is (a union b) except (a intersect b) (Again, you might be able to say union all instead of union.) Not sure which will be faster. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
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 date) > FROM circuit > WHERE internal_id = $1;' LANGUAGE 'sql'; Not with those data types. Plus, there is an implicit conversion date --> timestamp --> date in the above, which can get you in trouble. You could use TIMESTAMP and INTERVAL instead: contract_start TIMESTAMP WITHOUT TIME ZONE contract_term INTERVAL select (contract_start + contract_term) as contract_end; Simple, neh? The only trick is on the end of saving the data. You have the user input an integer, then save (using RULES or your interface code): "interval"(cast($term as varchar) || ' months') This approach makes you do a little more work on the data entry end of things, but speeds up querying considerably. Also, should your company policy change in the future to permit contract terms in weeks or years, you will be ready to accomodate it. -Josh Berkus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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) ) etc, where foonames and barnames are known to be, say <20 characters long. And the fooid's and barid's are arbitrary ints only known inside the db. The original reason for these numeric id's, (in another db system long long ago), was to conserve space(now irrelvant with 120G disks) and to make searching and index usage more efficient. Recently, there is increasing call for new apps and even ad-hoc queries.(Thank goodness people are finally interested in this data!) The artificial numeric id's make it a lot harder for naive users to understand the data structure, and sometimes actually requires an extra order of joins. 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? My db is not huge: longest table has 100k tuples, biggest table has 1k pages, total pg_dump output is 51 Mbytes. Typical activity: ~6000 updates and inserts/day, 30,000 selects/day. -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "The Dying Detective" ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[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;SERVER=MyServer;PORT=5432;ReadOnly=0;Protocol=6.4"""
set dbConn = new
connection
dbConn.CursorLocation =
adUseServer dbConn.Open strConn, "MyUser",
"MyPasswd"
strSQL = "select * from table1
where id = 1234"
set rs = new
recordset
rs.open strSQL, dbConn,
adOpenDynamic, adLockBatchOptimistic
In table1 the field 'field1' is of type 'money'
The next command
rs!field1 = ccur(form1.field1.text)
rs.update
cause the following message:
ERROR: Attribute 'field1' is of the tupe 'money' but expression is of type
'float8'.
You will need to rewrite or cast the
expression.
My PostgreSQL version is 7.1.
How I do to work in 'ODBC ambient' with no large
changes ?
Silvio
2000Info
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 enough to notice. The real question you should ask yourself is whether your text names are really good candidates to be primary keys. Are there ever any duplicates? Do you ever rename objects? If your answer to either is "yes" then the names won't do as unique identifiers. There's an old saying that meaningful keys are bad database design; check the mailing list archives for some examples. (F'r instance, I seem to recall a story about a bank that embedded branch numbers into account numbers, and then had terrible troubles anytime a customer moved...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 > that I would expect to do this is below. Incidentally, on an insert, > would the trigger fire > once, twice, or infinitely? > > create table test( > a integer, > b integer > ) without oids; > > create function test_func( test.a%TYPE ) > RETURNS ? > AS > 'update test set b = a where a = $1;' > language SQL > with (isstrict); > > create trigger test_trig after update > on test for each row > execute procedure test_func( a ); If you intend to modify the updated row only with information available in the row itself (as in your example), a BEFORE trigger in PL/pgSQL modifying and returning NEW should get the job done. 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 6: Have you searched our list archives? http://archives.postgresql.org
