Re: [GENERAL] Need help on compiling postgres source code from cloned repo
Thanks, Adrian. Able to install 10beta1 successfully. - -- Thanks, Rajan. -- View this message in context: http://www.postgresql-archive.org/Need-help-on-compiling-postgres-source-code-from-cloned-repo-tp5969667p5969696.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on compiling postgres source code from cloned repo
On 07/01/2017 11:31 PM, rajan wrote: Hi, I have a cloned repository of postgres and I want to compile the source for *REL_10_BETA1* alone. Or just go here and grab the tarball: https://www.postgresql.org/ftp/source/v10beta1/ Following are the steps I am planning to do, -> git checkout -b rel10beta1 REL_10_BETA1 (and verifying current branch is newly created one) Is there a reason you want to start a new branch and not just checkout the existing branch? -> Execute ./configure --prefix=/opt/PostgreSQL/10beta --with-pgport=6432 --enable-integer-datetimes --enable-thread-safety --enable-debug -> And then make and make install --enable-integer-datetimes --enable-thread-safety are the default so they do not need to be specified. In fact in 10 you can't create float datetimes: https://www.postgresql.org/docs/10/static/release-10.html " Remove support for floating-point datetimes/timestamps (Tom Lane) This removes configure's --disable-integer-datetimes option. Floating-point datetimes/timestamps have not been the default since Postgres 8.3 and have few advantages. " Will the above steps work? Please assist. - -- Thanks, Rajan. -- View this message in context: http://www.postgresql-archive.org/Need-help-on-compiling-postgres-source-code-from-cloned-repo-tp5969667.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on postgres related to insertion of a tuple.
On Thu, Jul 16, 2015 at 5:18 PM, M Tarkeshwar Rao m.tarkeshwar@ericsson.com wrote: a. How can we prevent This has been seen to occur with buggy kernels; consider updating your system alarm ? b. how can resolve This has been seen to occur with buggy kernels; consider updating your system alarm ? This defense has been added by this commit some time ago: commit: ffae5cc5a6024b4e25ec920ed5c4dfac649605f8 author: Tom Lane t...@sss.pgh.pa.us date: Mon, 25 Sep 2006 22:01:10 + Add a check to prevent overwriting valid data if smgrnblocks() gives a wrong answer, as has been seen to occur with a buggy Linux kernel. Not really our bug, but it's a simple test in a seldom-used control path, so might as well have a defense. And if you look at the comment on top of this error message this code is a protection against lseek(SEEK_END) that may be buggy on some old kernels. Hence the answer to both questions is in the error message: upgrade your system to a newer kernel or migrate to a newer server. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on postgres related to insertion of a tuple.
On 07/16/2015 01:18 AM, M Tarkeshwar Rao wrote: Hi all, We have a table name auditTrailLogEntry where we were not able to able to insert in auditTrailLogEntry table. we have reindex the table and insertion issue has been resolved . we executed following command: *_Audit tail insertion issue. _* INSERT INTO auditTrailLogEntry (event, inNodeID, inNodeName, sourceID, inTime, outNodeID, outNodeName, destinationID, outTime, bytes, cdrs, tableIndex, noOfSubfilesInFile, recordSequenceNumberList ) VALUES (68, NULL, NULL, E'proc_PGWDiskCollectorGroup1_0_1434566783_1027213', E'2015-06-17 11:46:32.224', E'3434', E'PGW ACC MVNE2 Raw', E'DEPCF003.2015-06-1711-41-06.8493.BGWP21S5M2P1.000328122', E'2015-06-17 11:46:33.790', 39193, 78, 952 *_Got following error messages_* ERROR: unexpected data beyond EOF in block 260 of relation pg_tblspc/16388/PG_9.1_201105231/16384/16418 *HINT: This has been seen to occur with buggy kernels; consider updating your system.* Failed to transfer a row to the database table auditTrailLogEntry. The SQL statement to execute was: INSERT INTO auditTrailLogEntry (event, inNodeID, inNodeName, sourceID, inTime, outNodeID, outNodeName, destinationID, outTime, bytes, cdrs, tableIndex, noOfSubfilesInFile, recordSequenceNumberList ) VALUES (68, NULL, NULL, E'proc_PGWDiskCollectorGroup1_0_1434566783_1027213', E'2015-06-17 11:46:32.224', E'3434', E'PGW ACC MVNE2 Raw', E'DEPCF003.2015-06-1711-41-06.8493.BGWP21S5M2P1.000328122', E'2015-06-17 11:46:33.790', 39193, 78, 952 Just to be clear this is a duplicate of this thread?: http://www.postgresql.org/message-id/84bc7ab0d621a74893ec9c9e151293b0226d8...@esessmb207.ericsson.se *Can you please suggest following:* -- a. How can we prevent This has been seen to occur with buggy kernels; consider updating your system alarm ? b. how can resolve This has been seen to occur with buggy kernels; consider updating your system alarm ? Regards Tarkeshwar -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in tuning
Phoenix Kiula wrote My PG server is still going down. After spending the weekend doing a CLUSTER of my largest table (it's a RAID 1 system with SATA hard disks and 4 GB memory, mostly devoted to PG) I still have this issue. When I do a top command, 99% of the CPU and about 15% of the memory is being taken by PG. When I press a c in the top UI, I see that postmaster is doing some CLUSTER. However, I don't do any more clustering. The only automatic setting I can think of are autovacuum. So, question: to the degree that my system allows for performance, what steps can I take to find out what's happening? I see some things mentioned: I/O, vacuum settings, pg_stats, pg_activity -- is there a simple guide somewhere which shows me step by step what to do? Google hasn't been much help. Postgresql performance tunings brings a lot of esoteric articles. SELECT * FROM pg_stat_activity; This will tell you what the server thinks it is doing. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-in-tuning-tp5813983p5813987.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
Hi all Something it's tricky for me here, see my trigger I wrote below. What can I do to insert c_code from center table INTO center_changed table with ONLY the c_code where the update was made or where an INSERT of the new entry INTO center table what made . Let's say the center table has got the following values. When I try to change c_dsc from KITWE to KIT where c_code ='0204' the trigger should take cde 0204 and insert it into center_changed table with a time stamp. So the main problem I have it's to populate the table called center_changed. c_cde |c_desc | c_active ++-- 0094 | GABORONE WAREHOUSE | f 0204 | KITWE | t CREATE TABLE center_changed ( c_cdetext NOT NULL, stamp timestamp NOT NULL ); CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; END IF; RETURN NULL; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Wednesday, May 21, 2014 9:34 PM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Thank you very much, I will have a look. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, May 21, 2014 3:20 PM To: Khangelani Gama; pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2 On 05/21/2014 02:54 AM, Khangelani Gama wrote: Hi I have a postgres 9 database, inside this database I need to create a new table called *center_changed* that gets inserted by any change that take place in a table called *center*. So I need to create trigger to do this. *Example: * Table name*: center*(c_cde, c_dsc, ops_cde, grp_cde); Now on the table called *center , *I need to create an INSERT and UPDATE trigger will insert the *c_cde * of the inserted or updated *center* into the *center_changed* table Please help me I have this syntax below, but please help me with the overall query. CREATE TRIGGER check_center BEFORE INSERT OR UPDATE ON *cente*r FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); See here: http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html Example 40-4. A PL/pgSQL Trigger Procedure For Auditing Thanks -- Adrian Klaver adrian.kla...@aklaver.com CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
Please help -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Thursday, May 22, 2014 9:38 AM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Hi all Something it's tricky for me here, see my trigger I wrote below. What can I do to insert c_code from center table INTO center_changed table with ONLY the c_code where the update was made or where an INSERT of the new entry INTO center table what made . Let's say the center table has got the following values. When I try to change c_dsc from KITWE to KIT where c_code ='0204' the trigger should take cde 0204 and insert it into center_changed table with a time stamp. So the main problem I have it's to populate the table called center_changed. c_cde |c_desc | c_active ++-- 0094 | GABORONE WAREHOUSE | f 0204 | KITWE | t CREATE TABLE center_changed ( c_cdetext NOT NULL, stamp timestamp NOT NULL ); CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; END IF; RETURN NULL; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Wednesday, May 21, 2014 9:34 PM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Thank you very much, I will have a look. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, May 21, 2014 3:20 PM To: Khangelani Gama; pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2 On 05/21/2014 02:54 AM, Khangelani Gama wrote: Hi I have a postgres 9 database, inside this database I need to create a new table called *center_changed* that gets inserted by any change that take place in a table called *center*. So I need to create trigger to do this. *Example: * Table name*: center*(c_cde, c_dsc, ops_cde, grp_cde); Now on the table called *center , *I need to create an INSERT and UPDATE trigger will insert the *c_cde * of the inserted or updated *center* into the *center_changed* table Please help me I have this syntax below, but please help me with the overall query. CREATE TRIGGER check_center BEFORE INSERT OR UPDATE ON *cente*r FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); See here: http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html Example 40-4. A PL/pgSQL Trigger Procedure For Auditing Thanks -- Adrian Klaver adrian.kla...@aklaver.com CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
Hi My problem is on two lines, INSERT statements, I get an error saying c_cde does not exist INSERT INTO center_changed SELECT c_cde, now(); - My problem is here: I get an error saying c_cde does not exist, I cant how I can give center_change table the results. Center table has got many rows so I need to only get c_cde for only the row that was updated RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO center_changed SELECT c_cde, now(); My problem is here: I get an error saying c_cde does not exist, I cant how I can give center_change table the results. Center table has got many rows so I need to only get c_cde for only the row that was inserted. -Original Message- From: Andrew Sullivan [mailto:a...@crankycanuck.ca] Sent: Thursday, May 22, 2014 2:39 PM To: Khangelani Gama Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2 [off list] I don't know about others, but I don't see what your problem is. Is the problem that you are getting the new value in the center_changed table? That's because you're doing an AFTER trigger ON UPDATE. So you have the new values. Look at the special values NEW and OLD in the bit of the manual about trigget functions? A On Thu, May 22, 2014 at 02:07:41PM +0200, Khangelani Gama wrote: Please help -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Thursday, May 22, 2014 9:38 AM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Hi all Something it's tricky for me here, see my trigger I wrote below. What can I do to insert c_code from center table INTO center_changed table with ONLY the c_code where the update was made or where an INSERT of the new entry INTO center table what made . Let's say the center table has got the following values. When I try to change c_dsc from KITWE to KIT where c_code ='0204' the trigger should take cde 0204 and insert it into center_changed table with a time stamp. So the main problem I have it's to populate the table called center_changed. c_cde |c_desc | c_active ++-- 0094 | GABORONE WAREHOUSE | f 0204 | KITWE | t CREATE TABLE center_changed ( c_cdetext NOT NULL, stamp timestamp NOT NULL ); CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; END IF; RETURN NULL; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Wednesday, May 21, 2014 9:34 PM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Thank you very much, I will have a look. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, May 21, 2014 3:20 PM To: Khangelani Gama; pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2 On 05/21/2014 02:54 AM, Khangelani Gama wrote: Hi I have a postgres 9 database, inside this database I need to create a new table called *center_changed* that gets inserted by any change that take place in a table called *center*. So I need to create trigger to do this. *Example: * Table name*: center*(c_cde, c_dsc, ops_cde, grp_cde); Now on the table called *center , *I need to create an INSERT and UPDATE trigger will insert the *c_cde * of the inserted or updated *center* into the *center_changed* table Please help me I have this syntax below, but please help me with the overall query. CREATE TRIGGER check_center BEFORE INSERT OR UPDATE ON *cente*r FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); See here: http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html Example 40-4. A PL/pgSQL Trigger Procedure For Auditing Thanks -- Adrian Klaver adrian.kla...@aklaver.com CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail
Re: [GENERAL] Need help on triggers - postgres 9.1.2
On 22/05/2014 13:48, Khangelani Gama wrote: Hi My problem is on two lines, INSERT statements, I get an error saying c_cde does not exist INSERT INTO center_changed SELECT c_cde, now(); - My problem is here: I get an error saying c_cde does not exist, I cant how I can give You need to specify where c_cde is coming from - so OLD.c_cde or NEW.c_cde (or else SELECT FROM... if it's coming from somewhere else). Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
Thanks , I will try that -Original Message- From: Raymond O'Donnell [mailto:r...@iol.ie] Sent: Thursday, May 22, 2014 2:54 PM To: Khangelani Gama; Andrew Sullivan; pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2 On 22/05/2014 13:48, Khangelani Gama wrote: Hi My problem is on two lines, INSERT statements, I get an error saying c_cde does not exist INSERT INTO center_changed SELECT c_cde, now(); - My problem is here: I get an error saying c_cde does not exist, I cant how I can give You need to specify where c_cde is coming from - so OLD.c_cde or NEW.c_cde (or else SELECT FROM... if it's coming from somewhere else). Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
-Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Thursday, May 22, 2014 9:38 AM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Hi all Something it's tricky for me here, see my trigger I wrote below. What can I do to insert c_code from center table INTO center_changed table with ONLY the c_code where the update was made or where an INSERT of the new entry INTO center table what made . Let's say the center table has got the following values. When I try to change c_dsc from KITWE to KIT where c_code ='0204' the trigger should take cde 0204 and insert it into center_changed table with a time stamp. So the main problem I have it's to populate the table called center_changed. c_cde |c_desc | c_active ++-- 0094 | GABORONE WAREHOUSE | f 0204 | KITWE | t CREATE TABLE center_changed ( c_cdetext NOT NULL, stamp timestamp NOT NULL ); CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; END IF; RETURN NULL; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); This should work: CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN INSERT INTO center_changed VALUES(new.c_cde, now()); RETURN NEW; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
On Thu, May 22, 2014 at 02:48:48PM +0200, Khangelani Gama wrote: CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO center_changed SELECT c_cde, now(); PostgreSQL probably refuses to take wild guesses where c_cde might come from. You need to tell it. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
Many Thanks, I will try it now -Original Message- From: Igor Neyman [mailto:iney...@perceptron.com] Sent: Thursday, May 22, 2014 2:55 PM To: Khangelani Gama; Andrew Sullivan; pgsql-general@postgresql.org Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Thursday, May 22, 2014 9:38 AM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Hi all Something it's tricky for me here, see my trigger I wrote below. What can I do to insert c_code from center table INTO center_changed table with ONLY the c_code where the update was made or where an INSERT of the new entry INTO center table what made . Let's say the center table has got the following values. When I try to change c_dsc from KITWE to KIT where c_code ='0204' the trigger should take cde 0204 and insert it into center_changed table with a time stamp. So the main problem I have it's to populate the table called center_changed. c_cde |c_desc | c_active ++-- 0094 | GABORONE WAREHOUSE | f 0204 | KITWE | t CREATE TABLE center_changed ( c_cdetext NOT NULL, stamp timestamp NOT NULL ); CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; END IF; RETURN NULL; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); This should work: CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN INSERT INTO center_changed VALUES(new.c_cde, now()); RETURN NEW; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); Regards, Igor Neyman CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
On 05/22/2014 06:01 AM, Khangelani Gama wrote: Many Thanks, I will try it now In the link I sent previously: http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html there is a handy section (40.9.1. Triggers on data changes) that explains what information is available to a plpgsql trigger and how to access it. Might help to take out some of the mystery of what is going on. -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
Thank you very much to everyone, it worked -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Thursday, May 22, 2014 3:01 PM To: 'Igor Neyman'; 'Andrew Sullivan'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Many Thanks, I will try it now -Original Message- From: Igor Neyman [mailto:iney...@perceptron.com] Sent: Thursday, May 22, 2014 2:55 PM To: Khangelani Gama; Andrew Sullivan; pgsql-general@postgresql.org Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 -Original Message- From: Khangelani Gama [mailto:kg...@argility.com] Sent: Thursday, May 22, 2014 9:38 AM To: 'Adrian Klaver'; 'pgsql-general@postgresql.org' Subject: RE: [GENERAL] Need help on triggers - postgres 9.1.2 Hi all Something it's tricky for me here, see my trigger I wrote below. What can I do to insert c_code from center table INTO center_changed table with ONLY the c_code where the update was made or where an INSERT of the new entry INTO center table what made . Let's say the center table has got the following values. When I try to change c_dsc from KITWE to KIT where c_code ='0204' the trigger should take cde 0204 and insert it into center_changed table with a time stamp. So the main problem I have it's to populate the table called center_changed. c_cde |c_desc | c_active ++-- 0094 | GABORONE WAREHOUSE | f 0204 | KITWE | t CREATE TABLE center_changed ( c_cdetext NOT NULL, stamp timestamp NOT NULL ); CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO center_changed SELECT c_cde, now(); RETURN NEW; END IF; RETURN NULL; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); This should work: CREATE OR REPLACE FUNCTION check_center_changes() RETURNS TRIGGER AS $center_changed$ BEGIN INSERT INTO center_changed VALUES(new.c_cde, now()); RETURN NEW; END; $center_changed$ LANGUAGE plpgsql; CREATE TRIGGER center_changed AFTER INSERT OR UPDATE ON center FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); Regards, Igor Neyman CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
On 05/21/2014 02:54 AM, Khangelani Gama wrote: Hi I have a postgres 9 database, inside this database I need to create a new table called *center_changed* that gets inserted by any change that take place in a table called *center*. So I need to create trigger to do this. *Example: * Table name*: center*(c_cde, c_dsc, ops_cde, grp_cde); Now on the table called *center , *I need to create an INSERT and UPDATE trigger will insert the *c_cde * of the inserted or updated *center* into the *center_changed* table Please help me I have this syntax below, but please help me with the overall query. CREATE TRIGGER check_center BEFORE INSERT OR UPDATE ON *cente*r FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); See here: http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html Example 40-4. A PL/pgSQL Trigger Procedure For Auditing Thanks -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on triggers - postgres 9.1.2
Thank you very much, I will have a look. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Wednesday, May 21, 2014 3:20 PM To: Khangelani Gama; pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help on triggers - postgres 9.1.2 On 05/21/2014 02:54 AM, Khangelani Gama wrote: Hi I have a postgres 9 database, inside this database I need to create a new table called *center_changed* that gets inserted by any change that take place in a table called *center*. So I need to create trigger to do this. *Example: * Table name*: center*(c_cde, c_dsc, ops_cde, grp_cde); Now on the table called *center , *I need to create an INSERT and UPDATE trigger will insert the *c_cde * of the inserted or updated *center* into the *center_changed* table Please help me I have this syntax below, but please help me with the overall query. CREATE TRIGGER check_center BEFORE INSERT OR UPDATE ON *cente*r FOR EACH ROW EXECUTE PROCEDURE check_center_changes(); See here: http://www.postgresql.org/docs/9.3/interactive/plpgsql-trigger.html Example 40-4. A PL/pgSQL Trigger Procedure For Auditing Thanks -- Adrian Klaver adrian.kla...@aklaver.com CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help to implement Proximity search feature
Thanks for your reply, i am totally new to Postgis. we have Database, but not ready for Geocode use. what i understood from different blog, we should have latitude and longitude either based on addresses, or postal code we have. However if I will get the lat and long, need to calculate earth distance to give location based on user location. Formula i got, is calculating shortest distance, which always not true practically. Please address my concern, how i will get rid of these issues. Regards, Itishree On Wed, Jan 15, 2014 at 11:28 PM, Oleg Bartunov obartu...@gmail.com wrote: check knn search, http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf On Wed, Jan 15, 2014 at 8:04 PM, itishree sukla itishree.su...@gmail.com wrote: Deal all, In my project, there is a requirement to implement proximity search feature. We are running a mobile app, for which proximity search is require. Can any one guide me how i can achieve this using postgis, or is there any other way i can achieve this. We are using postgresql 9.2. Thanks in advance . Regards, Itishree
Re: [GENERAL] Need Help to implement Proximity search feature
Please see the comment at the bottom of this post. On 16/01/14 22:52, itishree sukla wrote: Thanks for your reply, i am totally new to Postgis. At least you've got into it, I keep meaning do do so myself. we have Database, but not ready for Geocode use. what i understood from different blog, we should have latitude and longitude either based on addresses, or postal code we have. However if I will get the lat and long, need to calculate earth distance to give location based on user location. Formula i got, is calculating shortest distance, which always not true practically. Please address my concern, how i will get rid of these issues. Regards, Itishree On Wed, Jan 15, 2014 at 11:28 PM, Oleg Bartunov obartu...@gmail.com mailto:obartu...@gmail.com wrote: check knn search, http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf http://www.sai.msu.su/%7Emegera/postgres/talks/pgcon-2010-1.pdf On Wed, Jan 15, 2014 at 8:04 PM, itishree sukla itishree.su...@gmail.com mailto:itishree.su...@gmail.com wrote: Deal all, In my project, there is a requirement to implement proximity search feature. We are running a mobile app, for which proximity search is require. Can any one guide me how i can achieve this using postgis, or is there any other way i can achieve this. We are using postgresql 9.2. Thanks in advance . Regards, Itishree Pleas bottom post like I am here, it is the norm for these mailing lists. Some comments interspersed are also okay. It makes it easier for people to see the context of what you are saying. Thanks, Gavin
Re: [GENERAL] Need Help to implement Proximity search feature
On 1/16/2014 1:52 AM, itishree sukla wrote: we have Database, but not ready for Geocode use. what i understood from different blog, we should have latitude and longitude either based on addresses, or postal code we have. However if I will get the lat and long, need to calculate earth distance to give location based on user location. Formula i got, is calculating shortest distance, which always not true practically. Please address my concern, how i will get rid of these issues. for determining closest proximity, you probably don't need accurate spherical earth distance, simple cartesian distance is good enough as you're only interested in relative values. for this, you can use the built in postgresql POINT data type, and the - distance operator. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help to implement Proximity search feature
check knn search, http://www.sai.msu.su/~megera/postgres/talks/pgcon-2010-1.pdf On Wed, Jan 15, 2014 at 8:04 PM, itishree sukla itishree.su...@gmail.com wrote: Deal all, In my project, there is a requirement to implement proximity search feature. We are running a mobile app, for which proximity search is require. Can any one guide me how i can achieve this using postgis, or is there any other way i can achieve this. We are using postgresql 9.2. Thanks in advance . Regards, Itishree -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help Restoring Old Backup
On 12/15/2013 11:00 AM, Steve Knott wrote: Hi, I have a really old database (5+ years old) that was backed up stored away for a while. Now I want some data out of it. Not sure what you mean here? Do you mean you stored the database data directory or did a dump and stored that? I originally thought that if I made a backup and kept around the original database install.exe file, I could reinstall the db restore the data. It looks like that is not happening. The old database was somewhere in 8.2.x before it was shut down. Whenever I try to restore, I get: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1490: 1259 26787 TABLE users client ... pg_restore: [custom archiver] found unexpected block ID (1257691431) when reading data -- expected 2049 So here you are using an old dump file produced with -Fc switch? What version of pg_restore are you using? A dump/restore should work across architectures, but for troubleshooting purposes have you changed machines? I tried restoring on an 8.2, 8.4 and 9.0, but each fail with similar error messages. How similar, the same table, the same unexpected block id message, or other? Is there anyway to repair this file or break it up some I can get most of the data out of it? It appears to be stopping half way through. If I could get it past this point, I could at least reclaim the tables after this issue. Well you could use the below to selectively dump items. http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html -l --list List the contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed. -L list-file --use-list=list-file Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored. list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples. Thanks for any help, Steve -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with upsert
Richard Dunks richarddu...@gmail.com wrote: I will run this command 7 times with different daily table. [ ... ] a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE a.firewall [ ... ] When I run the command I get an error ERROR: column reference firewall is ambiguous LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... Any idea what I am doing wrong? You are not showing us the actual query or the actual error message. (Note the mismatch in aliases qualifying firewall near the ends of the above lines.) Please cut and paste the entire query and the entire error message, and make sure they are from the same run. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with upsert
Hi, ERROR: column reference firewall is ambiguous LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... set firewal = misses a table HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl 2013/12/4 Eric Lamer e...@phoenixsecure.com Hi, I need some help with upsert. Some info on what I want to do: Each day I create a table for my firewall logs. I have one entry for one occurrence of all the field except sum, which is the number of occurrence I have of each log that match all the field. My table has the following field: firewall,action,src_zone,src_ip,dst_zone,dst_ip,proto,port,hex1,hex2,sum Each day I want to copy the last 7 days into one table so I have one table with the last 7 days of logs. So I want to copy the data from 7 tables into 1. If the row does not exist I just insert and if the row already exist I just update the sum (existing sum + new sum). Public.test is the table I use for the last 7 days logs. daily.daily_20131202 is table for 1 day. I will run this command 7 times with different daily table. WITH upsert as (update public.test T set firewall=S.firewall,action=S.action,src_zone=S.src_zone,src_ip=S.src_ip,dst_zone=S.dst_zone,dst_ip=S.dst_ip,proto=S.proto,port=S.port,hex1=S.hex1,hex2=S.hex2,sum=T.sum+S.sum from daily.daily_20131202 S where (T.firewall=S.firewall and T.action=S.action and T.src_zone=S.src_zone and T.src_ip=S.src_ip and T.dst_zone=S.dst_zone and T.dst_ip=S.dst_ip and T.proto=S.proto and T.port=S.port and T.hex1=S.hex1 and T.hex2=S.hex2) RETURNING * ) insert into public.test select * from daily.daily_20131202 a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE a.firewall=b.firewall and a.action=b.action and a.src_zone=b.src_zone and a.src_ip=b.src_ip and a.dst_zone=b.dst_zone and a.dst_ip=b.dst_ip and a.proto=b.proto and a.port=b.port and a.hex1=b.hex1 and a.hex2=b.hex2); When I run the command I get an error ERROR: column reference firewall is ambiguous LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... Any idea what I am doing wrong? Also, is there an easier way to do that? Thanks for the help.
Re: [GENERAL] Need help with upsert
LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... As you can see it is b.firewall where b is alias to table upsert. That's why I don't understand the error. From: Serge Fonville [mailto:serge.fonvi...@gmail.com] Sent: December 4, 2013 12:53 PM To: Eric Lamer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Need help with upsert Hi, ERROR: column reference firewall is ambiguous LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... set firewal = misses a table HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl 2013/12/4 Eric Lamer e...@phoenixsecure.com mailto:e...@phoenixsecure.com Hi, I need some help with upsert. Some info on what I want to do: Each day I create a table for my firewall logs. I have one entry for one occurrence of all the field except sum, which is the number of occurrence I have of each log that match all the field. My table has the following field: firewall,action,src_zone,src_ip,dst_zone,dst_ip,proto,port,hex1,hex2,sum Each day I want to copy the last 7 days into one table so I have one table with the last 7 days of logs. So I want to copy the data from 7 tables into 1. If the row does not exist I just insert and if the row already exist I just update the sum (existing sum + new sum). Public.test is the table I use for the last 7 days logs. daily.daily_20131202 is table for 1 day. I will run this command 7 times with different daily table. WITH upsert as (update public.test T set firewall=S.firewall,action=S.action,src_zone=S.src_zone,src_ip=S.src_ip,dst_ zone=S.dst_zone,dst_ip=S.dst_ip,proto=S.proto,port=S.port,hex1=S.hex1,hex2=S .hex2,sum=T.sum+S.sum from daily.daily_20131202 S where (T.firewall=S.firewall and T.action=S.action and T.src_zone=S.src_zone and T.src_ip=S.src_ip and T.dst_zone=S.dst_zone and T.dst_ip=S.dst_ip and T.proto=S.proto and T.port=S.port and T.hex1=S.hex1 and T.hex2=S.hex2) RETURNING * ) insert into public.test select * from daily.daily_20131202 a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE a.firewall=b.firewall and a.action=b.action and a.src_zone=b.src_zone and a.src_ip=b.src_ip and a.dst_zone=b.dst_zone and a.dst_ip=b.dst_ip and a.proto=b.proto and a.port=b.port and a.hex1=b.hex1 and a.hex2=b.hex2); When I run the command I get an error ERROR: column reference firewall is ambiguous LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... Any idea what I am doing wrong? Also, is there an easier way to do that? Thanks for the help. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Need help with upsert
Le mercredi 04 décembre 2013 à 17:48 +, Eric Lamer a écrit : Each day I want to copy the last 7 days into one table so I have one table with the last 7 days of logs. So I want to copy the data from 7 tables into 1. If the row does not exist I just insert and if the row already exist I just update the sum (existing sum + new sum). Unless you have a specific reason not to, ISTM you could make your life _much_ easier with just one table and a date column? Public.test is the table I use for the last 7 days logs. daily.daily_20131202 is table for 1 day. I will run this command 7 times with different daily table. Also, is there an easier way to do that? I would just insert all rows into one big table with a date field, and then make a select count() with the appropriate where clause on the date field and group by clause. -- Salutations, Vincent Veyron Legal cases, contracts and insurance claims management http://libremen.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with upsert
Hello, On Dec 4, 2013, at 12:39 PM, Eric Lamer e...@phoenixsecure.com wrote: Hi, I need some help with upsert. Some info on what I want to do: Each day I create a table for my firewall logs. I have one entry for one occurrence of all the field except sum, which is the number of occurrence I have of each log that match all the field. My table has the following field: firewall,action,src_zone,src_ip,dst_zone,dst_ip,proto,port,hex1,hex2,sum Each day I want to copy the last 7 days into one table so I have one table with the last 7 days of logs. So I want to copy the data from 7 tables into 1. If the row does not exist I just insert and if the row already exist I just update the sum (existing sum + new sum). Public.test is the table I use for the last 7 days logs. daily.daily_20131202 is table for 1 day. I will run this command 7 times with different daily table. WITH upsert as (update public.test T set firewall=S.firewall,action=S.action,src_zone=S.src_zone,src_ip=S.src_ip,dst_zone=S.dst_zone,dst_ip=S.dst_ip,proto=S.proto,port=S.port,hex1=S.hex1,hex2=S.hex2,sum=T.sum+S.sum from daily.daily_20131202 S where (T.firewall=S.firewall and T.action=S.action and T.src_zone=S.src_zone and T.src_ip=S.src_ip and T.dst_zone=S.dst_zone and T.dst_ip=S.dst_ip and T.proto=S.proto and T.port=S.port and T.hex1=S.hex1 and T.hex2=S.hex2) RETURNING * ) insert into public.test select * from daily.daily_20131202 a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE a.firewall=b.firewall and a.action=b.action and a.src_zone=b.src_zone and a.src_ip=b.src_ip and a.dst_zone=b.dst_zone and a.dst_ip=b.dst_ip and a.proto=b.proto and a.port=b.port and a.hex1=b.hex1 and a.hex2=b.hex2); When I run the command I get an error ERROR: column reference firewall is ambiguous LINE 3: ... a WHERE NOT EXISTS (SELECT * FROM upsert b WHERE b.firewall... Any idea what I am doing wrong? In your UPDATE statement, I'd suggest explicitly putting the T table alias before each column you're setting. That will make the assignment more explicit and hopefully get around the error. Also, is there an easier way to do that? Thanks for the help. Best, Richard Dunks
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
Le vendredi 25 octobre 2013 à 04:50 +0200, Andreas a écrit :
well, not quite
We are not talking about files but databases within the db server.
Lets keep 3 copies total
the idea is to start with the database db_test today (2013/10/24)
2013/10/25: rename db_test to db_test_13025 and import the latest
dump into a new db_test
2013/10/26: rename db_test to db_test_13026 ... import
2013/10/27: rename db_test to db_test_13027 ... import
2013/10/28: rename db_test to db_test_13028 ... import
Now we've got db_test and 4 older copies.
Find the oldest copy and drop it. -- drop db_test_131025
or better every day drop every copy but the 3 newest.
and so on
this needs to be done by an external cron script or probaply by a
function within the postgres database or any other administrative database.
The point is to give the assistant a test-db where he could mess things up.
In the event he works longer than a day on a task his work shouldn't be
droped completely when the test-db gets automatically replaced.
I assume db_test is created from a dump file? if that's the case, and if
your system allows it, using logrotate on the dump is very
straithforward; e.g. to rotate an archive everyday, keeping a weekly
archive over 52 two weeks, simply create the
file /etc/logrotate.d/myapp :
#Create rotation for myapp's backups
/var/backups/myapp/myapp.gz {
weekly
missingok
rotate 52
notifempty
}
--
Salutations, Vincent Veyron
http://marica.fr/site/demonstration
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
On 25 Říjen 2013, 3:53, Andreas wrote: Hi, I'd like to set up a DB-Server that keeps copies of our productive db for an external db-assistant. He should prepare chores on the test-server and mail the sql scripts to me. I'll look over those scripts and run them against the productive db myself. So I'd like to have a daily cron job dump the main db, rename the test-db to something with a date in it. Like test_db -- test_db_20131024 Create a new test_db and import the dump of the main db. So far no problem but how could I limit the number of test_dbs to 5? I'd like to keep those test_dbs 5 days and then drop them. Hi, I assume that's something that needs to be done by your script, there's certainly nothing in PostgreSQL itself to do that. You may for example run a daily cron script that lists all databases on the test server, parses the database name and drops those older than 5 days. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
On Fri, Oct 25, 2013 at 10:53 AM, Andreas maps...@gmx.net wrote: Hi, I'd like to set up a DB-Server that keeps copies of our productive db for an external db-assistant. He should prepare chores on the test-server and mail the sql scripts to me. I'll look over those scripts and run them against the productive db myself. So I'd like to have a daily cron job dump the main db, rename the test-db to something with a date in it. Like test_db -- test_db_20131024 Create a new test_db and import the dump of the main db. So far no problem but how could I limit the number of test_dbs to 5? I'd like to keep those test_dbs 5 days and then drop them. A simple script kicked by a cron job would do the work for you easily... -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
On Friday, October 25, 2013 03:53:14 AM Andreas wrote:
Hi,
I'd like to set up a DB-Server that keeps copies of our productive db
for an external db-assistant.
He should prepare chores on the test-server and mail the sql scripts to me.
I'll look over those scripts and run them against the productive db myself.
So I'd like to have a daily cron job dump the main db, rename the
test-db to something with a date in it.
Like test_db -- test_db_20131024
Create a new test_db and import the dump of the main db.
So far no problem but how could I limit the number of test_dbs to 5?
I'd like to keep those test_dbs 5 days and then drop them.
#delete files older than 5 days
$ find . -mtime +4 -exec rm '{}' \;
Put that in cron too :)
--
Regards
Shridhar
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
Am 25.10.2013 04:15, schrieb Shridhar Daithankar:
On Friday, October 25, 2013 03:53:14 AM Andreas wrote:
Hi,
I'd like to set up a DB-Server that keeps copies of our productive db
for an external db-assistant.
He should prepare chores on the test-server and mail the sql scripts to me.
I'll look over those scripts and run them against the productive db myself.
So I'd like to have a daily cron job dump the main db, rename the
test-db to something with a date in it.
Like test_db -- test_db_20131024
Create a new test_db and import the dump of the main db.
So far no problem but how could I limit the number of test_dbs to 5?
I'd like to keep those test_dbs 5 days and then drop them.
#delete files older than 5 days
$ find . -mtime +4 -exec rm '{}' \;
Put that in cron too :)
well, not quite
We are not talking about files but databases within the db server.
Lets keep 3 copies total
the idea is to start with the database db_test today (2013/10/24)
2013/10/25: rename db_test to db_test_13025 and import the latest
dump into a new db_test
2013/10/26: rename db_test to db_test_13026 ... import
2013/10/27: rename db_test to db_test_13027 ... import
2013/10/28: rename db_test to db_test_13028 ... import
Now we've got db_test and 4 older copies.
Find the oldest copy and drop it. -- drop db_test_131025
or better every day drop every copy but the 3 newest.
and so on
this needs to be done by an external cron script or probaply by a
function within the postgres database or any other administrative database.
The point is to give the assistant a test-db where he could mess things up.
In the event he works longer than a day on a task his work shouldn't be
droped completely when the test-db gets automatically replaced.
Regards
Andreasd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help how to manage a couple of daily DB copies.
Dear, Append following command to crontab: D=`date -d -5day +%Y%m%d`;echo DROP DATABASE test_db_$D | psql Maybe you should change the psql to your psql path. -- Original -- From: Andreas;maps...@gmx.net; Date: Fri, Oct 25, 2013 09:53 AM To: pgsql-generalpgsql-general@postgresql.org; Subject: [GENERAL] Need help how to manage a couple of daily DB copies. Hi, I'd like to set up a DB-Server that keeps copies of our productive db for an external db-assistant. He should prepare chores on the test-server and mail the sql scripts to me. I'll look over those scripts and run them against the productive db myself. So I'd like to have a daily cron job dump the main db, rename the test-db to something with a date in it. Like test_db -- test_db_20131024 Create a new test_db and import the dump of the main db. So far no problem but how could I limit the number of test_dbs to 5? I'd like to keep those test_dbs 5 days and then drop them.
Re: [GENERAL] Need help with Inet type
Hi All, I am trying to install postgresql-jdbc but facing java error. sun java is already installed but postgresql-jdbc installing openjdj. please let me know how to resolve this and if you require any other details. - AROHI -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-with-Inet-type-tp5770342p5770565.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with Inet type
On Wed, 2013-09-11 at 21:49 -0700, arohi wrote: Hi All, I am trying to install postgresql-jdbc but facing java error. sun java is already installed but postgresql-jdbc installing openjdj. please let me know how to resolve this and if you require any other details. - AROHI -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-help-with-Inet-type-tp5770342p5770565.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. Hello Arohi, If you downloaded the JDBC driver from www.postgrseql.org it is just a jar file. You can choose the type, etc. It doesn't install anything else. Any other application using Java can access it assuming paths and permissions are correct. Cheers, Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with Inet type
## Eric Lamer (eric.la...@intact.net): I can do something like: Select * From logs Where src_ip IN (Select ip from ip_table where zone = 'ZONE_a'); Of course that does not work since it does not work with Inet type and I cannot use because I have more than 1 row return from the second select. How about: SELECT DISTINCT logs.* FROM logs JOIN ip_table ON logs.srcip ip_table.ip WHERE zone = 'ZONE_a'; Regards, Christoph -- Spare Space -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help
thanks for ur help,this was the requirement which assigned for us,so i had to ask even though we are having many options.thanks again -- View this message in context: http://postgresql.1045698.n5.nabble.com/Here-is-my-problem-tp5766954p5767778.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help
Basavaraj wrote i have two unrelated tables as their is no common column to apply joins or anything, so i want to join those tables... I can understand being required to join them (for some unstated reason) but a simple want does not seem a strong enough reason to go through the pain... ...using simple query... If you have to join these tables together the complexity of the resultant query should be irrelevant. Furthermore how one defines simple is quite subjective. finally the result shoule be name| address|email|mobileNo|firstname|lastName| -- abc some1mail1 1234564 def xyz 5 records | 10 records | Very thankful for solution I have no clue what you mean when you indicate 5 records | 10 records in the final result. I'm tempted to ask you leading questions but instead am going to ask that you consider your goal more closely and be more explicit in your description/request. If you just want to go and play with it you can consider two possible options: SELECT * FROM tbl1 CROSS JOIN tbl2 --(this will return X times Y rows - or 10 times 5 = 50) or not valid SQL, just an example SELECT * FROM (SELECT row_number, * FROM tbl1) t1 FULL OUTER JOIN (SELECT row_number, * FROM tbl2) t2 USING (row_number) -- this will return 10 rows with 5 of them containing NULL values for t1 columns where t2 has row numbers not existing in t1. What you are doing, by the example given, is wrong. Proposing an alternative is impossible since you have not explained WHY you feel you need to do this or WHAT you are actually trying to accomplish. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Here-is-my-problem-tp5766954p5767617.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need help
On 8/16/2013 6:35 AM, Basavaraj wrote: finally the result shoule be name| address|email|mobileNo|firstname|lastName| -- abc some1mail1 1234564 def xyz 5 records | 10 records | First, a table is an unordered SET of tuples(rows). it only has an order when one is applied to it. so which rows of table 1 would go with what rows of table 2? Second, if that 2nd table has 10 records and the first table only has 5, what would those other 5 look like in your example? no answer I can think of makes sense. all rows of a recordset have to have the same fields. If this data is unrelated, then it does not belong together in a relation. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help
On Aug 9, 2013, at 19:59, nandan nandand1...@gmail.com wrote: Hello All ; Please help me in knowing below queries which are in Mysql to Postgresql. Postgres and MySQL are very different in these respects. Answering these questions is rather pointless, as it will not give you an understanding of how the role and grant system works in PostgreSQL and you'll need that kind of understanding if you're as concerned about security as you appear to be. Have a look at chapters 19 20 in the fine manual: http://www.postgresql.org/docs/9.2/static/index.html Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help
On 8/9/2013 10:59 AM, nandan wrote:
Hello All ;
Please help me in knowing below queries which are in Mysql to Postgresql.
1.
SELECT user,host,password
FROM mysql.user
WHERE password = '';
SET PASSWORD FOR user@host =
PASSWORD ('newpass');
postgres has no concept of user@host.host restrictions can be
implemented in the pg_hba.conf file.
ALTER ROLE user WITH PASSWORD 'newpass';
2.
SELECT user,host,password
FROM mysql.user
WHERE user = '';
DELETE FROM mysql.user
WHERE user = '';
FLUSH PRIVILEGES;
no such concept in postgres. you would drop a user with..
DROP ROLE username;
but there are no empty/null users.
3.
SELECT user,host,password
FROM mysql.user
WHERE user = 'root' AND host = '%';
DROP USER root@'%';
FLUSH PRIVILEGES;
no root user in postgres.
4.
SELECT user,host,password
FROM mysql.user
WHERE length(password) 41;
SET PASSWORD FOR user@host =
PASSWORD ('newpass');
not even sure why you would set all users who's password is not 41 chars
long to a specific password, so I'm not going there.
5.
Do not enable insecure password generation option
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep old_passwords
6.
Enable secure password authentication option by blocking connections from
all accounts that have passwords stored in it.
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep secure_auth
these two don't make any sense in postgres.
7.
Binary logging should be enabled to log all completed transactions, and
allow for point-in-time recovery. This can be enabled via the log-bin
status variable in the mysql configuration file. As an example, the
following entry will place all binary log files in the /var/lib/mysql/logs
directory, and use 'binlog' as the filename prefix to get binary log files
names such as binlog.01:
log-bin=/var/lib/mysql/logs/binlog
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep log_bin
read the section on WAL Archiving in the postgres user manual, if done
correctly, this allows Point In Time Recovery.
8.
Prevent the grant statement from creating new users unless a non-empty
password is specified (v5.0.2+)
Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:
mysqladmin var | grep sql_mode
the GRANT statement doesn't create roles, CREATE ROLE (or CREATE USER) does.
9.
Do not allow new user creation by a user that does not have write access to
the mysql.user table. Note that this setting may not appear when viewing
status variables via show variables or mysqladmin var, and should
therefore be validated by checking the MySQL config file.
only users/roles with the specific CREATEUSER or SUPERUSER attributes
can create/modify users.
9.
Identify and remove privileges on non-existent database objects by issuing
the following sample SQL statements:
...
nonexisting objects don't have any privileges to identify or remove.
10.
SUPER privileges can be verified by issuing the following SQL statement:
SELECT user, host,
super_priv AS 'SUPER'
FROM mysql.user
WHERE super_priv = 'Y';
easy way:
in psql comand line shell, use the \du metacommand to display all
users, note who has superuser.
harder way:
there's probably a query of the pg_catalog metadata but I'm not looking
it up.
11.
Additional global admin privileges can be reviewed by issuing the following
sample SQL statement:
SELECT user, host,
super_priv AS 'SUPER',
shutdown_priv AS 'SHUTDOWN',
process_priv AS 'PROCESS',
grant_priv AS 'GRANT',
create_user_priv AS 'CREATE_USER',
file_priv AS 'FILE',
reload_priv AS 'RELOAD',
show_db_priv AS 'SHOW_DATABASES',
lock_tables_priv AS 'LOCK_TABLES',
repl_slave_priv AS 'REPL_SLAVE',
repl_client_priv AS 'REPL_CLIENT'
FROM mysql.user
WHERE super_priv = 'Y' OR shutdown_priv = 'Y'
OR process_priv = 'Y' OR grant_priv = 'Y'
OR create_user_priv = 'Y' OR file_priv = 'Y'
OR reload_priv = 'Y' OR show_db_priv = 'Y'
OR lock_tables_priv = 'Y'
OR repl_slave_priv = 'Y'
OR repl_client_priv = 'Y';
see answer to previous. postgres is shutdown from the system shell,
not from within postgres. most of those other global privileges don't
exist on postgres, just SUPERUSER, CREATEUSER, CREATEDB attributes.
12.
SHUTDOWN privileges can be verified by issuing the following SQL statement:
no such privilege.
What is the SQL Query or command for Postgres to check the following
points?
1.No blank passwords
2.No anonymous-user accounts
3.No remotely-accessible root
Re: [GENERAL] Need help compiling from souce
I found out what was happening. When the path variables were manually set, something in build.pl was overwriting 64 bit to 32 bit (not sure why. The build was saying 64 was detected and will be used, but then many files were being compiled as 32 bit). I used a cmd prompt that came with Msft vs express 2012 called 64x cross tools. It had some preset env variables. So, maybe I missed a var, or maybe the prompt didn't allow 64 bit to be switched to 32. Whatever the case, it compiled and installed. Thank you for taking the time to look through the file and answer me. -Jake On Jun 26, 2013, at 5:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jake Silverman jakerosss...@gmail.com writes: Sorry about that, and thanks for taking the time to help me. Here is the full file: https://www.dropbox.com/s/c4dwf47nob0i7fr/file4.txt [ lots and lots of error C2065: 'BLCKSZ' : undeclared identifier error C2065: 'XLOG_SEG_SIZE' : undeclared identifier etc etc ] It looks like you're missing some of the macros that normally get set up by the configure script on Unix builds. I confess I'm not familiar with how configuration goes on an MSVC build, but did you follow all the steps in http://www.postgresql.org/docs/devel/static/install-windows.html ? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help compiling from souce
Jake Silverman jakerosss...@gmail.com writes: I'm trying to compile pg 9.3 beta 1 from source using microsoft visual studio on a computer running windows 7. When I build I run into around 1600 errors and around 36 warnings. I imagine I'm doing something horribly wrong. The following is the start of my error log: http://pastebin.com/PdGdvWT7 You didn't paste enough to show any actual errors ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help compiling from souce
Sorry about that, and thanks for taking the time to help me. Here is the full file: https://www.dropbox.com/s/c4dwf47nob0i7fr/file4.txt -Jake (Just realized I didn't hit reply all. I'm sorry for sending you this message twice.) On Wed, Jun 26, 2013 at 11:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jake Silverman jakerosss...@gmail.com writes: I'm trying to compile pg 9.3 beta 1 from source using microsoft visual studio on a computer running windows 7. When I build I run into around 1600 errors and around 36 warnings. I imagine I'm doing something horribly wrong. The following is the start of my error log: http://pastebin.com/PdGdvWT7 You didn't paste enough to show any actual errors ... regards, tom lane
Re: [GENERAL] Need help compiling from souce
Jake Silverman jakerosss...@gmail.com writes: Sorry about that, and thanks for taking the time to help me. Here is the full file: https://www.dropbox.com/s/c4dwf47nob0i7fr/file4.txt [ lots and lots of error C2065: 'BLCKSZ' : undeclared identifier error C2065: 'XLOG_SEG_SIZE' : undeclared identifier etc etc ] It looks like you're missing some of the macros that normally get set up by the configure script on Unix builds. I confess I'm not familiar with how configuration goes on an MSVC build, but did you follow all the steps in http://www.postgresql.org/docs/devel/static/install-windows.html ? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
James B. Byrne byrn...@harte-lyne.ca writes: If all the elements contained in the standard templates had their ownerships changed to that of the owner of the new database then my problem would never have arisen. I do not understand why this is not the case. Is there a reason why this is so? I don't see why you expect that. Should a non-superuser database owner have the ability to redefine, say, sum(int4)? You might as well just give him superuser privileges. In PG's security model, ownership of a database does *not* automatically confer any privileges with respect to the contained objects. It doesn't really give much at all except the ability to drop or rename the database as a whole. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On Thu, February 21, 2013 20:27, Adrian Klaver wrote: My previous not withstanding there is a reason I can see why this not so. Just because a user does not own an object does not mean they cannot use it. This allows a DBA to set up a template with a privilege scheme that suits their needs and then can be replicated. Under your proposal every time a database was created the privilege scheme would need to be reestablished. You want the one user model which can be had by doing everything as a superuser. This is why it is generally recommended to have various roles defined in your database cluster. One role being sufficiently privileged to do the superuser work and others for other tasks. It seems strange to me that a trusted extension, one that can be added by any database owner, is prevented from being treated as trusted in the default configuration. I have no opinion on whether or not plpgsql should be included by default in newly created databases but, I do object that it is included in such a way as to make its management by the subsequent database owner impossible. Lacking the expertise myself might I impose upon you to suggest what configuration of roles would permit the plpgsql extension to be owned by the database owner when added from a template? I am quite willing to use a template2 of my own devising to create new databases but I would rather not have to create a template for every user that might be granted the DBCREATE privilege. This an issue because each project requires at least two separate userids that require the DBCREATE role and both are used to automatically drop and create test and development databases as part of the testing arrangements specific to their project. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On 02/22/2013 07:25 AM, James B. Byrne wrote: On Thu, February 21, 2013 20:27, Adrian Klaver wrote: My previous not withstanding there is a reason I can see why this not so. Just because a user does not own an object does not mean they cannot use it. This allows a DBA to set up a template with a privilege scheme that suits their needs and then can be replicated. Under your proposal every time a database was created the privilege scheme would need to be reestablished. You want the one user model which can be had by doing everything as a superuser. This is why it is generally recommended to have various roles defined in your database cluster. One role being sufficiently privileged to do the superuser work and others for other tasks. It seems strange to me that a trusted extension, one that can be added by any database owner, is prevented from being treated as trusted in the default configuration. I have no opinion on whether or not plpgsql should be included by default in newly created databases but, I do object that it is included in such a way as to make its management by the subsequent database owner impossible. It would seem there is an interaction between the extension mechanism and CREATE LANGUAGE that is not entirely clear to me. Looking at the extension packaging for plpgsql shows that all it does is call CREATE LANGUAGE and add the COMMENT. The control files has superuser = false which as I understand it means the EXTENSION can be created by non-superusers subject to privilege restrictions on the CREATE LANGUAGE and COMMENT command. In the CREATE LANGUAGE docs there is this: The default is that trusted languages can be created by database owners, but this can be adjusted by superusers by modifying the contents of pg_pltemplate. By default in pg_pltemplate plpgsql has tmpldbacreate = 't' which would seem to mean it can be created by non-superusers. The issue from what I am seeing is that when the cluster is created the template databases have plpgsql created in them by the superuser(postgres) and that ownership cannot really be transferred. Lacking the expertise myself might I impose upon you to suggest what configuration of roles would permit the plpgsql extension to be owned by the database owner when added from a template? I am quite willing to use a template2 of my own devising to create new databases but I would rather not have to create a template for every user that might be granted the DBCREATE privilege. This an issue because each project requires at least two separate userids that require the DBCREATE role and both are used to automatically drop and create test and development databases as part of the testing arrangements specific to their project. At this point I am not sure how to do this with out creating role that has superuser privileges. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
Adrian Klaver adrian.kla...@gmail.com wrote: At this point I am not sure how to do this with out creating role that has superuser privileges. Something like this?: -- Set up the template using database superuser. create database template2; \c template2 drop extension plpgsql; vacuum freeze analyze; \c postgres update pg_database set datistemplate = true where datname = 'template2'; checkpoint; -- Create a user who can own the database and plpgsql. create user bob with createdb; set role bob; create database bob template template2; \c bob create extension plpgsql; -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On 02/22/2013 11:59 AM, Kevin Grittner wrote: Adrian Klaver adrian.kla...@gmail.com wrote: At this point I am not sure how to do this with out creating role that has superuser privileges. Something like this?: -- Set up the template using database superuser. create database template2; \c template2 drop extension plpgsql; vacuum freeze analyze; \c postgres update pg_database set datistemplate = true where datname = 'template2'; checkpoint; -- Create a user who can own the database and plpgsql. create user bob with createdb; set role bob; create database bob template template2; \c bob create extension plpgsql; If you do as above plpgsql is created as with owner postgres. To get owner to be bob you need to do: \c bob set role bob; create extension plpgsql; Either way you still get the error on the COMMENT which is what is tripping up the OP. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
Adrian Klaver adrian.kla...@gmail.com wrote: On 02/22/2013 11:59 AM, Kevin Grittner wrote: Adrian Klaver adrian.kla...@gmail.com wrote: At this point I am not sure how to do this with out creating role that has superuser privileges. Something like this?: -- Set up the template using database superuser. create database template2; \c template2 drop extension plpgsql; vacuum freeze analyze; \c postgres update pg_database set datistemplate = true where datname = 'template2'; checkpoint; -- Create a user who can own the database and plpgsql. create user bob with createdb; set role bob; create database bob template template2; \c bob create extension plpgsql; If you do as above plpgsql is created as with owner postgres. To get owner to be bob you need to do: \c bob set role bob; create extension plpgsql; Good point, I forgot that the user was reset by \c. Either way you still get the error on the COMMENT which is what is tripping up the OP. The good news is that it seems to be fixed on HEAD: test=# drop database bob; DROP DATABASE test=# set role bob; SET test= create database bob template template2; CREATE DATABASE test= \c bob bob You are now connected to database bob as user bob. bob= create extension plpgsql; CREATE EXTENSION bob= \dL List of languages Name | Owner | Trusted | Description -+---+-+-- plpgsql | bob | t | PL/pgSQL procedural language (1 row) -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On 02/22/2013 02:25 PM, Kevin Grittner wrote: To get owner to be bob you need to do: \c bob set role bob; create extension plpgsql; Good point, I forgot that the user was reset by \c. Either way you still get the error on the COMMENT which is what is tripping up the OP. The good news is that it seems to be fixed on HEAD: I should have been clearer, the problem is in the dump file created from the database. When you try to restore it chokes on the COMMENT line unless you do the restore as a superuser. test=# drop database bob; DROP DATABASE test=# set role bob; SET test= create database bob template template2; CREATE DATABASE test= \c bob bob You are now connected to database bob as user bob. bob= create extension plpgsql; CREATE EXTENSION bob= \dL List of languages Name | Owner | Trusted | Description -+---+-+-- plpgsql | bob | t | PL/pgSQL procedural language (1 row) -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On Thu, February 21, 2013 12:38, James B. Byrne wrote: I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; Wrong line. This is the line COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; And yes, I went through this a year ago with PG-9.1 and resolved it once by switching to template0 in the connection configuration. Now it is back with PG-9.2. using the exact same configuration code because evidently plpgsql is added regardless. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne byrn...@harte-lyne.ca wrote: On Thu, February 21, 2013 12:38, James B. Byrne wrote: I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; Wrong line. This is the line COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; And yes, I went through this a year ago with PG-9.1 and resolved it once by switching to template0 in the connection configuration. Now it is back with PG-9.2. using the exact same configuration code because evidently plpgsql is added regardless. curious why you want to do this. there was actually some debate back in the day about pros/cons of having pl/pgsql be a built-in feature, which as you can see is where things are going. if you don't mind surgery with a shotgun, you can simply drop the extension after the load resolves. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On Thu, February 21, 2013 13:23, Merlin Moncure wrote: On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne byrn...@harte-lyne.ca wrote: On Thu, February 21, 2013 12:38, James B. Byrne wrote: I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; Wrong line. This is the line COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; And yes, I went through this a year ago with PG-9.1 and resolved it once by switching to template0 in the connection configuration. Now it is back with PG-9.2. using the exact same configuration code because evidently plpgsql is added regardless. curious why you want to do this. there was actually some debate back in the day about pros/cons of having pl/pgsql be a built-in feature, which as you can see is where things are going. I want to do this because my automated test harness is choking because it cannot add an absolutely worthless COMMENT to that extension. It cannot add the comment because the language extension is added to the database with an incorrect owner. A database created by userid X should, in ALL RESPECTS, be OWNED by userid X. When the ownership of database Y is changed from user A to user B then all of the attributes of database Y should become owned by B. For some reason this is not the case with the plpgsql language extension. if you don't mind surgery with a shotgun, you can simply drop the extension after the load resolves. I have tried this and it does not work. It does not work for the simple reason that the test harness recreates the test database from the dump file each run. The dump file is created with a COMMENT statement which cannot be applied to the plpgsql language extension statement unless the user that connects to the database is a superuser. That condition makes the granting of DBCREATE to another userid somewhat pointless. I have resolved this by: 1. as the postgres user creating a copy of template1 (template2) 2. as the postgres user assigning the test userid as owner of template2 3. as the postgres user dropping the plpgsql extension from template2 4. as the test user adding the plpgsql extension to template2 5. specifying template2 in the database connection configuration file. I believe, however, that this entire situation is a defect in postgresql-9.2 and 9.1. The plpgsql language extension should not be included in new databases if it does not already exist in the selected template or when no template is used at all. Surely the local DBA is the final arbiter of what a given installation wishes to have in their databases and forcing them to go through hoops to accomplish this is hardly user-friendly. Further, if a language, or for that matter any, extension is added to a new database from a template or other source then that extension should be owned by the owner of the resulting database and not by any other userid. If there is a good reason as to why this should be otherwise I would certainly like to have it explained to me. The current arrangement is not really satisfactory as it requires either separate template databases for each userid granted the DBCREATE role or the superuser role has to be granted in place of the DBCREATE role. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On 02/21/2013 09:38 AM, James B. Byrne wrote: I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; And for some reason this statement causes my test suit to fail with this error: psql:/home/byrnejb/Projects/Software/theHeart/code/proforma_rails_3_2/db/structure.sql:22: ERROR: must be owner of extension plpgsql I do not understand why this happens since, as I read this, if the plpgsql extension already exists in the database, which it does, then this statement should not be executed at all. But it does. Further, I do not understand why or how plpgsql is being included into databases on create as I have removed it from template1 and it does not exist in template0 to begin with. Actually it does exist in template0. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On 02/21/2013 12:14 PM, James B. Byrne wrote: On Thu, February 21, 2013 13:23, Merlin Moncure wrote: On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne byrn...@harte-lyne.ca wrote: On Thu, February 21, 2013 12:38, James B. Byrne wrote: I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; Wrong line. This is the line COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; And yes, I went through this a year ago with PG-9.1 and resolved it once by switching to template0 in the connection configuration. Now it is back with PG-9.2. using the exact same configuration code because evidently plpgsql is added regardless. curious why you want to do this. there was actually some debate back in the day about pros/cons of having pl/pgsql be a built-in feature, which as you can see is where things are going. I want to do this because my automated test harness is choking because it cannot add an absolutely worthless COMMENT to that extension. It cannot add the comment because the language extension is added to the database with an incorrect owner. A database created by userid X should, in ALL RESPECTS, be OWNED by userid X. When the ownership of database Y is changed from user A to user B then all of the attributes of database Y should become owned by B. For some reason this is not the case with the plpgsql language extension. if you don't mind surgery with a shotgun, you can simply drop the extension after the load resolves. I have tried this and it does not work. It does not work for the simple reason that the test harness recreates the test database from the dump file each run. The dump file is created with a COMMENT statement which cannot be applied to the plpgsql language extension statement unless the user that connects to the database is a superuser. That condition makes the granting of DBCREATE to another userid somewhat pointless. I have resolved this by: 1. as the postgres user creating a copy of template1 (template2) 2. as the postgres user assigning the test userid as owner of template2 3. as the postgres user dropping the plpgsql extension from template2 4. as the test user adding the plpgsql extension to template2 5. specifying template2 in the database connection configuration file. template1=# \dL List of languages Name| Owner | Trusted | Description ---+--+-+-- plpgsql | postgres | t | PL/pgSQL procedural language plpythonu | postgres | f | (2 rows) template1=# alter language plpgsql owner to aklaver; ALTER LANGUAGE template1=# \dL List of languages Name| Owner | Trusted | Description ---+--+-+-- plpgsql | aklaver | t | PL/pgSQL procedural language plpythonu | postgres | f | (2 rows) The current arrangement is not really satisfactory as it requires either separate template databases for each userid granted the DBCREATE role or the superuser role has to be granted in place of the DBCREATE role. That is sort of the point of the template system, different templates for different situations. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On Thu, February 21, 2013 16:02, Adrian Klaver wrote: On 02/21/2013 12:14 PM, James B. Byrne wrote: The current arrangement is not really satisfactory as it requires either separate template databases for each userid granted the DBCREATE role or the superuser role has to be granted in place of the DBCREATE role. That is sort of the point of the template system, different templates for different situations. Creating a new database from the system provided standard templates is not what I would consider a different situation requiring a specialized template for each and every user granted the DBCREATE role. Requiring that seems to me to be busywork and a complete waste of DBA resources. If all the elements contained in the standard templates had their ownerships changed to that of the owner of the new database then my problem would never have arisen. I do not understand why this is not the case. Is there a reason why this is so? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On 02/21/2013 03:18 PM, James B. Byrne wrote: On Thu, February 21, 2013 16:02, Adrian Klaver wrote: On 02/21/2013 12:14 PM, James B. Byrne wrote: The current arrangement is not really satisfactory as it requires either separate template databases for each userid granted the DBCREATE role or the superuser role has to be granted in place of the DBCREATE role. That is sort of the point of the template system, different templates for different situations. Creating a new database from the system provided standard templates is not what I would consider a different situation requiring a specialized template for each and every user granted the DBCREATE role. Requiring that seems to me to be busywork and a complete waste of DBA resources. If all the elements contained in the standard templates had their ownerships changed to that of the owner of the new database then my problem would never have arisen. I do not understand why this is not the case. Is there a reason why this is so? Hmm, you might be on to something: I changed owner in template1 to me: p_test=# \c template1 You are now connected to database template1 as user postgres. template1=# \dL List of languages Name| Owner | Trusted | Description ---+--+-+-- plpgsql | aklaver | t | PL/pgSQL procedural language plpythonu | postgres | f | (2 rows) Create new database as me: template1=# \c - aklaver You are now connected to database template1 as user aklaver. template1= create database p_test template=template1 owner=aklaver; CREATE DATABASE template1= \c p_test You are now connected to database p_test as user aklaver. In new database language is owned by me. p_test= \dL List of languages Name| Owner | Trusted | Description ---+--+-+-- plpgsql | aklaver | t | PL/pgSQL procedural language plpythonu | postgres | f | (2 rows) Dump the database: aklaver@panda:~ /usr/local/pgsql92/bin/pg_dump p_test -C -U aklaver -p 5442 -f p_test.sql Dropped the database: postgres= drop database p_test ; DROP DATABASE Restored it: aklaver@panda:~ /usr/local/pgsql92/bin/psql -d postgres -U aklaver -p 5442 -f p_test.sql SET SET SET SET SET CREATE DATABASE ALTER DATABASE You are now connected to database p_test as user aklaver. SET SET SET SET SET CREATE EXTENSION psql:p_test.sql:39: ERROR: must be owner of extension plpgsql Now plpgsql is back to being owned by postgres: postgres= \c p_test You are now connected to database p_test as user aklaver. p_test= \dL List of languages Name | Owner | Trusted | Description -+--+-+-- plpgsql | postgres | t | PL/pgSQL procedural language (1 row) The issue seems to be, from the p_test.sql file: CREATE DATABASE p_test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; It is creating the database using template0 instead for the template specified in the CREATE DATABASE run from psql. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help extripating plpgsql
On 02/21/2013 03:18 PM, James B. Byrne wrote: On Thu, February 21, 2013 16:02, Adrian Klaver wrote: On 02/21/2013 12:14 PM, James B. Byrne wrote: The current arrangement is not really satisfactory as it requires either separate template databases for each userid granted the DBCREATE role or the superuser role has to be granted in place of the DBCREATE role. That is sort of the point of the template system, different templates for different situations. Creating a new database from the system provided standard templates is not what I would consider a different situation requiring a specialized template for each and every user granted the DBCREATE role. Requiring that seems to me to be busywork and a complete waste of DBA resources. If all the elements contained in the standard templates had their ownerships changed to that of the owner of the new database then my problem would never have arisen. I do not understand why this is not the case. Is there a reason why this is so? My previous not withstanding there is a reason I can see why this not so. Just because a user does not own an object does not mean they cannot use it. This allows a DBA to set up a template with a privilege scheme that suits their needs and then can be replicated. Under your proposal every time a database was created the privilege scheme would need to be reestablished. You want the one user model which can be had by doing everything as a superuser. This is why it is generally recommended to have various roles defined in your database cluster. One role being sufficiently privileged to do the superuser work and others for other tasks. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help understanding WAL and checkpoints
Hi Albe --- On Wed, 2013/2/6, Albe Laurenz laurenz.a...@wien.gv.at wrote: drew_hunt wrote: I'm trying to get my head around WAL and checkpoints and need to ask a couple of questions before I get a headache. Firstly, I see the terms WAL log, WAL file and transaction log all over the place - are these the same thing (i.e. files in the pg_xlog directory)? Usually they mean the same thing. For exact definitions, read http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL WAL consists of a stream of WAL records and is physically represented as WAL segment files (in pg_xlog). I'm a bit confused by this paragraph in the docs: Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before the checkpoint. At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file. (The changes were previously flushed to the WAL files.) ( see: http://www.postgresql.org/docs/current/static/wal-configuration.html ) a special checkpoint record is written to the log file. - which log file is meant here? The WAL. The changes were previously flushed to the WAL files. - does previously here mean at a previous point in time or in previous PostgreSQL versions? The former. - at what point are changes flushed to WAL files? The change must be on disk in a WAL segment before the transaction can commit. So say I perform an operation like : UPDATE foo SET bar='baz' are the following assumptions correct? - The first time this changed data hits the disk, it is as an entry in the WAL log - At some point a checkpoint occurs, and the changed data is written to the actual data file from system memory (the dirty data pages?) - the only time the actual data files will be updated from the WAL log (i.e. not from system memory) will be after a crash, when the logs are replayed from the last checkpoint? All three are correct. Many thanks for the answers, its cleared things up for me :) - drew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help understanding WAL and checkpoints
drew_hunt wrote: I'm trying to get my head around WAL and checkpoints and need to ask a couple of questions before I get a headache. Firstly, I see the terms WAL log, WAL file and transaction log all over the place - are these the same thing (i.e. files in the pg_xlog directory)? Usually they mean the same thing. For exact definitions, read http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-ARCHIVING-WAL WAL consists of a stream of WAL records and is physically represented as WAL segment files (in pg_xlog). I'm a bit confused by this paragraph in the docs: Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before the checkpoint. At checkpoint time, all dirty data pages are flushed to disk and a special checkpoint record is written to the log file. (The changes were previously flushed to the WAL files.) ( see: http://www.postgresql.org/docs/current/static/wal-configuration.html ) a special checkpoint record is written to the log file. - which log file is meant here? The WAL. The changes were previously flushed to the WAL files. - does previously here mean at a previous point in time or in previous PostgreSQL versions? The former. - at what point are changes flushed to WAL files? The change must be on disk in a WAL segment before the transaction can commit. So say I perform an operation like : UPDATE foo SET bar='baz' are the following assumptions correct? - The first time this changed data hits the disk, it is as an entry in the WAL log - At some point a checkpoint occurs, and the changed data is written to the actual data file from system memory (the dirty data pages?) - the only time the actual data files will be updated from the WAL log (i.e. not from system memory) will be after a crash, when the logs are replayed from the last checkpoint? All three are correct. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help for import of text file
Am 15.12.2012 22:22, schrieb Peter Bex: On Sat, Dec 15, 2012 at 10:16:55PM +0100, Peter Bex wrote: A simple sed(1) expression should do the trick: sed -E 's/ +/ /g' old-file new-file I just remembered where I could check, and the GNU sed equivalent is: sed -r 's/ +/ /g' old-file new-file Sorry for the confusion. With sed as startingpoint I figured it out. Those 3 steps make the input files consumable for COPY 1. dos2unix 2. sed -i 's/[ \t]*$//' 3. sed -i 's/ / /g' The input files get created by a simple windows batch where I can't change anything. It uses echo to attach a line of 4 parameters to those textfiles. How would you manage if one or more of those parameters contained blanks in some cases? This doesn't appear, yet. But I consider this as luck. :} The real column formats are ( TEXT, TEXT, DATE, TIME ).
Re: [GENERAL] Need help for import of text file
On Sun, Dec 16, 2012 at 06:48:35PM +0100, Andreas wrote:
With sed as startingpoint I figured it out.
Those 3 steps make the input files consumable for COPY
1. dos2unix
2. sed -i 's/[ \t]*$//'
3. sed -i 's/ / /g'
You can reduce this to one invocation by separating the commands
by a semicolon (or by passing multiple -e flags)
sed -i 's/[ \t]*$//;s/ / /g'
The input files get created by a simple windows batch where I can't
change anything.
It uses echo to attach a line of 4 parameters to those textfiles.
How would you manage if one or more of those parameters contained blanks
in some cases?
This doesn't appear, yet. But I consider this as luck. :}
The real column formats are ( TEXT, TEXT, DATE, TIME ).
Well, that's a bit trickier and my sed skills are rather rusty.
I'd probably use awk for these more complex tasks:
awk '/\(.*\)/ { gsub(/ +/, ); } { print $0 }'
The gsub command acts like sed's s command with the g modifier.
By prefixing the block with the gsub command with a regex, it only
acts on that regex. The regex in this example only looks for an opening
and a closing paren anywhere on the line; you might need to tweak it
to more closely match your case. Alternatively, you could implement
a counter that skips the four lines (which can be done with both sed
and awk).
If it gets more complex than this, you can always write a proper
program in a real language to do it. This can be easier to maintain.
Cheers,
Peter
--
http://sjamaan.ath.cx
--
The process of preparing programs for a digital computer
is especially attractive, not only because it can be economically
and scientifically rewarding, but also because it can be an aesthetic
experience much like composing poetry or music.
-- Donald Knuth
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help for import of text file
On 12/16/2012 01:12 PM, Peter Bex wrote:
On Sun, Dec 16, 2012 at 06:48:35PM +0100, Andreas wrote:
With sed as startingpoint I figured it out.
Those 3 steps make the input files consumable for COPY
1. dos2unix
2. sed -i 's/[ \t]*$//'
3. sed -i 's/ / /g'
You can reduce this to one invocation by separating the commands
by a semicolon (or by passing multiple -e flags)
sed -i 's/[ \t]*$//;s/ / /g'
The input files get created by a simple windows batch where I can't
change anything.
It uses echo to attach a line of 4 parameters to those textfiles.
How would you manage if one or more of those parameters contained blanks
in some cases?
This doesn't appear, yet. But I consider this as luck. :}
The real column formats are ( TEXT, TEXT, DATE, TIME ).
Well, that's a bit trickier and my sed skills are rather rusty.
I'd probably use awk for these more complex tasks:
awk '/\(.*\)/ { gsub(/ +/, ); } { print $0 }'
The gsub command acts like sed's s command with the g modifier.
By prefixing the block with the gsub command with a regex, it only
acts on that regex. The regex in this example only looks for an opening
and a closing paren anywhere on the line; you might need to tweak it
to more closely match your case. Alternatively, you could implement
a counter that skips the four lines (which can be done with both sed
and awk).
If it gets more complex than this, you can always write a proper
program in a real language to do it. This can be easier to maintain.
Cheers,
Peter
why not use the squeeze option of tr.
tr -s
--
Stephen Clark
*NetWolves*
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com
Re: [GENERAL] Need help for import of text file
On Sun, Dec 16, 2012 at 06:30:24PM -0500, Steve Clark wrote: why not use the squeeze option of tr. tr -s I wasn't aware of that one, it's even simpler and more elegant. Thanks! For this particular case, tr(1) won't do for the same reason the simple sed(1) expression I gave won't do: it should only be applied to the data, not the extra 4 lines of meta-data. Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help for import of text file
On Dec 15, 2012, at 1:06 PM, Andreas wrote:
Hi,
I need to import textfiles that have 5 columns but there is just blanks as
delimitors.
I could use COPY to read them but there is a time column that shows times as
h:mm.ss,ms in the morning and hh:mm.ss,ms in the afternoon.
Problem here is in the morning the first digit of the hour is shown as a
blank so there are 2 blanks before the time so COPY misstakes this as an
empty column and gets confused.
Can someone point me in the direction of an COPY option I'm not aware of, or
alternativly to some console tool that I can put in the batch before the
import step and replace the 2 blanks with 1 blank.
I use an OpenSuse server so some linux tool would do.
regards
Andreas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
sed and awk are your friends.
You might consider some text processing prior to import. I do this a lot
because I work with external datasets that require all kinds of massaging.
For example:
sed -e 's/^\s{2}/ /g' filename | psql DATABASE -c 'COPY table_name from STDIN'
the above will replace 2 spaces appearing at the front of the file with one
space, then pipe the result to psql copy command that expects input from STDIN.
Hope that is helpful
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help for import of text file
On 12/15/2012 11:06 AM, Andreas wrote: Hi, I need to import textfiles that have 5 columns but there is just blanks as delimitors. I could use COPY to read them but there is a time column that shows times as h:mm.ss,ms in the morning and hh:mm.ss,ms in the afternoon. Problem here is in the morning the first digit of the hour is shown as a blank so there are 2 blanks before the time so COPY misstakes this as an empty column and gets confused. Can someone point me in the direction of an COPY option I'm not aware of, or alternativly to some console tool that I can put in the batch before the import step and replace the 2 blanks with 1 blank. I use an OpenSuse server so some linux tool would do. How big a file are we talking about? I found using the OO/LibreOffice spreadsheet good for this, assuming a reasonable file size. If you use the CSV import you can make the columns where you want them and then save the file with another delimiter(I tend to use the pipe symbol |). Then use that file with COPY. regards Andreas -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help for import of text file
On Sat, Dec 15, 2012 at 08:06:44PM +0100, Andreas wrote: Hi, Problem here is in the morning the first digit of the hour is shown as a blank so there are 2 blanks before the time so COPY misstakes this as an empty column and gets confused. Can someone point me in the direction of an COPY option I'm not aware of, or alternativly to some console tool that I can put in the batch before the import step and replace the 2 blanks with 1 blank. I use an OpenSuse server so some linux tool would do. A simple sed(1) expression should do the trick: sed -E 's/ +/ /g' old-file new-file GNU sed also allows in-place editing using -i, so you can avoid writing it to a second file. Some seds accept a different flag to enable extended regexps. Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help for import of text file
On Sat, Dec 15, 2012 at 10:16:55PM +0100, Peter Bex wrote: A simple sed(1) expression should do the trick: sed -E 's/ +/ /g' old-file new-file I just remembered where I could check, and the GNU sed equivalent is: sed -r 's/ +/ /g' old-file new-file Sorry for the confusion. Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in reclaiming disk space by deleting the selected records
Hi, On Friday, September 14, 2012 01:29:59 AM Steve Crawford wrote: 2. CLUSTER is typically way faster than VACUUM FULL and rebuilds the indexes as well but it temporarily requires sufficient disk-space to write out a copy of the table being clustered. Thats not the case anymore since 9.0 btw. These days VACUUM FULL does the same thing CLUSTER does just without sorting. Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in reclaiming disk space by deleting the selected records
On 09/14/2012 05:35 AM, Andres Freund wrote: Hi, On Friday, September 14, 2012 01:29:59 AM Steve Crawford wrote: 2. CLUSTER is typically way faster than VACUUM FULL and rebuilds the indexes as well but it temporarily requires sufficient disk-space to write out a copy of the table being clustered. Thats not the case anymore since 9.0 btw. These days VACUUM FULL does the same thing CLUSTER does just without sorting. That's true - I should have pointed that out. But it also means that you can get into a corner if you need to vacuum full large tables when you have limited free disk space - something the OP should consider since reclaiming disk space was one of his motivations. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in reclaiming disk space by deleting the selected records
On 09/13/2012 06:33 AM, Yelai, Ramkumar IN BLR STS wrote: Hi All, I am a beginner in Postgresql and Databases. I have a requirement that reclaiming disk space by deleting the rows in a selected time span. I went through the documents and articles to know how to get the table size (_http://wiki.postgresql.org/wiki/Disk_Usage_) But before let the user delete, I have to show the size of the records size in the selected time span. But here I don’t know how to calculate the selected records size. In addition to this, I assume that after deleted the records I have to run VACUUM FULL command to reclaiming the space( Please correct me if I am wrong or let me know the best approach) . The table looks like this CREATE TABLE IF NOT EXISTS SN_SamplTable ( ID integer NOT NULL, “Data” integer, CLIENT_COUNT_TIMESTAMP timestamp without time zone ); Please help me to how to proceed on this. Some things to consider: 1. If you have indexes on the table you need to consider the additional disk space recovered there. 2. CLUSTER is typically *way* faster than VACUUM FULL and rebuilds the indexes as well but it temporarily requires sufficient disk-space to write out a copy of the table being clustered. 3. If you can pre-plan for removing old data, for example you are collecting log data and need a rolling 3-months, then table partitioning is the way to go. You do this using an empty parent tables and putting the data into child tables each of which covers a specific time-span, perhaps one child-table per month or per week. When the data is no longer required you simply dump the child table if desired and then drop the child table. This is a virtually instant process that does not cause table bloat. Partitioning by date is only one way. You could determine that you need to drop data by user-ID and partition that way. Or by a combination of ID and date-range. But this method does not work if you need to remove arbitrary date ranges. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on autovacuum in postgres 9.1.2
Khangelani Gama wrote: I need help in turning off autovacuum in the Database that's running postgres 9.1.2 DB. I tried to turn it off by putting off in postgresql.cont file and restarted the postmaster but when I run show autovacuum; query I still see autovacuum set on inside the database. autovacuum on (1 row) postgresql.conf currently looks as follows. Should I remove the hash sign that's before autovacuum = off? #autovacuum = off # Enable autovacuum subprocess? 'on' [snip] Exactly. See http://www.postgresql.org/docs/9.1/static/config-setting.html Hash marks (#) designate the rest of the line as a comment. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on autovacuum in postgres 9.1.2
On 08/30/2012 06:52 PM, Khangelani Gama wrote: Hi I need help in turning off autovacuum in the Database that’s running postgres 9.1.2 DB. I tried to turn it off by putting “off” in postgresql.cont postgresql.conf, I presume. Why do you want to turn autovaccum off? That's almost never the right thing to do; if anything you should usually be turning it *up*. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on autovacuum in postgres 9.1.2
Thanks at lot. -Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Thursday, August 30, 2012 1:22 PM To: Khangelani Gama *EXTERN*; pgsql-general@postgresql.org Subject: RE: [GENERAL] Need help on autovacuum in postgres 9.1.2 Khangelani Gama wrote: I need help in turning off autovacuum in the Database that's running postgres 9.1.2 DB. I tried to turn it off by putting off in postgresql.cont file and restarted the postmaster but when I run show autovacuum; query I still see autovacuum set on inside the database. autovacuum on (1 row) postgresql.conf currently looks as follows. Should I remove the hash sign that's before autovacuum = off? #autovacuum = off # Enable autovacuum subprocess? 'on' [snip] Exactly. See http://www.postgresql.org/docs/9.1/static/config-setting.html Hash marks (#) designate the rest of the line as a comment. Yours, Laurenz Albe CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on autovacuum in postgres 9.1.2
On 08/30/2012 03:52 AM, Khangelani Gama wrote: Hi I need help in turning off autovacuum in the Database that’s running postgres 9.1.2 DB. I tried to turn it off by putting “off” in postgresql.cont file and restarted the postmaster but when I run “*show autovacuum;* “ query I still see autovacuum set on inside the database. autovacuum on In addition to what has already been said, it is important to note that changing the setting to off does not entirely turn off autovacuum. http://www.postgresql.org/docs/9.1/interactive/runtime-config-autovacuum.html Note that even when this parameter is disabled, the system will launch autovacuum processes if necessary to prevent transaction ID wraparound. See Section 23.1.4 for more information. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with SQL query and finding NULL array_agg
On 2012-08-02 21:32, David Johnston wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Condor
Sent: Thursday, August 02, 2012 4:35 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help with SQL query and finding NULL
array_agg
On 2012-08-01 23:59, David Johnston wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Condor
Sent: Wednesday, August 01, 2012 4:16 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Need help with SQL query and finding NULL
array_agg
Hello ppl,
I have a problem with one sql query, can some one help me. My
query
is:
SELECT array_agg(month) AS month, array_agg(status) AS status,
array_agg(service) AS service, case when
array_upper(array_agg(phone),
1) is not null THEN array_agg(phone) else array_agg(mobile) END
FROM
bills WHERE status 1 GROUP BY mobile
I try with simple query to identify did array_agg(phone) is empty
or
null and if is it to return me mobile field, if not empty to
return
me phone.
Mobile field
exist always, but phone may exists for that mobile may not
exists.
One mobile can have few services like:
mob servicephone
1321543434 64
1321543434 66 1325
I try few thing but sql only return me records that phone is not
empty, but I need them both.
Any one has ideas what I can do ?
ARRAY_AGG() is never an empty array since there is always at least
a
single record that is going to be aggregated. In your case your
array
will have NULL values when phone numbers are missing but the
upper
bound will still show a positive number.
SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1
You would need to write a custom aggregation that ignores NULL and
thus could return an empty array if no valid phone numbers are
present.
The proper logic would be:
CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL
THEN
... END
You also likely want to use:
ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only
present a single time and ensure that an all-NULL situation
results
in a single element instead of one NULL for each input record.
Hopefully this help because I couldn't make heads nor tails as to
what
exactly your issue is. The lack of input data, the current
output,
and the desired output limits my ability to understand and help.
One last comment: I would generally avoid naming the output of an
ARRAY_AGG(column) the same name as the input column. I generally,
at
minimum, make the output column name plural to reflect the fact
that
it contains multiple values of whatever is stored in the source
column.
David J.
Hello,
I understand what is the problem, but I can't combine your example
with my
case.
I write my example in middle of the night and may be I miss to say
explain
much more about the structure:
mob servicephone month
132999 64 1
1321543434 66 1325 1
1321543434 67 1325 2
First record when phone is empty and mob is 132999 the number is
correct.
Second two records also is correct, but the phone is not empty so I
need
that filed phone, they have services and month when to start.
I'm unable to use phone_agg[1] IS NULL because sql return me error
that can't
use the phone_agg[1]
Im expect that result:
month | status | service | array_agg
-+-+-+---
{07} | {0}| {64} | {132999}
{08,07} | {0,0} | {66,67} | {1325,1325}
In the end I will have arrays for every phone which service will
use.
Hopefully this will help.
The first thing I did was break up the query into parts
0) data
1) aggregation
2) conditional return
Note I am using the ability for the CTE to provide column names so
the contained queries are not cluttered with AS alias constructs.
In order to make things simpler I avoid storing NULL in the phones
array and instead store N/A if the phone is missing. This lets me
use op ANY/ALL(array) later on to check on the contents of the
array. The result of that condition is called final_phones and it
either matches the mobs or the phones array depending on whether
all of the phone numbers are missing.
Another option is to use the masterphones array where the value
stored into the array is the phone number if present otherwise it
is
the mob number.
WITH data (mob, service, phone, mth) AS (
VALUES
('132999','64',NULL,'1')
, ('132999','65','12345','1')
, ('1321543434','66','1325','1')
, ('1321543434','67','1325','2')
)
, maingroup (mob, mobiles, services, phones, months, masterphones) AS
(
SELECT mob, array_agg(mob), array_agg(service),
array_agg(COALESCE(phone,'N/A')), array_agg(mth),
array_agg(COALESCE(phone, mob))
FROM
Re: [GENERAL] Need help with SQL query and finding NULL array_agg
On 2012-08-01 23:59, David Johnston wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Condor
Sent: Wednesday, August 01, 2012 4:16 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Need help with SQL query and finding NULL
array_agg
Hello ppl,
I have a problem with one sql query, can some one help me. My query
is:
SELECT array_agg(month) AS month, array_agg(status) AS status,
array_agg(service) AS service, case when
array_upper(array_agg(phone),
1) is not null THEN array_agg(phone) else array_agg(mobile) END FROM
bills
WHERE status 1 GROUP BY mobile
I try with simple query to identify did array_agg(phone) is empty or
null and if
is it to return me mobile field, if not empty to return me phone.
Mobile field
exist always, but phone may exists for that mobile may not exists.
One mobile can have few services like:
mob servicephone
1321543434 64
1321543434 66 1325
I try few thing but sql only return me records that phone is not
empty, but I
need them both.
Any one has ideas what I can do ?
ARRAY_AGG() is never an empty array since there is always at least a
single record that is going to be aggregated. In your case your
array
will have NULL values when phone numbers are missing but the upper
bound will still show a positive number.
SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1
You would need to write a custom aggregation that ignores NULL and
thus could return an empty array if no valid phone numbers are
present.
The proper logic would be:
CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL THEN
... END
You also likely want to use:
ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only
present a single time and ensure that an all-NULL situation results
in a single element instead of one NULL for each input record.
Hopefully this help because I couldn't make heads nor tails as to
what exactly your issue is. The lack of input data, the current
output, and the desired output limits my ability to understand and
help.
One last comment: I would generally avoid naming the output of an
ARRAY_AGG(column) the same name as the input column. I generally, at
minimum, make the output column name plural to reflect the fact that
it contains multiple values of whatever is stored in the source
column.
David J.
Hello,
I understand what is the problem, but I can't combine your example with
my case.
I write my example in middle of the night and may be I miss to say
explain much more
about the structure:
mob servicephone month
132999 64 1
1321543434 66 1325 1
1321543434 67 1325 2
First record when phone is empty and mob is 132999 the number is
correct.
Second two records also is correct, but the phone is not empty so I
need
that filed phone, they have services and month when to start.
I'm unable to use phone_agg[1] IS NULL because sql return me error
that can't
use the phone_agg[1]
Im expect that result:
month | status | service | array_agg
-+-+-+---
{07} | {0}| {64} | {132999}
{08,07} | {0,0} | {66,67} | {1325,1325}
In the end I will have arrays for every phone which service will use.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with SQL query and finding NULL array_agg
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Condor
Sent: Thursday, August 02, 2012 4:35 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Need help with SQL query and finding NULL
array_agg
On 2012-08-01 23:59, David Johnston wrote:
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Condor
Sent: Wednesday, August 01, 2012 4:16 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Need help with SQL query and finding NULL
array_agg
Hello ppl,
I have a problem with one sql query, can some one help me. My query
is:
SELECT array_agg(month) AS month, array_agg(status) AS status,
array_agg(service) AS service, case when
array_upper(array_agg(phone),
1) is not null THEN array_agg(phone) else array_agg(mobile) END FROM
bills WHERE status 1 GROUP BY mobile
I try with simple query to identify did array_agg(phone) is empty or
null and if is it to return me mobile field, if not empty to return
me phone.
Mobile field
exist always, but phone may exists for that mobile may not exists.
One mobile can have few services like:
mob servicephone
1321543434 64
1321543434 66 1325
I try few thing but sql only return me records that phone is not
empty, but I need them both.
Any one has ideas what I can do ?
ARRAY_AGG() is never an empty array since there is always at least a
single record that is going to be aggregated. In your case your array
will have NULL values when phone numbers are missing but the upper
bound will still show a positive number.
SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1
You would need to write a custom aggregation that ignores NULL and
thus could return an empty array if no valid phone numbers are
present.
The proper logic would be:
CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL
THEN
... END
You also likely want to use:
ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only
present a single time and ensure that an all-NULL situation results
in a single element instead of one NULL for each input record.
Hopefully this help because I couldn't make heads nor tails as to what
exactly your issue is. The lack of input data, the current output,
and the desired output limits my ability to understand and help.
One last comment: I would generally avoid naming the output of an
ARRAY_AGG(column) the same name as the input column. I generally, at
minimum, make the output column name plural to reflect the fact that
it contains multiple values of whatever is stored in the source
column.
David J.
Hello,
I understand what is the problem, but I can't combine your example with my
case.
I write my example in middle of the night and may be I miss to say explain
much more about the structure:
mob servicephone month
132999 64 1
1321543434 66 1325 1
1321543434 67 1325 2
First record when phone is empty and mob is 132999 the number is
correct.
Second two records also is correct, but the phone is not empty so I
need
that filed phone, they have services and month when to start.
I'm unable to use phone_agg[1] IS NULL because sql return me error
that can't
use the phone_agg[1]
Im expect that result:
month | status | service | array_agg
-+-+-+---
{07} | {0}| {64} | {132999}
{08,07} | {0,0} | {66,67} | {1325,1325}
In the end I will have arrays for every phone which service will use.
Hopefully this will help.
The first thing I did was break up the query into parts
0) data
1) aggregation
2) conditional return
Note I am using the ability for the CTE to provide column names so the
contained queries are not cluttered with AS alias constructs.
In order to make things simpler I avoid storing NULL in the phones array and
instead store N/A if the phone is missing. This lets me use op
ANY/ALL(array) later on to check on the contents of the array. The result of
that condition is called final_phones and it either matches the mobs or the
phones array depending on whether all of the phone numbers are missing.
Another option is to use the masterphones array where the value stored into
the array is the phone number if present otherwise it is the mob number.
WITH data (mob, service, phone, mth) AS (
VALUES
('132999','64',NULL,'1')
, ('132999','65','12345','1')
, ('1321543434','66','1325','1')
, ('1321543434','67','1325','2')
)
, maingroup (mob, mobiles, services, phones, months, masterphones) AS (
SELECT mob, array_agg(mob), array_agg(service),
array_agg(COALESCE(phone,'N/A')), array_agg
Re: [GENERAL] Need help with SQL query and finding NULL array_agg
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Condor Sent: Wednesday, August 01, 2012 4:16 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Need help with SQL query and finding NULL array_agg Hello ppl, I have a problem with one sql query, can some one help me. My query is: SELECT array_agg(month) AS month, array_agg(status) AS status, array_agg(service) AS service, case when array_upper(array_agg(phone), 1) is not null THEN array_agg(phone) else array_agg(mobile) END FROM bills WHERE status 1 GROUP BY mobile I try with simple query to identify did array_agg(phone) is empty or null and if is it to return me mobile field, if not empty to return me phone. Mobile field exist always, but phone may exists for that mobile may not exists. One mobile can have few services like: mob servicephone 1321543434 64 1321543434 66 1325 I try few thing but sql only return me records that phone is not empty, but I need them both. Any one has ideas what I can do ? ARRAY_AGG() is never an empty array since there is always at least a single record that is going to be aggregated. In your case your array will have NULL values when phone numbers are missing but the upper bound will still show a positive number. SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1 You would need to write a custom aggregation that ignores NULL and thus could return an empty array if no valid phone numbers are present. The proper logic would be: CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL THEN ... END You also likely want to use: ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only present a single time and ensure that an all-NULL situation results in a single element instead of one NULL for each input record. Hopefully this help because I couldn't make heads nor tails as to what exactly your issue is. The lack of input data, the current output, and the desired output limits my ability to understand and help. One last comment: I would generally avoid naming the output of an ARRAY_AGG(column) the same name as the input column. I generally, at minimum, make the output column name plural to reflect the fact that it contains multiple values of whatever is stored in the source column. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in transferring FP to Int64 DateTime
Hi Adrian, Thanks for the suggestion! We thought about that too, but concerned about the possible performance penalty trigger based replication would bring. If there is no other alternative, we will give your suggestion a try... Cheers, Benson - Original Message - From: Adrian Klaver adrian.kla...@gmail.com To: Benson Jin benson@troo.com Cc: pgsql-general@postgresql.org Sent: Wednesday, June 6, 2012 10:02:02 PM Subject: Re: [GENERAL] Need help in transferring FP to Int64 DateTime On 06/06/2012 09:20 AM, Benson Jin wrote: Hi All, I am sure this question has been asked before, however, I failed to find any related topics in the internet. We have a database about 100GB in size. It was started back in 7.x days and has been upgraded along the way to 9.0. Because of the historical reason, all timestamps are stored in FloatingPoint. To use pg9, we had to compile it with FP support instead of the default Int64. Some recent research shows that PG team will eventually dump FP support in favor of int64 in future, we figured we need to make the move to Int64 before the database gets even larger. The challenge we now face is how to convert the database fast enough, so that required down time is minimized. Searching through the internet for 2 days yields little fruit so far... Can someone please provide enlighten us? A thought. Dependent on sufficient disk space. Use one of the trigger based(non-binary) replication tools i.e. Slony, Bucardo, etc. Compile a Postgres 9.0 instance using the default of integer timestamps. Set up replication PG 9.0 FP -- PG 9.0 integer Once the standby is close to the primary, shut off access to the primary and let the standby completely catch up. Shut down the primary and promote the standby as the new primary. I have not actually done this, which is why I propose it as a thought. Hopefully, others will weigh in on the advisability of the above. Cheers, Bo Jin -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in transferring FP to Int64 DateTime
Hi Josh, Thanks for your prompt reply! Yes, you were right about the parameter. We have upgraded a few times in the past. It was not so painful, as our data as rather small. As our business grows, it becomes harder and harder to do any upgrade that requires downtime. There is never a good time to do this type of changes. However, a better time would be earlier rather than later, as our data size is growing steadily. The early we do it, the less downtime we will have to bear. I guess my question is how to do the change without downtime or with the least downtime? Is there a recommended steps we should take? Also, is there an installer for Windows 64bit with --disable-integer-datetimes available? Cheers, Benson - Original Message - From: Josh Kupershmidt schmi...@gmail.com To: Benson Jin benson@troo.com Cc: pgsql-general@postgresql.org Sent: Wednesday, June 6, 2012 8:01:35 PM Subject: Re: [GENERAL] Need help in transferring FP to Int64 DateTime On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin benson@troo.com wrote: I am sure this question has been asked before, however, I failed to find any related topics in the internet. We have a database about 100GB in size. It was started back in 7.x days and has been upgraded along the way to 9.0. Because of the historical reason, all timestamps are stored in FloatingPoint. To use pg9, we had to compile it with FP support instead of the default Int64. I take it you had to compile 9.0 with --disable-integer-datetimes because you wanted to use pg_upgrade to perform one of your database upgrades, yes? Otherwise you would have been able to just dump-and-restore into a 9.0 database with integer timestamps. At any rate, you must have performed a dump-and-restore at some point since your 7.x days, since pg_upgrade can handle databases only back to 8.3. Some recent research shows that PG team will eventually dump FP support in favor of int64 in future, we figured we need to make the move to Int64 before the database gets even larger. FWIW, I don't think there's any urgent push to get rid of float timestamps as a compile-time option, even though [1] claims the option is deprecated. Float timestamps were the default through 8.3, which isn't quite ancient history yet, and it seems likely there are many users in the same boat who would be upset about not being able to use pg_upgrade if we removed that option. Josh [1] http://www.postgresql.org/docs/current/static/datatype-datetime.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in transferring FP to Int64 DateTime
On 06/07/2012 08:29 AM, Benson Jin wrote: Hi Adrian, Thanks for the suggestion! We thought about that too, but concerned about the possible performance penalty trigger based replication would bring. If there is no other alternative, we will give your suggestion a try... AFAIK there is no direct way to do a binary fp timestamp -- integer timestamp conversion, otherwise pg_upgrade would handle it. To make that jump it needs to go through a text based representation. That leaves a dump/restore cycle or non-binary replication. Cheers, Benson -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in transferring FP to Int64 DateTime
On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin benson@troo.com wrote: I am sure this question has been asked before, however, I failed to find any related topics in the internet. We have a database about 100GB in size. It was started back in 7.x days and has been upgraded along the way to 9.0. Because of the historical reason, all timestamps are stored in FloatingPoint. To use pg9, we had to compile it with FP support instead of the default Int64. I take it you had to compile 9.0 with --disable-integer-datetimes because you wanted to use pg_upgrade to perform one of your database upgrades, yes? Otherwise you would have been able to just dump-and-restore into a 9.0 database with integer timestamps. At any rate, you must have performed a dump-and-restore at some point since your 7.x days, since pg_upgrade can handle databases only back to 8.3. Some recent research shows that PG team will eventually dump FP support in favor of int64 in future, we figured we need to make the move to Int64 before the database gets even larger. FWIW, I don't think there's any urgent push to get rid of float timestamps as a compile-time option, even though [1] claims the option is deprecated. Float timestamps were the default through 8.3, which isn't quite ancient history yet, and it seems likely there are many users in the same boat who would be upset about not being able to use pg_upgrade if we removed that option. Josh [1] http://www.postgresql.org/docs/current/static/datatype-datetime.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help in transferring FP to Int64 DateTime
On 06/06/2012 09:20 AM, Benson Jin wrote: Hi All, I am sure this question has been asked before, however, I failed to find any related topics in the internet. We have a database about 100GB in size. It was started back in 7.x days and has been upgraded along the way to 9.0. Because of the historical reason, all timestamps are stored in FloatingPoint. To use pg9, we had to compile it with FP support instead of the default Int64. Some recent research shows that PG team will eventually dump FP support in favor of int64 in future, we figured we need to make the move to Int64 before the database gets even larger. The challenge we now face is how to convert the database fast enough, so that required down time is minimized. Searching through the internet for 2 days yields little fruit so far... Can someone please provide enlighten us? A thought. Dependent on sufficient disk space. Use one of the trigger based(non-binary) replication tools i.e. Slony, Bucardo, etc. Compile a Postgres 9.0 instance using the default of integer timestamps. Set up replication PG 9.0 FP -- PG 9.0 integer Once the standby is close to the primary, shut off access to the primary and let the standby completely catch up. Shut down the primary and promote the standby as the new primary. I have not actually done this, which is why I propose it as a thought. Hopefully, others will weigh in on the advisability of the above. Cheers, Bo Jin -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help : PostgreSQL Installation on Windows 7 64 bit
Wendi/Craig I have seen an installation issue very similar to this. It has been happening on Windows 7 x86 systems. We are using postgres as the DB for our application and I have incorporated the postgres installer into our installer, we also use Bitrock. We are using postgres 8.4.4. It's been installing perfect fine. But just recently things started going wrong on only a few systems. After some research I found out that the postgres installer was throwing an error, that it can't find the postgres.conf file. When I looked I found the data folder empty. I also discovered that the postgres user was never installed. The one thing I have verified is that it has something to do with the fact that the Windows account that I was running the install from has a space in it. IE. MSI Test This is still happening in the most recent installer, postgres 9.1.2.1. We have been installing this on both Windows 7 x86 and x64 systems, Home, Pro and Ultimate. The Professional version is in a network setting connected to an internal netork. Shawn M Eckley Software Engineer Stonewedge Corporation 240 Andover st. Wilmington, MA 01887 978-203-0642 Ext. 113 seck...@stonewedge.netmailto:seck...@stonewedge.net This electronic message is intended only for the use of the individual or entity named above and may contain information which is privileged and/or confidential. If you are not the intended recipient, be aware that any disclosure, copying, distribution, dissemination or use of the contents of this message is prohibited. If you have received this message in error, please notify the sender immediately.
[GENERAL] Re: [GENERAL] Need Help Installing Dblink…(Desperately…)
On Sat, Nov 12, 2011 at 1:02 PM, Jerry Levan jerry.le...@gmail.com wrote: I think I have somehow botched my installation of 9.1.1 on Mac OSX 10.7.2 I have a mac running 9.1.1. I then built dblink from the contrib directory and I did not see an install option in the make file. The Makefile should be including the global did you try a 'make install' ? I tried copying the dblink.so file to the postgresql library directory but i cannot figure out how to install the definitions.. google: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink/Attic/README.dblink?rev=1.12.4.1 Any clues would be helpful. Thanks Jerry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Need Help Installing Dblink…(Desperately…)
On Nov 12, 2011, at 3:34 PM, Scott Mead wrote: On Sat, Nov 12, 2011 at 1:02 PM, Jerry Levan jerry.le...@gmail.com wrote: I think I have somehow botched my installation of 9.1.1 on Mac OSX 10.7.2 I have a mac running 9.1.1. I then built dblink from the contrib directory and I did not see an install option in the make file. The Makefile should be including the global did you try a 'make install' ? I went back and did the make install from the dblink directory it installed stuff in the share directory but invoking dblink functions failed with an unknown definition. I invoked the create extension command but it did no good... I went back to a previous version and loaded the older dblink.sql file and it now appears to be working…at least the dblink function calls that my sql uses is appearently working. I tried copying the dblink.so file to the postgresql library directory but i cannot figure out how to install the definitions.. google: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink/Attic/README.dblink?rev=1.12.4.1 This is pre-extension stuff… Any clues would be helpful. Thanks Jerry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help Installing Dblink…(Desperately…)
On 11/12/11 10:02 AM, Jerry Levan wrote: I have a mac running 9.1.1. I then built dblink from the contrib directory and I did not see an install option in the make file. did you build this 9.1.1 or is this a standard distribution? if you built it, did you install it with `make install` ? when you built it, it should have built the contrib automatically, and that should have been installed to the $SHAREDIR/contrib directory (often in /usr/share/pgsql91/ or similar) To install a contributed extension that was built with postgres, simply execute the SQL command... create extension dblink; -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: [GENERAL] Need Help Installing Dblink…(Desperately…)
Jerry Levan jerry.le...@gmail.com writes: On Nov 12, 2011, at 3:34 PM, Scott Mead wrote: The Makefile should be including the global did you try a 'make install' ? I went back and did the make install from the dblink directory it installed stuff in the share directory but invoking dblink functions failed with an unknown definition. I invoked the create extension command but it did no good... Vague handwaving like that is an excellent way to guarantee that nobody can help you, because we can't figure out what you did wrong (or, maybe, what the code did wrong) based on this. We need to see exactly what commands you gave and what results you got. Please read http://wiki.postgresql.org/wiki/Guide_to_reporting_problems (In general, though, in PG 9.1 you do not source contrib SQL scripts directly. CREATE EXTENSION is the way to install a contrib module into a database.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Re: [GENERAL] Need Help Installing Dblink…(Desperately…)
On Nov 12, 2011, at 5:49 PM, Tom Lane wrote: Jerry Levan jerry.le...@gmail.com writes: On Nov 12, 2011, at 3:34 PM, Scott Mead wrote: The Makefile should be including the global did you try a 'make install' ? I went back and did the make install from the dblink directory it installed stuff in the share directory but invoking dblink functions failed with an unknown definition. I invoked the create extension command but it did no good... Vague handwaving like that is an excellent way to guarantee that nobody can help you, because we can't figure out what you did wrong (or, maybe, what the code did wrong) based on this. We need to see exactly what commands you gave and what results you got. Please read http://wiki.postgresql.org/wiki/Guide_to_reporting_problems (In general, though, in PG 9.1 you do not source contrib SQL scripts directly. CREATE EXTENSION is the way to install a contrib module into a database.) regards, tom lane You are, as usual, correct in your criticism. My only excuse is that I hit the panic button when things got out of control ;( My first bad step was moving the source folder after I built the main database server, clients, etc and then trying to build the dblink extension. It appears that some *.h files had been linked via absolute paths causing the compilation of the dblink package build to fail. Moving the source folder back to its original location fixed that problem. The second difficulty was that I did not understand about extensions and how to install and enable the rascals. After I built the documentation and did some reading about extensions I was able to (I hope) properly install the dblink package. My sql codes that use dblink now act the same in 9.1.1 as they did in 9.0.x I will find out for sure tomorrow when I upgrade two more macs… Jerry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help With a A Simple Query That's Not So Simple
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Thoen
Sent: Monday, October 31, 2011 6:51 PM
To: Postgrresql
Subject: [GENERAL] Need Help With a A Simple Query That's Not So Simple
I think this should be easy, but I can't seem to put the SQL together
correctly and would appreciate any help. (I'm using Pg 8.4 in CentOS 5.5, if
that matters.)
I have a table of Farms and a table of crops in a 1:M relationship of Farms
: Crops. There are lots of different crops to choose form but for now I'm
only interested in two crops; corn and soybeans. Some farms grow only corn
and some grow only soybeans, and some grow both. What I'd like to know is,
which Farms and how many are growing only corn, which and how many are
growing soybeans and which and how many are growing both? I can easily get
all the corn growers with:
SELECT a.*
FROM farms a
JOIN crops b
ON a.farm_id=b.farm_id
WHERE crop_cd='0041'
I can do the same with soybeans (crop_cd= '0081') and then I could subtract
the sum of these from the total of all farms that grow either corn or
soybeans to get the number of farms growing both, but having to do all those
queries sounds very time consuming and inefficient. Is there a better way to
get the farm counts or data by categories like farms growing only corn,
farms growing only soybeans, farms growing both? I'm also interested in
possibly expanding to a general case where I could select more than two
crops. and get counts of the permutations.
Here's a sketch of the relevant pieces of the data base.
Tables:
farms crops
=== ===
farm_id bigint (pkey) crop_id (pkey)
type farm_id foreign key to farms
size crop_cd 0041 = corn 0081=soybeans
... year
...
Any help would be much appreciated.
TIA,
- Bill Thoen
---
General Idea:
WITH crop_one AS (
SELECT farm_id, crop_cd AS crop_one_cd ...
), crop_two AS (
SELECT farm_id, crop_cd AS crop_two_cd
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
Records with NULL for crop_one_cd only grow crop 2, records with NULL for
crop_two_cd only grow crop 1, records where neither field is NULL grow
both.
Not sure regarding the general case. You likely want to use ARRAY_AGG to
get a result like:
Farm_id_100, { 'CROP_CD_1', 'CROP_CD_2' }
You could then probably get a query to output something like:
(crop_id, farms_exclusive, farms_shared, farms_without)
Where each of the farms_ columns is an array of farm_ids that match the
particular conditional
= ALL (exclusive); != ALL = ANY (shared); != ANY (without)
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help With a A Simple Query That's Not So Simple
On 10/31/2011 5:05 PM, David Johnston wrote:
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bill Thoen
Sent: Monday, October 31, 2011 6:51 PM
To: Postgrresql
Subject: [GENERAL] Need Help With a A Simple Query That's Not So Simple
[...]
What I'd like to know is,
which Farms and how many are growing only corn, which and how many are
growing soybeans and which and how many are growing both?
[...]
Is there a better way to
get the farm counts or data by categories like farms growing only corn,
farms growing only soybeans, farms growing both? I'm also interested in
possibly expanding to a general case where I could select more than two
crops. and get counts of the permutations.
[...]
---
General Idea:
WITH crop_one AS (
SELECT farm_id, crop_cd AS crop_one_cd ...
), crop_two AS (
SELECT farm_id, crop_cd AS crop_two_cd
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
Records with NULL for crop_one_cd only grow crop 2, records with NULL for
crop_two_cd only grow crop 1, records where neither field is NULL grow
both.
Not sure regarding the general case. You likely want to use ARRAY_AGG to
get a result like:
Farm_id_100, { 'CROP_CD_1', 'CROP_CD_2' }
You could then probably get a query to output something like:
(crop_id, farms_exclusive, farms_shared, farms_without)
Where each of the farms_ columns is an array of farm_ids that match the
particular conditional
= ALL (exclusive); != ALL = ANY (shared); != ANY (without)
David J.
Thanks David! That worked great! When I filled in the the query from the
general idea in your example above like so:
WITH crop_one AS (
SELECT farm_id, crop_cd AS corn FROM gfc_inilmoidia_2007 WHERE
crop_cd ='0041'
), crop_two AS (
SELECT farm_id, crop_cd AS soybeans FROM gfc_inilmoidia_2007
WHERE crop_cd = '0081'
)
SELECT *
FROM crop_one
FULL OUTER JOIN crop_two USING (farm_id)
;
It produced the following (which is essentially the base of what I'm
looking for):
farm_id | corn | soybeans
-+--+--
1473 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1474 | 0041 | 0081
1475 | 0041 |
1475 | 0041 |
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1476 | 0041 | 0081
1477 | 0041 |
1478 | 0041 | 0081
1479 | 0041 |
1480 | | 0081
1480 | | 0081
Thanks so much for the quick reply. You've also just opened up a whole
new area of query possibilities for me of which I wasn't aware
- Bill Thoen
Re: [GENERAL] Need Help : PostgreSQL Installation on Windows 7 64 bit
On 10/19/2011 01:52 PM, Wendi Adrian wrote: I installed Windows 7 Professional on my workstation which connect with office network. OK, it's on the network, that's fine. What I need to know is whether it's part of a Windows domain, or whether it's running in standalone workstation mode. This information is shown in the control panel, on the initial system control panel screen. It shows your computer name, whether it's on a domain or a workgroup, and the name of the domain or workgroup. I tried to reinstall after disconnect from the network but still failed. So I am assuming that PostgreSQl cannot be installed on workstation (because it does successfully installed on my laptop). PostgreSQL should install fine on any workstation, network connected or not. There is something different about your workstation, perhaps Group Policy rules, user account setup, etc, that is causing the installation to fail. To help you, I NEED YOU TO COLLECT THE INFORMATION I REQUESTED ABOUT THE INSTALLATION ERROR, INCLUDING THE INSTALLER LOGS. Please READ THE LINK I SENT TO YOU, which contains instructions on how to collect that information. I repeat: Follow these instructions and do what they say, then post the information you collected as a result, or I cannot help you: http://wiki.postgresql.org/wiki/Troubleshooting_Installation Would you please to let me know what is the effect of language for PostgreSQL installation and do you have solution for this? If I had a solution, I wouldn't have to ask you for more information. I haven't seen another report of this problem, so there's something different about your computer. The trick will be to figure out what it is, and why it's causing a problem for PostgreSQL. As for language: I mean the language WINDOWS is in, not the language PostgreSQL is in. Does your computer use English for menu items and other user interface? Or is it in a different language? If a different language, which language? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help : PostgreSQL Installation on Windows 7 64 bit
On 10/19/2011 09:21 AM, Wendi Adrian wrote: Do anyone can help me to solve this problem? Or, PostgreSQL does not support Windows 7 Professional 64 bit? PostgreSQL does support Windows 7 Pro 64-bit; that's one of the OSes I use and it works fine. It would be helpful to know which language your Windows install is in. Please also collect the information listed in this wiki page: http://wiki.postgresql.org/wiki/Troubleshooting_Installation It's also important to specify whether your computer is on a domain or is standalone. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Help : PostgreSQL Installation on Windows 7 64 bit
Hi Craig, thanks for your response. I installed Windows 7 Professional on my workstation which connect with office network. I tried to reinstall after disconnect from the network but still failed. So I am assuming that PostgreSQl cannot be installed on workstation (because it does successfully installed on my laptop). Would you please to let me know what is the effect of language for PostgreSQL installation and do you have solution for this? Please advise. Thanks and regards, Wendi From: Craig Ringer ring...@ringerc.id.au To: Wendi Adrian wendiadrians...@yahoo.com Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Wednesday, October 19, 2011 12:28 PM Subject: Re: [GENERAL] Need Help : PostgreSQL Installation on Windows 7 64 bit On 10/19/2011 09:21 AM, Wendi Adrian wrote: Do anyone can help me to solve this problem? Or, PostgreSQL does not support Windows 7 Professional 64 bit? PostgreSQL does support Windows 7 Pro 64-bit; that's one of the OSes I use and it works fine. It would be helpful to know which language your Windows install is in. Please also collect the information listed in this wiki page: http://wiki.postgresql.org/wiki/Troubleshooting_Installation It's also important to specify whether your computer is on a domain or is standalone. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.
On 13/09/11 10:54, Reid Thompson wrote: Could someone point me in the right direction.. Thanks - reid Given the example data, how do I write a query that will give me the resultset: [snip] I.E. for each distinct val, return the record with the most recent date. Isn't it something simple like this? SELECT val, max(date) GROUP BY val; -Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.
I would think to do it like SELECT DISTINCT ON (val) val,date ORDER BY date DESC, val ASC I haven't tested this, but it's similar to things I've done recently, and I'm pretty sure this will do what you want. On Mon, Sep 12, 2011 at 8:54 PM, Reid Thompson jreidthomp...@gmail.com wrote: Could someone point me in the right direction.. Thanks - reid Given the example data, how do I write a query that will give me the resultset: 1 2011-01-01 2 2011-01-06 3 2011-01-05 4 2011-01-09 5 2011-01-05 6 2011-01-08 I.E. for each distinct val, return the record with the most recent date. ex data val date 1 2011-01-01 2 2011-01-02 3 2011-01-03 4 2011-01-04 5 2011-01-05 5 2011-01-01 4 2011-01-02 6 2011-01-03 4 2011-01-04 3 2011-01-05 2 2011-01-06 4 2011-01-07 6 2011-01-08 4 2011-01-09 5 2011-01-01 2 2011-01-02 4 2011-01-03 2 2011-01-04 1 2011-01-01 2 2011-01-02 3 2011-01-03 4 2011-01-04 3 2011-01-05 1 2011-01-01 2 2011-01-02 3 2011-01-03 4 2011-01-04 5 2011-01-01 --- $ cat sampledata|sort -k1,2 1 2011-01-01 1 2011-01-01 1 2011-01-01 2 2011-01-02 2 2011-01-02 2 2011-01-02 2 2011-01-02 2 2011-01-04 2 2011-01-06 3 2011-01-03 3 2011-01-03 3 2011-01-03 3 2011-01-05 3 2011-01-05 4 2011-01-02 4 2011-01-03 4 2011-01-04 4 2011-01-04 4 2011-01-04 4 2011-01-04 4 2011-01-07 4 2011-01-09 5 2011-01-01 5 2011-01-01 5 2011-01-01 5 2011-01-05 6 2011-01-03 6 2011-01-08 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- -BEGIN GEEK CODE BLOCK- Version: 3.12 GIT d+ s: a-- C(++) UL+++ P$ L+++ E- W+ N o? K w--- O- M- V? PS+++ PE(-) Y+ PGP-+++ t+++ 5+ X(+) R+ tv b+ DI++ D+ G+ e* h! !r y** --END GEEK CODE BLOCK-- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help with dropping a view please
I created a new view called clients2 with the same member tables as clients and it works I can query it. But due to the many hardcoded places that use clients view, I have to have clients view. So I tried to drop clients view but cannot #DROP view clients; ERROR: missing chunk number 0 for toast value 16538 I've no idea if this will work, but have you tried renaming the clients view and renaming clients2 to clients to replace it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
