[SQL] max() unexpected type conversion
Hello.
I'm having some trouble with an unexpected conversion performed by the max()
function.
If max() operates on a 'varchar' type column, the result appears with 'text'
type.
Should I expect this behaviour in the current and later versions ?
I mention this because I'm using ODBC in VB, and this type conversion has a
bad outcome, because 'text' columns require a specific treatment (a second
reading of the column contents returns null). Therefore, if this conversion
is beeing made as a result of a design option, I'll have to use a different
approach (something like 'select ... order by ... desc limit 1').
A small sample follows, giving a 'text' type result:
create table test
(
testcol varchar(4) not null
);
insert into test (testcol) values ('A');
insert into test (testcol) values ('A');
insert into test (testcol) values ('B');
insert into test (testcol) values ('B');
insert into test (testcol) values ('C');
insert into test (testcol) values ('C');
select max(testcol) from test;
Thank you
Hélder M. Vieira
---(end of broadcast)---
TIP 1: 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] max() unexpected type conversion
"=?iso-8859-1?Q?H=E9lder_M._Vieira?=" <[EMAIL PROTECTED]> writes: > If max() operates on a 'varchar' type column, the result appears with 'text' > type. > Should I expect this behaviour in the current and later versions ? This is true of just about everything, not only max() -- there are *no* functions yielding varchar in recent PG releases. regards, tom lane ---(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] tid_le comparison for tuple id (ctid) values?
[PostgreSQL 8.1.0 on i686-pc-linux-gnu] I would like to suggest that there be a less-than (or greater-than) operator for the 'tid' type. I used to use oid's for finding and distinguishing duplicate data. Now that oid's are not included by default (and I do not quarrel with that change), I thought I could use ctid's instead. Suppose I have a table steps: create table steps(x text, y text, z text) but I want there to be a primary key(x,y). If I try to do: create table temp_steps(x text, y text, z text, primary key(x,y)) insert into temp_steps select * from steps; drop table steps; alter table temp_steps rename to steps; I get an error that "duplicate key violates unique constraint". Some of the rows in steps differ only in value of z. OK, I'll just fix the data... I thought I could force values of x to be distinct with: (I've done this several times in the past with oid's) update steps set x=x||'X' from steps s where steps.key1=s.key1 and steps.key2=s.key2 and step.ctidhttp://www.postgresql.org/docs/faq
Re: [SQL] tid_le comparison for tuple id (ctid) values?
george young writes: >update steps set x=x||'X' from steps s where steps.key1=s.key1 and > steps.key2=s.key2 and step.ctid But this fails because there is no less-than operator (or function) on > type "tid". Probably a good thing, too, since if there was it wouldn't have anything reliable to do with the age of the tuple. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] idea for a geographically distributed database: how best to implement?
On Thu, 17 Nov 2005 19:44, Andy Ballingall wrote: > I've got a database for a website which is a variant of the 'show stuff near > to me' sort of thing. > > Rather than host this database on a single server, I have a scheme in mind > to break the database up geographically so that each one can run comfortably > on a small server, but I'm not sure about the best way of implementing it. There are a couple of philosophical perspectives I've come across in previous work with cadastral data that may be useful. 1) A given point may be in more than one "hierarchy" of geographical units (ofbiz.org implements this quite well with different region types and a "region relationship type" table). 2) Some relevant hierarchies involve irregular shapes. e.g. a) postal/zip codes are not squares e.g. b) Often a postal/zip code system for a country is only loosely based on states/province, as sometimes "twin cities" can straddle a river and state/province boundary, but the postal centre for both sides is handled from one side. (The same often applies for business servicing). An example that comes to mind is Mildura (Victoria/Australia) and its smaller sibling across the Murray River, Wentworth (New South Wales/Australia). e.g. c) the "capture" area for many offices are often based on the state/province, again, not square. 3) There are ways of looking at "approximate nearness" of irregular shapes (although the same applies to squares) using terms such as "centric" and "centroid", which involve things like drawing the smallest circle AROUND a region that encloses ALL of it, or the largest circle INSIDE a region. It might be relevant to consider the "slightly outside but close" points in your "replication" as well. (In my previous example, Mildura and Wentworth would be included in the databases of branch offices in BOTH Vic and NSW, although one office should be considered authoritative, as services for both might be through Victorian branch offices, although Wentworth customers are likely to contact the NSW branch office). It is useful to consider "extending" the diameter of the circle enclosing the entire region by x%. Yep, "squaring the circle" and "circling the square" are useful in the real world. 4) There are a lot of useful bits and pieces at opengis.org and postgis.org 5) There are many useful GIS functions in postgres to decide if a point is inside an irregular polygon (including whether it has both convex and concave bits), and whether any two shapes overlap. Hope these points are useful. -- David T. Bath [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: 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] unplanned sub-select error?
I have a query: insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status, ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from prd_part where pnum = 1014),'work','2005-Nov-15',50,75); That used to work fine under 7.1.3 but now gives the error: ERROR: cannot handle unplanned sub-select Anyone know what this means? Is there a good reason why this update should no longer work? Or is this a bug? Kyle wyatterp.com ---(end of broadcast)--- TIP 1: 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] unplanned sub-select error?
Kyle Bateman <[EMAIL PROTECTED]> writes: > I have a query: > insert into mtr_reg_v_wt (ropnum, inum, pnum, rquant, value, status, > ddate, fr_proj, to_proj) values (28985,1,1014,1,(select cost from > prd_part where pnum = 1014),'work','2005-Nov-15',50,75); > That used to work fine under 7.1.3 but now gives the error: > ERROR: cannot handle unplanned sub-select You need to offer a little more context, like what PG version you are using now and what is the underlying DDL --- I suspect some rules or views are involved here, but you didn't show them to us. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] tid_le comparison for tuple id (ctid) values?
On Mon, 21 Nov 2005 16:19:28 -0500 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > >update steps set x=x||'X' from steps s where steps.key1=s.key1 and > > steps.key2=s.key2 and step.ctid > > But this fails because there is no less-than operator (or function) on > > type "tid". > > Probably a good thing, too, since if there was it wouldn't have anything > reliable to do with the age of the tuple. Well, I don't have any need for it to correlate with the age of the tuple. My use of step.ctid
Re: [SQL] max() unexpected type conversion
This is true of just about everything, not only max() -- there are *no* functions yielding varchar in recent PG releases Thanks for the information. I added an explicit cast to the max() output and it works fine. Anyway, for those using VB, this is potentially hazardous. Knowing that the underlying data is of type 'varchar', one has to be always very cautious about the 'text' conversion, otherwise all readings after the first will return null instead of valid data. Helder M. Vieira ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Please help to wite the constraint.
I can't understand why you are doing this big cycle.. but certainly when constraints can't help you.. you can use triggers to enforce integrity.. On 11/20/05, Grigory O. Ptashko <[EMAIL PROTECTED]> wrote: > Hello, everybody! > > I don't whether it is possible to do the following but anyway I can't. > I need to write a constraint as described below. > Here are four tables: > > > CREATE TABLE countries > (id SERIAL, > name VARCHAR(255), > PRIMARY KEY (id) > ); > > CREATE TABLE countries_names > (id INT NOT NULL, > id_lang INT NOT NULL, > name VARCHAR(255), > PRIMARY KEY (id, id_lang), > FOREIGN KEY (id) REFERENCES countries (id), > FOREIGN KEY (id_lang) REFERENCES lang (id) > > ); > > CREATE TABLE contact_info_fields > (id SERIAL, > name VARCHAR(255) NOT NULL, > PRIMARY KEY (id) > ); > > CREATE TABLE contact_info_records > (id_user INT NOT NULL, > id_ci_field INT NOT NULL, > id_lang INT NOT NULL, > value TEXT, > PRIMARY KEY (id_user, id_ci_field, id_lang), > FOREIGN KEY (id_user) REFERENCES users (id), > FOREIGN KEY (id_ci_field) REFERENCES contact_info_fields (id), > FOREIGN KEY (id_lang) REFERENCES lang (id) > ); > > > > The last table contains contact information records of different types. These > types are taken from the table contact_info_fields. In particular, there can > be the type 'country' say with id=1. Then the contact_info_records table can > contain the following info: id_ci_field=1 and the VALUE field must contain a > country's name but ONLY if it exists in the countries table (column 'name'). > So it turns out to be a wierd foreign key. Is it possible to write such a > constraint? > > Thanks! > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: 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
