[SQL] Re: [HACKERS] [Fwd: Optimization in C]
> This solution isn't good when there are +1 tuples in the table, it's > slowly... anybody can help me ? : Someone already responded, and asked some questions about what you are really trying to do. If you didn't get the message, let us know or check the mail archives. Regards. - Thomas
Re: [SQL] 7.0.3 BUG
> >> ERROR: copy: line 3910, Bad timestamp external representation > >> '2000-01-05 00:00:60.00+08' > >> Weird because those timestamps were generated by default now(). ... > Is there a work-around to this aside from manually changing the dump file? > Distribution Version: Linux Mandrake release 7.2 (Odyssey) for > i586 > It was shipped with Mandrake-Linux 7.2 > >> migrate=# select version(); > >> version > >> --- > >> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.3 ... > We can be sure that the compiler is relatively bug free because it was > used to recompile the entire Linux distribution... Ah ha (or rather, ha ha ha)! I'd suggest using the RPMs posted on the postgresql.org ftp site, which include a sample .rpmrc file which fixes disasterous bugs in Mandrake's default compiler settings for building RPMs. Specifically, Mandrake sets the -ffast-math flag, which the gcc folks warn is not compatible with -On optimizations. When I build RPMs I kill the fast-math option, and the rounding troubles go away. The rounding trouble does not show up on other platforms or Linux distros because no one else ignores the gcc recommendations to this extent :( - Thomas
[SQL] Re: [INTERFACES] outer join in PostgreSql
Mauricio Hipp Werner wrote: > > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? The PostgreSQL outer join is accomplished using SQL92 syntax. You will not find real outer joins *except* in the current beta release, and beware that there may be some tweaks to the grammar to help with conformance to the standard. In any case, check the standard or try something like "select * from t1 left outer join t2 on (i)".
[HACKERS] Re: How to modify type in table?
>I am compiling postgresql 7.1beta4. How would i change the 8k row > limit? There is no 8k row limit anymore. But if there were, you would modify a #define in the source code and recompile (check the docs for more details). - Thomas
Re: [HACKERS] why the DB file size does not reduce when 'delete' the data in DB?
> I had deleted a very large number of records out of my SQL table in order to > decrease the harddisk space. But after I use command 'ls -l > /usr/local/pgsql/data/base/', it is found that the size of concerning files > do not reduce due to the effect of 'delete' SQL command. What should I do > if I would like to decrease the harddisk space? Run "vacuum" from SQL or "vacuumdb" from the command line. Tables will be reduced in size, though currently indices are not. - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: [SQL] Re: why the DB file size does not reduce when 'delete'the data in DB?
> do you really know the problem of PGSQL storage manager? it DOES NOT > reuse deleted record space. it also grows database size when you just > update but not insert record. it is a MS ACCESS like storage manager. > it is a functional bug. there is logic bug, performance bug... imho a designed-in feature can not be called a bug, even if you disagree with its intent or implementation. The term "bug" should be reserved for code which does not behave as designed. You are not quite factually correct above, even given your definition of "bug". PostgreSQL does reuse deleted record space, but requires an explicit maintenance step to do this. We have continuing discussions on how to evolve the performance and behavior of PostgreSQL, and you can check the archives on these past discussions. Regards. - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
> I'll take a stab at in in PLPGSQL and post the results. OK. date_part() is your friend ;) - Thomas ---(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] No Documentation for to_char(INTERVAL, mask)
> > Given the lack of to_char(interval), I'd like to write a PLPGSQL > > function to fill the gap in the meantime... > I mean is too much difficult write a 'interval' to_char() version in > some procedural language without access to real (internal) form of > 'interval'. I agree with Karel's point that it may be a pain to use a procedural language to manipulate a "stringy" interval value. If you use a C function instead, you can get access to the internal manipulation functions already present, as well as access to system functions to manipulate a tm structure. A combination of contrib/ and src/backend/utils/adt/ information could give you a start on the C implementation (and that is rather easily moved into the backend later). I haven't tried the PL/PGSQL approach however. If you decide to proceed on that, let us know how it goes! - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [PATCHES] Error in the date field (with NULL value...).Thanks!
> 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
- Thomas
---(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: [HACKERS] Re: [SQL] aliases break my query
> At one time Bruce had made some patches to emit informative notice > messages about implicit FROM entries, but that got turned off again > for reasons that I forget... It was triggered with common cases from the "outer join" syntax. It took a while to track down since it was introduced while I was working on the syntax feature :( If it *really* needs to be put back in, then we should do so with a flag so we can disable the warning at compile time, run time, and/or in the outer join parser area. But imho sprinkling the parser with warnings for allowed syntax is heading the wrong direction. If it is legal, allow it. If it is illegal, disallow it. If it is confusing for some, but works fine for others, it shouldn't become "sort of legal" with a warning. - Thomas
Re: [SQL] JOIN syntax
> Looking at SQL92, it seems that I ought to be able to do this:
> SELECT * FROM invoice INNER JOIN
> (SELECT * FROM invoice_line WHERE lineno > 1)
> ON invoice.invno = invoice_line.invno;
> ERROR: parser: parse error at or near "("
Wrong syntax (for Postgres anyway). We don't yet have subselects in the
syntax. Thanks for the example though; it seems like some variant should
be allowed, though perhaps not as written above.
Something like
select * from invoice inner join
(select * from invoice_line where lineno > 1)
as IL (invno, yada)
on invoice.invno = IL.invno;
seems like it could be legal. Your variant of this may have trouble
hooking up invoice_line.invno after the subselect, since the subselect
result may lose the linkage with the underlying input table.
But, all this is theoretical, since Postgres doesn't yet do the right
thing at all.
- Thomas
Re: [SQL] extract last months data
> I just want to extract last months data, but I don't know whether the
> month ended with 29,30 or 31 (external program, that uses postgres),
select * from t1
where d >= (date_trunc('month', timestamp 'today')
- interval '1 month')
and d < date_trunc('month', timestamp 'today');
- Thomas
Re: [SQL] Need Help With Dates.
> I just migrated a database from MySQL to postgreSQL and am having trouble
> wit postgres' dates.
> MySQL dealt with dates very well, but i don't see the same sort of
> functionality in postgres.
??
> The database is an archive of imformation, and i would like to do a cron'd
> select for an interval based on the date.
> I can get the current date. But i don't know how to have the computer
> properly figure out the past dates.
>
> The select format has been:
> SELECT blah FROM blah2
> WHERE date BETWEEN (past_date) and (current_date);
> This select is computed monthly.
> And i do not want to have to change the variables every month when this
> needs to run. Nor do i think that i should have to result to perl
> processing to solve this dilemma. I have tried (i think) every possible
> function and operation to try to get this to work.
It is not clear to me *exactly* what query you used to run. Were
"past_date" and "current_date" some local program variable in the MySQL
front end? How did you set them in a way which required no external
programming or variable substitution?
> The problem is trying to figure out whether an extra day should be added
> for leap years. (It obviously should, but how do i tell the computer that
> it should).
The computer already knows. How about
select * from t1 where d between
(date_trunc('month', date 'today') - interval '1 month')
and
date_trunc('month', date 'today');
There are *lots* of date/time capabilities in Postgres (if I do say so
myself ;) so I'd be suprised if you don't find what you need.
Good luck.
- Thomas
Re: [SQL] Problem with joins
> I want to get a list of data sources with the corresponding data, > if the data exists, or with null, if the data doesn't. But anyway > I need the data sources (all of them). You want an outer join. Postgres doesn't have that yet, but you can mimic it. > I have also tried: > select source_name,data_value > from source,data where data_source_id=source_id > union > select source_name,source_id,NULL from source,data > This is a bit better, in the sense that I get back all I need, but there > are too many lines: when there is data, I get the line with the data value > and also with NULL. Close. Try select source_name,data_value from source,data where data_source_id=source_id union select source_name,source_id,NULL from source where source_id not in (select data_source_id from data); - Thomas
Re: [SQL] Aww, sorry (last day of month)
> What i ment was, that I have information about workers, and I have to make
> report on them. Report should contain status about workers with the
> restriction , that the data is about last day of the month. I use perl as
> a scripting language, and how the hell should I know how many days were in
> that particular month (28,29,30,31) :). If there are no means by doing it
> in sql, I write a code to ask for a , mmm, special day. *sigh*
> Maybe something with date_trunc?
Someone else had the suggestion, but specifically something like
... where d1 >= (date_trunc('month', timestamp 'today') - interval '1
day')
and d1 < date_trunc('month', timestamp 'today');
should get you data for the previous month. Substitute the "today" field
for something else as you may require.
- Thomas
Re: [SQL] Type conversion
> select TheNumberFromConsole/(select max(division_point) from table1) Try select '1234.5678'/(select max(division_point) from table1); The quotes around the apparent floating point number keeps Postgres from assuming that it *is* a floating point number, and it later decides that it must have been a numeric() type. - Thomas
Re: [SQL] SQL question
> The immediate cause of this gripe was discussed just a day or so ago > on one or another of the pgsql lists. The timestamp-to-date conversion > routine has this weird idea that it should kick out an error instead > of returning NULL when presented with a NULL timestamp. That's a bug > IMHO, and I've already changed the code in current sources. That's not a bug, that was a feature, sort of. At least when I coded it, Postgres *refused* to call any routine with NULL input, assuming that NULL would be returned. A clever short-circuit, and the elog(ERROR) in the conversion routine was just a safety net. Because it was also the case that any routine returning a NULL pointer crashed the backend. Now that those things aren't true, we are rewriting history to say that they were bugs all along, eh? ;) - Thomas
Re: [SQL] Median
> Maybe someone else has an idea. I implemented a different algorithm several years ago. It is an O(log(N)) process (unlike most other techniques), and was borrowed from the "Algorithms" book (it's at work; but it is the classic "yellow jacket" book with Fortran code and the other volume with *really bad* C code which looks like Fortran ;) Anyway, once the bugs were fixed (and there were several :( and a better endgame algorithm was coded, it was suitable for hypercube distributed processing, where you just exchange a small amount of info between iterations. And it involved simply *counting* how many values were above and below an initial or updated guess, then iterating on a new guess. afaik that wouldn't be suitable for the existing aggregate capabilities, but multi-pass algorithms would be nice to be able to do. - Thomas
Re: [SQL] Time Aggregates
> I'm currently doing this:
> SELECT symbol, date_trunc('minute', posted),
> min(price), max(price), avg(price)
> FROM trade
> GROUP BY symbol, date_trunc('minute', posted);
> to get a list of minute-averages of trade prices. I get the feeling
> that this is bad form, that I should be doing this some other way. Is
> that the case?
Looks OK to me. If you are doing this *a lot* (i.e. many more queries
than inserts), then you might want to set up another column which
contains date_trunc('minute',posted) to avoid the calculation. Something
like
create table trade (
symbol text,
posted timestamp,
price integer,
mpost timestamp
)
then define a rule to update mpost when posted gets set (haven't done
that part).
- Thomas
