check date validity

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.

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.

--- 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

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:

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

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