Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables
Brent Wood wrote: Lorenzo Thurman wrote: I have three tables using date fields. I want to retrieve the oldest date contained in the tables. Can someone show me an example of a query that would do that? You could write a custom function doing the same sort of thing, or (perhaps more portable) use generic sqls views like: create view min_dates as select min(date1) from table1as date_1 union select min(date2) from table2 as date_1 union select min(date3) from table3 as date_1; then either: select min(date_1) from min_dates; or create view min_date as select min(date_1) as min_date from min_dates; then just: select min_date from min_date; Cheers Brent Wood ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Thanks for the tip! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables
Lorenzo Thurman wrote: I have three tables using date fields. I want to retrieve the oldest date contained in the tables. Can someone show me an example of a query that would do that? TIA I think I have it, but if anyone has any comments, I'd appreciate it: select min(old) as oldest from (select distinct min(create) as old from tab1 UNION select distinct min(time_now) as old from tab1 UNION select distinct min(create_time) as old from tab2) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables
Tom Lane wrote: Dann Corbit [EMAIL PROTECTED] writes: [EMAIL PROTECTED] On Behalf Of Lorenzo Thurman I have three tables using date fields. I want to retrieve the oldest date contained in the tables. Can someone show me an example of a query that would do that? Just do a union and return the min That's probably not enough detail for a newbie ... SELECT min(x) FROM ( SELECT min(datecol1) AS x FROM table1 UNION ALL SELECT min(datecol2) AS x FROM table2 UNION ALL SELECT min(datecol3) AS x FROM table3 ) ss; Exercise for newbie: which of the AS clauses are redundant? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Um, all of them? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] NEWBIE: How do I get the oldest date contained in 3 tables
I have three tables using date fields. I want to retrieve the oldest date contained in the tables. Can someone show me an example of a query that would do that? TIA ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dump fails
Thanks for the reply. I've tried recompiling with my install build settings, but no luck. I've posted a message on the Gentoo forums. Hopefully they will have an answer. If they do, I'll post back here for future reference. On Apr 19, 2005, at 1:01 AM, Tom Lane wrote: Lorenzo Thurman [EMAIL PROTECTED] writes: I'm trying that right now. I think there may be mis-match in the build settings between upgrades of postgresql. The USE settings may be at fault: - - pg-hier: Enables recursive queries like Oracle's 'CONNECT BY' feature. [ rolls eyes... ] Yup, that's Gentoo all right: throw in random patches that have been rejected by the upstream developers. Now that I think about it, this failure is exactly what that patch is known to cause, because it makes an incompatible change in Query structures and hence in on-disk view rule representation. I think these may have been changed since the original install. Go back to your prior setting, or even better stop using Gentoo's hacked-up version. I'm not sure why we even bother to answer support requests from Gentoo users, when what they are using is not our software but some randomly-modified variant. I wonder what other brokennesses Gentoo may be including ... (Just for the record: I work for Red Hat, which has a rather different notion of the level of reliability it wants to ship. So take my opinion with the appropriate grain of salt. But I'm a mite ticked off at the moment --- you're not the first person to have been bitten by this, and you likely won't be the last, and I think it's entirely because Gentoo has such a low quality standard for the patches they ship.) regards, tom lane Tech/Library Combo Lab Manager Northwestern University Office Tech MG49 mailto:[EMAIL PROTECTED] voice: 847-467-6565 pager: 847-536-0094 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dump fails
What I was trying to do was export the database on one computer and import it onto another. I gave up trying to fix the export problem since I had an old backup of the database. It was old enough that it was short three tables, but I have the raw tab delimited data so I just reconstructed the database on this new machine. I've been running Gentoo for about a year and a half now, and in the early days, I did not fully understand all of the possible USE settings, but I've gotten more comfortable with it over time. As you probably know, once you've settled on what your USE settings should be, you can rebuild your system to reflect those new settings. I did that, and since everything appeared to be working OK, I assumed everything was OK, but obviously the damage to PostgreSQL was already done. Anyway, I think my settings now are pretty conservative and I know ot to play around with the Postgres USE flags. One of the reasons I'm migrating is to do a complete rebuild and apply what I've learned about Gentoo from scratch on a new computer. Here are my settings, as you asked. I don't think they're too out of line, but... On Apr 19, 2005, at 6:06 p, Russell Smith wrote: I read your post in the forums. And as Tom suggested, it's going nothing to do with pg_dump, you need to remerge postgresql at the very least, and with some C and USE flags you understand. The Usual Gentoo causes come to mind first. USE flags set correctly? what are they? USE=X -gnome -gtk -gtk2 cups -kde -qt What are your GCC flags. I see a lot of gentoo users who just about turn on every compiler flag without actually knowing what they do, or how they effect things. Are your C_FLAGS conservative? CFLAGS=-O2 -mtune=G3 -fno-strict-aliasing -pipe I've been using Postgresql on gentoo for both 7.4, and 8.0 from beta to 8.0.2 with no problems. But then I always set my C_FLAGS to something conservative like CGLAGS=-march=i586 -mcpu=i586 -O2 -pipe yes, it may seems a Gentoo Conservative buy I don't get broken software. Always check extra patches applied to the default distribution if you ever have trouble to weed out problem. And never build with and USE flags you don't understand the implications of. Especially package specific ones. I've always been a bit concerned about the patches myself. I understand Tom's frustration, as Redhat is in business and ships quality checked software, and Gentoo is run by a community group. Of which I think may of the packagers are not tied to the projects they are packaging. But I also think there is often fault with the Gentoo user attempting to bleed his system a little too much for speed, without considering the stability or even understand it. My Break-Dancing days are over, but there's always the Funky Chicken --The Full Monty
[GENERAL] pg_dump fails
I'm running postgresql 7.4.7 on x86 and have run into a problem trying to export my database. When I use pg_dump --file=stats.out --format=t printstats, (and I may have the syntax wrong), I get this error: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: did not find '}' at end of input node pg_dump: The command was: SELECT (SELECT usename FROM pg_user WHERE usesysid = datdba) as dba, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database WHERE datname = 'printstats' I tried googling for an answer, and found a couple of references to this error, but nothing that could help me. Can anyone help me out here? Thanks Tech/Library Combo Lab Manager Northwestern University Office Tech MG49 mailto:[EMAIL PROTECTED] voice: 847-467-6565 pager: 847-536-0094 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_dump fails
On Apr 18, 2005, at 4:46 p, Tom Lane wrote: Also, does select * from pg_user provoke the same error in every database of your installation, or only this one? If the latter, it could be a data-corruption kind of problem. I only have one database right now. When I try select * from pg_user, I get this error: ERROR: did not find '}' at end of input node I installed this from Gento's portage repository. There are 10 types of people in this world: those who understand binary, those who don't --Unknown
Re: [GENERAL] pg_dump fails
I'm trying that right now. I think there may be mis-match in the build settings between upgrades of postgresql. The USE settings may be at fault: - - pg-hier: Enables recursive queries like Oracle's 'CONNECT BY' feature. - - pg-vacuumdelay : Adds in the vacuum inter-page delay feature. - - pg-intdatetime : Enables --enable-integer-datetimes configure option, which changes PG to use 64-bit integers for timestamp storage I think these may have been changed since the original install. On Apr 18, 2005, at 11:57 p, Tom Lane wrote: Lorenzo Thurman [EMAIL PROTECTED]> writes: ERROR: did not find '}' at end of input node I installed this from Gento's portage repository. Gentoo has a history of supplying broken compilers, software, etc ... bleeding edge stuff tends to cut you occasionally :-( You might try updating your compiler and then rebuilding PG. regards, tom lane I got an object I was sure it was point ClassCastException -- Jens Alfke
