Hi Ivan,
Sorry, I can't remember all you said in
earlier posts, but I was wondering, your log file says:
HINT: Rebuild the index with REINDEX.
Did you do that, and did it solve the
problem?
regards
Iain
- Original Message -
From:
pginfo
To: Tom Lane
Cc: [EMAIL
hi,
I'm not familiar with iso2709 but there is a program called Octopus that may do what
you want. It's open source software and can be found at octopus.enhydra.org -
worth a try anyway.
Regards
Iain
- Original Message -
From:
Matteo
Braidotti
To: pgsql-sql
or,
# update table set birthdate = to_date('2000-01-01','-MM-DD');
I'm not sure which is better though I tend to do it this way.
regards
Iain
- Original Message -
From: Achilleus Mantzios [EMAIL PROTECTED]
To: Pablo Digonzelli [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday
the employee's department?. It seems like a
handbook case to me but maybe I missed something.
regards
Iain
I can't figure out an efficient way to do this. Basically I had a typical
3-tier relationship:
(Employee - Department - Division)
However, at some point the need to move employees arose
OK, thanks. That seems to make sense.
regards
Iain
- Original Message -
From: "Tom Lane" [EMAIL PROTECTED]
To: "Iain" [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, December 02, 2004 11:54 AM
Subject: Re: [SQL] invalid 'having'
Hi,
Just a quick question out of curiosity, I was just wondering if this is
supposed to be valid sql:
select count(*) as cnt
from sometable
group by somecolumn
having cnt 1
This isn't valid in pg (7.4.6), but this is:
select count(*)
from sometable
I seem to recall it was mine. I made the mistake of assuming it wasn't
concurrency safe and was gently corrected by one of the community. I
think it might have been a Tim/Tam Lane. Wonder what happened to him? :-)
M tim tams
rgds
Homer
---(end of
Using the the sub-select is one way, but there is another way.
If the function can be declared as strict or immutable the you can call it
as many times as you like in a single transaction and it will only be
evaluated once. As far as I know this does work as advertised.
Check
program to create a session and actually do the listening,
that is if I havn't missed anything else...
Thanks again,
Iain
- Original Message -
From: Mike Rylander [EMAIL PROTECTED]
To: Iain [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, September 16, 2004 1:03 AM
Subject: Re
Hi All,
I've come across a situation where I'd
like to use some kind of "out-of-transaction trigger" to do some processing
after changes to some tables, but without extending the duration of the main
transaction. Of course, it's important that the processing be
# select 9223372036854775807 = 9223372036854775807::float;
?column?
--
t
(1 row)
# select 9223372036854775807 = 9223372036854775807.0::float;
?column?
--
t
(1 row)
# select 9223372036854775807 = 9223372036854775807.::numeric;
a database
that used float data...
regards
Iain
---(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
is in the admin section and some is in the documentation of the client
programs (pg_dump and pg_restore are client programs iirc).
Also, consider upgrading to 7.4 to ensure you get the best support.
Good luck,
Iain
- Original Message -
From: Worik [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent
ideas anyway. This may of course be much more work than
anyone wants to get into... I don't have much experience with text searching
systems, but something reasonably sophisticated would probably get you
there.
Regards
Iain
---(end of broadcast)---
TIP
. Personally, I've grown to appreciate the id approach
over the years, but my mind is always open to other ideas.
regards
iain
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
(B
This is a great topic though, I find it really interesting.
Regards
Iain
- Original Message -
From: "Josh Berkus" [EMAIL PROTECTED]
To: "Iain" [EMAIL PROTECTED]; "Markus Bertheau" [EMAIL PROTECTED];
[EMAIL PROTECTED]
Hi,
for my 2c worth, performance is the least important of the things you need
to consider regarding use of surrogate keys.
I use surrogate keys for all situations except the simplest code/description
tables, and this is only when the code has no meaning to the application. If
IF foo IS NULL
THEN ...
ELSIF foo
THEN ...
ELSE ...
END IF;
here the foo expression woll be executed twice
if you can use an immutable or stable function then the overhead would be
minimal as the system knows that it doesn't need to re-evaluate it.
regards
Iain
If you have a lot of tips, you could create a unique indexed tip number
column. Select the highest tip number using:
select tip_number from tips order by tip_number desc limit 1;
Then generate a random number and select using that tip_number.
Of course, you would have to allow for the
and trying to isolate the problem.
It would be a good idea to give all the relevant version numbers too
regards
iain
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
of the select
and test them using ANALYSE SELECT ... .I'm using 7.4.1.
The result from the select is the same, even if you don't make an index on
the function result.It's just a performance consideration. If the index
isn't used, then you don't need to create it.
regards
Iain
- Original Message
Sorry, did I write ANALYSE SELECT ?
It's supposed to be EXPLAIN [ANALYSE] [VERBOSE] SELECT ...
- Original Message -
From: Daniel Henrique Alves Lima [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 1:21 PM
Subject: Re: [SQL] Simple SQL question
Thank you,
howabout:
select sum(case when c = '*' then 0 else 1 end) as count_not_star from
tablename
If you want to process all records but only count thouse without a * in c
then this will do the trick.
regards
iain
- Original Message -
From: Abdul Wahab Dahalan [EMAIL PROTECTED]
To: [EMAIL
mentioned.
HTH
Iain
---(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
Hi,
I don't think thatyou gave enough
information to get any direct help, for example, what are these
"sub-selects"?
I often see performance problems arising
from procedural code that puts selects that don't use indexesinside loops.
That's a plain programming issue, and understanding your
DB Version 7,4.
I had a look through the docs, but the
information on table functions (those returning SETOF something) seems to have
gone missing from the 7.4 docs. I found something under 7.3, but it didn't
answer my question,which is...
I've written a
Hi Tom,
You didn't say what PG version you are using, but I'd venture to bet
it is pre-7.4.
Sorry I didn't give the version number, here is the output from select
version():
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC 2.95.3
This is the first time that
Hi All,
I found this interesting and thought I'd
offer it up for comment.
I have the following delete:
delete from tableB where id not in (select
id from tableA);
There are about 100,000 rows in table A,
and about 500,000 in table B. id is
Postgres' SQL cache, but it is well known in Oracle
circles that using bind variables is is a critical part of system design,
not just for security, but for performance and scalability. I suspect that
the same issues apply more or less to postgres.
Correct me if I'm wrong, please...
regards
Iain
in a different order. It may be that using different dump options could get
around this though - such as generating the dump as insert statements.
If you come up wth any interesting information as you progress with this,
I'd be happy to hear about it.
Regards
Iain
- Original Message -
From: [EMAIL
to checks.
Now, I am wondering if there is a clever work around to this? It escapes me
for now anyway.
Regards
Iain
- Original Message -
From: Karel Zak [EMAIL PROTECTED]
To: Christoph Haller [EMAIL PROTECTED]
Cc: Alexander M. Pravking [EMAIL PROTECTED];
[EMAIL PROTECTED]
Sent: Tuesday, December
');
insert into test1 values ('2002/ 3/22');
insert into test1 values ('2002/03/2');
insert into test1 values ('2002/03/ 2');
insert into test1 values ('2002/3/2');
insert into test1 values ('2002/14/02');
It's no silk purse, but it's short and sweet and I'm satisfied.
Thanks guys.
Rregards
Iain
Hi Colin,
You have done a nice job of demonstrating the documented behaviour of
sequences.
Sequences are designed for speed and high concurrency. They do not guarantee
contiguous numbers and are not included in any transaction as I understand
it.
regards
Iain
- Original Message -
From
for the
id (ir didn't use the default) but still used (and therefore incremented)
the sequence.
Regards
Iain
- Original Message -
From: Burr, Colin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, November 26, 2003 8:37 AM
Subject: [SQL] Bug: Sequence generator insert
Dear Sir,
I
Hi All,
I have a problem with a before update
trigger that I was wondering if someone might be able to give me some help
with.
DB version is7.2. Here is a cut down
version of an already simple trigger and function that is returning the
error:
-
From:
Iain
To: [EMAIL PROTECTED]
Sent: Thursday, November 20, 2003
11:37 AM
Subject: fmgr_info: function
15238119: cache lookup failed
Hi All,
I have a problem with a before update
trigger that I was wondering if someone might
Hi all,
To take a simple example here is what I
wanted to do:
select 1 as one, 2 as two,
one + two as three;
but it doesn't work. I've checked it out
though, and I found that I can do this:
select one, two, one + two
from (select 1 as one,
37 matches
Mail list logo