> > A user was setup and had GRANT permissions on individual tables in a database.
>After the user was dropped, using the /z from psql command line showed the user ID
>still existing in the individual table permissions. After the drop, several other
>users then were unable to execute SELECT o
...
> When specifying a column data type as timestamp it is always converted to
>timestamptz.
That is true for 7.2.x and earlier, but 7.3 will have the defaults
reversed.
In the meantime, use explicit WITH TIME ZONE and WITHOUT TIME ZONE
clauses on the declaration.
- Thomas
> timestamp(current_date, current_time)
> does work under 7.1 but under 7.2 there is an parser error!
Please check the release notes and the SQL standard for why you can not
call a function timestamp() directly; the syntax is now reserved for
specifying a data type, not a function call.
For th
> ... it should be possible to enter 'time' fields as "10" for 10:00:00.
Try prepending a "T" to the front of the string (also allowed per
ISO-8601). Will be fixed in the next release to allow the number-only
form.
- Thomas
---(end of broadcast)--
...
> Ok, so how should things work, then? While I agree that SQL92's spec
> is awkward and limited, we'd need a pretty good argument for breaking
> standards. Oliver is already wearing me down in this regard.
Well, the standard sucks ;)
My reference on this is Date and Darwen (I think that D
> > I've opined several times that interval should account for three
> > separate units: months, days, and seconds. But our time-meister
> > Tom Lockhart doesn't seem to have taken any interest in the idea.
I have taken an interest in the idea. But have not implemented it and
have not concluded
...
> This previously worked but now it will only work if I put some sort of
> expression in the string (ie a % at the end)...
I am guessing that you have some blanks or other characters at the end
of the string. Simple test cases here do not show a problem.
- Thomas
--
> A select statement with a where clause on a numeric column tested
> for equality against null always return empty result.
This is correct behavior per SQL9x standards. NULL is *not* a value, but
represents the absense of information so tests FALSE in comparisons of
any kind.
hth
...
> I don't really consider this a bug; at least, fixing it would imply not
> const-simplifying the result expressions of CASEs, which is a cure far
> worse than the disease IMHO. Does anyone think we *should* allow CASE
> to defeat const-simplification?
No. Constant-folding during parsing sho
> This is the bug report against glibc that prompted the change:
>
>http://bugs.gnu.org/cgi-bin/gnatsweb.pl?cmd=view%20audit-trail&database=default&pr=2738
> |> Ah, but this might explain why I've always seen on my Linux box a 1
> |> second offset returned from mktime() for dates before 1970. Eve
> > I don't think that our code checks explicitly for a "-1" return, since
> > the range is checked just before the call, but it would probably be a
> > good idea if it did
> As I noticd yesterday, glibc's mktime() has in the current snapshot
> been changed to return -1 for dates before the epoch.
...
> Good call Tom. ... I'm going to file a PR w/ FreeBSD. I know the
> attached patch is something of a hack, but it works. I'm not totally
> wild about altering the original time object, but I don't know that I
> have a choice in this case. Does anyone switch timezones and only
> adjust th
> Looks like it's a "bug" in mktime() on FreeBSD: it doesn't seem to do
> so well with invalid times that happen between daylight savings
> time... or is that a postgres thing for not kicking up an error (out
> of bounds time)? Or should 2am PST be converted to 3am? -sc
Here is the man page on
> $ uname -a
> FreeBSD ninja1.internal 4.5-STABLE FreeBSD 4.5-STABLE #0: Fri Apr 5 18:08:12 PST
>2002 [EMAIL PROTECTED]:/opt/obj/opt/src/sys/NINJA i386
> $ psql
> # SELECT timestamp '2002-4-7 2:0:0.0';
> timestamptz
>
> 2036-06-02 22:57:08-07
> # SELECT versi
> date/time storage problem: timestamp parsed incorrectly...
> It looks like a bad parser or defaults for time values. The example code below
>explains the problem best. I'm not sure why, or where... but it took me about a day
>to track down (PostgreSQL is never wrong!). If I include a timezo
> osk=> select age('2001-03-29','2001-03-01');
> ---
> 27 days 23:00
> osk=> select age('2002-03-29','2002-03-01');
> ---
> 1 mon
> osk=> select version();
> PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
...
> look at the difference betwen results of execution fu
> Sample Code
> SELECT time('10:10');
Read the release notes to see that this is no longer allowed, to allow
other SQL9x features to be implemented.
- Thomas
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
> timestamp(timestamp('a timestamp)) no longer works
> I do this reasonably often in my code by way of being paranoid
> that I might have a date, or a time, where I for sure _really_
> want it to be a timestamp...
> pcnz=# select timestamp('2002-03-01'::timestamp);
> ERROR: parser: parse error at
> In a transaction until you commit your transaction through a jdbc
> connection, each time you use the Current_timestamp (or 'now'), the
> timestamps is always the same, and when your transaction is very long you
> have undesirable effect.
This is not a bug, but the behavior required by SQL9x af
> Just discovered that column::date works fine but to_date(column, 'DD.MM.YY')
> causes the problem. I was trying to write portable SQL, but never mind!
Portable (as in SQL9x) would be
cast(column as date)
which is also accepted by PostgreSQL...
- Thomas
---
> Is this a bug?
> > Am loading date fields from text in one table to date in another.
> > Format of the text dates is 'DD.MM.YY', so that's the format mask
> > I use. Dates for 2001 work OK - '02.09.01' translates as
> > '2001-09-02', but '02.09.00' translates to '0001-09-02 BC'!
> > The y2k.htm
> Could you please tell me how to set DateStyle? or where can I get the
> instruction about it?
In the reference page docs on your machine or at:
http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-set.html
I'm not sure I agree with every nuance of the recommendations for SET
DATES
...
> Yes, it works!
> But now postgres accepts input and returns output
> in GMT, not local time like before! Is it a feature?
This strategy will not work in general unless you *do* set the time zone
to GMT (if it works at one boundary, say in the fall, then it will fail
at the other boundary in
> MACADDR type & 00:00:00:00:00:00
> postgresql version 7.1.3, compiled with gcc 2.95.2 on solaris 2.6, running on
>solaris 2.8.
...
> provisioning=> select macaddr '00:00:00:00:00:00';
> it's not null, so shouldn't it be retrieving as '00:00:00:00:00:00'?
> not that i ever expect to encounter th
...
> Here again the column cannot be named without using quotes. But I'd
> see issuing a warning about it as unwarranted nannyism.
Yup. I'm not suggesting a change, but the more explicit discussion on
*why* this may be acceptable is probably helpful. I think we are pretty
close in 'tude on the
> > avi=> create view z as select x as user from y;
> > My expected result: an error message in CREATE VIEW
> This is not a bug, it is intentional behavior. "AS" names are not
> restricted to be non-reserved words.
Sure, but his point is that the resulting view has a column which is
named "use
> I've seen a few postings in multiple newsgroups saying that in 7.1.x and
> up, literals in SQL statements are implicitly cast to strings.
In some contexts, that statement is true, yes. The cases where this is
true is when the parser is trying to match literals with available
function calls. If
> ... I'd be sorely tempted to replace all three by a single
> function that takes integer and returns numeric.
Yikes. Although numeric is theoretically nice, it is hundreds of times
slower than native doubles. We've already moved it in to some of the
other aggregate math functions without much d
...
> Is there no way to get this working?
Sure there is. The code in 7.1.x needs to have USE_AUSTRALIAN_RULES
evaluate to "true" (there is an #if, not #ifdef), so you should set it
equal to one (rather than just have it exist).
I think that "-DUSE_AUSTRALIAN_RULES=1" should work; you can set it
> \h select in psql says (among other things)
> [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
> I think it should read
> [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
I agree. Bruce ("Mr. Backslash" :) are you planning on picking this up?
- Thomas
-
...
> I think that if it actually reused them instead of deleting old files...
That is in fact what it does for at least the upcoming 7.2 release.
- Thomas
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
htt
> > Hmm. The underlying math works:
...
> "Works" is in the eye of the beholder, perhaps.
Of course ;)
> ... I would think that the
> difference of two times should be an interval (which would allow the
> sum() to work, since we have sum(interval)). But there is no
> time-minus-time operator.
...
> I think we have agreed that 'current' is a Bad Idea and should be
> eliminated from the date/time datatypes...
I've started purging it from the timestamp code I'm working on for 7.2.
Should be gone by the start of beta...
- Thomas
---(end of br
> timetest=# select sum(timeend-timebegin) as totaltime from timetest;
> ERROR: Unable to select an aggregate function sum(time)
Hmm. The underlying math works:
lockhart=# select time '10:01' - time '10:00';
--
00:01:00
lockhart=# select sum(time '10:01' - time '10:00');
ERROR: Unab
> Ok, I now understand more about how Postgres handles these older dates but...
It isn't quite as nice and clean as we'd like. For example, the cutoff
dates for assuming that time zone is correctly supported are arbitrarily
chosen to be near the Unix system time range boundaries of 1901 and
2038,
> when comparing timestamps i always get
> ERROR: Bad timestamp external representation 'm'
> this works:
> select timestamp (content) from factversionelement where factid=100311 and
>elementname='newsdate';
>
> 2010-01-02 16:00:00+01
> this does not:
> select factid fr
> I'm having this bug ramdonly, some time with a "vacuum analyze" goes
> away but not always
Likely not related at all.
> obelix3=> set datestyle=postgres,us;
> SET VARIABLE
> obelix3=> select * from orden_visita where fecha_emision <= 'today'::date;
> numero | corredor | ejecutivo | remoto
> Short Description
> DATE_PART() BUG? We have an SQL statement that is giving wrong output.
> tbs=# SELECT version();
> -
> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
> Here is a simple example:
> SELECT DATE_PART('DAY
> > In 7.0.3, I'm converting seconds from midnight to a time of
> > day using interval ( 'seconds '). This worked fine in
> > 7.0.3 and gives a Bad interval external representation message
> > in 7.1.
> This has never been the intended or documented format; it should
> be ' seconds'. Allowing bo
> ... (though the regression tests perhaps
> deserve a share of the blame too, for not covering that case).
> Patch to appear shortly...
Will the patch include a case for the regression test? Or could someone
(other than me??!!) volunteer to cover that?
- Thomas
> When I execute the following query:
> SELECT DISTINCT title FROM division ORDER BY UPPER(title);
> I get:
> ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list
> If I remove DISTINCT, the query works fine.
> Is this illegal or a known bug?
Illegal. I believe that allowi
> > Yes it does, for ambiguous cases such as yours.
> Which means that independent of the date style, it should give a date error
> either way?
No, it means that for ambiguous cases (e.g. '2-10-1997') it will assume
European or US conventions were used. It will rarely reject a date on
grounds of
(cc'd back to the -bugs list)
> I'm truly sorry about this, shouldn't have posted that bug report, since it
> was me, after a little research.
OK, thanks for following up!
> I run the Mandrake 7.1 distribution, which was equipped with PostgreSQL 6.5,
> or so I believe. I then upgraded to 7.0.3,
> Dates and times (and timestamps) don't behave as expected
...
> For the most part, I get wrong results when using timestamps
> and other date or time types and functions.
You MUST have more details here, since your vague problem description
simply cannot be attributed to PostgreSQL itself.
Fo
> Automatically thinks that the last value is a US style date.
> Date style is set to EURO, but I assume this has no affect on the date
> parsing at insert time.
Yes it does, for ambiguous cases such as yours.
> If the dates are entered as 'ccyy.mm.dd' it is okay - unfortunately all my
> dates a
> Using postgresql 6.5.2 on RedHat linux 6.1. Getting the following
> error while creating function:
> ERROR: Procedures cannot take more than 8 arguments
> Any pointers? Do not tell me to go for version 7. I may not have that
> time to go for a change.
You have two choices:
1) do the Thing Whi
> > While running an ODBC test suite against PostgreSQL 7.1, the test
> > software reported that the ODBC command locate("", "") failed.
> > It expected the result to be zero and it got a 1.
> Evidently your ODBC test suite hasn't read the spec. See 4.2.2.2 in
> either SQL92 or SQL99:
>
> Any ideas on how to fix this so that I can use c++ functions compiled with g++
> in the PL/pgSQL extensions?
To implement function overloading, g++ (and other c++ compilers) use
"name mangling" to allow functions and methods with the same name but
with different arguments to be handled by stand
> Long Description
> I use expression like int4(reltime()) to get interval length in
>seconds. When is NULL backend crashes (psql session):
This is likely fixed in the upcoming 7.1, since NULL handling has been
greatly improved for "by value" types like reltime. However, you *could*
be doing th
> When postgresql converts string representation of date to timestamp, he make one
>minute back error. Any insert or update with string date representation damage real
>value of date!
> BUT if you use string date representation without time part, your data will be
>shifted one day back!!!
> Tes
> Short Description
> date_part returns wrong day for 1974-04-28
> Long Description
> The date_part function is returning the wrong value for the date 1974-04-28. It
>seems to work with other similar dates (+/- a day, +/- a year).
> Feel free to contact me for more information.
You are likely r
> > Have you tried to use "date 'today'" rather than "now()"? As in
> > select to_char(date 'today' - 1, '-MM-DD');
> That will still fail in 7.0.* when the selected date is a DST transition
> day, because of the bug in date-to-timestamp conversion (which will
> happen at the input to to_cha
> It's April 3 and I'm developing an update routine to maintain expired
> records, some of which expired on April 1. When these records didn't get
> updated, I investigated and identified the alleged bug (which is
> potentially devastating based on date intensive calculations in financial
> appli
Tom Lane wrote:
>
> Thomas Lockhart <[EMAIL PROTECTED]> writes:
> >> --- ./results/horology.out Mon Apr 2 17:06:59 2001
> >> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
> >> 03:31:00-08
&g
> > The RELTIME function is miscalculating dates.
> > (all my graphs were wrong today!)
Just an aside: INTERVAL is the preferred type for, uh, intervals.
RELTIME is used internally for historical reasons. In particular,
INTERVAL maintains the distinction between qualitative units such as
months a
> How can I "binds" PostgreSQL to consider '' as null ?
You can modify src/backend/utils/{datetime,date,timestamp}.c to accept
an empty string as a null value. But imho it is better to fix this in
your input file, perhaps using sed:
sed "s/''/NULL/g" < informix.dump > pg.dump
> I'm using postgres 7.0.2. When I use date_part('day', date) sometimes I
> get wrong values. Ie:
> and date_part('day', '1999-3-28')=27
> and date_part('day', '2000-3-26')=25
> Is it a bug?
It is a problem evaluating day numbers around daylight savings time
transitions. Fixed (I believe) in the
> ERROR: Attribute 'a' is of type 'f' but expression is of type 'int4'
> You will need to rewrite or cast the expression
> Then I tried the next insert statement and the backend crashed.
...
> Sample Code
> create type f (input = int4in, output=int4out, internallength=4);
> create table
There is no bug. You want case-insensitive pattern matching, which is
not what your call to "tolower()" accomplishes.
The query
SELECT company from user_tbl where company like lower('%SEaN%');
will find all strings which contain "sean" (note case!). It will not
match strings containing "Sean"
> Long Description
> Mandrake 7.2 Postgresql 7.0.2 glibc 2.1.3-18.
> If you have a field type timestamp and if you try to insert this value in that field
>"01/01/2001 10:10:00.00 CET" or a value with the seconds part to "00" postgresql
>transform that value in "60".
> So if you try to select the
> I am getting output that doesn't make sense from a simple SQL statement.
> I am expecting to get a '0'::text returned by this statement, but
> instead I'm getting very, very small numbers, or other weird things.
> It's a catch for NULL values and isn't interpreting them correctly when
> it gets
> now if I delete de database dbname1 and try to create the database dbname2
> it works.
Your hard disk is full??
- Thomas
> I have configured and made postgresql according to its INSTALL
> instructions on a RedHat 7 pc system.
> I have added the appropriate line in /etc/ld.so.conf as suggested
> pointing to the lib directory under the postgresql installation.
You need to run ldconfig to actually update the shared li
> No, the behavior is not platform-specific. I'm on HP-PA:
Hmm. Don't see that on my Linux box :(
We don't have regression tests which cover this case?
> The problem is that we round the fractional seconds part to two digits
> only after we've separated seconds from the other fields. (I imagi
> > It seems that there is a problem when retrieving a timestamp value (rounding).
> > NO minute has 61 seconds. Am I wrong?
> > radius=# select timestamp 'Tue 23 Jan 21:38:59.997 2001';
> > -
> > Tue 23 Jan 21:38:60.00 2001 ART
> Actually, such leap-seconds are p
> I get the right answer from current sources, and the wrong one on 7.0.2.
> It's ye olde date-to-timestamp-off-an-hour-at-DST-boundary problem.
> Are you not running in a USA timezone?
Oh right. I'm usually in GMT, which is a bad place to test time zone
behavior, eh?
- Tho
> Query to reproduce the bug (7.0.3):
> edge=# select date_part('dow','4/1/2001'::date)::int4;
> --
> 6
> Clearly, the first of April ... is not on Saturday ... (and my app) was displaying
>April
> 2001 wrong.
I'm not seeing this on my 7.0.2 RPM installation, or on my from-cvs
cu
> ... Should we print the "days" +/-
> sign only when it is not the same as the "year" sign? Seems like the
> way to go.
It isn't quite that easy, but you have the right idea. My new code omits
signs unless there is a negative field value, then all subsequent fields
are signed.
That seems to be
> Secondly, we have a problem with interval math, detailed below. Should
> be reproducible on any platform(?)
> Version: 7.0.2 (is it fixed in the latest?)
Yes, but may need a little more fixup...
> =# select '2 years ago'::interval + '1 year 360 days 00:00'::interval;
>?column?
> -
> I tried to use function date_part('quarter', date) in query and I have
> got wrong results...
Yeesh! Don't know what I was thinking at the time, but it sure is wrong.
If you have PostgreSQL built from source, then you can fix this by
editing src/backend/utils/timestamp.c on or around line 2056.
> I tried to use function date_part('quarter', date) in query and I have
> got wrong results...
Yeesh! Don't know what I was thinking at the time, but it sure is wrong.
If you have PostgreSQL built from source, then you can fix this by
editing src/backend/utils/timestamp.c on or around line 2056.
> Compiler used (example: gcc 2.8.0) : RPM for Mandrake Linux
...
> When inserting timestamps or interval types whose precision is one full
> minute, Postgres substracts one minute from the data and add 60 seconds
> (00:01:00 becomes 00:00:60). Later, when reading this data Postgres s
> It works for me:
> regression=# select t1.*, t2.* from t1 natural left outer join t2;
> id | id2 | id
> +-+
> 1 | 1 | 1
My recollection is that SQL9x requires that the join result lose the
link to the original table names. That is,
select id, id2 from t1 natural left outer
> > gcc generated a fatal error message.
> > gcc: Internal compiler error: program cc1 got fatal signal 11 (segv)
> > Can you help?
> You're complaining to the wrong people. See the gcc documentation for
> their bug-reporting address.
I've built PostgreSQL with egcs and gcc-2.95 over and over an
[EMAIL PROTECTED] wrote:
>
> Gavin Evans ([EMAIL PROTECTED]) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> Aggregate Function (AVG) not calculated correctly
>
> Long Description
> Using the following code:
> SELECT airline_code, AVG(dis
> Odd. I'm using 4.0 here without any difficulty, and I don't recall
> anyone reporting similar problems with recent readlines (I was afraid
> you had a 2.something version...). Has anyone else seen a problem with
> 4.0.1?
We just ran across a problem with 3.x vs 4.x libraries when installing
a
> I don't worry, we have to_char/date already better than original
> Oracle's to_char() :-)
:)
Yes, and you'll find that the code will settle down and need very little
attention from here on. Our other date/time code has been around for 3
or 4 years now, and goes months without anyone even aski
(Sorry for diving in late; I was out of town the last few days)
> > This case I *would* have expected to produce 1 BC, but nope...
> Where is *guarantee* that the year is 4-digits?!
There is no guarantee of only four digits, but there is a convention
that two digit years refer to the current/pr
> > If you take any other month than October, it is working fine. So
> > 09-01-2000 + 1 month => 10-01-2000.
> regression=# select ('10-01-2000'::timestamp + ('1 month')::timespan);
> ?column?
>
> 2000-10-31 23:00:00-05
> (1 row)
> Thomas, isn't the addition of '1
> I suggest a little bit more investigation and testing before publishing
> rpm packages, a rpm package must be installable unless it cannot find a
> required package. if this is the case, then this package must be
> available. Any other behavior is just a joke. Sorry about that, just my
> 0.02$.
> Just to let you know it looks like ver 7.0 does not run on SuSE7.0 SMP
> Kernel. Has this been fixed on 7.02 at all?
As Peter asked, we need more info to help you. But fwiw, I've been
running on SMP linux for several years with no problems. SuSE will work
just fine too, so keep at it ;)
> PL/pgSQL functions:
> if one of parameters is NULL, all another parameters
> take NULL values regardless of transmitted NOT NULL values.
This was true in previous releases, but v7.1 will have this behavior
changed to allow passing a mix of NULL and non-NULL values to
subroutines.
> Here is a debug (level 2) output - does this help any
> more?... If not, what should I provide for you in terms of
> debugging?...
Hmm. I didn't find the update/select combination you specified in your
problem statement in your debugging output, but it wouldn't have likely
helped anywa
> Short Description
> Updating multiple bool values crashes backend
I cannot reproduce this example with 7.0.2 on my Linux-2.2.16 laptop. We
will need more details and a reproducible example to help out...
- Thomas
> to_char gives incorrect day conversion of the date 2000/03/26.
This is fixed in the current (development) sources.
- Thomas
> When I am trying to connect to my remote database kenyellow pages i.e.
> psql -h colo.kenyaweb.com -p 5432 kenyellowpages, am getting this error
> psql: ERROR: MultiByte strings (MB) must be enabled to use this function
I get this symptom when mixing a 7.0.x client with a 6.5.x server.
> I cannot change the format of the date style with set
> ... I want to use the European format i.e : DD/MM/ and not /MM/DD...
> NOTICE: DateStyle is ISO with European conventions
> SHOW VARIABLE
The ISO style is /MM/DD. The "European conventions" in this case
only affect the interpr
> Seems like you could just skip step 3 and call localtime() with fields
> indicating midnight of the specified date. Then use the complete
> localtime result (don't discard any fields) and you should be OK, no?
OK, I have a solution which involves mktime(). As a side effect, I've
stripped some
> Seems like you could just skip step 3 and call localtime() with fields
> indicating midnight of the specified date. Then use the complete
> localtime result (don't discard any fields) and you should be OK, no?
Pretty sure this won't work, since the complete localtime result will
not be local m
> >> Looks to me like an off-by-one kind of problem in deciding which
> >> timezone applies to midnight of a transition day.
> > The date->timestamp conversion code gets this right, so you might want
> > to look at that.
> Au contraire: the cited examples appear to prove that the
> date->timestamp
> > it's not date_part() bug, it's to_date() bug:
> Looks to me like an off-by-one kind of problem in deciding which
> timezone applies to midnight of a transition day.
Probably a bit worse (but no problem to solve ;): you need to make sure
that you rotate the date type to the correct time z
> Is this patch based on the current snapshot? I have applied several
> unixODBC patches in the past several days that may already fix these
> problems.
> > This is a report on my attempts to install unixODBC (1.8.9)
afaik, unixODBC != psqlODBC, though they have common roots. Is that
actually th
92 matches
Mail list logo
