Re: [SQL] have you feel anything when you read this ?
Stephan Szabo wrote:
On Tue, 4 Apr 2006, Eugene E. wrote:
Stephan Szabo wrote:
On Fri, 31 Mar 2006, Eugene E. wrote:
Peter Eisentraut wrote:
Eugene E. wrote:
the problem is: you'll get this four byte sequence '\000' _instead_
of NUL-byte anyway.
What you seem to be missing is that PostgreSQL data can be represented
in textual and in binary form. What you in psql is the textual form.
If you want the binary form you need to select it. Then you can pass
the exact bytes back and forth.
your sentence is not true.
I can not select exact bytes even if i use BYTEA type
No, that is still using the textual form. If you use PQexecParams and set
the last argument to show you want binary data, you should get binary
data.
ok
then i am using PQexecParams
the following tiny program shows a wonderful lameness...
#include
#include
#include "libpq-fe.h"
int
main (void)
{
PGconn * conn;
PGresult * res;
char * val;
char * l;
intlen;
conn = PQconnectdb("user=scott password=tiger name=test_db");
PQexec(conn, "CREATE TABLE t (a BYTEA)");
PQexec(conn, "INSERT INTO t VALUES ('ab000cd')");
res = PQexecParams(conn, "SELECT a,length(a) FROM t", 0, NULL, NULL,
NULL, NULL, 1);
val = PQgetvalue(res,0,0);
l = PQgetvalue(res,0,1);
len = PQgetlength(res,0,0);
printf("what_we_retrive='screened' its_value_length=%i but
orig_length=%s\n",len,l);
PQclear(res);
PQfinish(conn);
return 0;
}
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] query to return hourly snapshot
Yes! Thanks you very much!
--- Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> > I am look for help in developing a query that will return the nearest
> > process record that was logged at or after each hour in a day (i.e.
> > hourly snapshot).
>
> Are you looking for something like this?
>
> SELECT p.process, date_trunc('hour', p.tstamp) AS hour
> FROM process AS p
> JOIN (
> SELECT date_trunc('hour', tstamp), min(tstamp)
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> GROUP BY date_trunc('hour', tstamp)
> ) AS s ON s.min = p.tstamp
> ORDER BY hour;
>
> Or, using PostgreSQL's non-standard DISTINCT ON clause:
>
> SELECT DISTINCT ON (date_trunc('hour', tstamp))
>process, date_trunc('hour', tstamp) AS hour
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> ORDER BY date_trunc('hour', tstamp), tstamp;
>
> --
> Michael Fuhr
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] query to return hourly snapshot
On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> I am look for help in developing a query that will return the nearest
> process record that was logged at or after each hour in a day (i.e.
> hourly snapshot).
Are you looking for something like this?
SELECT p.process, date_trunc('hour', p.tstamp) AS hour
FROM process AS p
JOIN (
SELECT date_trunc('hour', tstamp), min(tstamp)
FROM process
WHERE date_trunc('day', tstamp) = '2005-10-26'
GROUP BY date_trunc('hour', tstamp)
) AS s ON s.min = p.tstamp
ORDER BY hour;
Or, using PostgreSQL's non-standard DISTINCT ON clause:
SELECT DISTINCT ON (date_trunc('hour', tstamp))
process, date_trunc('hour', tstamp) AS hour
FROM process
WHERE date_trunc('day', tstamp) = '2005-10-26'
ORDER BY date_trunc('hour', tstamp), tstamp;
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] query to return hourly snapshot
I orginally sent this email to the [novice] list but did not get any response.
I am look for help in developing a query that will return the nearest process
record that was
logged at or after each hour in a day (i.e. hourly snapshot).
Below are typical sample data. Also, I've included a query used to return the
average of the
process value for each hour to give an example for the format that I am trying
to achieve.
Thanks for the help.
Regards,
Richard
select * from process limit 10;
tstamp| process
-+-
2005-10-25 21:10:41 | 3.56513
2005-10-25 21:10:42 | 3.56503
2005-10-25 21:10:43 | 3.56494
2005-10-25 21:10:44 | 3.56484
2005-10-25 21:10:45 | 3.56475
2005-10-25 21:10:46 | 3.56465
2005-10-25 21:10:47 | 3.56455
2005-10-25 21:10:48 | 3.56446
2005-10-25 21:10:49 | 3.56436
2005-10-25 21:10:50 | 3.56427
(10 rows)
select avg(process),
date_trunc('hour',tstamp) as date
from process
wheredate_trunc('day', tstamp) = '2005-10-26'
group by date_trunc('hour', tstamp)
order by date_trunc('hour', tstamp);
avg|date
--+-
9.79195118032606 | 2005-10-26 05:00:00
10.0249767947376 | 2005-10-26 06:00:00
8.88596018049452 | 2005-10-26 07:00:00
7.95090951088542 | 2005-10-26 08:00:00
8.10741349776586 | 2005-10-26 09:00:00
7.30079822791947 | 2005-10-26 10:00:00
7.10586501293712 | 2005-10-26 11:00:00
8.15196838166979 | 2005-10-26 12:00:00
8.26183129151662 | 2005-10-26 13:00:00
8.95141531440947 | 2005-10-26 14:00:00
10.562882253329 | 2005-10-26 15:00:00
10.863490825 | 2005-10-26 16:00:00
11.4077104069976 | 2005-10-26 17:00:00
12.4702264580744 | 2005-10-26 18:00:00
11.9155618293134 | 2005-10-26 19:00:00
11.5622152555012 | 2005-10-26 20:00:00
11.6527367563489 | 2005-10-26 21:00:00
10.3170960432442 | 2005-10-26 22:00:00
9.56747980806563 | 2005-10-26 23:00:00
(19 rows)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] have you feel anything when you read this ?
On Tue, 4 Apr 2006, Eugene E. wrote:
> Stephan Szabo wrote:
> > On Fri, 31 Mar 2006, Eugene E. wrote:
> >
> >
> >>Peter Eisentraut wrote:
> >>
> >>>Eugene E. wrote:
> >>>
> >>>
> the problem is: you'll get this four byte sequence '\000' _instead_
> of NUL-byte anyway.
> >>>
> >>>
> >>>What you seem to be missing is that PostgreSQL data can be represented
> >>>in textual and in binary form. What you in psql is the textual form.
> >>>If you want the binary form you need to select it. Then you can pass
> >>>the exact bytes back and forth.
> >>
> >>your sentence is not true.
> >>I can not select exact bytes even if i use BYTEA type
> >
> >
> > No, that is still using the textual form. If you use PQexecParams and set
> > the last argument to show you want binary data, you should get binary
> > data.
>
> Documentation says:
> ===
> PQexecParams
>
> Submits a command to the server and waits for the result, with the
> ability to pass parameters separately from the SQL command text.
> ===
>
> How should i use this func to change so-called "textual form" of a
> select-result to so-called "binary form" ?
>From the 8.1 docs (although I believe this applies back to 7.4):
PQexecParams
Submits a command to the server and waits for the result, with the
ability to pass parameters separately from the SQL command text.
PGresult *PQexecParams(PGconn *conn,
const char *command,
int nParams,
const Oid *paramTypes,
const char * const *paramValues,
const int *paramLengths,
const int *paramFormats,
int resultFormat);
PQexecParams is like PQexec, but offers additional functionality:
parameter values can be specified separately from the command string
proper, and query results can be requested in either text or binary
format. PQexecParams is supported only in protocol 3.0 and later
connections; it will fail when using protocol 2.0.
If parameters are used, they are referred to in the command string as
$1, $2, etc. nParams is the number of parameters supplied; it is the
length of the arrays paramTypes[], paramValues[], paramLengths[], and
paramFormats[]. (The array pointers may be NULL when nParams is zero.)
paramTypes[] specifies, by OID, the data types to be assigned to the
parameter symbols. If paramTypes is NULL, or any particular element in the
array is zero, the server assigns a data type to the parameter symbol in
the same way it would do for an untyped literal string. paramValues[]
specifies the actual values of the parameters. A null pointer in this
array means the corresponding parameter is null; otherwise the pointer
points to a zero-terminated text string (for text format) or binary data
in the format expected by the server (for binary format). paramLengths[]
specifies the actual data lengths of binary-format parameters. It is
ignored for null parameters and text-format parameters. The array pointer
may be null when there are no binary parameters. paramFormats[] specifies
whether parameters are text (put a zero in the array) or binary (put a one
in the array). If the array pointer is null then all parameters are
presumed to be text. resultFormat is zero to obtain results in text
format, or one to obtain results in binary format. (There is not currently
a provision to obtain different result columns in different formats,
although that is possible in the underlying protocol.)
---
Note the last argument to the function, and the last couple of sentences
in the above describe how to use resultFormat. It'd be nice if we could
get an interface which allowed mixing, but that's secondary to can we get
binary data or not.
Here's a similar app to the one you sent which for me seemingly gives the
binary data:
#include
#include
#include "libpq-fe.h"
int
main (void)
{
PGconn * conn;
PGresult * res;
char * val;
inti;
intlen;
conn = PQconnectdb("user=sszabo password=a dbname=sszabo");
PQexec(conn, "CREATE TABLE t (a BYTEA)");
PQexec(conn, "INSERT INTO t VALUES ('ab000cd')");
res = PQexecParams(conn, "SELECT a FROM t", 0, NULL, NULL,
NULL, NULL, 1);
val = PQgetvalue(res,0,0);
len = PQgetlength(res,0,0);
printf("what_we_retrive='%s' its_value_length=%i\n",val,len);
for (i=0; i < len; ++i) {
printf("Position %d is %d (%c)\n", i, val[i], val[i]);
}
PQclear(res);
PQfinish(conn);
return 0;
}
---(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
Re: [SQL] have you feel anything when you read this ?
On Apr 4, 2006, at 19:30 , Eugene E. wrote: Stephan Szabo wrote: On Fri, 31 Mar 2006, Eugene E. wrote: Peter Eisentraut wrote: No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. Documentation says: === PQexecParams Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text. === How should i use this func to change so-called "textual form" of a select-result to so-called "binary form" ? It looks like you quoted http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html#AEN25123 If you read the rest of the explanation of PQexecParams, I think you'll find the answer you're looking for. (Please note I don't have any experience using libpq. I'm just trying to help you find an answer to your question.) The usage block includes the parameters PQexecParams accepts. PGresult *PQexecParams(PGconn *conn, const char *command, int nParams, const Oid *paramTypes, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); That last parameter (resultFormat) looks promising. The next sentence gives query result formats: PQexecParams is like PQexec, but offers additional functionality: parameter values can be specified separately from the command string proper, and query results can be requested in either text or binary format. And in the next paragraph: ... resultFormat is zero to obtain results in text format, or one to obtain results in binary format. ... Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
Stephan Szabo wrote: On Fri, 31 Mar 2006, Eugene E. wrote: Peter Eisentraut wrote: Eugene E. wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. your sentence is not true. I can not select exact bytes even if i use BYTEA type No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. Documentation says: === PQexecParams Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text. === How should i use this func to change so-called "textual form" of a select-result to so-called "binary form" ? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
