Re: [SQL] SQL function to validate money input
On Wednesday 15 October 2003 22:46, Zhao, Scott wrote: > 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. Well, you can always write your own, but: 1. How were you planning to use it? 2. What were you planning to do if the value provided isn't valid as money? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] indexing timestamp fields
Is it a good idea to index timestamp fields? what about date fields in general? thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] indexing timestamp fields
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 ---(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
Re: [SQL] Sorting problem
I think what you actually want is natural sorting. Ries > -Oorspronkelijk bericht- > Van: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Jean-Luc Lachance > Verzonden: woensdag 15 oktober 2003 17:43 > Aan: George A.J > CC: [EMAIL PROTECTED] > Onderwerp: Re: [SQL] Sorting problem > > > 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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] indexing timestamp fields
On Wed, Oct 15, 2003 at 21:25:17 +0100, teknokrat <[EMAIL PROTECTED]> wrote: > > 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 Unless that query returns only a small fraction of the table (which would be unusual for a before some date restriction) or you order by the date, an index probably won't help. ---(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
> 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. > If data from "View" without ID, how can I do? My View: trx_date | trx_time | descriptions| payment_method | debet | credit | creator +--+--+- ---+---+--+- 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | Visa | 3800 | | middink 2003-10-16 | 03:28:30 | Payment - Thank You | Visa | | 4.00 | middink 2003-10-08 | 18:17:40 | Payment - Thank You | Cash | | 5.00 | middink ---(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] Calc
On Thursday 16 October 2003 10:37 am, Muhyiddin A.M Hayat wrote: > > 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. > > If data from "View" without ID, how can I do? > > My View: > > trx_date | trx_time | descriptions| > payment_method | debet | credit | creator > +--+--+ >- ---+---+--+- > 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | > Visa > > | 3800 | | middink > > 2003-10-16 | 03:28:30 | Payment - Thank You | > Visa > > | | 4.00 | middink > > 2003-10-08 | 18:17:40 | Payment - Thank You | > Cash > > | | 5.00 The id field only keeps the transactions in the correct order so you can sum the previous transactions. You can do the same thing with your date and time fields (though using a single datetime field would be easier). A caution, though: you will have trouble if two transactions share the same date and time. Cheers, Steve ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Can't convert numeric to_char() in catenate function
Hi there.
Im trying to do the usual row catenation function, but for a "numeric" type column. I
have this:
CREATE OR REPLACE FUNCTION "verticat" (text, numeric) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL OR $1 =
THEN to_char($2,'9D99')
ELSE $1 || '', '' || to_char($2,'D99')
END
' LANGUAGE 'sql';
This works without problem with "float" or other type with direct casting to text
(after replacing the "to_char(...)" with $2::text), but this one gives me an "ERROR:
parser: parse error at or near "9""... wath is wrong with it??
It would also be nice to trim the spaces placed by to_char, with something like
"ltrim(' ', to_char($2,'9D99'))", but that yields yet anoter error: "ERROR:
parser: parse error at or near "'"".
Any help 'preciated. Thanks!
---(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] Can't convert numeric to_char() in catenate function
Yusuf, > CREATE OR REPLACE FUNCTION "verticat" (text, numeric) RETURNS text AS ' > SELECT CASE WHEN $2 IS NULL THEN $1 > WHEN $1 IS NULL OR $1 = > THEN to_char($2,'9D99') > ELSE $1 || '', '' || to_char($2,'D99') You need to escape your single quotes by double-quoting them, eg.: to_char($2,''D99'') -Josh Berkus Aglio Database Solutions San Francisco ---(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
[SQL] Postgres with OpenSSL
Hello, peopleware !!! I need to use Postgres with OpenSSL. The MySql have many options of Docs for this. Where is the options Docs for the Postgres with OpenSSL ? Thanks !!! SPS 2000info
Re: [SQL] Can't convert numeric to_char() in catenate function
I 'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I
'must' quote the quotes. I 'must' quote the quotes. I 'must' quote the quotes. I
'must' quote the quotes.
DANG!!
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes.
Err, sorry about that, I'm trying to keep something in my mind. I just wanted to tell
ya to forget my previous mail, I got it (tipical) just after sending it:
CREATE OR REPLACE FUNCTION "verticat" (text, numeric) RETURNS text AS '
SELECT CASE WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL OR $1 =
THEN ltrim('' '', to_char($2,''9D99''))
ELSE $1 || '', '' || ltrim('' '', to_char($2,''D99''))
END
' LANGUAGE 'sql';
Ciao.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the quotes.
I ''must'' quote the quotes. I ''must'' quote the quotes. I ''must'' quote the
quotes...
---(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] Postgres with OpenSSL
http://www.postgresql.org/docs/7.3/static/ssl-tcp.html Jordan S. Jones -- I am nothing but a poor boy. Please Donate.. https://www.paypal.com/xclick/business=list%40racistnames.com&no_note=1&tax=0¤cy_code=USD 2000info wrote: Hello, peopleware !!! I need to use Postgres with OpenSSL. The MySql have many options of Docs for this. Where is the options Docs for the Postgres with OpenSSL ? Thanks !!! SPS 2000info ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Postgres with OpenSSL
And http://www.postgresql.org/docs/7.3/static/client-authentication.html Jordan Jordan S. Jones wrote: http://www.postgresql.org/docs/7.3/static/ssl-tcp.html Jordan S. Jones -- I am nothing but a poor boy. Please Donate.. https://www.paypal.com/xclick/business=list%40racistnames.com&no_note=1&tax=0¤cy_code=USD ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Calc
> > trx_date | trx_time | descriptions| > > payment_method | debet | credit | creator > > +--+--+ > >- ---+---+--+- > > 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 | > > Visa > > > > | 3800 | | middink > > > > 2003-10-16 | 03:28:30 | Payment - Thank You | > > Visa > > > > | | 4.00 | middink > > > > 2003-10-08 | 18:17:40 | Payment - Thank You | > > Cash > > > > | | 5.00 > > The id field only keeps the transactions in the correct order so you > can sum the previous transactions. You can do the same thing with > your date and time fields (though using a single datetime field would > be easier). A caution, though: you will have trouble if two > transactions share the same date and time. if i would like to pleaced ID(Virtual ID) for example number of record for each record, how to ? num_rec | trx_date | trx_time | credit | descriptions | payment_method | debet -++--+--+--- -++--- 1 | 2003-10-09 | 21:55:02 | | Resto Biling : Rp. 13,800, Paid : Rp. 10,000 | Visa | 3800 2 | 2003-10-16 | 03:28:30 | 4.00 | Payment | Visa | 3 | 2003-10-08 | 18:17:40 | 5.00 | Payment | Cash | ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Object description at Client Window
Dear Friends, I am working with Postgres 7.3.4 on RH linux 7.2. I could get into the command prompt to describe a table structure. Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit training=# \d emp Table "emp" Column | Type | Modifiers+---+--- no | integer | name | character varying(20) | age | integer | training=# But I wanted to know whether this description could be availed at the command prompt. I am using PgAdmin3. I have checked the systems tables also. pg_tables can tell us only the table and the columns inside tables. Any idea to share with me, please. I am looking for something like sp_helptext in MS SQL server. Regards Kumar
Re: [SQL] Object description at Client Window
give psql -E a try.. It will display any internal SQL commands that it uses. Jordan S. Jones Kumar wrote: Dear Friends, I am working with Postgres 7.3.4 on RH linux 7.2. I could get into the command prompt to describe a table structure. Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit training=# \d emp Table "emp" Column | Type | Modifiers +---+--- no | integer | name | character varying(20) | age | integer | training=# But I wanted to know whether this description could be availed at the command prompt. I am using PgAdmin3. I have checked the systems tables also. pg_tables can tell us only the table and the columns inside tables. Any idea to share with me, please. I am looking for something like sp_helptext in MS SQL server. Regards Kumar -- I am nothing but a poor boy. Please Donate.. https://www.paypal.com/xclick/business=list%40racistnames.com&no_note=1&tax=0¤cy_code=USD
Re: [SQL] [GENERAL] Alias-Error
Jost Richstein <[EMAIL PROTECTED]> writes: > I am running a query with alias (a self join) against > version 7.3.4 on Suse Linux 7.3 and on FreeBSD (v5?). > It runs fine on Linux, but produces an error on > FreeBSD: "unknown alias C2". The string "unknown alias" appears nowhere in the 7.3 sources. Are you sure you are talking to a Postgres database? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
