On Friday 08 Jul 2005 12:02 pm, Ramakrishnan Muralidharan wrote:
>I have assuemed that the row will having eighter Debit account
> or Credit account, the following Query will give sum of debit and
> credit accounts
>
>SELECT SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN
> COALESCE( AMOU
Hi Listers,
I need to know the number of affected (Inserted, deleted, updated)
rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT
@@IDENTITY. Is there any @@RowCount similar statement in PostGres??
Alessandro - Liga One Sistemas
[EMAIL PROTECTED]
- Original Message
[Please start a new thread when asking new questions.]
On Fri, Jul 08, 2005 at 08:56:56AM -0300, jimmy.olsen wrote:
> I need to know the number of affected (Inserted, deleted, updated)
> rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT
> @@IDENTITY. Is there any @@RowCo
On Fri, Jul 08, 2005 at 05:03:37 +0200,
PFC <[EMAIL PROTECTED]> wrote:
>
> It's the first time I see a MySQLism in postgres !
This has meaning in more ways than one.
> However I like it, cos it might subvert some MySQL users, and
> provide easy answers to The Weekly Questio
SELECT SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN
COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN
COALESCE( CREDIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0
END ) AS CREDIT_AMT FROM
I don't know if it will use indexes (bitmapped OR indexes in 8.1 ?)... if
y
On Fri, Jul 08, 2005 at 15:49:20 +0200,
PFC <[EMAIL PROTECTED]> wrote:
>
>
> >> SELECT SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN
> >>COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN
> >>COALESCE( CREDIT , 0 ) <> 0 THEN COALESCE( AMOUNT , 0 ) ELSE 0
> >>END ) AS CREDIT
Chris Browne wrote:
> None of those transactions have COMMITted, so there are some 78 tuples
> "in limbo" spread across 16 transactions.
>
> If there were some "single secret place" with a count, how would you
> suggest it address those 78 tuples and 16 transactions that aren't yet
> (and maybe n
On Fri, Jul 08, 2005 at 07:12:26 -0700,
Steve Wampler <[EMAIL PROTECTED]> wrote:
>
> Hmmm, I understand this and don't doubt it, but out of curiousity, how
> does the current SELECT COUNT(*) handle this? It doesn't lock the entire
It only counts tuples visible to the current transaction.
> ta
SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT
sum(amount) FROM table WHERE credit=x) AS credit;
If most of the records are credits or debits you don't want to do this.
A single sequential scan through the table will be the best plan.
I thought that debit = source ac
Bruno Wolff III wrote:
> No, it is an exact count.
Yes, for the transaction, but it's an approximation of the number of
tuples in the table - which is probably what the people who worry about
its cost are more interested in (an approximate count for the table).
I'm also claiming that a true count
> So, leave COUNT(*) alone. But it would be very handy to have a
> way to get an approximate table size that is more accurate than is
> provided by a pg_class.reltuples that is only updated on vacuums.
Create 2 sequences, one for counting tuple additions and one for
counting tuple deletions.
Whe
On 7/8/05, Steve Wampler <[EMAIL PROTECTED]> wrote:
> > None of those transactions have COMMITted, so there are some 78 tuples
> > "in limbo" spread across 16 transactions.
> >
> > If there were some "single secret place" with a count, how would you
> > suggest it address those 78 tuples and 16 tra
On Fri, Jul 08, 2005 at 08:07:27AM -0700, Steve Wampler wrote:
> Bruno Wolff III wrote:
> > No, it is an exact count.
>
> Yes, for the transaction, but it's an approximation of the number of
> tuples in the table - which is probably what the people who worry about
> its cost are more interested in
On 7/8/05, Rod Taylor <[EMAIL PROTECTED]> wrote:
> Create 2 sequences, one for counting tuple additions and one for
> counting tuple deletions.
>
> When you INSERT a tuple, bump the "added" sequence (select nextval());
>
> When you DELETE a tuple, bump the "deleted" sequence (select nextval());
>
Steve Wampler <[EMAIL PROTECTED]> writes:
> So, leave COUNT(*) alone. But it would be very handy to have a
> way to get an approximate table size that is more accurate than is
> provided by a pg_class.reltuples that is only updated on vacuums.
If you want something cheap, you could use the same t
On Fri, 2005-07-08 at 17:34 +0200, Dawid Kuroczko wrote:
> On 7/8/05, Rod Taylor <[EMAIL PROTECTED]> wrote:
> > Create 2 sequences, one for counting tuple additions and one for
> > counting tuple deletions.
> >
> > When you INSERT a tuple, bump the "added" sequence (select nextval());
> >
> > Whe
On Fri, Jul 08, 2005 at 16:49:44 +0200,
PFC <[EMAIL PROTECTED]> wrote:
>
> >>SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT
> >>sum(amount) FROM table WHERE credit=x) AS credit;
> >
> >If most of the records are credits or debits you don't want to do this.
> >A single seq
Tom Lane wrote:
> Steve Wampler <[EMAIL PROTECTED]> writes:
>
>>So, leave COUNT(*) alone. But it would be very handy to have a
>>way to get an approximate table size that is more accurate than is
>>provided by a pg_class.reltuples that is only updated on vacuums.
>
> If you want something cheap,
which wouldn't have helped.) If true, I can handle that parsing myself
easily enough without exposing RelationGetNumberOfBlocks.
Is there a way to get EXPLAIN results in a non-text-formatted way for
easier use ?
I'm asking, because it seems the feature set grows by the minute in
posgre
Greetings,
A question about creating index for the following expression.
CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
May I know is it possible and how I may create index for the expression
such as "col1 || '-' || col2" for a table please?
Thanks a lot,
Emi
-
Ying Lu <[EMAIL PROTECTED]> writes:
> A question about creating index for the following expression.
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
You need more parentheses:
CREATE INDEX idx_t1 ON test ((col1 || '-' || col2));
regards, tom lane
---
Steve Wampler <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> If you want something cheap, you could use the same technique the
>> planner uses nowadays: take RelationGetNumberOfBlocks() (which is
>> guaranteed accurate) and multiply by reltuples/relpages.
> Yes - this would be an excellent appro
On Fri, Jul 08, 2005 at 12:08:41PM -0400, Ying Lu wrote:
>
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
>
> May I know is it possible and how I may create index for the expression
> such as "col1 || '-' || col2" for a table please?
See "Indexes on Expressions" in the documentation:
htt
> I'm also claiming that a true count for any active table is
> meaningless and am *not* suggesting that effort be spent on trying
> to produce such a true count.
That's a pretty big assumption that would in fact be WRONG.
We have managers interested in counting the number of objects we have
arou
On Fri, Jul 08, 2005 at 12:08:41 -0400,
Ying Lu <[EMAIL PROTECTED]> wrote:
> Greetings,
>
> A question about creating index for the following expression.
>
> CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
>
> May I know is it possible and how I may create index for the expression
> such a
On Fri, Jul 08, 2005 at 12:08:41PM -0400, Ying Lu wrote:
CREATE INDEX idx_t1 ON test (col1 || '-' || col2);
May I know is it possible and how I may create index for the expression
such as "col1 || '-' || col2" for a table please?
"The syntax of the CREATE INDEX command normally req
Christopher Browne wrote:
>>I'm also claiming that a true count for any active table is
>>meaningless and am *not* suggesting that effort be spent on trying
>>to produce such a true count.
>
>
> That's a pretty big assumption that would in fact be WRONG.
Please reread the message from Bruno and
On Fri, Jul 08, 2005 at 06:08:03PM +0200, PFC wrote:
>
>
> >which wouldn't have helped.) If true, I can handle that parsing myself
> >easily enough without exposing RelationGetNumberOfBlocks.
>
> Is there a way to get EXPLAIN results in a non-text-formatted way
> for easier use ?
I have what I call a "dictionary" table which supports a "master table".
This dictionary table is designed to hold generic data :
CREATE TABLE sup_data (link_id uniqueidentifier, field_name varchar(255),
field_data text) WITH OIDS; ...
It works well when you're digging into it to pull the s
Is it even possible to cluster a table based on the clustering scheme
(which is
not the link_id ...) from the master table?
Can you gurus think of a better strategy? :) (Please??) :)
You can create a functional index on a function which returns the desired
order by looking in the main t
30 matches
Mail list logo
