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 stdlib.h
#include stdio.h
#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] have you feel anything when you read this ?
On Wed, 5 Apr 2006, Eugene E. wrote: 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... What lameness? The fact that you're trying to use a binary (network order maybe) integer as a string? That's not the fault of PQexecParams but of the code calling it. It'd be nice for ease of use to be able to say, give me this column (the integer) as a string and this column (the bytea) as binary, especially as the commentary implies that the protocol supports it. But as I said before, that's a separate discussion from whether or not Peter's claim that it's possible to get binary data is false. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] SELECT composite type
Hello, I have a function get_xy that returns a composite type with columns x and y. I would like to SELECT these as well as some data from a table, like so: SELECT (get_xy(SetSRID(sightings.location, 26910), 4326)).x, (get_xy(SetSRID(sightings.location, 26910), 4326)).y, sightings.title FROM sightings WHERE sighting_id = 25; This statement works, but I don't want to duplicate the function call as this should be unnecessary. Selecting simply get_xy returns both fields in a single column, which is undesirable. I tried: SELECT foo.x, foo.y, sightings.title FROM sightings, get_xy(SetSRID(sightings.location, 26910), 4326) foo WHERE sighting_id = 25; But, because the function refers to sightings, I get this error: ERROR: function expression in FROM may not refer to other relations of same query level ...which is reasonable. So I basically want to call get_xy for every row in sightings, and use its output for two columns; or perhaps there is another way to think of this. I am using Postgres 8.1.2 (same with client) on FreeBSD, with PostGIS 1.1.1: $ postmaster --version postmaster (PostgreSQL) 8.1.2 Thanks in advance! Mike. -- Michael Burke Engineering Technologies Canada Ltd. - http://www.engtech.ca/ [EMAIL PROTECTED] 1 (902) 628-1705 ---(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
[SQL] IF statement in Select
Hello all, Is the use of IF statement restrict to Functions and Store Procedures? I'm trying to use an IF within Select... Example: create view v1 as select c1, c2, if c3 = 52 then 0 else 1 endif as base_irrf_13 from t1; Thanks in advance, Renato Cramer. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] IF statement in Select
You can use Case SELECT c1, c2, CASE WHEN c3 = 52 THEN 0 ELSE 1 END AS base_irrf_13 FROM t1;On 4/5/06, Renato Cramer [EMAIL PROTECTED] wrote:Hello all,Is the use of IF statement restrict to Functions and Store Procedures? I'm trying to use an IF within Select...Example:create view v1 asselect c1, c2, if c3 = 52 then 0 else 1 endif as base_irrf_13 from t1;Thanks in advance,Renato Cramer. ---(end of broadcast)---TIP 5: don't forget to increase your free space map settings-- William Leite Araújo
[SQL] Reverse Index ... how to ...
I'm still searching through Google and whatnot, but not finding anything off the bat ... is there some way of creating a 'REVERSE INDEX' on a column in a table? For instance, when I do a 'CLUSTER' to sort a table based on an INDEX, I'd like to sort it in reverse order, so would need the INDEX to go from 'GREATEST to LOWEST', vs 'LOWEST to GREATEST' ... Thx Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Reverse Index ... how to ...
On Wed, 5 Apr 2006, Marc G. Fournier wrote: I'm still searching through Google and whatnot, but not finding anything off the bat ... is there some way of creating a 'REVERSE INDEX' on a column in a table? For instance, when I do a 'CLUSTER' to sort a table based on an INDEX, I'd like to sort it in reverse order, so would need the INDEX to go from 'GREATEST to LOWEST', vs 'LOWEST to GREATEST' ... use something like select * into new_table from old_table order by some_key desc; Thx Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Reverse Index ... how to ...
Marc G. Fournier [EMAIL PROTECTED] writes: I'm still searching through Google and whatnot, but not finding anything off the bat ... is there some way of creating a 'REVERSE INDEX' on a column in a table? For instance, when I do a 'CLUSTER' to sort a table based on an INDEX, I'd like to sort it in reverse order, so would need the INDEX to go from 'GREATEST to LOWEST', vs 'LOWEST to GREATEST' ... You shouldn't need to worry about that during CLUSTER, as the system is perfectly capable of scanning an index in either forward or backward order at runtime. For example, regression=# explain select * from tenk1 order by unique1; QUERY PLAN Index Scan using tenk1_unique1 on tenk1 (cost=0.00..1572.00 rows=1 width=244) (1 row) regression=# explain select * from tenk1 order by unique1 desc; QUERY PLAN - Index Scan Backward using tenk1_unique1 on tenk1 (cost=0.00..1572.00 rows=1 width=244) (1 row) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Reverse Index ... how to ...
On Wed, 5 Apr 2006, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: I'm still searching through Google and whatnot, but not finding anything off the bat ... is there some way of creating a 'REVERSE INDEX' on a column in a table? For instance, when I do a 'CLUSTER' to sort a table based on an INDEX, I'd like to sort it in reverse order, so would need the INDEX to go from 'GREATEST to LOWEST', vs 'LOWEST to GREATEST' ... You shouldn't need to worry about that during CLUSTER, as the system is perfectly capable of scanning an index in either forward or backward order at runtime. For example, regression=# explain select * from tenk1 order by unique1; QUERY PLAN Index Scan using tenk1_unique1 on tenk1 (cost=0.00..1572.00 rows=1 width=244) (1 row) regression=# explain select * from tenk1 order by unique1 desc; QUERY PLAN - Index Scan Backward using tenk1_unique1 on tenk1 (cost=0.00..1572.00 rows=1 width=244) (1 row) Perfect, that was what I was looking for, thx ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SELECT composite type
On 5 apr 2006, at 17.57, Michael Burke wrote: So I basically want to call get_xy for every row in sightings, and use its output for two columns; or perhaps there is another way to think of this. You could try SELECT foo.x, foo.y, title FROM (SELECT get_xy(SetSRID(sightings.location, 26910), 4326) AS foo, sightings.title FROM sightings WHERE sighting_id = 25) bar; Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] SELECT composite type
Niklas Johansson [EMAIL PROTECTED] writes: On 5 apr 2006, at 17.57, Michael Burke wrote: So I basically want to call get_xy for every row in sightings, and use its output for two columns; or perhaps there is another way to think of this. You could try SELECT foo.x, foo.y, title FROM (SELECT get_xy(SetSRID(sightings.location, 26910), 4326) AS foo, sightings.title FROM sightings WHERE sighting_id = 25) bar; Note however that the above is only a cosmetic answer: you avoid typing the function call twice, but the planner will flatten the subquery into the outer query and thereby end up with two evaluations anyway. If you're really intent on avoiding the extra evaluation then you need to do something to prevent the flattening from happening. One handy trick is to use a LIMIT or OFFSET clause in the subquery as an optimization fence: SELECT foo.x, foo.y, title FROM (SELECT get_xy(SetSRID(sightings.location, 26910), 4326) AS foo, sightings.title FROM sightings WHERE sighting_id = 25 OFFSET 0) bar; There are some other features such as DISTINCT that also prevent flattening, but there seems no call for that here. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] problem comparing strings when different cluster / database encoding
Greetings, I've encountered a strange problem. We have a PG 8.0.x database cluster (in the sense used in initdb, i.e. bunch of databases) created with UNICODE encoding, namely cs_CZ.UTF-8 locale. When a database is created with a different encoding (in our case it's LATIN2) the string comparison doesn't work correctly. For example the query SELECT 'ě' = 'é'; returns 'true' which is obviously incorrect, as those two letters have different accents (I hope you can see that). And of course, it's not possible to create an unique index (or primary key) over a column of words (for example in a dictionnary), because false collisions are found, and the sorting works in a really strange way too. If the both cluster and database are in the same encoding (UNICODE or LATIN2), everything works fine. Below is a short description how the database cluster and the databases have been created. -- $ export LANG=cs_CZ.UTF-8 $ initdb ... (cluster created with UNICODE encoding, cs_CZ.UTF-8 locale) $ ... (postgres started, users created, etc.) $ createdb -E LATIN2 my_database; $ psql my_database; SELECT 'ě' = 'é'; (returns 'true', which is incorrect) -- If we create the cluster with LATIN2 encoding (or on the contrary the database is created with UNICODE encoding), everything works fine. For example the following works as expected. -- $ export LANG=cs_CZ (thus the ISO-8859-2 encoding is used) $ initdb ... (cluster created with LATIN2 encoding, cs_CZ locale) $ ... (postgres started, users created, etc.) $ createdb -E LATIN2 my_database; $ psql my_database; SELECT 'ě' = 'é'; (returns 'false', which is correct) -- I'm trying to solve this for several days, but unsuccesfully. Is there something I've missed? Some obvious solution I don't see? The queston is why we need different encodings for cluster / databases. (a) Until recently we've used LATIN2 cluster and LATIN2 databases (and applications expecting LATIN2 encoding) - that's the reason why we need LATIN2 databases. (c) On the other way some of the new clients want to 'internationalize' their applications, so we need UNICODE infrastructure too - that's the reason why we use UNICODE cluster and databases. I've came accross the nls_string function - with it it works fine, but that's not an option for us, as it would require rewriting all the SQL queries in the applications (and that's something we don't want). Thanks for your suggestions Tomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] problem comparing strings when different cluster / database encoding
Tomas Vondra [EMAIL PROTECTED] writes: I've encountered a strange problem. We have a PG 8.0.x database cluster (in the sense used in initdb, i.e. bunch of databases) created with UNICODE encoding, namely cs_CZ.UTF-8 locale. When a database is created with a different encoding (in our case it's LATIN2) the string comparison doesn't work correctly. That's right. This doesn't work, and is warned against in our docs (at least in recent versions). You really must keep server_encoding matching the encoding the locale expects, for all except trivial locales such as C that have no encoding expectations. We'd forbid these combinations if there were any fully portable way to detect which encoding the locale expects... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] problem comparing strings when different cluster / database encoding
Tomas Vondra wrote: Hi, I've encountered a strange problem. We have a PG 8.0.x database cluster (in the sense used in initdb, i.e. bunch of databases) created with UNICODE encoding, namely cs_CZ.UTF-8 locale. When a database is created with a different encoding (in our case it's LATIN2) the string comparison doesn't work correctly. Actually this is sort of expected. Your locale configuration expects a certain encoding; all databases should be created using that encoding for string comparison to work properly. What definitely is a bug is the fact that CREATE DATABASE allows you to create a database with an encoding different from the one defined by initdb. The conclusion is that you should create your databases using UTF-8 encoding if you are using cs_CZ.UTF-8, otherwise it won't work as expected. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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 ?
Stephan Szabo wrote: On Wed, 5 Apr 2006, Eugene E. wrote: 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... What lameness? The fact that you're trying to use a binary (network order maybe) integer as a string? That's not the fault of PQexecParams but of the code calling it. You're right ! That's is not a fault of PQexecParams at all. That's the fault of its design. (I pretty know why an integer has been not displayed, but why they designed this function that way ? i do not know) It'd be nice for ease of use to be able to say, give me this column (the integer) as a string and this column (the bytea) as binary, I always want to retrieve TEXT (and mostly INT) in TEXTUAL-FORM, and BYTEA always in BINARY-FORM. (at least by defaul) WHY SHOULD I RETRIVE A SINGLE BYTEA FIELD IN A SEPARATE QUERY ??? Look: if i define a field as of type BYTEA, doesn't it mean the field tends to store binary data ? If does, then WHY they (by default) convert its value to TEXTUAL-FORM which is not needed by default -- i already inform the server: i want some BINARY to in and out when i have choosed the type BYTEA ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
