Re: [GENERAL] DB structure for logically similar objects in different
Eci Souji wrote: Hi, I was wondering if anyone had any experience with this type of setup and could share what they've learned. Basically we've got several different states that an item can be in. From what I've seen the way many places seem to deal with them is something along the lines of making bool values that act as switches... Ex: table items: item_id name description is_active is_sold_out is_banned Now we've started to see some problems with this sort of design. Namely we need to run sanity tests on every page that hits the items table to make sure is_active is true, is_sold_out is false, is_banned is false so on and so forth. I was thinking of splitting up states into different tables ala... table items_active: item_active_id name description table items_sold_out: item_sold_out_id name description The upside to split up tables is that we don't have to run sanity checks all over the place and this setup allows us to replicate the items_active table (the most frequently hit one) out to other DB boxes to help alleviate some of the select load. One of the downsides to this setup is we lose the power of a primary listing_id. The only way around that I could think of would be to have a separate listing table that kept track of what state an item was in and pointed to the primary key of that item in whatever state table it belonged too. You could just have a listing_id sequence that you get new numbers from and use that in your other tables. It can still be a primary key because it will be unique across your different tables. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] upgrade 8.0.4 to 8.1.4 breakage SOLVED
~ahem~ I'd forgotten to adjust pg_hba.conf All is good. Carry on. brian brian ally wrote: fedora core 2, upgrade from 8.0.4 - 8.1.4 via RPM I've just upgraded, everything /seemed/ to go swimmingly, but now i cannot connect via PHP. I can connect fine through psql, and all my tables have been restored fine. Using PEAR MDB2 wrappers (MDB2-2.0.3). Has anyone else seen this? Is this a PEAR issue? A similar upgrade on fedora core 4 went very well. The only difference there was it was done from source. Any debugging advice accepted. brian ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] upgrade 8.0.4 to 8.1.4 breakage
brian ally wrote: fedora core 2, upgrade from 8.0.4 - 8.1.4 via RPM Did you upgrade the php-pgsql library at the same time? Not 100% sure if you'll need to do that - probably will need to because the postgres internals will have changed quite a bit between those versions. -- Postgresql php tutorials http://www.designmagick.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: [GENERAL] Best open source tool for database design / ERDs?
I use the azzuri eclipse plugin. It's rudimentary, but get's the job done for smaller (a couple of dozens of tables) models. It has a commercial version which might be even better. t.n.a. On 28 May 2006 05:19:04 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: What open source tool do people here like for creating ER diagrams? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best open source tool for database design / ERDs?
Druid works ok http://druid.sourceforge.net/index.html BTJ On 28 May 2006 05:19:04 -0700 [EMAIL PROTECTED] wrote: What open source tool do people here like for creating ER diagrams? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] [ODBC] information request on postgresql -- oracle connect via odbc
Hi all, i'm looking for a method to connect natively a postgresql db to oracle ( maybe via odbc ? ) with something similar tothe oracle dblink. I connected successfully a oracle instance to a postgresql instance using unix-odbc, now i need to proceed on the other way. Can you please help me with some suggestion ? Thanks for your attention, best regards Glauco Mancini
Re: [GENERAL] Free 1000 Rupee bank note
What the hell is Rupees? On 5/29/06, AKHILESH GUPTA [EMAIL PROTECTED] wrote: Hi there, Help me by taking this survey. We can both get 1000 Rupees! Click here: http://rewards.popstarnetworkpanel.com/?r=EVEkOCgmiSJTBGsFDi0Oi=gmailp=4z=1tc=2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [ODBC] information request on postgresql -- oracle
On mán, 2006-05-29 at 10:21 +0200, Glauco Mancini wrote: i'm looking for a method to connect natively a postgresql db to oracle ( maybe via odbc ? ) with something similar to the oracle dblink. I connected successfully a oracle instance to a postgresql instance using unix-odbc, now i need to proceed on the other way. Can you please help me with some suggestion ? maybe plperlu and DBD::Oracle ? gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Free 1000 Rupee bank note
The legal tender in India, I guess. P. Joe Kramer wrote: What the hell is Rupees? On 5/29/06, AKHILESH GUPTA [EMAIL PROTECTED] wrote: Hi there, Help me by taking this survey. We can both get 1000 Rupees! Click here: http://rewards.popstarnetworkpanel.com/?r=EVEkOCgmiSJTBGsFDi0Oi=gmailp=4z=1tc=2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Free 1000 Rupee bank note
Joe Kramer schrieb: What the hell is Rupees? bart.gif ;) Indian money of course. Regards Tino (PS: looked like spam for me) ---(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: [GENERAL] [ODBC] information request on postgresql -- oracle
Ragnar schrieb: On mán, 2006-05-29 at 10:21 +0200, Glauco Mancini wrote: i'm looking for a method to connect natively a postgresql db to oracle ( maybe via odbc ? ) with something similar to the oracle dblink. I connected successfully a oracle instance to a postgresql instance using unix-odbc, now i need to proceed on the other way. Can you please help me with some suggestion ? maybe plperlu and DBD::Oracle ? Or easier: http://pgfoundry.org/projects/dbi-link/ Which uses that approach imho. Regards Tino ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] a row disapearing
Rafal Pietrak wrote: On Sat, 2006-05-27 at 14:06 -0400, Bruce Momjian wrote: Is this a feature, or a bug? And in fact, is there a construct to get both the count() and its selectors *in*case*, when the count is ZERO? All the above in postgres 8.1. It is supposed to work that way. In the first query, we have to return a row to show you the count, while in the second query, there is no 'id' value to show you, so we return nothing (nothing to GROUP BY). But is there a way to achieve one row output with both the count() and its selector, when the ocunt is ZERO? I'm digging this, because it looke like I need a VIEW, that returns such count() no matter what. And in fact the selector (which is coming from subquery) is more important for me in that case, than the count() itself (well, I need to distinquish zero from something, but nothing more). Is there a way to see it? SELECT dummy.id, count(xxx.id) FROM (SELECT :id as id FROM dual) as dummy LEFT JOIN xxx using (id) GROUP BY id; You owe the Oracle a natural left join replacement. /Nis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a row disapearing
On Mon, 2006-05-29 at 12:32 +0200, Nis Jorgensen wrote: Rafal Pietrak wrote: But is there a way to achieve one row output with both the count() and its selector, when the ocunt is ZERO? SELECT dummy.id, count(xxx.id) FROM (SELECT :id as id FROM dual) as dummy LEFT JOIN xxx using (id) GROUP BY id; You owe the Oracle a natural left join replacement. Luckily I've already figured that out, after Richard hinted me on using a JOIN. Thenx! -- -R ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] The server's LC_CTYPE locale
Tom Lane wrote: Michael Ben-Nes [EMAIL PROTECTED] writes: Im got the following error when the query string was one of the Hebrew chars: SELECT upper('׳©'); ERROR: invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with the database encoding. Hmph. I can't reproduce that here (using Fedora 4's version of he_IL.utf8 anyway). I assume your client_encoding was also UTF8? The troublesome character came through in your email as \327\251 (D7 A9) ... is that what you were actually entering? The reference to F9 in the other error message makes me think the character got munged somewhere in the email chain ... the Client Encoding is UTF8. Strangely I no longer get the second error: ERROR: invalid UTF-8 byte sequence detected near byte 0xf9 The first error returned: # SELECT lower('ש'); ERROR: invalid multibyte character for locale HINT: The server's LC_CTYPE locale is probably incompatible with the database encoding. The character that I sent is: [ש] U+05E9 #1513; HEBREW LETTER SHIN Im out of ideas, What else I should check ? regards, tom lane -- -- Michael Ben-Nes - Internet Consultant and Director. http://www.epoch.co.il - weaving the Net. Cellular: 054-4848113 -- ---(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: [GENERAL] DB structure for logically similar objects in different states...
# [EMAIL PROTECTED] / 2006-05-28 16:13:20 -0400: Basically we've got several different states that an item can be in. From what I've seen the way many places seem to deal with them is something along the lines of making bool values that act as switches... Ex: table items: item_id name description is_active is_sold_out is_banned Now we've started to see some problems with this sort of design. Namely we need to run sanity tests on every page that hits the items table to make sure is_active is true, is_sold_out is false, is_banned is false so on and so forth. I was thinking of splitting up states into different tables ala... table items_active: item_active_id name description table items_sold_out: item_sold_out_id name description would views help? CREATE VIEW items_to_sell AS SELECT item_id, name, description FROM items WHERE is_active = 1 AND is_sold_out = 0 AND is_banned = 0; -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] UTF-8 context of BYTEA datatype??
Hi! Within a UTF-8 encoded database, I have a table: CREATE TABLE pics (id serial not null unique, img bytea); The table is originally initialized with a set of IDs. Then I'm using perl-script to insert apropriate images by means of UPDATEing rows: --within my script called 'job'--- my $db = DBI-connect('DBI:Pg:dbname=mydb') or die DBI; my $z = $db-prepare(UPDATE pics set img=? where id=?) or die PREPARE; my $rc = $z-execute($content, $FILEID) or die EXEC; - But the result is somewhat unexpected: ---console output-- DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding UTF8: 0x89 EXEC at ./job line 22, chunk 1. - How come the bytearea is *interpreted* as having encoding? Or to put it the other way around: What column datatype should I use for an opoque binary value? (my postgres is 8.1.4) -- -R ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UTF-8 context of BYTEA datatype??
Am Montag, 29. Mai 2006 13:35 schrieb Rafal Pietrak: How come the bytearea is *interpreted* as having encoding? If you pass data in text mode, all data is subject to encoding handling. If you don't want that, you need to use the binary mode. Or to put it the other way around: What column datatype should I use for an opoque binary value? bytea is the one. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UTF-8 context of BYTEA datatype??
On Mon, May 29, 2006 at 01:35:58PM +0200, Rafal Pietrak wrote: The table is originally initialized with a set of IDs. Then I'm using perl-script to insert apropriate images by means of UPDATEing rows: --within my script called 'job'--- my $db = DBI-connect('DBI:Pg:dbname=mydb') or die DBI; my $z = $db-prepare(UPDATE pics set img=? where id=?) or die PREPARE; my $rc = $z-execute($content, $FILEID) or die EXEC; - But the result is somewhat unexpected: ---console output-- DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding UTF8: 0x89 EXEC at ./job line 22, chunk 1. - How come the bytearea is *interpreted* as having encoding? Actually, it's not the bytea type that is being interpreted, it's the string you're sending to the server that is. Before you send bytea data in a query string, you have to bytea encode it first. The DBD::Pg manpage seems to suggest something like: $rv = $sth-bind_param($param_num, $bind_value, { pg_type = DBD::Pg::PG_BYTEA }); Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] DB structure for logically similar objects in different
Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-05-28 16:13:20 -0400: Basically we've got several different states that an item can be in. From what I've seen the way many places seem to deal with them is something along the lines of making bool values that act as switches... Ex: table items: item_id name description is_active is_sold_out is_banned Now we've started to see some problems with this sort of design. Namely we need to run sanity tests on every page that hits the items table to make sure is_active is true, is_sold_out is false, is_banned is false so on and so forth. I was thinking of splitting up states into different tables ala... table items_active: item_active_id name description table items_sold_out: item_sold_out_id name description would views help? CREATE VIEW items_to_sell AS SELECT item_id, name, description FROM items WHERE is_active = 1 AND is_sold_out = 0 AND is_banned = 0; Views work for querying the chunks of data that match different states, but if I was looking for information based on a single item_id wouldn't I still need the sanity checks? - Ec ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DB structure for logically similar objects in different
Chris wrote: Eci Souji wrote: Hi, I was wondering if anyone had any experience with this type of setup and could share what they've learned. Basically we've got several different states that an item can be in. From what I've seen the way many places seem to deal with them is something along the lines of making bool values that act as switches... Ex: table items: item_id name description is_active is_sold_out is_banned Now we've started to see some problems with this sort of design. Namely we need to run sanity tests on every page that hits the items table to make sure is_active is true, is_sold_out is false, is_banned is false so on and so forth. I was thinking of splitting up states into different tables ala... table items_active: item_active_id name description table items_sold_out: item_sold_out_id name description The upside to split up tables is that we don't have to run sanity checks all over the place and this setup allows us to replicate the items_active table (the most frequently hit one) out to other DB boxes to help alleviate some of the select load. One of the downsides to this setup is we lose the power of a primary listing_id. The only way around that I could think of would be to have a separate listing table that kept track of what state an item was in and pointed to the primary key of that item in whatever state table it belonged too. You could just have a listing_id sequence that you get new numbers from and use that in your other tables. It can still be a primary key because it will be unique across your different tables. A shared sequence would help, but if I tried to use the single listing_id as my reference how could I figure out what table (and thus what state) the item was in? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Question Regarding DELETE FROM ONLY
Hello List: Please observe the following example that reproduces my problem: CREATE TABLE ptable (code VARCHAR) WITHOUT OIDS; CREATE TABLE CREATE TABLE ctable (code VARCHAR, name VARCHAR) INHERITS (ptable) WITHOUT OIDS; NOTICE: merging column code with inherited definition CREATE TABLE INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One'); rnd=# SELECT * FROM ptable; code -- code_one (1 row) rnd=# SELECT * FROM ctable; code | name ---+-- code_one | Code One (1 row) DELETE FROM ONLY ctable WHERE code ~* 'code_one'; rnd=# SELECT * FROM ptable; code -- (0 rows) The record in ctable AND the record in ptable are both deleted even though I specified ONLY ctable in the delete phrase. Why is this happening? The two tables in this example represent a greatly simplified version of what I'm doing in developing an application and if ONLY doesn't work then I've got a big problem. Have I misunderstood inheritance altogether? TIA... rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best open source tool for database design / ERDs?
On 5/29/06, Bjørn T Johansen [EMAIL PROTECTED] wrote: Druid works ok http://druid.sourceforge.net/index.html Are there a couple of screenshots available on the net, a flash demo perhaps? t.n.a. ---(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: [GENERAL] Best open source tool for database design / ERDs?
On Mon, 29 May 2006 14:43:19 +0200 Tomi NA [EMAIL PROTECTED] wrote: On 5/29/06, Bjørn T Johansen [EMAIL PROTECTED] wrote: Druid works ok http://druid.sourceforge.net/index.html Are there a couple of screenshots available on the net, a flash demo perhaps? t.n.a. Don't know... BTJ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UTF-8 context of BYTEA datatype??
On Mon, 2006-05-29 at 14:01 +0200, Martijn van Oosterhout wrote: How come the bytearea is *interpreted* as having encoding? Actually, it's not the bytea type that is being interpreted, it's the string you're sending to the server that is. Before you send bytea data in a query string, you have to bytea encode it first. The DBD::Pg manpage seems to suggest something like: $rv = $sth-bind_param($param_num, $bind_value, { pg_type = DBD::Pg::PG_BYTEA }); Hmmm, despite initial euphoria, this doesn't actually work. Subsequently I've also tried putting SQL_BINARY in place of that hash-ref, and plain DBD::Pg::PG_BYTEA, and also I tried to use 'TYPE =' instead of pg_type. (All those hints in man DBI). None of that worked either. But I also did: $db-do('SET client_encoding = LATIN1') or die SET; just after connect and before prepare, and this produced a slightly different result no ERROR, but the image was cut short to 9-bytes inside the database data-row. Would perl have interpreted this command according to it's semantics? And change it's own default string handling accordingly!? Not knowing the internals, I wouldn't bet on whichever, but I have my doughts - my quess is thet DBI driver doesn't go that far. So if it hasn't interpretted the 'SET client_encodding' internally, but just passed that to database, the only thing that changed is the database frontend context. So may be the original error came from the database itself anyway? Any ideas? (still hopping I wont have to write a C-level interface function just to test what's really happening :) -- -R ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Question Regarding DELETE FROM ONLY
On Mon, May 29, 2006 at 08:40:43AM -0400, Terry Lee Tucker wrote: INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One'); rnd=# SELECT * FROM ptable; code -- code_one (1 row) rnd=# SELECT * FROM ctable; code | name ---+-- code_one | Code One (1 row) These aren't two distinct records -- they're the same record, the one in ctable, as the following queries show: SELECT tableoid::regclass, * FROM ptable; SELECT tableoid::regclass, * FROM ctable; You can use FROM ONLY to see that the record doesn't actually exist in ptable: SELECT tableoid::regclass, * FROM ONLY ptable; DELETE FROM ONLY ctable WHERE code ~* 'code_one'; rnd=# SELECT * FROM ptable; code -- (0 rows) The record in ctable AND the record in ptable are both deleted even though I specified ONLY ctable in the delete phrase. Why is this happening? Because there was only one record, the one in ctable, and you deleted it. When you inserted the record into ctable that's the only place it went. The query against ptable showed records in the parent table (none) and records in its child tables (one). After you delete the record from the child the subsequent query against the parent returns zero rows because both tables are now empty (the parent was always empty and the child had its one record deleted). -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] no prompt in psql!!!
We haven't made much progress on this lately, but the good news is that I don't remember anyone reporting a problem for about a year, so I figured it was fixed. I think it has to be assumed to be a buggy terminal program. --- Gurjeet Singh wrote: Hi, If I use mingw's rxvt.exe shell, I can't see any prompt in psql. In a message to -patches (http://archives.postgresql.org/pgsql-hackers-win32/2003-09/msg00019.php), Bruce said: The MinGW command console seems to have some problems with flushing output to the screen (no prompt, no errors display), but the same binary runs fine in CMD.EXE. We can look at that later. I don't know if this problem has been fixed! I am running Win2K. psql is running just fine from cmd.exe, and from the sh.exe run from within that!! Following are the sessions: From MinGW: $ psql test postgres \set VERSION = 'PostgreSQL 8.2devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)' AUTOCOMMIT = 'on' VERBOSITY = 'default' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = ' ' DBNAME = 'test' USER = 'postgres' PORT = '5432' ENCODING = 'SQL_ASCII' \q $ From CMD and sh within that: E:\Documents and Settings\Gurjeetpsql test postgres Welcome to psql 8.2devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. test=# \set VERSION = 'PostgreSQL 8.2devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)' AUTOCOMMIT = 'on' VERBOSITY = 'default' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = ' ' DBNAME = 'test' USER = 'postgres' PORT = '5432' ENCODING = 'SQL_ASCII' test=# \q E:\Documents and Settings\Gurjeetsh sh-2.04$ psql test postgres Welcome to psql 8.2devel, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. test=# \set VERSION = 'PostgreSQL 8.2devel on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)' AUTOCOMMIT = 'on' VERBOSITY = 'default' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = ' ' DBNAME = 'test' USER = 'postgres' PORT = '5432' ENCODING = 'SQL_ASCII' test=# \q sh-2.04$ Thanks in advance, Gurjeet. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] The server's LC_CTYPE locale
Michael Ben-Nes [EMAIL PROTECTED] writes: The character that I sent is: [שâ] U+05E9 #1513; HEBREW LETTER SHIN Well, that does work out to D7 A9 in UTF8, if I'm doing the arithmetic correctly. I can't replicate any problem in either 8.1.4 or HEAD. It's possible that this is a bug that's been fixed since 8.1.3, but I don't recall any change in that area. I think more likely the difference is between the he_IL.utf8 locale definitions in Fedora 4 and Debian. Perhaps you should check for available updates to the locale. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question Regarding DELETE FROM ONLY
On Monday 29 May 2006 09:43 am, Michael Fuhr [EMAIL PROTECTED] thus communicated: -- On Mon, May 29, 2006 at 08:40:43AM -0400, Terry Lee Tucker wrote: -- INSERT INTO ctable (code, name) VALUES ('code_one', 'Code One'); -- rnd=# SELECT * FROM ptable; -- code -- -- -- code_one -- (1 row) -- -- rnd=# SELECT * FROM ctable; -- code | name -- ---+-- -- code_one | Code One -- (1 row) -- -- These aren't two distinct records -- they're the same record, the -- one in ctable, as the following queries show: -- -- SELECT tableoid::regclass, * FROM ptable; -- SELECT tableoid::regclass, * FROM ctable; -- -- You can use FROM ONLY to see that the record doesn't actually exist -- in ptable: -- -- SELECT tableoid::regclass, * FROM ONLY ptable; -- -- DELETE FROM ONLY ctable WHERE code ~* 'code_one'; -- -- rnd=# SELECT * FROM ptable; -- code -- -- -- (0 rows) -- -- The record in ctable AND the record in ptable are both deleted even though I -- specified ONLY ctable in the delete phrase. Why is this happening? -- -- Because there was only one record, the one in ctable, and you deleted -- it. When you inserted the record into ctable that's the only place -- it went. The query against ptable showed records in the parent -- table (none) and records in its child tables (one). After you -- delete the record from the child the subsequent query against the -- parent returns zero rows because both tables are now empty (the -- parent was always empty and the child had its one record deleted). -- -- -- -- Michael Fuhr -- Thanks for the response Michael. I'm beginning to see the light. ---(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: [GENERAL] Restoring databases from a different installment on Windows
Berislav Lopac [EMAIL PROTECTED] wrote I have recently reinstalled my Windows mychine, including the PostgreSQL server, but (due to a system crash, unrelated to Postgres) I wasn't able to dump my databases to import them now. However, I have a full copy of the original system, including all the files of the original Postgres installation. Is there a way to restore the databases from the original installation into the new one? For example, in MySQL I would be able to just copy the data files; is there something similar in Postgres? If you don't have a tablespace built (i.e. all data are in your initial data directory), then just like MySQL, you can replace your current data directory with that folder. Regards, Qingqing ---(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
[GENERAL] National Number to text conversion
Hello,is there any known way to convert numbers to their text equivalent in English and other languages?like 25 -- Twenty FiveI am interested in Arabic specifically.
Re: [GENERAL] National Number to text conversion
Make a table that you can reference. Fill it with Arabic, English or any language that you wish. Make it available to the list so those who wish can particpate and receive a copy of the finished product. Bob - Original Message - From: Samer Abukhait To: pgsql-general@postgresql.org Sent: Monday, May 29, 2006 8:09 AM Subject: [GENERAL] National Number to text conversion Hello,is there any known way to convert numbers to their text equivalent in English and other languages?like 25 -- Twenty FiveI am interested in Arabic specifically.
Re: [GENERAL] National Number to text conversion
Not sure that i understand..Fill what exactly in the table?I am seeking for a dynamic way to combine text numbersPlease also note that in Arabic .. text numbers behave differently according to the item you are counting (male or female Items) On 5/29/06, Bob Pawley [EMAIL PROTECTED] wrote: Make a table that you can reference. Fill it with Arabic, English or any language that you wish. Make it available to the list so those who wish can particpate and receive a copy of the finished product. Bob - Original Message - From: Samer Abukhait To: pgsql-general@postgresql.org Sent: Monday, May 29, 2006 8:09 AM Subject: [GENERAL] National Number to text conversion Hello,is there any known way to convert numbers to their text equivalent in English and other languages?like 25 -- Twenty FiveI am interested in Arabic specifically.
Re: [GENERAL] Free 1000 Rupee bank note
...and as I learned the last time I returned from India, no bank outside of India will exchange rupees, because it's technically illegal to take rupees outside the country. So unless you happen to be in or going to India, 1000 rupees isn't too useful. On May 29, 2006, at 2:37 AM, Tino Wildenhain wrote: Joe Kramer schrieb: What the hell is Rupees? bart.gif ;) Indian money of course. Regards Tino (PS: looked like spam for me) ---(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 ---(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: [GENERAL] National Number to text conversion
Bob Pawley wrote: Make a table that you can reference. Fill it with Arabic, English or any language that you wish. Make it available to the list so those who wish can particpate and receive a copy of the finished product. Bob - Original Message - From: Samer Abukhait To: pgsql-general@postgresql.org Sent: Monday, May 29, 2006 8:09 AM Subject: [GENERAL] National Number to text conversion Hello, is there any known way to convert numbers to their text equivalent in English and other languages? like 25 -- Twenty Five I am interested in Arabic specifically. I was thinking the same. But Bob's reply has me wondering if there are any online resources for this sort of thing. Specifically, i have a look-up table, with most (possibly all - i don't know) countries' 2-letter ISO codes and english names. Just this weekend, i've learned that i'll require the names in french, as well. I'd thought about feeding the names to Babelfish, but, if such a list exists somewhere that'd be a real time saver. I have a list of Canadian provinces, in both official languages, to swap (w00t!). brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Free 1000 Rupee bank note
And even if you can convert it, as of today 1000 Rupees is 17.16745 Euro, or 21.85553 US Dollar, which, concerning the convertion fee, would not give a real good meal in Europe or USA. Harald - on different matter: did you ever dream of visiting CERN? The place where the antimatter for exploding Vatican is created? To eat in cantinas with the worlds highest propability to stand in queue with future or past Nobel Prize Winners? To talk about Web 2.5 at the place where Web 0.1 up to Web 1.0 were developed? register at www.europython.org! ---(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: [GENERAL] Free 1000 Rupee bank note
Perhaps it was in reference to Hyrule Rupees? :) http://en.wikipedia.org/wiki/Rupee_(Legend_of_Zelda) Ben wrote: ...and as I learned the last time I returned from India, no bank outside of India will exchange rupees, because it's technically illegal to take rupees outside the country. So unless you happen to be in or going to India, 1000 rupees isn't too useful. On May 29, 2006, at 2:37 AM, Tino Wildenhain wrote: Joe Kramer schrieb: What the hell is Rupees? bart.gif ;) Indian money of course. Regards Tino (PS: looked like spam for me) ---(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 ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] National Number to text conversion
Em Segunda 29 Maio 2006 12:22, brian ally escreveu: I was thinking the same. But Bob's reply has me wondering if there are any online resources for this sort of thing. Specifically, i have a look-up table, with most (possibly all - i don't know) countries' 2-letter ISO codes and english names. Just this weekend, i've learned that i'll require the names in french, as well. I'd thought about feeding the names to Babelfish, but, if such a list exists somewhere that'd be a real time saver. At least for pt_BR (Brazilian Portuguese) there are lots of functions around that can be used. What they have of bad is that the names are expressed as an array inside the function itself. I believe that the suggestion was to use one of these functions -- for any language, with modifications for number gender, as said it is needed in arabian -- and instead of storing the names in an array storing them in a table would be interesting. I believe that this table should contain: - numbers from 0 to 20, 30, 40, 50, 60, 70, 80, 90, 100 (this has a variation in pt_BR), 200, 300, 400, 500, 600, 700, 800, 900, 1000 - suffixes for thousands, millions, billions, etc. - suffixes for fractions (cents, dimes, quarters, etc.) - gender (in pt_BR they are genderless, in pt the male gender is the dominant with regards to the language, so I'd define the male gender as default in a function...) - language (if this becomes a function to share with other people, I'd make English the default) - groupping character, decimal separator (in pt_BR and en_US they are reversed, for example) I don't see it easily done in a multi-language way due to several particularities of each language... If this is going to be used for money, things might get a bit harder (IIRC, in Portugal they have the money symbol after the integer part and before the decimal part, making it a new decimal separator...)... Anyway, this is what I believe was suggested to be done :-) Sorry for being so discursive... -- Jorge Godoy [EMAIL PROTECTED] ---(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: [GENERAL] National Number to text conversion
Samer Abukhait [EMAIL PROTECTED] writes: I am seeking for a dynamic way to combine text numbers There's some code in the money datatype for this, but it's hard-wired for English. I have a vague feeling that I've seen a Perl module for the task, which might possibly have international capability. Search CPAN --- if you find it you could embed it into a plperl function easily enough. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] National Number to text conversion
am 29.05.2006, um 17:09:10 +0200 mailte Samer Abukhait folgendes: Hello, is there any known way to convert numbers to their text equivalent in English and other languages? like 25 -- Twenty Five IIRC i have @work a book about sed and awk, and, iirc, there are a code for awk or sed for this task. I'm not sure... I am interested in Arabic specifically. Uhm, sorry, not for arabic... -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Charset conversion error
Hi, I have an error after updating my database up to 8.1.4 version.SQL Error: ERROR: character 0xb9 of encoding WIN1251 has no equivalent in MULE_INTERNAL'. My client program encoding is windows-1251 and database encoding is koi8. What can I do to rectify the situation?Verba volent, scripta manentDan Black
Re: [GENERAL] Temp Tables
2006/5/25, Brandon E Hofmann [EMAIL PROTECTED]: (..)I tried defining composite types, but get a runtime error that it isn'tavailable. That is you postgres-- William Leite AraújoEspecialista em Geoprocessamento- UFMG Bacharel em Ciêncida da Computação - UFMGMSN:[EMAIL PROTECTED]ICQ:222159351GTalk: [EMAIL PROTECTED]Yahoo: [EMAIL PROTECTED]Skype: william.bh
Re: [GENERAL] Temp Tables
2006/5/25, Brandon E Hofmann [EMAIL PROTECTED]: (..)I tried defining composite types, but get a runtime error that it isn'tavailable. That is you postgresql version? Why you need return the temporary table type? Why create a temporary table if you use a function return type setof? -- William Leite Araújo
Re: [GENERAL] List of countries (WAS: National Number to text conversion)
brian ally wrote: I was thinking the same. But Bob's reply has me wondering if there are any online resources for this sort of thing. Specifically, i have a look-up table, with most (possibly all - i don't know) countries' 2-letter ISO codes and english names. Just this weekend, i've learned that i'll require the names in french, as well. I'd thought about feeding the names to Babelfish, but, if such a list exists somewhere that'd be a real time saver. Do you mean something like http://www.iso.org/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-fr1-semic.txt Unfortunately the list is in ALL CAPS. I believe I can extract a decapitated version from one of my systems, if you have time to wait until tomorrow. /Nis ---(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
[GENERAL] ERROR: checkpoint request failed
When I try and create a new database I get the following error: ERROR: checkpoint request failed HINT: Consult the server log for details. but the error log (/var/log/postgresql/postgresql-8.1-main.log) just repeats the same error message. This is Postgres 8.1, from Debian Unstable debs. There are other databases on the same server, I somehow managed to create them ( a few months ago) -- -S Sports Photography in South Yorkshire Derbyshire http://www.stuartgrimshaw.co.uk ---(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: [GENERAL] List of countries (WAS: National Number to text conversion)
Nis Jorgensen wrote: brian ally wrote: I was thinking the same. But Bob's reply has me wondering if there are any online resources for this sort of thing. Specifically, i have a look-up table, with most (possibly all - i don't know) countries' 2-letter ISO codes and english names. Just this weekend, i've learned that i'll require the names in french, as well. I'd thought about feeding the names to Babelfish, but, if such a list exists somewhere that'd be a real time saver. Do you mean something like http://www.iso.org/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-fr1-semic.txt Unfortunately the list is in ALL CAPS. I believe I can extract a decapitated version from one of my systems, if you have time to wait until tomorrow. /Nis Thanks, Nis. Here's my revised list, using those (errors of letter-case entirely my own, owing to my hazy understanding of capitilisation for hyphenated french nouns): ISO-3166Name (en) Name(fr) 'AF''Afghanistan' 'Afghanistan' 'AL''Albania' 'Albanie' 'DZ''Algeria' 'Algérie' 'AS''American Samoa''Samoa Américaines' 'AD''Andorra' 'Andorre' 'AO''Angola''Angola' 'AI''Anguilla' 'Anguilla' 'AQ''Antarctica''Antarctique' 'AG''Antigua and Barbuda' 'Antigua-et-barbuda' 'AR''Argentina' 'Argentine' 'AM''Armenia' 'Arménie' 'AW''Aruba' 'Aruba' 'AX''Åland Islands' 'Åland, Îles' 'AU''Australia' 'Australie' 'AT''Austria' 'Autriche' 'AZ''Azerbaijan''Azerbaïdjan' 'BS''Bahamas' 'Bahamas' 'BH''Bahrain' 'Bahreïn' 'BD''Bangladesh''Bangladesh' 'BB''Barbados' 'Barbade' 'BY''Belarus' 'Bélarus' 'BE''Belgium' 'Belgique' 'BZ''Belize''Belize' 'BJ''Benin' 'Bénin' 'BM''Bermuda' 'Bermudes' 'BT''Bhutan''Bhoutan' 'BO''Bolivia' 'Bolivie' 'BA''Bosnia and Herzegovina''Bosnie-herzégovine' 'BW''Botswana' 'Botswana' 'BV''Bouvet Island' 'Bouvet, Île' 'BR''Brazil''Brésil' 'IO' 'British Indian Ocean Territory' 'Océan Indien, Territoire Britannique de l'' 'BN''Brunei Darussalam' 'Brunéi Darussalam' 'BG''Bulgaria' 'Bulgarie' 'BF''Burkina Faso' 'Burkina Faso' 'BI''Burundi' 'Burundi' 'KH''Cambodia' 'Cambodge' 'CM''Cameroon' 'Cameroun' 'CA''Canada''Canada' 'CV''Cap Verde' 'Cap-vert' 'KY''Cayman Islands''Caïmanes, Îles' 'CF''Central African Republic' 'Centrafricaine, République' 'TD''Chad' 'Tchad' 'CL''Chile' 'Chili' 'CN''China' 'Chine' 'CX''Christmas Island' 'Christmas, Île' 'CC''Cocos (Keeling) Islands' 'Cocos (Keeling), Îles' 'CO''Colombia' 'Colombie' 'KM''Comoros' 'Comores' 'CD' 'Congo, Democratic Republic of the' 'Congo, la République Démocratique du' 'CG''Congo, Republic of''Congo' 'CK''Cook Islands' 'Cook, Îles' 'CR''Costa Rica''Costa Rica' 'CI''Côte d'Ivoire' 'Côte D'ivoire' 'HR''Croatia/Hrvatska' 'Croatie' 'CU''Cuba' 'Cuba' 'CY''Cyprus''Chypre' 'CZ''Czech Republic''Tchèque, République' 'DK''Denmark' 'Danemark' 'DJ''Djibouti' 'Djibouti' 'DM''Dominica' 'Dominique' 'DO''Dominican Republic''Dominicaine, République' 'TL''East Timor''Timor-Leste' 'EC''Ecuador' 'Équateur' 'EG''Egypt' 'Égypte' 'SV''El Salvador' 'El Salvador' 'GQ''Equatorial Guinea' 'Guinée équatoriale' 'ER''Eritrea' 'Érythrée' 'EE''Estonia' 'Estonie' 'ET''Ethiopia' 'Éthiopie' 'FK''Falkland Islands (Malvina)''Falkland, Îles (Malvinas)' 'FO''Faroe Islands' 'Féroé, Îles' 'FJ''Fiji' 'Fidji' 'FI''Finland' 'Finlande' 'FR''France''France' 'GF''French Guiana' 'Guyane Française' 'PF''French Polynesia' 'Polynésie Française' 'TF''French Southern Territories' 'Terres Australes Françaises' 'GA''Gabon' 'Gabon' 'GM''Gambia''Gambie' 'GE''Georgia' 'Géorgie' 'DE''Germany' 'Allemagne' 'GH''Ghana' 'Ghana' 'GI''Gibraltar' 'Gibraltar' 'GR''Greece''Grèce' 'GL''Greenland' 'Groenland' 'GD''Grenada' 'Grenade' 'GP''Guadeloupe''Guadeloupe' 'GU''Guam' 'Guam' 'GT''Guatemala' 'Guatemala' 'GG''Guernsey' 'Guernesey' 'GN''Guinea''Guinée' 'GW''Guinea-Bissau' 'Guinée-bissau' 'GY''Guyana''Guyana' 'HT''Haiti' 'Haïti' 'HM''Heard and McDonald Islands''Heard, Île et Mcdonald, Îles' 'VA' 'Holy See (City Vatican State)' 'Saint-siège (État de la Cité du Vatican)' 'HN''Honduras' 'Honduras' 'HK''Hong Kong' 'Hong-kong' 'HU''Hungary' 'Hongrie' 'IS''Iceland' 'Islande' 'IN''India' 'Inde' 'ID''Indonesia' 'Indonésie' 'IR''Iran
Re: [GENERAL] ERROR: checkpoint request failed
Stuart Grimshaw [EMAIL PROTECTED] writes: When I try and create a new database I get the following error: ERROR: checkpoint request failed HINT: Consult the server log for details. but the error log (/var/log/postgresql/postgresql-8.1-main.log) just repeats the same error message. What appears *before* that in the log? There should be some error message coming out of the bgwriter. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Charset conversion error
Hi, I have an error after updating my database up to 8.1.4 version. SQL Error: ERROR: character 0xb9 of encoding WIN1251 has no equivalent in MULE_INTERNAL'. My client program encoding is windows-1251 and database encoding is koi8. What can I do to rectify the situation? It suggests that Windows-1251's 0xb9 cannot be converted to KOI8. You should check your Windows-1251 data. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] National Number to text conversion
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 is there any known way to convert numbers to their text equivalent in English and other languages? See: http://search.cpan.org/~rvasicek/Lingua-Num2Word-0.07/Num2Word.pm Easy enough to put into a pl/perlu. I've used the English version successfully before. I am interested in Arabic specifically. I did not see Lingua::AR::Numbers in the list on that page, but it should be easy enough to create one based on the existing modules (and release it so others can use it as well). See also: http://search.cpan.org/search?query=Arabic - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200605291907 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEe3/gvJuQZxSWSsgRAmGtAJ4/Pt2rz7uOaaaobpM05q4J/TqENgCfXwDc B5IzxhxXMC4wmr7kaxmE0js= =0FIt -END PGP SIGNATURE- ---(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: [GENERAL] Status of gist locking in 8.1.3?
Tom Lane writes: CREATE INDEX shouldn't block any concurrent SELECT, regardless of which index AM is involved. The problem was that the table needed a vacuum full. It was a large table and had done a massive update. It is not that it was blocked, but that it was just taking a very long time. Is there a way to tell what tables have locks on them? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Status of gist locking in 8.1.3?
Francisco Reyes wrote: Tom Lane writes: CREATE INDEX shouldn't block any concurrent SELECT, regardless of which index AM is involved. The problem was that the table needed a vacuum full. It was a large table and had done a massive update. It is not that it was blocked, but that it was just taking a very long time. Is there a way to tell what tables have locks on them? SELECT * from pg_locks ; (version 7.4 and above at least, don't have an install earlier than that). -- Postgresql php tutorials http://www.designmagick.com/ ---(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
[GENERAL] whats the standard text search query?
I have this small web aplication.When i used mysql i used to do somthing like:SELECT * FROM table WHERE name like '%searchstring%' OR description like '%searchstring%';that doesnt really looks eficient... well... actually it works with no problem. anyway... what's the standard query for a text search?thanks in advance
Re: [GENERAL] whats the standard text search query?
On May 30, 2006, at 12:46 , Pedro wrote: SELECT * FROM table WHERE name like '%searchstring%' OR description like '%searchstring%'; that doesnt really looks eficient... well... actually it works with no problem. Depending on your needs, that may work just fine. PostgreSQL includes regex matching as well. anyway... what's the standard query for a text search? If you're looking for full text search, take a look at tsearch2 in contrib. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Status of gist locking in 8.1.3?
Chris writes: Is there a way to tell what tables have locks on them? SELECT * from pg_locks ; (version 7.4 and above at least, don't have an install earlier than that). And this is per DB right? Any way to tell locks in all DBs? In particular if planning to bounce back the DB would be nice to know if anyone had any locks open.. ---(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: [GENERAL] Status of gist locking in 8.1.3?
Francisco Reyes wrote: Chris writes: Is there a way to tell what tables have locks on them? SELECT * from pg_locks ; (version 7.4 and above at least, don't have an install earlier than that). And this is per DB right? No, this is per system. Not sure how it works with permissions (eg if you have access to one db but not another does it still show?) but as a superuser, it will show all locks across the whole system. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings