I just discovered that to_date() function does not check if supplied
date is correct, giving surprising (at least for me) results:
fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
to_date
2003-12-01
or even
fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
to_date
---
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> As far as I know these results are correct in terms of the underlying
> C-library function mktime(). This function is intended to be used when
> adding/subtracting intervals from a given timestamp.
Which one? mktime() or to_dat
On Tue, Dec 02, 2003 at 10:57:14AM +0100, Karel Zak wrote:
> > > If not, has something changed in 7.4?
>
> No change in 7.4. Maybe in 7.5 or in some 7.4.x.
Well, let's see.
> The others PostgreSQL stuff which full parse (means check ranges)
> date/time is less optimistic with this:
>
> # se
On Tue, Dec 02, 2003 at 07:44:54PM +0900, Iain wrote:
> T've been following this thread with interest because I have a related
> problem. Basically we are storing dates in CHAR fields with al the
> associated problems. I'd like to do it, but changing everything to date
> fields isn't practical for
On Tue, Dec 02, 2003 at 01:55:06PM +0300, Alexander M. Pravking wrote:
> Try using check (VALUE::text::date).
Assuming check expects boolean result, it's (VALUE::text::date IS NOT NULL)
--
Fduch M. Pravking
---(end of broadcast)---
TIP
On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
> date
>
> 2003-10-26
> (1 row)
Try using '2003-10-26 0:00:00'::date + 1;
integ
Dear SQL and plPgSQL gurus, I seek for your wisdom.
I have a variable assignment via SELECT INTO in my function, and I want
to separate 3 cases:
1) no rows found;
2) one row found;
3) many rows found (ambiguous select).
The first case is simple, I use FOUND variable for it.
But how about the thir
Hello, PostgreSQL users and developers.
I've got a memory usage problem when I try to do a subselect on the same
table as the main select (PostgreSQL 7.3.4 on FreeBSD). Here's my query:
SELECT sreq(s1.id, 'ipacct_ip', now()), s1.*
FROMservices s1
WHERE EXISTS (
SELECT 1
On Sun, May 23, 2004 at 12:28:18PM -0400, Tom Lane wrote:
> "Alexander M. Pravking" <[EMAIL PROTECTED]> writes:
> > I've got a memory usage problem when I try to do a subselect on the same
> > table as the main select (PostgreSQL 7.3.4 on FreeBSD). Here's m
On Mon, Jun 14, 2004 at 01:20:14PM -0300, Eric Lemes wrote:
> Hello,
>
> - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
> - Timezone: Brazil (GMT-3, I think).
What's about daylight saving time for you?
I'm almost sure the DST boundary is near the date in your example.
However, with 7.3.4 on F
On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote:
> Now you treat NULLs as false.
Nope. NULL is neither true, nor false. It's "unknown", or "undefined".
fduch=# SELECT 1 WHERE NULL::boolean;
?column?
--
(0 rows)
fduch=# SELECT 1 WHERE NOT NULL::boolean;
?column?
--
(0 rows)
I was looking for how can I give an alias for the table being updated
(something like UPDATE table_name table_alias SET ...), but the current
syntax doesn't allow that.
What I need is to:
fduch=# SELECT * from test order by typ, name;
typ | name | x
-+--+---
1 | bar |
1 | baz |
On Tue, Jun 29, 2004 at 12:49:55PM -0400, Tom Lane wrote:
> > So I have two questions:
> > Q1, cognitive. Why the alias for the updated table is restricted?
>
> Because the SQL standard doesn't allow an alias there. We've talked
> about allowing one anyway, but no one's gotten around to it. AFAI
I was impressed that LIKE operator can be indexed in 7.4 with non-C
locale. But how about the reverse condition?
What I need is to:
SELECT * FROM prefixes WHERE 'literal' LIKE prefix || '%';
or
SELECT * FROM prefixes WHERE 'literal' ~ ('^' || prefix);
Prefix is of type text (variable-length), whi
On Mon, Jul 19, 2004 at 10:00:50AM +0100, Luis Sousa wrote:
> I worked around this problem returning the difference between the two
> dates, using extract doy from both.
> Anyway, this will cause a bug on my code when changing the year. Any ideas?
Why don't you use the minus operator?
SELECT '20
On Tue, Sep 28, 2004 at 03:02:02PM +0100, T E Schmitz wrote:
> Hello,
>
> Is it possible to set up a table CHECK, which ensures that column A is
> NOT NULL if column B = 'x' ?
Sure.
[EMAIL PROTECTED] CREATE TABLE test (
[EMAIL PROTECTED](# a integer check (case when b = 'x' then a is not null e
It looks a bit strange that CIDR output depends on datatype it has been
casted from:
fduch=# SELECT '1.1.1.1'::cidr;
cidr
1.1.1.1/32
(1 row)
fduch=# SELECT '1.1.1.1'::inet::cidr;
cidr
-
1.1.1.1
(1 row)
However these two seem to be 'equal' in terms of backend:
fduch
On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote:
> Is the int4 a UNIX epoch? ie. seconds since 1970?
>
> If so, then this will generally work:
>
> SELECT CAST(int4field AS abstime);
>
> or
>
> SELECT int4field::abstime;
http://www.postgresql.org/idocs/index.php?datatyp
On Tue, Aug 13, 2002 at 03:14:38PM +0800, Christopher Kings-Lynne wrote:
> > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says:
> >
> > The types abstime and reltime are lower precision types which are used
> > internally. You are discouraged from using any of these types in
How can I obtain results from an EXECUTE statement
within a pl/PgSQL function?
E.g., something like
value := EXECUTE ''SELECT '' || quote_ident(field_name) || '' FROM ...'';
Thanks in advice.
--
Fduch M. Pravking
---(end of broadcast)---
TIP 3:
On Fri, Nov 01, 2002 at 10:48:00PM -0500, Bruce Momjian wrote:
> Tim, I guess your problem is dead index pages that can't be reclaimed,
> and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a
> /contrib/reindexdb script in 7.3.
As I see, contrib/reindexdb requires perl for commandline
p
On Sat, Nov 02, 2002 at 08:20:04PM -0500, Bruce Momjian wrote:
> Alexander M. Pravking wrote:
> > As I see, contrib/reindexdb requires perl for commandline
> > procesing. I don't think it's a good idea, since
> > e.g. FreeBSD 5.0-CURRENT have no perl in standard dis
I've met the following problem.
I had successfully written a function divide_into_days(timestamp, timestamp)
which returns setof (timestamp, timestamp) pairs - a list of days the
given interval is divided into.
What I want is to use each record from resultset to pass to another
function, something
On Sun, Aug 17, 2003 at 11:32:58AM +0100, Richard Huxton wrote:
> I just had a look at EXPLAIN ANALYSE output for an SQL function I'm trying to
> write. The WHERE clause wasn't too complex, but the explain output took up a
> dozen lines.
>
> Boiling the problem down, I've looked at a clause of t
On Fri, Sep 19, 2003 at 01:54:01PM +0200, Andreas Joseph Krogh wrote:
> I usually backup my database with pg_backup without the -t option. But now I
> need to only backup certain tables(say tab1 and tab2), is this possible with
> pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success.
On Fri, Sep 19, 2003 at 04:30:57PM +0200, Andreas Joseph Krogh wrote:
> > Here's a perl script I used to dump all the tables separately.
> > I'm not sure most of options do work there, I didn't test ;-)
> > It won't be hard to make it dump certain tables, I think.
>
> Thanks for your suggestion, b
I noted that such a function returns an empty rowset if a NULL value is
passed as an argument. Is it a bug or feature? I wish it was a feature,
because I probably want to use this behavour.
Here's an example:
CREATE TYPE ts_bounds AS (
sdate timestamptz,
edate timestamptz
);
On Thu, Nov 13, 2003 at 05:14:27PM +, Richard Huxton wrote:
> RETURNS NULL ON NULL INPUT or STRICT indicates that the function always
> returns NULL whenever any of its arguments are NULL. If this parameter is
> specified, the function is not executed when there are NULL arguments;
> instead
On Thu, Nov 13, 2003 at 12:27:58PM -0500, Tom Lane wrote:
> "Alexander M. Pravking" <[EMAIL PROTECTED]> writes:
> > Does "NULL result" mean an empty rowset if the function returns a record?
>
> No, it means a null record. "Empty rowset" w
On Thu, Nov 13, 2003 at 12:35:41PM -0500, Tom Lane wrote:
> "Alexander M. Pravking" <[EMAIL PROTECTED]> writes:
> > Very well then... Can I return a null record from such function
> > explicitly? Sorry, I could't find it anywhere in docs or examples.
>
>
30 matches
Mail list logo
