Re: [SQL] UTF8 encoding and non-text data types
"Joe" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Oh? Interesting. But even if we wanted to teach Postgres about that, >> wouldn't there be a pretty strong risk of getting confused by Arabic's >> right-to-left writing direction? Wouldn't be real helpful if the entry >> came out as 4321 when the user wanted 1234. Definitely seems like >> something that had better be left to the application side, where there's >> more context about what the string means. >> > The Arabic language is written right-to-left, except ... when it comes to > numbers. I don't think that matters anyways. Unicode strings are always in "logical" order, not display order. Displaying the string in the right order is up to the display engine in the Unicode world-view. I'm not sure what to think about this though. It may be that Arabic notation are close enough that it would be straightforward (IIRC decimal notation was invented in the Arabic world after all). But other writing systems have some pretty baroque notations which would be far more difficult to convert. If anything I would expect this kind of conversion to live in the same place as things like roman numerals or other more flexible formatting. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] JOIN a table twice for different values in the same query
Colin Wetherbee wrote: Colin Wetherbee wrote: Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) This is true, but FWIW, my application will mostly be joining for the name of the airport or the city, not the code. I'll keep the idea of using the codes as keys in mind, though. Thanks for pointing that out. Oh, now I remember why I'm using IDs as keys. ;) The code isn't always going to be an airport, and, for example, a train station in Buenos Aires could conceivably have the same code as a shipping port in Rotterdam, which, in turn, might well be JFK. :) Note that IATA codes are _NOT_ unique. The current list of IATA trigrams list upward of 300 duplicate codes. If you include the train stations, there might be additional collisions. You could consider using the ICAO four-letter identifiers instead. They are unique, and are preferred by airspace management authorities. A mapping to the corresponding IATA code exists. --Magne ---(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] Is DATETIME an ANSI-SQL type?
Hi, Well the subject says it all: Can anyone tell me if DATETIME is an ANSI SQL type? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Is DATETIME an ANSI-SQL type?
Joost Kraaijeveld wrote: Hi, Well the subject says it all: Can anyone tell me if DATETIME is an ANSI SQL type? No. The Datetime types defined in SQL (ISO/IEC 9075:2003) are DATE TIME WITHOUT TIME ZONE TIME WITH TIME ZONE TIMESTAMP WITHOUT TIME ZONE TIMESTAMP WITH TIME ZONE --Magne ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] JOIN a table twice for different values in the same query
Magne Mæhre wrote: Colin Wetherbee wrote: Colin Wetherbee wrote: Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) This is true, but FWIW, my application will mostly be joining for the name of the airport or the city, not the code. I'll keep the idea of using the codes as keys in mind, though. Thanks for pointing that out. Oh, now I remember why I'm using IDs as keys. ;) The code isn't always going to be an airport, and, for example, a train station in Buenos Aires could conceivably have the same code as a shipping port in Rotterdam, which, in turn, might well be JFK. :) Note that IATA codes are _NOT_ unique. The current list of IATA trigrams list upward of 300 duplicate codes. If you include the train stations, there might be additional collisions. You could consider using the ICAO four-letter identifiers instead. They are unique, and are preferred by airspace management authorities. A mapping to the corresponding IATA code exists. I have both ICAO and IATA codes in my database, but users who typically won't know (or even be aware of) ICAO codes will be using the front end. In fact, in the front end, the users will see something like the following (with the respective, unique, application-specific port ID hidden in the background). Houston, TX (IAH - George Bush Intercontinental Airport) New York, NY (JFK - John F. Kennedy International Airport) Dubai, United Arab Emirates (DXB - Dubai International Airport) Which should be unique enough. :) Colin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Is DATETIME an ANSI-SQL type?
=?ISO-8859-1?Q?Magne_M=E6hre?= <[EMAIL PROTECTED]> writes: > Joost Kraaijeveld wrote: >> Can anyone tell me if DATETIME is an ANSI SQL type? > No. > The Datetime types defined in SQL (ISO/IEC 9075:2003) are > DATE > TIME WITHOUT TIME ZONE > TIME WITH TIME ZONE > TIMESTAMP WITHOUT TIME ZONE > TIMESTAMP WITH TIME ZONE If you need to port from a system that used DATETIME as a type name, consider defining DATETIME as a domain over whichever of the standard types seems to have the closest semantics (likely the last of these). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] UTF8 encoding and non-text data types
Joe writes: > The Arabic language is written right-to-left, except ... when it comes to > numbers. Perhaps they read their numbers right to left but use a little-endian notation. -- John Hasler [EMAIL PROTECTED] Elmwood, WI USA ---(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] SQL dealing with subquery
Hi all,
I'm having a conceptual problem with a subquery here - any help would be
appreciated.
I have a table treaty_rates with columns payor, payee, rate where payor and
payee are countries.
Sample set:
'US','UK',5
'US','Ireland',5
'US','Netherlands',5
'US','China',10
'Canada','US',0
'Canada','Ireland',0
'Canada','Netherlands',5
'Canada','UK,5
'Belgium','Netherlands',0
There is no certainty that the same payees exist for each payor (i.e. not
all countries have treaties with other all other countries)
I want to select all rows where the payee is in some array (e.g, in
('Netherlands','Ireland')) and the rate is not the same
In other words, in the sample above, I only want to return:
'Canada','Ireland',0
'Canada','Netherlands',5
The initial query to limit the rows to the specified payees is obvious, but
I can't get my mind around how to compare each set of rows where the payor
is the same country. Running select payor, payee, rate where payee in
('Netherlands','Ireland') will return 132 rows (66 pairs).
Any suggestions on how to compare the pairs would be appreciated.
Bryan
[SQL] SQL dealing with subquery comparison
Hi all,
I'm having a conceptual problem with a subquery here - any help would be
appreciated.
I have a table treaty_rates with columns payor, payee, rate where payor and
payee are countries.
Sample set:
'US','UK',5
'US','Ireland',5
'US','Netherlands',5
'US','China',10
'Canada','US',0
'Canada','Ireland',0
'Canada','Netherlands',5
'Canada','UK,5
'Belgium','Netherlands',0
There is no certainty that the same payees exist for each payor (i.e. not
all countries have treaties with other all other countries)
I want to select all rows where the payee is in some array (e.g, in
('Netherlands','Ireland')) and the rate is not the same
In other words, in the sample above, I only want to return:
'Canada','Ireland',0
'Canada','Netherlands',5
The initial query to limit the rows to the specified payees is obvious, but
I can't get my mind around how to compare each set of rows where the payor
is the same country. Running select payor, payee, rate where payee in
('Netherlands','Ireland') will return 132 rows (66 pairs).
Any suggestions on how to compare the pairs would be appreciated. (Obviously
I could also run into someone asking me for more than 2 payees and asking
for the combination payor/payee with the lowest rate for each individual
payor).
Thanks,
Bryan
Re: [SQL] Is DATETIME an ANSI-SQL type?
Hi, On Tue, 2008-01-15 at 10:53 -0500, Tom Lane wrote: > =?ISO-8859-1?Q?Magne_M=E6hre?= <[EMAIL PROTECTED]> writes: > > Joost Kraaijeveld wrote: > >> Can anyone tell me if DATETIME is an ANSI SQL type? > > > No. > > The Datetime types defined in SQL (ISO/IEC 9075:2003) are > > > DATE > > TIME WITHOUT TIME ZONE > > TIME WITH TIME ZONE > > TIMESTAMP WITHOUT TIME ZONE > > TIMESTAMP WITH TIME ZONE > > If you need to port from a system that used DATETIME as a type name, > consider defining DATETIME as a domain over whichever of the standard > types seems to have the closest semantics (likely the last of these). Thanks for the confirmation and tips. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Update PK Violation
Hi all, i have a problem with one update sentence sql. example to produce: create table temp (num integer primary key, name varchar(20)); insert into temp values (1, 'THE'); insert into temp values (2, 'BOOK'); insert into temp values (3, 'IS'); insert into temp values (4, 'ON'); insert into temp values (5, 'THE'); insert into temp values (6, 'RED'); insert into temp values (7, 'TABLE'); -- now i need insert new row at position 4, for this i need increase the field 'num' 4 to 5, 5 to 6, 6 to 7 and 7 to 8 update temp set num = num + 1 where num > 5; -- but this give an key violation error, because the postgresql try change row 4 to 5 and the 5 exist. -- to contornate the situation i have make update temp set num = 8 where num = 7; update temp set num = 7 where num = 6; update temp set num = 6 where num = 5; update temp set num = 5 where num = 4; -- so then i can insert the row... insert into temp values (5, 'NOT'); -- and obtain the result what i need. select num, name from temp ---result -- 1, 'THE' 2, 'BOOK' 3, 'IS' 4, 'NOT' 5, 'ON' 6, 'THE' 7, 'RED' 8, 'TABLE' --- the big question is... have one way to the command (update temp set num = num + 1 where num > 5;) works ? -- consideration, i can´t delete the primary key -- using PG 8.2 / Windows Thanks for all Franklin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Update PK Violation
On Jan 15, 2008 3:03 PM, Franklin Haut <[EMAIL PROTECTED]> wrote: > Hi all, > > i have a problem with one update sentence sql. > > example to produce: > > create table temp (num integer primary key, name varchar(20)); > > insert into temp values (1, 'THE'); > insert into temp values (2, 'BOOK'); > insert into temp values (3, 'IS'); > insert into temp values (4, 'ON'); > insert into temp values (5, 'THE'); > insert into temp values (6, 'RED'); > insert into temp values (7, 'TABLE'); > > -- now i need insert new row at position 4, for this i need increase the > field 'num' 4 to 5, 5 to 6, 6 to 7 and 7 to 8 > update temp set num = num + 1 where num > 5; > -- but this give an key violation error, because the postgresql try > change row 4 to 5 and the 5 exist. > --- the big question is... have one way to the command (update temp SNIP > set num = num + 1 where num > 5;) works ? > -- consideration, i can´t delete the primary key > -- using PG 8.2 / Windows Normally, I'd say you're doing it wrong, as PKs aren't supposed to change all the time. You're using this as a uniquer sequencer number, not a real PK. However, there are a few different work-arounds you might be able to implement, depending on your needs. 1: Drop the unique index in a transaction, put it back before you're done. This method has some serious locking issues you might run into, but if you only have one or two processes accessing your data, and it all happens in a quick succession, it should be safe. Since, if something in your activity fails, the transaction rolls back and your original unique index is still there. begin; drop index abc_pk_dx; update table set id = id + 1 where id > 5; create index unique abc_pk_dx on table (id); commit; 2: Put gaps in your sequence. Since you're not likely to have billions of billions of words, you can put gaps in your id sequence. I.e. 0, 20, 40, 60, 80, 100, so on. Add a word in the middle just give it a number like 50. If you run out of space, then lock the table and spread it out again. Shouldn't be necessary very often, if ever. If you need unlimited space between each, then switch to numeric. 3: Use an id to numeric lookup table. I.e. have a table hanging off to the side that has the REAL sequence numbers, and don't ever change them in the original table, but have another column there (or in the side table) that connects them to each other. Hope one of those ideas helps. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SQL dealing with subquery
On Jan 15, 2008 1:04 PM, Bryan Emrys <[EMAIL PROTECTED]> wrote:
> In other words, in the sample above, I only want to return:
> 'Canada','Ireland',0
> 'Canada','Netherlands',5
Try (untested):
SELECT t2.*
FROM (SELECT payor
FROM treaty_rates
WHEREpayee IN ('Netherlands', 'Ireland')
GROUP BY payor
HAVING MIN (rate) != MAX (rate)) t1
JOIN
treaty_rates t2 ON t1.payor = t2.payor
WHERE t2.payee IN ('Netherlands', 'Ireland');
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] obtaining the query string inside a trigger
Hi all. Im working on a "on each statement" update trigger, so NEW and OLD are NULL. Suppose a simple query like 'update mytable set id=id+500 where id < 50'...There is a way to obtaining the 'set id=..' and the where clause in some way? Thanks! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
