[GENERAL] Correct update statement
Hi Please help, we are using postgreSQL 9.2.4. I need to update over 9000 rows. See the query below: A table called contact has got *addr_id *field as null which is incorrect. So now I need to update contact table for each account (cus_acno is in cus table) where contact_addr_id is null. For example using the first of the results below: I need take set addr_id (in contact table) to 187479 where cus_acno = 243492 and con_id = 119360 Example: select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null; cus_acno | con_id | addr_id --++- 243492 | 119360 | 187479 393701 | 119824 | 458532 388538 | 118413 | 453178 Thanks 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.
[GENERAL] TODO: Expose parser support for decoding unicode escape literals to user
Hi all I just noticed a Stack Overflow question (http://stackoverflow.com/q/20124393/398670) where someone's asking how to decode '\u` style escapes *stored in database text fields* into properly encoded text strings. The parser supports this for escape-strings, and you can write E'\u011B' to get 'ě' because of http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html. I don't see this exposed in a way that users can call directly, though. 'decode(bytea, text)' has the 'escape' input, but it expects octal. It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the work, but that's downright awful. Am I missing something obvious, or is this something that'd be a good new-developer TODO? -- Craig Ringer 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] Correct update statement
update contacts set addr_id=b.addr_id from (select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null) b where contacts.con_id=b.con_id On 05/15/2014 09:19 AM, Khangelani Gama wrote: Hi Please help, we are using postgreSQL 9.2.4. I need to update over 9000 rows. See the query below: A table called contact has got addr_id field as null which is incorrect. So now I need to update contact table for each account (cus_acno is in cus table) where contact_addr_id is null. For example using the first of the results below: I need take set addr_id (in contact table) to 187479 where cus_acno = 243492 and con_id = 119360 Example: select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null; cus_acno | con_id | addr_id --++- 243492 | 119360 | 187479 393701 | 119824 | 458532 388538 | 118413 | 453178 Thanks 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.
Re: [GENERAL] Correct update statement
Thank very much *From:* pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] *On Behalf Of *Sim Zacks *Sent:* Thursday, May 15, 2014 10:42 AM *To:* pgsql-general@postgresql.org *Subject:* Re: [GENERAL] Correct update statement update contacts set addr_id=b.addr_id from (select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null) b where contacts.con_id=b.con_id On 05/15/2014 09:19 AM, Khangelani Gama wrote: Hi Please help, we are using postgreSQL 9.2.4. I need to update over 9000 rows. See the query below: A table called contact has got *addr_id *field as null which is incorrect. So now I need to update contact table for each account (cus_acno is in cus table) where contact_addr_id is null. For example using the first of the results below: I need take set addr_id (in contact table) to 187479 where cus_acno = 243492 and con_id = 119360 Example: select distinct(cus_acno), contact.con_id, address.addr_id from address join person using (addr_id) join cus using (per_id) join link_contact using (cus_acno) join contact using (con_id) where contact.addr_id is null; cus_acno | con_id | addr_id --++- 243492 | 119360 | 187479 393701 | 119824 | 458532 388538 | 118413 | 453178 Thanks 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. 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.
Re: [GENERAL] are analyze statistics synced with replication?
If you don't do read queries on the slave than it will not have hot data/pages/rows/tables/indexes in ram like the primary ? (it smoked weed and was happy doing nothing so it was happy, but when responsibility came (being promoted to master) it failed hard) On Thu, May 15, 2014 at 6:46 AM, Kevin Goess kgo...@bepress.com wrote: We have a master/slave setup with replication. Today we failed over to the slave and saw disk I/O go through the roof. Are the pg_statistic statistics synced along with streaming replication? Are you expected to have to do a vacuum analyze after failing over? That's what we're trying now to see if it makes a difference. Our next step will be to fall back to the first host and see where this one went wrong (society? lax discipline at home? the wrong sort of friends?)
Re: [GENERAL] TODO: Expose parser support for decoding unicode escape literals to user
On 05/15/2014 01:31 AM, Craig Ringer wrote: Hi all I just noticed a Stack Overflow question (http://stackoverflow.com/q/20124393/398670) where someone's asking how to decode '\u` style escapes *stored in database text fields* into properly encoded text strings. The parser supports this for escape-strings, and you can write E'\u011B' to get 'ě' because of http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html. I don't see this exposed in a way that users can call directly, though. 'decode(bytea, text)' has the 'escape' input, but it expects octal. It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the work, but that's downright awful. Am I missing something obvious, or is this something that'd be a good new-developer TODO? Not sure if this is what you want?: test= SELECT quote_literal(E'test \u011B'); quote_literal --- 'test ě' -- 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
[GENERAL] Re: TODO: Expose parser support for decoding unicode escape literals to user
Adrian Klaver-4 wrote
On 05/15/2014 01:31 AM, Craig Ringer wrote:
Hi all
I just noticed a Stack Overflow question
(http://stackoverflow.com/q/20124393/398670) where someone's asking how
to decode '\u` style escapes *stored in database text fields* into
properly encoded text strings.
The parser supports this for escape-strings, and you can write E'\u011B'
to get 'ě' because of
http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html.
I don't see this exposed in a way that users can call directly, though.
'decode(bytea, text)' has the 'escape' input, but it expects octal.
It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the
work, but that's downright awful.
Am I missing something obvious, or is this something that'd be a good
new-developer TODO?
Not sure if this is what you want?:
test= SELECT quote_literal(E'test \u011B');
quote_literal
---
'test ě'
Except the data is already in the database and there is no way to put an E
in front of a column name and cause PostgreSQL to process the escapes
embedded in the column's value in the same way it processes a literal.
WITH src (txt) AS ( VALUES ('A \u011B C') )
SELECT txt FROM src;
Hence the need for a function to perform the same process that the parser
performs when dealing with literals.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/TODO-Expose-parser-support-for-decoding-unicode-escape-literals-to-user-tp5804012p5804042.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] Re: TODO: Expose parser support for decoding unicode escape literals to user
On 05/15/2014 07:13 AM, David G Johnston wrote:
Adrian Klaver-4 wrote
On 05/15/2014 01:31 AM, Craig Ringer wrote:
Hi all
I just noticed a Stack Overflow question
(http://stackoverflow.com/q/20124393/398670) where someone's asking how
to decode '\u` style escapes *stored in database text fields* into
properly encoded text strings.
The parser supports this for escape-strings, and you can write E'\u011B'
to get 'ě' because of
http://postgresql.1045698.n5.nabble.com/Unicode-escapes-in-literals-td1992313.html.
I don't see this exposed in a way that users can call directly, though.
'decode(bytea, text)' has the 'escape' input, but it expects octal.
It's possible to use PL/PgSQL's 'EXECUTE' to use the parser to do the
work, but that's downright awful.
Am I missing something obvious, or is this something that'd be a good
new-developer TODO?
Not sure if this is what you want?:
test= SELECT quote_literal(E'test \u011B');
quote_literal
---
'test ě'
Except the data is already in the database and there is no way to put an E
in front of a column name and cause PostgreSQL to process the escapes
embedded in the column's value in the same way it processes a literal.
Yea, that is a problem.
WITH src (txt) AS ( VALUES ('A \u011B C') )
SELECT txt FROM src;
Hence the need for a function to perform the same process that the parser
performs when dealing with literals.
David J.
--
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] are analyze statistics synced with replication?
On Thu, May 15, 2014 at 6:39 AM, Dorian Hoxha dorian.ho...@gmail.comwrote: If you don't do read queries on the slave than it will not have hot data/pages/rows/tables/indexes in ram like the primary ? Yeah, that was the first thing we noticed, the cacti graph shows it took two hours for the page cache to fill up our 64GB of RAM, but I/O didn't stop sucking after that.
Re: [GENERAL] Backups over slave instead master?
On Thu, May 1, 2014 at 12:39:44PM -0700, bricklen wrote:
On Thu, May 1, 2014 at 8:54 AM, Shaun Thomas stho...@optionshouse.com wrote:
On 05/01/2014 10:31 AM, Edson Richter wrote:
I'm wondering if would be possible to execute these backups in the
slave
server instead, so I can avoid the overhead of backups on master
system?
If you're on PostgreSQL 9.3, you can backup the slave server safely. If
not, you'll need to run this command on the master system first:
SELECT pg_start_backup('some-label');
After the backup is done, run this on the master server:
SELECT pg_stop_backup();
Or alternatively, if backup = pg_dump, then backups can taken from the slave
too. Have a look at pg_xlog_replay_pause() + pg_dump +
pg_xlog_replay_resume().
http://www.postgresql.org/docs/current/static/functions-admin.html#
FUNCTIONS-RECOVERY-CONTROL-TABLE
Uh, what is the pause for? So the transaction will not be cancelled?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Somebody hijacked @psql ?
Looks like someone hijacked the tweeter account @psql, and is posting what looks like spam on http://forum.postgresql.org.pl, and then spaming those posts with the tweeter account. I just wanted to let the person in charge know. Cheers, -- Martín Marqués 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] Backups over slave instead master?
On Thu, May 15, 2014 at 1:55 PM, Bruce Momjian br...@momjian.us wrote: On Thu, May 1, 2014 at 12:39:44PM -0700, bricklen wrote: Or alternatively, if backup = pg_dump, then backups can taken from the slave too. Have a look at pg_xlog_replay_pause() + pg_dump + pg_xlog_replay_resume(). http://www.postgresql.org/docs/current/static/functions-admin.html# FUNCTIONS-RECOVERY-CONTROL-TABLE Uh, what is the pause for? So the transaction will not be cancelled? Yes.
