Re: [SQL] length of recordset read through a cursor
> > After declaring a cursor, one way of obtaining the length of the resultset > is to perform a "MOVE 0" and read the PQcmdStatus which returns a "MOVE nn" > where nn is the length of the resultset. (A negative MOVE can then be used > to allow starting to fetch records from the beginning of the resultset.) > > Is there another, possibly faster way? > Looks like you're using libpq (because you mention PQcmdStatus), then after declaring a cursor and FETCH ALL, try 1.3.4. Retrieving SELECT Result Information PQntuples Returns the number of tuples (rows) in the query result. int PQntuples(const PGresult *res); I'm not exactly sure what you're trying to achieve or going to do, so if I misunderstood you, ask again. Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] converting interval to timestamp
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If the number of seconds between timestamps is acceptable then this is the solution I use: SELECT ABS(EXTRACT(EPOCH FROM t.field1) - EXTRACT(EPOCH FROM t.field2)) AS diff FROM table AS t; Adjust to your column and WHERE needs. One caveat: this only works for dates within the UNIX epoch ( after 1969-12-31). On Thursday 31 July 2003 12:05 pm, teknokrat wrote: > The difference of two dates/timestamps always gives an interval. is > there a way to convert this interval into number such as number of > milliseconds or number of days? > > Also does anyone know what field type an interval would map to in jdbc? > > thanks > > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match - -- Mike Rylander -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE/KmAOgRDV1fFA+3cRAgbVAJ9B03Pxsn+N+Xg2C/a4gw3j28KSsgCeNA7+ y2rYedgRdTY/BiNSfVJTvOs= =kVkm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] duplicate dates
Title: I have one table that has a date/time field in it. I'd like to identify the records in the database where the date/time fields are the same. How can I do this? Do I need to create a view or temp table? Is there a way to run through one table multiple times. Thanks. Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]
Re: [SQL] duplicate dates
Jodi Kanter wrote:
> I have one table that has a date/time field in it. I'd like to identify
> the records in the database where the date/time fields are the same. How
> can I do this? Do I need to create a view or temp table? Is there a way
> to run through one table multiple times.
If you plan to review the data multiple times, and the table is rather
large, then it probably makes sense to make a temp table.
select a.* into temp table my_dupes from
dated_table a, dated_table b
where a.event_on = b.event_on
-- Assuming that the primary key is on (field1, field2, field3)
and a.key_field1 <> b.key_field1
and a.key_field2 <> b.key_field2
and a.key_field3 <> b.key_field3;
You could then rummage through my_dupes as needed. Note that if there
are more than 2 simultaneous records, it will list all of them
multiple times :-(.
You may also want to be more precise about what you mean by 'date/time
fields are the same'. They go down to fractions of a second, so you
shouldn't have a huge number of collisions.
--
output = ("cbbrowne" "@" "libertyrms.info")
Re: [SQL] Fwd: Bad Join moment - how is this happening?
Sorry to be slow on responses... Thanks to everyone who replies. On Wed, 30 Jul 2003, Richard Huxton wrote: > On Wednesday 30 July 2003 21:07, Jamie Lawrence wrote: > > I fully admit that I've been staring at this too long, and simply don't > > understand what is wrong. Apologies aside, any kind sql hackers who care > > to look this over will earn my undying gratitude, and a beer in the bar > > of your choice, should we ever meet. > > I'll take that beer (assuming I'm right) I think you nailed it first. You appear to be in London, which I haven't made it to in ~15 years, and mailing it is probably a bad idea. Ever make it to the NYC area? > > I appear to be getting a cartesean product when I select against the view > > 'addenda', when I want a left inner join. That is, I want documents > > records matched to addenda records only when there is a record in > > d_addenda with a documents_id that matches the id field in documents. > > I think this is the "adding a table into the FROM" feature of PG. You're > referring to documents.xxx in the select and d.id in the FROM. PG tries to > help out by adding the table into the FROM for you - hence cartesian join. > > I think you can turn this "feature" off in the config file in 7.3.x (haven't > checked this though) This was exactly it. Thanks for the help, this was really making me crazy. -j -- Jamie Lawrence[EMAIL PROTECTED] anger, bargaining, depression, and, finally, acceptance ---(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] What day is it - when it isn't NOW()?
'2003-08-04' is ambiguous. It could be a text string, a date, something
else. So cast it.
SELECT to_char('2003-08-04'::date, 'Day');
> SELECT to_char(now(), 'Day'); returns Friday as I'd hoped.
> But how do I return the day of the week for a specific date other
> than now()? I'm looking for something along the lines of:
> SELECT to_char('2003-08-04', 'Day') without much success.
>
> Can anyone point me to the right function/combination of functions
> to achieve this?
>
> Thanks!
>
> ---
> Thomas Good e-mail: [EMAIL PROTECTED]
> Programmer/Analyst phone: (+1) 718.818.5528
> Residential Services fax: (+1) 718.818.5056
> Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
>
> // Welches ist das groessere Verbrechen?
> // Massenvernichtungswaffen besitzen oder sie erfinden?
>
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
signature.asc
Description: This is a digitally signed message part
Re: [SQL] What day is it - when it isn't NOW()?
Thomas,
> SELECT to_char(now(), 'Day'); returns Friday as I'd hoped.
> But how do I return the day of the week for a specific date other
> than now()? I'm looking for something along the lines of:
> SELECT to_char('2003-08-04', 'Day') without much success.
>
> Can anyone point me to the right function/combination of functions
> to achieve this?
Well, you could try the online docs under "Functions and operators" .
Two possibilities:
SELECT to_char('2003-08-04'::TIMESTAMP, 'Day') should make the to_char version
work for you.
Alternately, SELECT EXTRACT(dow FROM '2003-08-04') will give you a numerical
(0-6) day of the week.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] What day is it - when it isn't NOW()?
Hi,
SELECT to_char(now(), 'Day'); returns Friday as I'd hoped.
But how do I return the day of the week for a specific date other
than now()? I'm looking for something along the lines of:
SELECT to_char('2003-08-04', 'Day') without much success.
Can anyone point me to the right function/combination of functions
to achieve this?
Thanks!
---
Thomas Good e-mail: [EMAIL PROTECTED]
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
// Welches ist das groessere Verbrechen?
// Massenvernichtungswaffen besitzen oder sie erfinden?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] What day is it - when it isn't NOW()?
On Fri, 1 Aug 2003, Josh Berkus wrote:
> Thomas,
>
> > than now()? I'm looking for something along the lines of:
> > SELECT to_char('2003-08-04', 'Day') without much success.
> >
>
> Well, you could try the online docs under "Functions and operators" .
Hey Josh, how goes...yeah I bumbled around date/time functions for awhile but
couldn't find the right syntax...maybe I need new glasses? ;-)
> Alternately, SELECT EXTRACT(dow FROM '2003-08-04') will give you a numerical
> (0-6) day of the week.
I tried this but I don't need an integer returned so I went with the
to_char() approach. Thanks for tip - and thanks to Rod as well.
On 1 Aug 2003, Rod Taylor wrote:
>
> SELECT to_char('2003-08-04'::date, 'Day');
>
This is what I went for as it is the closest to the MySQL/Oracle syntax
I use for the same purpose, particularly Oracle:
$query = qq |SELECT DATE_FORMAT('$start_date', '%W')| if ($dbtype eq 'mysql');
$query = qq |SELECT TO_CHAR('start_date'::date, 'Day') if ($dbtype eq 'postgres');
$query = qq |SELECT TO_CHAR(TO_DATE('$start_date'), 'Day') FROM DUAL| if ($dbtype eq
'oracle');
Thanks again fellas - have a nice weekend!
Cheers
---
Thomas Good e-mail: [EMAIL PROTECTED]
Programmer/Analyst phone: (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile: (+1) 917.282.7359
// Welches ist das groessere Verbrechen?
// Massenvernichtungswaffen besitzen oder sie erfinden?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
I don't seem to have any plsql specfic documentation, and the rest of my
oracle documentation isn't specfific enough. Anyone else?
Robert Treat
On Thursday 31 July 2003 00:12, Bruce Momjian wrote:
> Does Oracle have a syntax for this?
>
> ---
>
> Robert Treat wrote:
> > On Wednesday 23 July 2003 19:06, Bruce Momjian wrote:
> > > Robert Treat wrote:
> > > > On Wed, 2003-07-23 at 15:38, [EMAIL PROTECTED] wrote:
> > > > > FOR myrec IN EXECUTE myinfo LOOP
> > > > > biglist := myrec.info;
> > > > > END LOOP;
> > > >
> > > > One other thing, I hate when I have to do things like the above, can
> > > > we get a TODO like:
> > > >
> > > > allow 'EXECUTE var INTO record' in plpgsql
> > >
> > > So the TODO would be?
> > >
> > > Allow PL/pgSQL EXECUTE to return a single record outside a loop
> >
> > that's what I wrote, but not what I meant :-) I do like the sound of it
> > though, but really what I meant to say was:
> > EXECUTE var1 INTO var2
> > but this assumes a number of things, namely that executing var1 will
> > return only one field, and one row. I guess that would be:
> > Allow PL/pgSQL EXECUTE to return a single variable outside a loop
> >
> > Robert Treat
> > --
> > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faqs/FAQ.html
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Robert, > I don't seem to have any plsql specfic documentation, and the rest of my > oracle documentation isn't specfific enough. Anyone else? As far as I can tell from my PL/SQL guide to Oracle8, PL/SQL does not permit exectution of strings-as-queries at all. So there's no equivalent in PL/SQL. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] NEW and a subselect in a rule
On Thu, Jul 31, 2003 at 10:00:07AM +0200, Christoph Haller wrote: > > > > So, I met such a problem: it's impossible to use NEW in a subselect > > used in a (non-select) rule. The error is: > > exist>. Is this a way to do that newertheless (without using of a > > function, of course)? > > > Could we see the CREATE RULE command causing the error? I've sent the other message (with the Subject: Nonexistent NEW relation...) into the list. The code is there. The problem is already closed (not solved!). Thanks anyway. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] backend cpu usage? [7.2]
[postgresql-7.2, x86 linux] How can I get cpu usage info in 7.2 about the backend process of my db connection? I tried looking at the pg_stat_get_backend_pid function but it requires a backend_id that I don't know how to get. If I can get the backend pid I can fork a 'ps' command from my app, though it would be nicer to get it directly through sql. -- George Young -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "The Dying Detective" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
On Fri, Aug 01, 2003 at 01:06:18PM -0700, Josh Berkus wrote: > As far as I can tell from my PL/SQL guide to Oracle8, PL/SQL does not permit > exectution of strings-as-queries at all. So there's no equivalent in PL/SQL. I'm not an Oracle bunny but they seem to have something vaguely similar to what we do; they call it "EXECUTE IMMEDIATE" and the concept is described as "Dynamic SQL". http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/10_dynam.htm#4376 Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Guys, > I'm not an Oracle bunny but they seem to have something vaguely similar > to what we do; they call it "EXECUTE IMMEDIATE" and the concept is > described as "Dynamic SQL". Aha. I see it now; a pretty awful OO-package-style format. I don't think we want to imitate this. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
OK, so what should the TODO item be? --- Josh Berkus wrote: > Guys, > > > I'm not an Oracle bunny but they seem to have something vaguely similar > > to what we do; they call it "EXECUTE IMMEDIATE" and the concept is > > described as "Dynamic SQL". > > Aha. I see it now; a pretty awful OO-package-style format. I don't think we > want to imitate this. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Bruce, > OK, so what should the TODO item be? Go with the simple and intuitive: EXECUTE query_var INTO record_var; -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: TODO item for plpgsql Was Re: [SQL] obtuse plpgsql function needs
Added. --- Josh Berkus wrote: > Bruce, > > > OK, so what should the TODO item be? > > Go with the simple and intuitive: > > EXECUTE query_var INTO record_var; > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] backend cpu usage? [7.2]
george young <[EMAIL PROTECTED]> writes: > How can I get cpu usage info in 7.2 about the backend process of my db > connection? I tried looking at the pg_stat_get_backend_pid function > but it requires a backend_id that I don't know how to get. > If I can get the backend pid I can fork a 'ps' command from my app, > though it would be nicer to get it directly through sql. There's a pg_backend_pid() function in 7.3 that returns your own PID. Not sure if it was in 7.2. regards, tom lane ---(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
[SQL] Efficient deletions?
Hello,
This is my first message to this list. I'm curently trying to
improve my postgres skillsm I've assigned myself a project involving a
database whose dataset is quite larger than those I had to use until
now.
I'm curently facing a trouble with the DELETE query: I need to
delete from a table a set of rows choosen by a sub-select. The query
curently looks like this:
DELETE FROM foo WHERE (col1, col2) IN (SELECT...)
It seems from the output of ANALYSE that the sub-query is executed for
each foo rows. That's really not efficient as the set returned by the
sub-select is constant.
I had a similar trouble with a SELECT query but it was eavily
solved by placing one of the sub-select in the FROM part of the query.
Unfortunately it look that can't be done this way with DELETE.
Is there a way to instruct the planner that this sub-query is
constant over the time of the DELETE execution? Or is there another
more efficent way to express this?
I conducted tests with a sub-select returning about 20,000
rows and a foo table of 370,000 rows, out of patience I had to
interrupt the query after five hours. That worries me as this query is
part of an update process that could be fired several times a day.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
