Thread: check date validity
I need this function :
CheckDate('2002-02-29') return false
CheckDate('2002-02-28') return true
How to write ?
Thanks for any advice .
In article <20040116054046.E4E751C173283@smtp.vip.163.com>,
"LitelWang" <wlxyk@vip.163.com> writes:
> I need this function :
> CheckDate('2002-02-29') return false
> CheckDate('2002-02-28') return true
Why would you want to do that? Just try to insert '2002-02-29' into
your DATE column, and PostgreSQL will complain.
--- Harald Fuchs <hf99@protecting.net> wrote:
> In article
> <20040116054046.E4E751C173283@smtp.vip.163.com>,
> "LitelWang" <wlxyk@vip.163.com> writes:
>
> > I need this function :
> > CheckDate('2002-02-29') return false
> > CheckDate('2002-02-28') return true
>
> Why would you want to do that? Just try to insert
> '2002-02-29' into
> your DATE column, and PostgreSQL will complain.
That will cause the whole transaction to abort, which
is probably not what is wanted.
I don't know any way around this in Postgres. Best to
check this in application code.
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
Jeff Eckermann wrote:
>>>I need this function :
>>>CheckDate('2002-02-29') return false
>>>CheckDate('2002-02-28') return true
>>
>>Why would you want to do that? Just try to insert
>>'2002-02-29' into
>>your DATE column, and PostgreSQL will complain.
>
> That will cause the whole transaction to abort, which
> is probably not what is wanted.
>
> I don't know any way around this in Postgres. Best to
> check this in application code.
You could give this a try:
http://www.joeconway.com/str_validate.tar.gz
Drop in the contrib directory of a postgres source tree, untar, and then
make and install like any other contrib.
Here's some info from the README:
==================================
str_valid(text, oid) - returns true or false
Synopsis
str_valid(<string> text, <type_oid> oid)
Inputs
string
The string representing the value to be cast to a given data type
type_oid
The oid of the type to which <string> should be castable
Note: it may be convenient to use the form 'typename'::regtype to
represent the type oid.
Outputs
Returns 't' (true) if the cast will succeed, 'f' (false) if it will fail
Limitations
Currently the only supported data types are:
- date
- timestamp
- interval
Example usage
regression=# select str_valid('yesterday','timestamp'::regtype);
str_valid
-----------
t
(1 row)
regression=# select str_valid('next month','interval'::regtype);
str_valid
-----------
f
(1 row)
===================
HTH,
Joe
This may be ham handed or overkill but I had the same problem, I didn't
want to TRY to put an invalid date into my database, so I wrote
CREATE FUNCTION "rick"."f_u_is_date" (varchar) RETURNS boolean AS'
-- FUNCTION f_u_Is_Date -- assumes YYYYMMDDHHMMSS
DECLARE
av_Date ALIAS FOR $1;
li_Year SMALLINT;
li_Month SMALLINT;
li_Day SMALLINT;
li_Hour SMALLINT;
li_Minute SMALLINT;
li_Second SMALLINT;
li_Days_In_Month INTEGER[12] := ''{ 31, 28, 31, 30, 31, 30, 31, 31, 30,
31, 30, 31}'';
BEGIN
-- 1 length = 14
-- 2 all digits whitespace is FATAL!
IF av_Date !~ ''^[0-9]{14}$'' THEN
-- not 14 digits
RETURN False;
END IF;
-- 3 parse
li_Year := Cast( SubStr( av_Date, 1, 4 ) AS SMALLINT );
li_Month := Cast( SubStr( av_Date, 5, 2 ) AS SMALLINT );
li_Day := Cast( SubStr( av_Date, 7, 2 ) AS SMALLINT );
li_Hour := Cast( SubStr( av_Date, 9, 2 ) AS SMALLINT );
li_Minute := Cast( SubStr( av_Date, 11, 2 ) AS SMALLINT );
li_Second := Cast( SubStr( av_Date, 13, 2 ) AS SMALLINT );
-- test date parts in range
-- and days in a month
IF ( li_Second >= 0 ) AND ( li_Second <= 59 )
AND ( li_Minute >= 0 ) AND ( li_Minute <= 59 )
AND ( li_Hour >= 0 ) AND ( li_Hour <= 23 )
AND ( li_Day >= 1 ) AND ( li_Day <= li_Days_In_Month[ li_Month ] )
AND ( li_Month >= 1 ) AND ( li_Month <= 12 )
AND ( li_Year >= 2000 )
THEN
-- date parts in range
RETURN True;
ELSE
-- February and leap year is the only exception
IF ( li_Month = 2 )
AND ( li_Day = 29 )
AND ( ( ( Mod( li_Year, 4 ) = 0 )
OR ( Mod( li_Year, 400 ) = 0 ) )
AND ( Mod( li_Year, 100 ) <> 0 ) )
THEN
-- leap year, February has 29 days
RETURN True;
ELSE
-- date parts not in range
RETURN False;
END IF;
END IF;
RETURN True;
END; -- f_u_Is_Date
'LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
LitelWang wrote:
>I need this function :
>
>CheckDate('2002-02-29') return false
>CheckDate('2002-02-28') return true
>
>How to write ?
>
>Thanks for any advice .
>
>
>
On Fri, 2004-01-16 at 06:58, Harald Fuchs wrote:
> In article <20040116054046.E4E751C173283@smtp.vip.163.com>,
> "LitelWang" <wlxyk@vip.163.com> writes:
> > I need this function :
> > CheckDate('2002-02-29') return false
> > CheckDate('2002-02-28') return true
>
> Why would you want to do that? Just try to insert '2002-02-29' into
> your DATE column, and PostgreSQL will complain.
But it won't complain usefully. It will just abort the transaction.
It's difficult to determine what went wrong when Postgres craps out,
which is at least in part why many on this list recommend duplicating
all the database validation logic in your application for EVERY type.
To me, this seems like a waste of effort, since both the application and
the DB server have to confirm that every date (for example, but applies
to every other type as well) is valid. But I can't see how to do it any
other way, since the prevailing consensus among the PG devs seems to be
that any problem with the values of data is an application problem, not
a database problem, so don't expect to get any help from the server
other than "Sorry, that transaction is now gone. Hope you can reproduce
the work! Have a nice day."
Thanks,
b.g.
How can I get the column names of a table with sql ? Thanks in advance Alexander Antonakakis
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
On Tue, 20 Jan 2004, Alexander Antonakakis wrote:
> How can I get the column names of a table with sql ?
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%';
will work, I think.
Regards,
- --
Devrim GUNDUZ
devrim@gunduz.org devrim.gunduz@linux.org.tr
http://www.TDMSoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFADQwotl86P3SPfQ4RAo7QAKDbpCxKPhgsoMuvqYPgWIv/4Yp71ACePcd7
brSaT7Ur5cUZ9bz54bii9Qg=
=B1/n
-----END PGP SIGNATURE-----
Devrim GUNDUZ said: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > Hi, > > On Tue, 20 Jan 2004, Alexander Antonakakis wrote: > >> How can I get the column names of a table with sql ? > > SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%'; > > will work, I think. If you want COLUMNS and not TABLES and are using 7.4 then use the views provided in the information_schema. eg select column_name from information_schema.columns where table_name = 'mytable'; Look at the view, you can select all sorts of info, and filter on other criteria too. John Sidney-Woollett
in postgresql database# \d tablename regards Eric ----- Original Message ----- From: "Alexander Antonakakis" <motoris@sdf.lonestar.org> To: <pgsql-general@postgresql.org> Sent: Tuesday, January 20, 2004 7:23 AM Subject: [GENERAL] sql to get the column names of a table > How can I get the column names of a table with sql ? > Thanks in advance > > Alexander Antonakakis > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html