Getting unexpected results from regexp_replace
I freely admit this may be my problem. Writing regular expression patterns is more an art than a skill. However, I am getting an unexpected result from regex_replace(). I have a table that is partially defined as follows (names and email addresses hidden for privacy): user_name user_email “A" “B” “b(x)" “C” "ct(home)" “D" “E" "ae(home)” The second entry is an email address - b - followed by the name of an individual (x) in parentheses. The email address for C and E have the word “home” in parentheses appended to the email address. I want to delete the parenthetical expression including the parentheses for all email addresses. I also have a column (not shown) called email_list that contains a comma separated list of all email addresses associated with each name or NULL if there is no list. I create a table: CREATE TABLE "households_with_email" AS SELECT user_name, regexp_replace(user_email, E'\(.*\)', '') AS user_email, family_list, street_address, city, state, zip, phone_list, email_list FROM "household_data" WHERE email_list != ‘'; I expected the regex_replace to the parenthetical text with the null character. Instead, it replaces the whole string in user_email with the null string: user_name user_email "Rodriguez” "" "Armstrong" "" "Bauer" "" "Berst" "" "Berst” "" I realize there may be some characteristic such as greedy matching that is causing this result, but if so, I don’t see how. The pattern indicates first find the ‘(‘ character, then match all characters until a ‘)’ character arrives. Those characters, including the parentheses should then be replaced with the null string. Or am I misinterpreting the pattern? Dan
Re: Getting unexpected results from regexp_replace
Dan Nessett writes: > SELECT user_name, regexp_replace(user_email, E'\(.*\)', '') AS user_email, > family_list, street_address, city, state, zip, phone_list, email_list > FROM "household_data" > WHERE email_list != ‘'; Because you used E'...', the backslashes are eaten by the string literal parser. So the pattern seen by regexp_replace() is just '(.*)', in which the parens are capturing parens not literal characters. Thus it matches the whole string. Personally I'd leave off the E, but if you must use it then double the backslashes. regards, tom lane
Re: Getting unexpected results from regexp_replace
Thanks. Doubling the backslashes did the trick. I tried to use the original expression without the E, but postgres threw an error and said to use the “E” version of the pattern. Dan > On Feb 21, 2021, at 8:50 AM, Tom Lane wrote: > > Dan Nessett writes: >> SELECT user_name, regexp_replace(user_email, E'\(.*\)', '') AS user_email, >> family_list, street_address, city, state, zip, phone_list, email_list >> FROM "household_data" >> WHERE email_list != ‘'; > > Because you used E'...', the backslashes are eaten by the string literal > parser. So the pattern seen by regexp_replace() is just '(.*)', in > which the parens are capturing parens not literal characters. Thus it > matches the whole string. > > Personally I'd leave off the E, but if you must use it then double the > backslashes. > > regards, tom lane > >
Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
No issues for us. We have used a low sample rate of 1% or so and gotten some very useful data. Particularly with logging nested statements so we can profile stored procs more easily than adding a large number of raise notice statements.
Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
On 2021-02-21 10:14:04 -0700, Michael Lewis wrote: > No issues for us. We have used a low sample rate of 1% or so and gotten some > very useful data. Oh, somehow I never noticed the auto_explain.sample_rate parameter in the docs. Good to know. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Script checking to see what database it's connected to
Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I sometimes forget to specify the database name, and so it fails. Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the magic sauce. This is what I've tried so far: postgres=# \if :DBNAME == postgres unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == postgres unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if :DBNAME == 'postgres' unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == 'postgres' unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# -- Angular momentum makes the world go 'round.
Re: Script checking to see what database it's connected to
On Mon, Feb 22, 2021 at 7:19 AM Ron wrote: > > Thus, I want to add a bit to the top of the script, something like this: > > \if :DBNAME = postgres > echo "must not run in postgres" > exit > \endif > > However, I can't seem to find the magic sauce. You have to use a dedicated variable. Something like SELECT :'DBNAME' = 'postgres' AS is_postgres \gset \if :is_postgres [...]
Re: Script checking to see what database it's connected to
On 2/21/21 4:18 PM, Ron wrote: Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I sometimes forget to specify the database name, and so it fails. Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the magic sauce. This is what I've tried so far: postgres=# \if :DBNAME == postgres unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == postgres unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if :DBNAME == 'postgres' unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == 'postgres' unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# Take it up a notch? Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/
How to determine server's own IP address? inet_server_addr not working
The documentation says that inet_server_addr() does this, but on our servers it is returning nothing.
Re: How to determine server's own IP address? inet_server_addr not working
On Sun, Feb 21, 2021 at 4:38 PM Guyren Howe wrote: > The documentation says that inet_server_addr() does this, but on our > servers it is returning nothing. > "Returns the IP address on which the server accepted the current connection, or NULL if the current connection is via a Unix-domain socket." You will need to demonstrate that the connection you are checking from isn't being made via a Unix-domain socket. David J.
Re: Script checking to see what database it's connected to
On 2/21/21 5:26 PM, Rob Sargent wrote: On 2/21/21 4:18 PM, Ron wrote: Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I sometimes forget to specify the database name, and so it fails. Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the magic sauce. This is what I've tried so far: postgres=# \if :DBNAME == postgres unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == postgres unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if :DBNAME == 'postgres' unrecognized value "postgres == postgres" for "\if expression": Boolean expected postgres@# postgres=# \if ':DBNAME' == 'postgres' unrecognized value ":DBNAME == postgres" for "\if expression": Boolean expected postgres@# Take it up a notch? Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/ I thought of that, yet so earnestly want avoid Yet Another Tiny Script. -- Angular momentum makes the world go 'round.
Re: Script checking to see what database it's connected to
Take it up a notch? Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/ I thought of that, yet so earnestly want avoid Yet Another Tiny Script. Isn't it a toss-up with putting the check in every sql script? Or make it /really/ fancy: use proper arg parsing; check for existence of the sql script; add a usage function; split stdout/stderr... No end of fun.
Re: Script checking to see what database it's connected to
On 2/21/21 5:26 PM, Julien Rouhaud wrote: On Mon, Feb 22, 2021 at 7:19 AM Ron wrote: Thus, I want to add a bit to the top of the script, something like this: \if :DBNAME = postgres echo "must not run in postgres" exit \endif However, I can't seem to find the magic sauce. You have to use a dedicated variable. Something like SELECT :'DBNAME' = 'postgres' AS is_postgres \gset That works... \if :is_postgres [...] That *almost* works, My script: $ cat test_pg.sql SELECT :'DBNAME' = 'postgres' AS is_postgres \gset \echo :is_postgres \if :is_postgres \echo 'connected to postgres' exit \else \echo 'not connected to postgres' \endif The output: $ psql12 -f test_pg.sql t connected to postgres got here psql:test_pg.sql:15: ERROR: syntax error at or near "exit" LINE 1: exit -- Angular momentum makes the world go 'round.
Re: Script checking to see what database it's connected to
On 2/21/21 6:49 PM, Rob Sargent wrote: Take it up a notch? Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/ I thought of that, yet so earnestly want avoid Yet Another Tiny Script. Isn't it a toss-up with putting the check in every sql script? I was hoping to "\include" that at the top of each script, since doing that once is easier than remembering to use the script each time. Or make it /really/ fancy: use proper arg parsing; check for existence of the sql script; add a usage function; split stdout/stderr... No end of fun. -- Angular momentum makes the world go 'round.
Re: Script checking to see what database it's connected to
Rob Sargent writes: >>> >>> Take it up a notch? Write a script which takes the dbname and the >>> script name: >>> >>> /pcode/ >>> >>> #!/bin/bash -e >>> if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi >>> dbn=$1; shift; >>> sql=$1; shift; >>> psql --dbname $dbn --file $sql >>> >>> /pcode/ >> >> I thought of that, yet so earnestly want avoid Yet Another Tiny Script. >> > Isn't it a toss-up with putting the check in every sql script? > Or make it /really/ fancy: use proper arg parsing; check for existence > of the sql script; add a usage function; split stdout/stderr... No end > of fun. that would be my approach. A general purpose 'launcher' script that does argument checking, logging and reporting. Stick it in your bin directory and then call that instead of psql directly. Now all your SQL scripts are just DDL/DML statements. Nice thing is you can do it in whatever scripting language your most comfortable with - bash, perl, ruby, python, whatever and it is available for whatever project your working on. -- Tim Cross
Re: Script checking to see what database it's connected to
On Mon, Feb 22, 2021 at 9:00 AM Ron wrote: > > On 2/21/21 5:26 PM, Julien Rouhaud wrote: > > On Mon, Feb 22, 2021 at 7:19 AM Ron wrote: > > Thus, I want to add a bit to the top of the script, something like this: > > \if :DBNAME = postgres > echo "must not run in postgres" > exit > \endif > > However, I can't seem to find the magic sauce. > > You have to use a dedicated variable. Something like > > SELECT :'DBNAME' = 'postgres' AS is_postgres \gset > > > That works... > > \if :is_postgres > [...] > > > That almost works, > > My script: > $ cat test_pg.sql > SELECT :'DBNAME' = 'postgres' AS is_postgres \gset > \echo :is_postgres > \if :is_postgres > \echo 'connected to postgres' > exit > \else > \echo 'not connected to postgres' > \endif > > The output: > $ psql12 -f test_pg.sql > t > connected to postgres > got here > psql:test_pg.sql:15: ERROR: syntax error at or near "exit" > LINE 1: exit Well, the supported commands did work. You should probably look at https://www.postgresql.org/docs/current/app-psql.html, you'd see that "exit" is not a supported command and you should instead use \q[uit]. I recommend looking at the semantics of \quit though, given your next message mentioning \include.
Re: Script checking to see what database it's connected to
On 2/21/21 7:19 PM, Julien Rouhaud wrote: [snip] Well, the supported commands did work. You should probably look at https://www.postgresql.org/docs/current/app-psql.html, you'd see that "exit" is not a supported command and you should instead use \q[uit]. I recommend looking at the semantics of \quit though, given your next message mentioning \include. I assumed "exit" is a valid command because I do this so often in bash... :( -- Angular momentum makes the world go 'round.
Re: How to determine server's own IP address? inet_server_addr not working
2021年2月22日(月) 8:42 David G. Johnston : > On Sun, Feb 21, 2021 at 4:38 PM Guyren Howe wrote: > >> The documentation says that inet_server_addr() does this, but on our >> servers it is returning nothing. >> > > "Returns the IP address on which the server accepted the current > connection, or NULL if the current connection is via a Unix-domain socket." > > You will need to demonstrate that the connection you are checking from > isn't being made via a Unix-domain socket. > Note also there's a bug in current versions where this function will erroneously return NULL if executed by a parallel worker, see [1]. This is fixed in HEAD but hasn't been backpatched [2]. [1] https://www.postgresql.org/message-id/cad21aoat4ahp0uxq91qpd7nl009tnuyqe-b14r3mnsvojte...@mail.gmail.com [2] https://git.postgresql.org/pg/commitdiff/5a6f9bce8dabd371bdb4e3db5dda436f7f0a680f Regards Ian Barwick -- EnterpriseDB: https://www.enterprisedb.com
Streaming replication between different OS
Hi, I have postgres 9.6 cluster running on Centos 6.8, so I just wanted to know that can I configure streaming replication with same postgres version i.e 9.6 running on centos 7. Suggestions are welcome as the Centos versions are different one is 6.8 and second one is 7. Also please let me know if there will be any challenge in case of failover.
Re: Script checking to see what database it's connected to
Julien Rouhaud schrieb am 22.02.2021 um 02:19: >> The output: >> $ psql12 -f test_pg.sql >> t >> connected to postgres >> got here >> psql:test_pg.sql:15: ERROR: syntax error at or near "exit" >> LINE 1: exit > > Well, the supported commands did work. You should probably look at > https://www.postgresql.org/docs/current/app-psql.html, you'd see that > "exit" is not a supported command and you should instead use \q[uit]. > I recommend looking at the semantics of \quit though, given your next > message mentioning \include. Since v11 "exit" and "quit" are allowed if no previous input was given on the line. It's only documented in the release notes[1] though, not in the manual. Thomas [1] https://www.postgresql.org/docs/release/11.0/
converting text to bytea
Hello This sounds simple, but im not getting the results when i cast text to bytea like this first_name::bytea . Is there another way to do this? regards
Re: converting text to bytea
Hi po 22. 2. 2021 v 7:37 odesílatel Yambu napsal: > Hello > > This sounds simple, but im not getting the results when i cast text to > bytea like this first_name::bytea . Is there another way to do this? > You should to use convert_to function https://www.postgresql.org/docs/current/functions-binarystring.html#FUNCTIONS-BINARYSTRING-CONVERSIONS Regards Pavel > regards >
