[GENERAL] Carriage return in text fields
Hello ! I have to transfert data from MS-Access to PostgreSQL 8.03 under Windows 2003 server. Text fields may contain a carriage return (CR-LF). I use the copy instruction. Under Pg-Admin III 1.21, I obtain an error message for that carriage return and a hint : use \n. If I use a filter on the cvs file replacing carriage return with \n, copy works but I have the char \n in the text fields, not the carriage return !? If I use PSQL, the carriage return are replaced by a space ! In Pg-Admin III I can insert a carriage return (with SHIFT ENTER) in a text field. How can I transfert my text fields WITH all the carriage return inside the text fields? Thanks for your tips... Best regards. Luc
Re: [GENERAL] Audit trail ?
Hello, Mike! Your solution for audit trail is wonderfull! Easy and elegant ! It helped me a lot and I successfully implemented it, with small modifications. Thanky you very much! - Original Message - From: Mike Rylander [EMAIL PROTECTED] To: Zlatko Matic [EMAIL PROTECTED] Cc: Postgresql-General pgsql-general@postgresql.org Sent: Sunday, May 29, 2005 6:21 PM Subject: Re: [GENERAL] Audit trail ? On 5/29/05, Zlatko Matic [EMAIL PROTECTED] wrote: Hello. I must have audit trail of all insert/update/delete on several table. I have several questions regarding that: 1. Is it better to have one audit trail table that collects insert/update/delete of all audited tables, or it is better to have separate audit trail table for every audited table ? 2. To use triggers or rules ? Example for both ? 3. Could someone give me an example of a successfull audit trail solution ? I'm running on lack of time, so any help would be precious... We use the audit table per real table approach. The SQL script to create the audit trail functions and triggers is attached. There are three example audit trail table creation calls right before the COMMIT. Hope that helps! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Subselects - recursion problem
On Mon, May 30, 2005 at 07:33:04PM +1000, Philip Rhoades wrote: People, The following script works (I have confirmed it by doing two separate views and doing a select on them) - but I don't understand why there isn't a recursion problem with c1.policy and c2.policy - is there some sort of trick happening? recursion problem ? It's called a correlated subquery. SQL is declarative, you state what you want and the database figures out how to get the answer for you. I think you need to go and read up on the basics of SQL. If you want to see *how* the database is working out the answer, use explain and it'll display the query plan. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpIGdtM6kmhx.pgp Description: PGP signature
[GENERAL] Subselects - recursion problem
People, The following script works (I have confirmed it by doing two separate views and doing a select on them) - but I don't understand why there isn't a recursion problem with c1.policy and c2.policy - is there some sort of trick happening? Thanks, Phil. SELECT c1.loc, c1.lob, c1.policy FROM crec AS c1 WHERE c1.t_type = '1' AND c1.t_diss = '2' AND c1.recon = 'Y' AND c1.policy = ( SELECT c2.policy FROM crec AS c2 WHERE c2.t_type = '1' AND c2.t_diss = '0' AND c2.recon = 'N' AND c1.loc = c2.loc AND c1.lob = c2.lob AND c1.policy = c2.policy ) ORDER BY c1.loc, c1.lob, c1.policy ; -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Mobile: +61:0411-185-652 Fax: +61:2:8923-5363 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Subselects - recursion problem
Philip Rhoades wrote: The following script works (I have confirmed it by doing two separate views and doing a select on them) - but I don't understand why there isn't a recursion problem with c1.policy and c2.policy - is there some sort of trick happening? The subquery is evaluated for each row of the outer query. The references to c1 are for each evaluation replaced by the current values of the outer query. For the purpose of the subquery, they behave like constants. Internally, the query might actually be transformed into a join of c1 and c2. Maybe that helps you grasp it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Subselects - recursion problem
Martijn, On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote: On Mon, May 30, 2005 at 07:33:04PM +1000, Philip Rhoades wrote: People, The following script works (I have confirmed it by doing two separate views and doing a select on them) - but I don't understand why there isn't a recursion problem with c1.policy and c2.policy - is there some sort of trick happening? recursion problem ? It's called a correlated subquery. SQL is declarative, you state what you want and the database figures out how to get the answer for you. I think you need to go and read up on the basics of SQL. Declarative or not, it looks strange having the output of the first select dependent on a second select, which is dependent on the output of the first select . . If you want to see *how* the database is working out the answer, use explain and it'll display the query plan. Sort (cost=402711.95..402711.96 rows=1 width=20) Sort Key: loc, lob, policy - Index Scan using crec_9 on crec c1 (cost=0.00..402711.94 rows=1 width=20) Index Cond: (t_diss = '2'::bpchar) Filter: ((t_type = '1'::bpchar) AND (recon = 'Y'::bpchar) AND (policy = (subplan))) SubPlan - Index Scan using crec_1 on crec c2 (cost=0.00..6.01 rows=1 width=10) Index Cond: (($0 = loc) AND ($1 = lob) AND ($2 = policy)) Filter: ((t_type = '1'::bpchar) AND (t_diss = '0'::bpchar) AND (recon = 'N'::bpchar)) I can see that PG doesn't use policy in the filter of the subplan at all but that doesn't help very much . . Thanks anyway. Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Mobile: +61:0411-185-652 Fax: +61:2:8923-5363 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Subselects - recursion problem
On Mon, May 30, 2005 at 08:32:15PM +1000, Philip Rhoades wrote: Martijn, On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote: recursion problem ? It's called a correlated subquery. SQL is declarative, you state what you want and the database figures out how to get the answer for you. I think you need to go and read up on the basics of SQL. Declarative or not, it looks strange having the output of the first select dependent on a second select, which is dependent on the output of the first select . . Not really, if you think about it. SQL is a form of relational algebra and like in normal algebra it's not unusual to have various variables relating to eachother in various ways. Some expressions may look recursive, but that's just another relationship. As you can see from the query plan, it scans through each record in c1 filtering out rows based on the easy conditions. It then works out the subquery for each row and compares the result with policy. If it works the row is returned otherwise it keeps going. There's no recursion. Perhaps the easiest way to think about it is having the subquery as a function, and write it like: SELECT c1.loc, c1.lob, c1.policy FROM crec AS c1 WHERE c1.t_type = '1' AND c1.t_diss = '2' AND c1.recon = 'Y' AND c1.policy = Subquery( c1 ) ORDER BY c1.loc, c1.lob, c1.policy ; That doesn't look recursive to me, yet it's the same thing... Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpFpoJ22dqVK.pgp Description: PGP signature
Re: [GENERAL] Subselects - recursion problem
Martijn, On Mon, 2005-05-30 at 21:08, Martijn van Oosterhout wrote: On Mon, May 30, 2005 at 08:32:15PM +1000, Philip Rhoades wrote: Martijn, On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote: recursion problem ? It's called a correlated subquery. SQL is declarative, you state what you want and the database figures out how to get the answer for you. I think you need to go and read up on the basics of SQL. Declarative or not, it looks strange having the output of the first select dependent on a second select, which is dependent on the output of the first select . . Not really, if you think about it. SQL is a form of relational algebra and like in normal algebra it's not unusual to have various variables relating to eachother in various ways. Some expressions may look recursive, but that's just another relationship. OK. As you can see from the query plan, it scans through each record in c1 filtering out rows based on the easy conditions. It then works out the subquery for each row and compares the result with policy. If it works the row is returned otherwise it keeps going. There's no recursion. OK, makes sense in English . . Perhaps the easiest way to think about it is having the subquery as a function, and write it like: SELECT c1.loc, c1.lob, c1.policy FROM crec AS c1 WHERE c1.t_type = '1' AND c1.t_diss = '2' AND c1.recon = 'Y' AND c1.policy = Subquery( c1 ) ORDER BY c1.loc, c1.lob, c1.policy ; That doesn't look recursive to me, yet it's the same thing... Hope this helps, No, that looks just as recursive to me but I get the picture from the English explanation so thanks again. Regards, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Mobile: +61:0411-185-652 Fax: +61:2:8923-5363 E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Problem with void integer
I have a big problem to jump from 7.2 to 8.0.3 In my application I was doing a major use of int and float And the difference between '0' and void NULL '' data content. Now it seems to me that void is not allowed for an integer ? What should I do ? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Problem with void integer
On Mon, May 30, 2005 at 03:46:09PM +0200, Alexandre Lollini wrote: I have a big problem to jump from 7.2 to 8.0.3 In my application I was doing a major use of int and float And the difference between '0' and void NULL '' data content. Now it seems to me that void is not allowed for an integer ? Ok, NULL, 0 (number zero) and '' (empty string) are all distinct. Oracle considers NULL and empty string to be the same, which causes confusion but that is not the problem here. In older versions of PostgreSQL, an empty string could be converted to an integer and would result in 0. This is contrary to SQL spec and was changed somewhere after 7.2. The release notes from back then should mention it somewhere. Also, PostgreSQL no longer freely translates between integer and float, you have to explicitly ask for a conversion that loses precision. What should I do ? Some versions had a hack to fix this, but I don't think 8.0 has that anymore... If you need more help, you need to post the query and exact error message. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpghjn5MJysH.pgp Description: PGP signature
[GENERAL] Postgres under Windows
Dear all, I need some help to get postgres working under windows. I installed postgress using the .msi I am able to start the pgadminiii and start the database server with no problems. Now I try to create a database as explained in the documentation: createdb mydb The systems asks for a password and when I enter the password it comes back with: could not connect to database template1: FATAL: password authentication failed for user xxx At first I thought that because the user xxx (which is not the postgres user) did not exist the error was given. So I went to pgadminiii and created the user xxx and gave him rights to create a database. But still the error remains. After that I made some changes to the pg_hba.conf file and changed the method hostall all 127.0.0.1/32 md5 INTO hostall all 127.0.0.1/32 trust But this does not help either. Why am I not able to connect to the database template1? regards, Danny __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgres under Windows
Did you restart the service? Did you change the postgresql.conf file to listen on tcp/ip all addresses? listen_addresses = '*' After that I made some changes to the pg_hba.conf file and changed the method hostall all 127.0.0.1/32 md5 INTO hostall all 127.0.0.1/32 trust ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres under Windows
Tony, I tried your suggestion but it is not helping me out. Restarting the server is not helping either. What I tried next is create the database in pgadminiii and assign my userid as the owner. But that is not working either. So I have run out of options. regards, Danny --- Tony Caduto [EMAIL PROTECTED] wrote: Did you restart the service? Did you change the postgresql.conf file to listen on tcp/ip all addresses? listen_addresses = '*' After that I made some changes to the pg_hba.conf file and changed the method hostall all 127.0.0.1/32 md5 INTO hostall all 127.0.0.1/32 trust ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster __ Do you Yahoo!? Yahoo! Small Business - Try our new Resources site http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] DBD::Pg for ActiveState Perl on WinXP
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I hope that you are well. Is anybody maintaining a Windows package for the DBD::Pg driver for ActiveState Perl ? I don't have VC++ and bcc32 isn't going to compile based on the instructions for Win32. There is an unofficial version you can try out here: http://www.blingforyourring.com/DBD-Pg.ppd Feedback on it welcome at [EMAIL PROTECTED] - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200505280743 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCmFmgvJuQZxSWSsgRApX/AKDC3gUUnjsCOmXmWArAO00fvfAu9ACffgnO lWYWTHshFzP09m/PaEzl5og= =3I1H -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres under Windows
Danny, On May 30, 2005, at 10:13 AM, Danny Gaethofs wrote: Now I try to create a database as explained in the documentation: createdb mydb The systems asks for a password and when I enter the password it comes back with: could not connect to database template1: FATAL: password authentication failed for user xxx If you just entered createdb mydb that is not going to work unless you are logged into a Windows account which has the same name as a PostgreSQL user account that can create databases. If you really called createdb with more options, please show us exactly what you typed and exactly what the error output is. You probably want something more like: createdb.exe -h 127.0.0.1 -U postgres mydb If you can do things OK with pgAdmin, then it is most likely there is just some incorrect option in the command line. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postgres under Windows
John, The command you gave createdb.exe -h 127.0.0.1 -U postgres mydb is working. The system gives me the result CREATE DATABASE. Looking into pgadminiii shows the database is created. Doing the same with my user account is not working. Thanks anyway it is making me understand postgres better. regards, Danny --- John DeSoi [EMAIL PROTECTED] wrote: Danny, On May 30, 2005, at 10:13 AM, Danny Gaethofs wrote: Now I try to create a database as explained in the documentation: createdb mydb The systems asks for a password and when I enter the password it comes back with: could not connect to database template1: FATAL: password authentication failed for user xxx If you just entered createdb mydb that is not going to work unless you are logged into a Windows account which has the same name as a PostgreSQL user account that can create databases. If you really called createdb with more options, please show us exactly what you typed and exactly what the error output is. You probably want something more like: createdb.exe -h 127.0.0.1 -U postgres mydb If you can do things OK with pgAdmin, then it is most likely there is just some incorrect option in the command line. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Problem with void integer
Please reply to the list so everyone can follow the discussion: On Mon, May 30, 2005 at 07:50:03PM +0200, Alexandre Lollini wrote: Here is my query sample (exact): snip INSERT INTO sometable ( someint4field ) VALUES ( '' ); To work. Sorry, an empty string is not an integer. If you want NULL, say NULL. Or find me another syntax to insert '' in an integer field. With POSTGRESQL 803 What I do not understand is why, when I /i dump.txt the data from my 7.2 dump All the void integers '' where set correctly. What do you mean? In 7.2 there is no integer displayed as '', only 0 and NULL. That '' used to convert to zero is *wrong* and won't be changed back, sorry... Now at run time impossible to insert a void integer. What is a void integer? You have either NULL or 0, there have never been any other choices. For the moment I have modified all the application to convert void to zero prior to insert/update This is NOT the expected behavior, but, I am forced to, to preserve run time. This *is* expected behaviour, an empty string is not zero, end of story. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpLctN0yV0V4.pgp Description: PGP signature
Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT
Can someone in the community comment on this question? I don't know the answer. --- [EMAIL PROTECTED] wrote: Hi, I was asking this question some time ago and was under impression that this will be fixed in 8.x. In general problem is, CREATE TEMP TABLE AS SELECT does not report any rows to the engine, seems like, so GET DIAGNOSTICS ROW_COUNT after the statement returns 0 as well as FOUND false. This was working in 7.3, but behavior changed in 7.4. My question is, will it be fixed or should I consider not stop using get diagnostic after ?create temp table as select? from now on. Honstly this was a very convinient feature especialy knowing that select count(*) not a fastes possible operation. Thank you. ---(end of broadcast)--- TIP 3: 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 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] numeric precision when raising one numeric to
Tom Lane wrote: Has anyone bothered to actually look into the code? regression=# select power(2::numeric,1000); power - 10715086071862673209484250490600018105614048117055336074437503883703510511249361224931983788156958581275946729175531468251871452856923140435984577574698574803934567774824230985421074605062371141877954182153046474983581941267398767559165543946077062914571196477686542167660429831652624386837205668069376. (1 row) AFAICT the only thing missing is a pg_operator entry linked to the function. Patch to add NUMERIC ^ NUMERIC operator added and applied. Catalog version bumped. This will be in 8.1. FYI, this already does the right thing (no rounding) if the second argument is an integal value, see power_var(). -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/include/catalog/catversion.h === RCS file: /cvsroot/pgsql/src/include/catalog/catversion.h,v retrieving revision 1.271 diff -c -c -r1.271 catversion.h *** src/include/catalog/catversion.h30 May 2005 06:52:38 - 1.271 --- src/include/catalog/catversion.h30 May 2005 20:54:25 - *** *** 53,58 */ /*mmddN */ ! #define CATALOG_VERSION_NO200505301 #endif --- 53,58 */ /*mmddN */ ! #define CATALOG_VERSION_NO200505302 #endif Index: src/include/catalog/pg_operator.h === RCS file: /cvsroot/pgsql/src/include/catalog/pg_operator.h,v retrieving revision 1.131 diff -c -c -r1.131 pg_operator.h *** src/include/catalog/pg_operator.h 14 Apr 2005 01:38:20 - 1.131 --- src/include/catalog/pg_operator.h 30 May 2005 20:54:26 - *** *** 695,700 --- 695,701 DATA(insert OID = 1760 ( *PGNSP PGUID b f 1700 1700 1700 1760 0 0 0 0 0 numeric_mul - - )); DATA(insert OID = 1761 ( /PGNSP PGUID b f 1700 1700 17000 0 0 0 0 0 numeric_div - - )); DATA(insert OID = 1762 ( %PGNSP PGUID b f 1700 1700 17000 0 0 0 0 0 numeric_mod - - )); + DATA(insert OID = 1038 ( ^PGNSP PGUID b f 1700 1700 17000 0 0 0 0 0 numeric_power - - )); DATA(insert OID = 1763 ( @PGNSP PGUID l f 0 1700 17000 0 0 0 0 0 numeric_abs - - )); DATA(insert OID = 1784 ( = PGNSP PGUID b f 1560 1560 16 1784 1785 1786 1786 1786 1787 biteq eqsel eqjoinsel )); ---(end of broadcast)--- TIP 3: 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] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT
Bruce Momjian pgman@candle.pha.pa.us writes: Can someone in the community comment on this question? I don't know the answer. I think it could be changed back without much work, but I have a feeling that we'd deliberately decided on the change of behavior. Can anyone recall a prior discussion, or want to vote with or against MLikharev? Note that the change is actually at the SPI level, and would affect SPI_processed for all code using CREATE AS/SELECT INTO through SPI, not only plpgsql. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] Inherited constraints and search paths (was
Added to TODO: * Prevent child tables from altering constraints like CHECK that were inherited from the parent table --- Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Doing anything to restrict dropping of inherited constraints seems like wasted effort and potentially annoying anyhow. Uh, why? Arguably the constraints are as much part of the parent table definition as the columns themselves. If you had check (f1 0) in the definition of a table, wouldn't you be pretty surprised to select from it and find rows with f1 0? regression=# create table parent(f1 int check (f1 0)); CREATE TABLE regression=# create table child() inherits(parent); CREATE TABLE regression=# alter table child drop constraint parent_f1_check; ALTER TABLE regression=# insert into child values(-1); INSERT 0 1 regression=# select * from parent; f1 -1 (1 row) I think a good argument can be made that the above behavior is a bug, and that the ALTER command should have been rejected. We've gone to great lengths to make sure you can't ALTER a child table to make it incompatible with the parent in terms of the column names and types; shouldn't this be true of check constraints as well? regards, tom lane ---(end of broadcast)--- TIP 3: 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 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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
[GENERAL] setting up default user
hi... i have a postgres setup with an actual user. i can do a 'psql -Ugforge' and give the password, and get into the app.. the problem i'm having is that i can't get into postrges as the default/root user... any ideas? thanks -bruce [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT
Hello, I was not able to find any traces from the previous discussion trend, but I believe that finished when I replaced GET DIAGNOSTIC with SELECT COUNT(). Perfectly fine workaround, but more I look at that more I see why GET DIAGNOSTIC was so convenient not to mentioned that SELECT COUNT() is not a fastest possible statement in PG. Ideally what I would like are: 1. “Official” word whether that will be supported or not, ether way is fine, but that will clear confusion for me and others. 2. Maybe some clause in docs clarifying behavior for the case Best regards. Bruce Momjian pgman@candle.pha.pa.us writes: Can someone in the community comment on this question? I don't know the answer. I think it could be changed back without much work, but I have a feeling that we'd deliberately decided on the change of behavior. Can anyone recall a prior discussion, or want to vote with or against MLikharev? Note that the change is actually at the SPI level, and would affect SPI_processed for all code using CREATE AS/SELECT INTO through SPI, not only plpgsql. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Connecting to Postgres from LAN
On Mon, 30 May 2005 21:47:43 +0300, Andrus wrote: I installed Postgres 8 to Windows XP and added a line hostall all 168.179.0.1/32 trust I think you should probably make that 168.179.0.0/24 - the /32 means that only the IP address 168.179.0.1 is covered by that line, so the pattern does not match. Regards John Gray ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Carriage return in text fields
on 5/30/05 2:19 AM, [EMAIL PROTECTED] purportedly said: I have to transfert data from MS-Access to PostgreSQL 8.03 under Windows 2003 server. Text fields may contain a carriage return (CR-LF). I use the copy instruction. Under Pg-Admin III 1.21, I obtain an error message for that carriage return and a hint : use \n. If I use a filter on the cvs file replacing carriage return with \n, copy works but I have the char \n in the text fields, not the carriage return !? If I use PSQL, the carriage return are replaced by a space ! In Pg-Admin III I can insert a carriage return (with SHIFT ENTER) in a text field. How can I transfert my text fields WITH all the carriage return inside the text fields ? The line break in a DOS text file is two characters, and you will need to escape both for a COPY command to work: \r\n. For best results, you may want to use the COPY command from the psql command line. I am not sure what you are using to convert the text file, but you should create a new text file so you can inspect and ensure your filter is working correctly. My thought is that if you are getting a literal '\n' in your field content it means that something is re-escaping and PG is actually getting '\\n'. Best, Keary Suska (719) 473-6431 (719) 440-9952 (cell) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Postgres under Windows
Ah, I didn't catch the fact that you where not doing it as the super user (postgres) For another account to work you need to grant that account the createdb right. Look up in the docs about the grant command. Glad to hear you have it working. Tony Caduto http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql 8.x The command you gave createdb.exe -h 127.0.0.1 -U postgres mydb is working. The system gives me the result CREATE DATABASE. Looking into pgadminiii shows the database is created. Doing the same with my user account is not working. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] a way to reset the postgres password to nothing..
hi... i've set up a postrges db, and i can get to it using a user/passwd. however, when i su into the postgres user, and i try to do a 'psql -U postgres' it prompts me for the password.. i have no idea what the password would be.. i created everything, and thought i had just given it a blank password... any ideas as to how i can change this, or reset the passwd to nothing... thanks bruce [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT
I found a discussion of this issue from December, 2004: http://archives.postgresql.org/pgsql-general/2004-12/msg00070.php The discussion trailed off with the idea that because no rows were returned to the function, the row_count should be zero, but then there was some discussion that FOUND was then inconsistent. Anyway, perhaps we should read through this and make a final determination. --- [EMAIL PROTECTED] wrote: Hello, I was not able to find any traces from the previous discussion trend, but I believe that finished when I replaced GET DIAGNOSTIC with SELECT COUNT(). Perfectly fine workaround, but more I look at that more I see why GET DIAGNOSTIC was so convenient not to mentioned that SELECT COUNT() is not a fastest possible statement in PG. Ideally what I would like are: 1. ?Official? word whether that will be supported or not, ether way is fine, but that will clear confusion for me and others. 2. Maybe some clause in docs clarifying behavior for the case Best regards. Bruce Momjian pgman@candle.pha.pa.us writes: Can someone in the community comment on this question? I don't know the answer. I think it could be changed back without much work, but I have a feeling that we'd deliberately decided on the change of behavior. Can anyone recall a prior discussion, or want to vote with or against MLikharev? Note that the change is actually at the SPI level, and would affect SPI_processed for all code using CREATE AS/SELECT INTO through SPI, not only plpgsql. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] numeric precision when raising one numeric to another.
Alvaro Herrera wrote: On Fri, May 20, 2005 at 01:03:08PM -0400, Tom Lane wrote: Oh, and while at it, it would be nice to solve the modulo bug that still lurks there: alvherre=# select 12345678901234567890 % 123; ?column? -- -45 (1 fila) alvherre=# select 12345678901234567890 % 123::numeric(4,1); ?column? -- 78.0 (1 fila) alvherre=# select 12345678901234567890 % 123::numeric(3,0); ?column? -- -45 (1 fila) alvherre=# select version(); version -- PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6 (Debian 1:3.3.6-4) (1 fila) I poked around on this one and found this in the comments in numeric::mod_var(): /* - * We do this using the equation * mod(x,y) = x - trunc(x/y)*y * We set rscale the same way numeric_div and numeric_mul do * to get the right answer from the equation. The final result, * however, need not be displayed to more precision than the inputs. * -- */ so I tried it: test= select 12345678901234567890 % 123; ?column? -- -45 (1 row) test= select 12345678901234567890 / 123; ?column? 100371373180768845 (1 row) test= select 100371373180768845::numeric * 123::numeric; ?column? -- 12345678901234567935 (1 row) test= select 12345678901234567890 - 12345678901234567935; ?column? -- -45 (1 row) and I was quite surprised at the result. Basically, it looks like the division is rounding _up_ the next integer on the /123 division, and that is causing the modulo error. In fact, should the /123 round up with numeric? I think there is an assumption in our code that div_var() will not round up, but in fact it does in this case. Here is 'calc' showing the same calculation: 12345678901234567890 % 123 78 12345678901234567890 / 123 ~100371373180768844.63414634146341463414 100371373180768845 * 123 ^^ rounded up by me 12345678901234567935 12345678901234567890 - 12345678901234567935 -45 and here is 'bc' doing integer division: 12345678901234567890 / 123 100371373180768844 100371373180768844 * 123 12345678901234567812 12345678901234567890 - 12345678901234567812 78 This is why 123::numeric(4,1) fixes it because the division returns on digit that is truncated, rather than rounding up to the next whole number. I am not sure how to fix this. Adding extra scale to the division would help, but if the division returned .999 and we had a scale of 2, it would still round up and the truncate would not see it. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] numeric precision when raising one numeric to another.
On Mon, May 30, 2005 at 11:29:48PM -0400, Bruce Momjian wrote: test= select 12345678901234567890 / 123; ?column? 100371373180768845 (1 row) Well, that's a bug, right? -- Alvaro Herrera (alvherre[a]surnet.cl) Industry suffers from the managerial dogma that for the sake of stability and continuity, the company should be independent of the competence of individual employees. (E. Dijkstra) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Carriage return in text fields
In my experience, there is no way to tell an embedded carriage return from and end-of-record carriage return other than counting intervening tabs. What I do, is fix it in access with a query for (believe it or not) * chr(13) * which returns all records that contain a CR. I then just delete the extraneous CR before exporting the data. If access could use a different record delimiter on export life would be good, but it doesn't. You could also run a script on your exported data that would count TABs on a line before the CR/LF was encountered, and delete any that appear too early. I am not smart enough to do that. Good luck. Ian On 5/30/05, Ets ROLLAND [EMAIL PROTECTED] wrote: Hello ! I have to transfert data from MS-Access to PostgreSQL 8.03 under Windows 2003 server. Text fields may contain a carriage return (CR-LF). I use the copy instruction. Under Pg-Admin III 1.21, I obtain an error message for that carriage return and a hint : use \n. If I use a filter on the cvs file replacing carriage return with \n, copy works but I have the char \n in the text fields, not the carriage return !? If I use PSQL, the carriage return are replaced by a space ! In Pg-Admin III I can insert a carriage return (with SHIFT ENTER) in a text field. How can I transfert my text fields WITH all the carriage return inside the text fields ? Thanks for your tips... Best regards. Luc ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] numeric precision when raising one numeric to another.
Alvaro Herrera wrote: On Mon, May 30, 2005 at 11:29:48PM -0400, Bruce Momjian wrote: test= select 12345678901234567890 / 123; ?column? 100371373180768845 (1 row) Well, that's a bug, right? I don't think so. The fuller answer is 100371373180768844.63414634146341463414, and that rounded to the nearest integer is 100371373180768845. I think people expect % do to that, except for integers. You could argue that numerics with zero scale are integers, but NUMERIC % NUMERIC doesn't behave like an integer operator --- it rounds to the proper precision. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT
Bruce Momjian pgman@candle.pha.pa.us writes: I found a discussion of this issue from December, 2004: http://archives.postgresql.org/pgsql-general/2004-12/msg00070.php That was the same complainant ;-) I dug through the CVS history and determined that the behavior changed at spi.c rev 1.87: 2003-03-09 22:53 tgl * Restructure parsetree representation of DECLARE CURSOR: now it's a utility statement (DeclareCursorStmt) with a SELECT query dangling from it, rather than a SELECT query with a few unusual fields in it. Add code to determine whether a planned query can safely be run backwards. If DECLARE CURSOR specifies SCROLL, ensure that the plan can be run backwards by adding a Materialize plan node if it can't. Without SCROLL, you get an error if you try to fetch backwards from a cursor that can't handle it. (There is still some discussion about what the exact behavior should be, but this is necessary infrastructure in any case.) Along the way, make EXPLAIN DECLARE CURSOR work. Looking at the code change, it may have just been a sloppy removal of a local variable, ie checking queryDesc-dest rather than a previously saved copy of same. The log message certainly doesn't suggest that I intended to change the behavior of CREATE TABLE AS. So the initial evidence is that this was not an intentional change. Do we want to revert it? The behavior has been in the field now for more than a full release cycle --- all 7.4.* releases behave this way --- so one could argue that we should leave it be. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
