Re: [SQL] Create View

[Richard Huxton]

On Wednesday 15 October 2003 02:01, Muhyiddin A.M Hayat wrote:
>
> Table Billing:
>
> id   trx_date   trx_time depart   payment_method   
> billing_amountamount_paid balance   creator 1  10/09/2003 
> 21:55:02   RestoVisa   13.800,00   
>  10.000,00   3.800,00   middink
>
> Table Payment
>
> id r   trx_datetrx_timedescriptions  
> payment_method   amount  creator 1 10/08/2003  18:17:40 
> Payment  Cash 2.000,00 middink
>
>
> I would like to create "View " from above table with result look like:
>
>
>  trx_date   trx_time descriptions  
>   payment_method   debet  
> credit balance creator 10/09/2003  21:55:02   Resto 
> Billing  13.800,00 Paid: 10.000,00 Visa 
> 3.800,00   3.800,00 middink 10/08/2003 
> 18:17:40Payment
>  Cash2.000,00  
> 1.800,00  middink

I'm not sure the above makes sense. I don't see any way to connect that 
particular payment to that particular bill. You also seem to have two 
different types of information in the billing table (billing *and* payment).

Also, the payment seems to be occurring before the bill - which can happen, 
but there doesn't seem to be any account it is paid into.

Generally, there are two ways to do this:
1. Set-returning functions (see the manuals and 
http://techdocs.postgresql.org)
2. A transaction/account page that you keep up to date in exactly the sort of 
format you want in your view. Triggers can make sure this happens 
automatically.

Unfortunately, if you're stuck with the tables you've shown us then I don't 
think you can solve your problem.
-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] about postgre SQL download

[Richard Huxton]

On Wednesday 15 October 2003 10:27, Illusiontechnologies wrote:
> Sir,
> We are using Windows 98,WindowsXP on our system. How we are going to
> download postgre SQL for this window platform. Which would be the closest
> mirror since we are from india.

I don't recommend using Windows 98 to host a database at all, XP is much more 
stable though. You have three main options...

To run the free version of PostgreSQL on Windows you'll need the (free) cygwin 
system which provides a unix-like environment on top of Windows.

http://www.cygwin.com/

This lets you select a number of packages to install, one of which is 
postgresql. You can find some details on setting up postgresql on Windows on 
the techdocs site.

http://techdocs.postgresql.org

The Cygwin method of running PostgreSQL is not considered as stable as running 
on top of Linux/BSD/other unix systems, but should be fine for 
developing/testing.

In addition, there are a number of commercial offerings of Windows versions, 
again there is a link on the techdocs site. I know of one person who has 
installed the dbexperts version and seems very happy.

Finally, the PostgreSQL developers are working on a native Windows version 
that should be ready for version 7.5, but that will be some 6-12 months off.

My personal preference would be to install Linux/FreeBSD on a machine and run 
it there - setting up Linux is much easier than it used to be. Which option 
you want to go for will depend upon your experience, time available and 
whether you want to spend any money.

HTH
-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] How can I produce the following desired result?

[greg]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


> How can I produce the following desired result?

SELECT * FROM mytable
JOIN 
(SELECT goodid, MAX(storehistoryid) AS storehistoryid FROM mytable GROUP by 1) AS a
USING (goodid,storehistoryid);


- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200310151046

-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/jV4PvJuQZxSWSsgRAmYlAJwL06D+VNkmAT7RDcjXPgGu9oPXkgCgx1SJ
OASzEJlWv6qi05xXhPH1NBY=
=szda
-END PGP SIGNATURE-




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] How can I produce the following desired result?

[Jean-Luc Lachance]

select distinct on( goodid) * from table order by goodid, storehistoryid
desc, totalnum, operationdate;


> aicean wrote:
> 
> How can I produce the following desired result?
> 
> goodidtotalnum   operationdate storehistoryid
>132  35.000  09-28-2003 66
>135  11.500  09-28-2003 61
>132  35.000  09-27-2003 60
>135  11.000  09-28-2003 59
>135  12.000  09-28-2003 58
>134 100.000  09-28-2003 57
>134 112.000  09-27-2003 56
>131   0.000  09-26-2003 54
>131  33.000  09-26-2003 51
>131  -2.000  09-26-2003 50
>  3 550.000  09-26-2003 49
>  3  52.000  09-26-2003 48
>132  35.000  09-27-2003 42
>  3 124.000  09-25-2003 41
>131  59.000  09-25-2003 40
>132  57.000  09-26-2003 39
>131   2.000  09-24-2003 38
>   3   2.000  09-20-2003 23
> 
> result:
> goodidtotalnum   operationdate storehistoryid
>132  35.000  09-28-2003 66
>135  11.500  09-28-2003 61
>134 100.000  09-28-2003 57
>131   0.000  09-26-2003 54
>  3 550.000  09-26-2003 49
> 
> I need   to select rows which storehistoryid is max as the same
> goodid .
> 
> 
> Thanks  in advance
> 
> 
>  aicean
>  Mailto:[EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Sorting problem

[Jean-Luc Lachance]

You are obviously not using C locale.
If you can't change it for some reason, you can use:

select * from accounts order by int4( trim( acno, '#'));

JLL

"George A.J" wrote:
> 
> hi all,
> i am using postgres 7.3.2 .i am converitng a mssql database to
> postgres.
> now i am facing a strange problem. sorting based on a varchar field is
> not working
> as expected. the non alphanumeric characters are not sorting based on
> the ascii
> value of them.
> 
> i have the following table structure..
> 
> create table accounts
> (
>   AcNo varchar (10),
>   Name varchar(100),
>   balance numeric(19,4)
> )
> 
> when i used the query select  * from accounts order by acno. the
> result is not correct
> 
> suppose that the acno field contains values '###1' ,'###2' ,'##10' ,
> '#100'
> the sort order in postgres is
> '###1'
> '##10'
> '#100'
> '###2'
>  But i want the result as follows
> '###1'
> '###2'
> '##10'
> '#100'
> 
> that means the ascii value of # should be considered for sorting..
> what is the problem. is it the behaviour of postgres.
> do i need to change any configuration. i am using all default
> configurations
> or is it a bug...?
> the problem actually is of < & > operators for varchar.
> 
> in a simple comparison
> 
> select '###2' < '##10'
> 
> returns false but i need true.
> 
> is there any solution exist. even if i replaced # with any non
> alphanumeric
> character the result is same..
> 
> pls help
> 
> jinu jose
> 
> --
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Escaping the $1 parameter in stored procedures

[robert]

Found a solution:

-- my_constraint(0) = turn off constraint
-- my_constraint(1) = turn ON constraint
CREATE OR REPLACE FUNCTION my_constraint(INTEGER)
RETURNS VARCHAR
  AS '
DECLARE
cmd VARCHAR;
BEGIN
IF $1 = 0
THEN
RAISE NOTICE ''Turning OFF constraints'';
cmd := ''ALTER TABLE $tName DROP CONSTRAINT "$1"'';
EXECUTE cmd;
cmd := ''ALTER TABLE $tName DROP CONSTRAINT "$2"'';
EXECUTE cmd;
ELSE
RAISE NOTICE ''Turning ON constraints'';
ALTER TABLE $tName
ADD FOREIGN KEY(key1) REFERENCES table1;
ALTER TABLE $tName
ADD FOREIGN KEY(key2) REFERENCES table2;
END IF;
RETURN ''OK'';
END;'
LANGUAGE plpgsql;"




[EMAIL PROTECTED] (robert) wrote in message news:<[EMAIL PROTECTED]>...
> I'm running Postgres 7.3.2 in Redhat 9.0.
> 
> I'm trying to execute a function below defined as a stored procedure
> 
>ALTER TABLE tms_schedule DROP CONSTRAINT "$1";
> 
> However, postgres thinks the "$1" is a parameter value.  How do I tell
> postgres to treat it as a literal $1?
> 
> TIA,
> Robert

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] indexing timestamp fields

[Christopher Browne]

teknokrat <[EMAIL PROTECTED]> writes:
> Is it a good idea to index timestamp fields? what about date fields in
> general?

If you need to order by a timestamp, then it can be worthwhile.

If that timestamp can be null, and is rarely populated, then you might
get a _big_ benefit from creating a partial index as with:

 create index by_some_date on my_table(some_date) where some_date is
   not null;
-- 
"cbbrowne","@","libertyrms.info"

Christopher Browne
(416) 646 3304 x124 (land)

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Calc

[Muhyiddin A.M Hayat]

Dear all,
 
I have below table
 
+-++-+
 | id   |    
db    
|cr                
          |
+-++-+
 |1 
|    
    
| 200  
|

 |2 
| 100    
|  
|

 |3 
| 100    
|  
|

 |4 
| 150    
|  
|

 |5 
|    
    
| 200  
|
 
I Would like to calc that balance, and look 
like
 

+-++-+---+
 | id   |    
db    
|cr                
          | 
bal    |
+-++-+---+
 |1 
|    
    
| 200  
|200   |

 |2 
| 100    
|  
|100   |

 |3 
| 100    
|  
|0    
|

 |4     
| 150    
|  
|-150  |

 |5 
|    
    
| 200  
|50 |
 
 
What can I do to get result like that
 
 
 
 
 
 
 
 
 
 

Re: [SQL] Calc

[Steve Crawford]

On Wednesday 15 October 2003 12:59 pm, Muhyiddin A.M Hayat wrote:
> Dear all,
>
> I have below table
>
> +-++-+
>
>  | id   |db|cr  |
>
> +-++-+
>
>  |1 || 200  |
>  |2 | 100|  |
>  |3 | 100|  |
>  |4 | 150|  |
>  |5 || 200  |
>
> I Would like to calc that balance, and look like
>
> +-++-+---+
>
>  | id   |db|cr  |
>  | bal|
>
> +-++-+---+
>
>  |1 || 200 
>  | |200   | 2 | 100|   
>  |   |100   | 3 | 100| 
>  | |0| 4 | 150 
>  |   |  |-150  | 5 |   
>  | | 200  |50 |
>
> What can I do to get result like that

Something like this:

select id,db,cr,(select sum(cr-db) from calc sub where sub.id <= 
calc.id) from calc;

This of course assumes that ID indicates the correct order of the 
entries and it will blow up if you allow nulls for cr or db (which 
you shouldn't since that would literally be interpreted as "they 
withdrew 10 and deposited an unknown amount"). If you have null 
values already and they should be interpreted as 0 just do this:

select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0)) from 
calc sub where sub.id <= calc.id) from calc;

I assume no responsibility for potential lack of scalability of this 
query. :) It's quite possible a faster solution exists - we'll see 
what comes up on the list.

Cheers,
Steve


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] indexing timestamp fields

[Christopher Browne]

teknokrat <[EMAIL PROTECTED]> writes:
> Christopher Browne wrote:
>
>> teknokrat <[EMAIL PROTECTED]> writes:
>>
>>>Is it a good idea to index timestamp fields? what about date fields in
>>>general?
>> If you need to order by a timestamp, then it can be worthwhile.
>> If that timestamp can be null, and is rarely populated, then you
>> might
>> get a _big_ benefit from creating a partial index as with:
>>  create index by_some_date on my_table(some_date) where some_date is
>>not null;
>
> I have a lot of queries of the " where timestamp < some date " type
> and was wondering if an index would improve performance. None of the
> timestamps are null and they are always populated

There isn't a single straight answer on this.  
It _might_ help; it might not.  

- It might be that adding "timestamp" to some existing index would be
better still.

- If the table is real big, and the "ts < other_ts" doesn't restrict
things very much, then you may merely sit in between an index scan
that touches every page of the table and a Seq Scan that does the
same.

Try creating the index, and do some EXPLAIN ANALYZE queries to see
what happens; that should give you an idea as to how effective this
is.
-- 
output = ("cbbrowne" "@" "libertyrms.info")

Christopher Browne
(416) 646 3304 x124 (land)

---(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] Calc

[Josh Berkus]

There have been several discussions on running totals on this list over the 
last couple of weeks.  See the archives for possible solutions.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] SQL function to validate money input

[Zhao, Scott]

Title: Message



Hi 
All,
 
Is there a function 
available to validate if the input is a correct money format? For 
exapmle, "23.56" is a correct number but "23.567" is incorrect. I just like to 
do this in postgres level not in my application code.
 
Thanks 

 
Scott