PgAmin view

[Shaozhong SHI]

If a PgAmin view is created and tables it queries are missing, what will
happen?

If a PdAmin view is created, and it may take 20 hours to complete, what
will happen?

Would views automatically run, when you start the PgAmin?

Regards,

David

postgres keeps having blocks

[Shaozhong SHI]

My postgres is playing up.

I terminated session that is causing blocks many time.

New block appears.

Endless.

What should I do?

Regards,

David

Re: print in plpython not appearing in logs

[Shaozhong SHI]

What is brilliant about plpython?  Any brilliant examples to look at?

On Friday, 2 December 2022, Ludwig Isaac Lim  wrote:

> I'm having problems wherein my print() statements inside my plpython
> stored proc are not appearing in postgresql log. I tried setting the
> file=sys.stderr
>
> To reproduce:
>
> CREATE OR REPLACE PROCEDURE p_ludwig_test()
> AS
> $$
>import sys
>try:
>   x = 1 / 0
>except:
>   plpy.log("hello")
>   print("oink oink", file=sys.stderr)
>   print("oink oink - v2")
>   plpy.log("haha")
> $$
> LANGUAGE plpython3u;
>
> call p_ludwig_test()
>
>
>
> Output:
> -
> 2022-12-02 11:46:11.324 UTC [19390] LOG:  hello
> 2022-12-02 11:46:11.324 UTC [19390] CONTEXT:  PL/Python procedure
> "p_ludwig_test"
> 2022-12-02 11:46:11.324 UTC [19390] STATEMENT:  call p_ludwig_test();
> 2022-12-02 11:46:11.324 UTC [19390] LOG:  haha
> 2022-12-02 11:46:11.324 UTC [19390] CONTEXT:  PL/Python procedure
> "p_ludwig_test"
> 2022-12-02 11:46:11.324 UTC [19390] STATEMENT:  call p_ludwig_test();
>
>
> Notice that the "oink oink"  is  not there.
>
>
> Relevant logging configuration:
> logging_collector = on
> log_directory = 'logs'
> log_min_messages = info
> log_min_error_statement = error
>
> PG version
> ---
>  PostgreSQL 14.6 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1
> 20180712 (Red Hat 7.3.1-15), 64-bit
>
>
>
> The reason why I'm trying to use print() is because plpy logger is quite
> verbose. Each logging will output 3 line
>
> for example:
>plpy.log("hello")
>
> Will generate the following 3 lines in the log:
> 2022-12-02 11:46:11.324 UTC [19390] LOG:  hello
> 2022-12-02 11:46:11.324 UTC [19390] CONTEXT:  PL/Python procedure
> "p_ludwig_test"
> 2022-12-02 11:46:11.324 UTC [19390] STATEMENT:  call p_ludwig_test();
>
> Another thing is there is another stored procedure that I have wherein the
> print() to stderr actually works, so I'm not sure what I'm doing wrong.
>
>
> Thank you in advance,
> Ludwig
>
>
>

How to select based on the condition of a column exists

[Shaozhong SHI]

The following does not work.
select count(test) from table where exists (select test from table)


How to  select based on the condition of a column exists?   It column test
is not exists, a message need to be returned.

Regards,

David

A function to find errors in groups in a table

[Shaozhong SHI]

There is a table full of grouped values like the following

nodeid link_type  primary
11   outflowlink   1
11  inflowlink  1
11  outflowlink 2

Primary of 1 indicates a primary water course.  Primary of 2 indicates a
secondary water course.

Obviously, one of the out flow links is an error, as its primacy value is
2.  It is wrong that water flows from a primary water course into a
secondary water course.

How can a function can be designed to find and report such errors?

Regards,

David

Set timeout just on a query?

[Shaozhong SHI]

Can timeout be set just on a query?

Can we do the following?

Begin

do a query
set timeout
Exception
report a record

End;

Regards,
David

How to get value wrapped in json?

[Shaozhong SHI]

A json object is like this - {3}.

How to get the value -3 out of this json object {3}

David

Re: parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

Well, I guess that does not work.
Never mind.

Regards,
David


On Thursday, 28 April 2022, Alvaro Herrera  wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > Expand and explain please.
>
> No, thanks.
>
> --
> Álvaro Herrera
>

Re: parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

Expand and explain please.
Regards,
David

On Thursday, 28 April 2022, Alvaro Herrera  wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > Why sleep(1)?
>
> It is sleeping to show that they are running concurrently.  If it runs
> five sleeps of one second each and the whole command lasts one second,
> then all sleeps ran in parallel.  Had the whole command taken five
> seconds, you would know that the queries ran serially.
>
> > It should be all active - doing work concurrently.
>
> They are all active simultaneously.  You just need to supply your own
> query, without any sleeps.
>
> --
> Álvaro Herrera
>

Re: parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

No, No.

Why sleep(1)?

It should be all active - doing work concurrently.

Regards,

David

On Thu, 28 Apr 2022 at 16:17, Alvaro Herrera 
wrote:

> On 2022-Apr-28, Shaozhong SHI wrote:
>
> > multiple similar query tasks are as follows:
> >
> > select * from a_table where country ='UK'
> > select * from a_table where country='France'
> > and so on
> >
> > How best to parallel-processing such types of multiple similar query
> tasks?
> >
> > Any example available?
>
> for country in UK France Germany Ireland; do
>   echo "select pg_sleep(1); select * from a_table where country =
> '${country//\'/''/}'"
> done | \
>   xargs -d"\n" -P10 -n1 psql -X -c
>
> Note the ${country/} stuff is a bash-ism.
>
> --
> Álvaro Herrera
>

How to set password in psql -h -d -U command line?

[Shaozhong SHI]

I tried various ways to set password in psql command line, but got no luck.

Can anyone help?

Regards,

David

parallel-processing multiple similar query tasks - any example?

[Shaozhong SHI]

multiple similar query tasks are as follows:

select * from a_table where country ='UK'
select * from a_table where country='France'
and so on

How best to parallel-processing such types of multiple similar query tasks?

Any example available?

Regards,

David

Configuration and performance of Postgres/PostGIS

[Shaozhong SHI]

Whenever geospatial functions such as St_intersects or recursive query
used, the Postgres/PostGIS appears to spawn away to many child queries and
just obliterate the CPU.  Nothing finishes.

That forced me to try out to do the some tasks on the FME server.

I tried to use this http://blog.cleverelephant.ca/2010/07/network
-walking-in-postgis.html in the Postgres/PostGIS.

I tried to linecombiner in FME.  LineCombiner | FME (safe.com)
.

With a large data set, the running of processors were monitored.  It was
estimated the Postgres/PostGIS one would take 16 days to complete.

But, it only took a few minute to do the same thing in FME.

This suggests that something is not right with the Postages Server.

Have anyone got experience with configuration and improving performance of
Postages Server?

Regards,

David

Can anyone confirm the flaw of postgres and how to deal with it?

[Shaozhong SHI]

I loaded several tables onto Postgres.

When you view, you can see all columns.

However, there are 'ghost columns' that I remember I used before, but not
now.

 select column_name::text from information_schema.columns where
table_name=a_table

keeps listing columns that I can not see in the current table.

Why does this happen?

What is the solution?

Regards,

David

Long running processes and commit writing to disk

[Shaozhong SHI]

When long running processes got disrupted, one may not see any expected
result.

How to make sure that the result of each operation is saved to disk in a
loop?

Regards,

David

how to find out field size?

[Shaozhong SHI]

Postgres documentation says:  maximum field size is 1 GB.
PostgreSQL: Documentation: 12: Appendix K. PostgreSQL Limits


So, how to find out the size of column to see how far it from the limit?

Regards,

David

Re: Re: How long does iteration over 4-5 million rows usually take?

[Shaozhong SHI]

Thanks, Karsten,

I would like the information to work planning purpose.

Regards,

David

On Sat, 2 Apr 2022 at 14:47, Karsten Hilbert 
wrote:

> > > On Apr 1, 2022, at 10:18 PM, Ron  wrote:
> > >
> > >  On 4/1/22 20:34, Shaozhong SHI wrote:
> > >>
> > >> I have a script running to iterate over 4-5 million rows.  It keeps
> showing up in red in PgAdmin.  It remains active.
> > >>
> > >> How long does iteration over 4-5 million rows usually take?
> >
> > 4-5 million times as long as it takes to do one iteration ( if you’re
> doing it correctly)
>
> I may not take quite that long because setup/teardown times might not be
> needed for each iteration.
>
> Best,
> Karsten
>
>
>

How long does iteration over 4-5 million rows usually take?

[Shaozhong SHI]

I have a script running to iterate over 4-5 million rows.  It keeps showing
up in red in PgAdmin.  It remains active.

How long does iteration over 4-5 million rows usually take?

Regards,

David

In what situation, a line feature could not be visible in PgAdmin?

[Shaozhong SHI]

I simply did the following:



Get intersections through self-joining lines table where a.fid not equal to
b.fid

Put buffered intersections with geometry from lines table together to view.


Some odd things were observed.


Buffer turned up, but supposed lines did not show.


Why does this happen?


Regards,


David

How to explicitly lock and unlock tables in pgsql?

[Shaozhong SHI]

Table locks present a barrier for progressing queries.

How to explicitly lock and unlock tables in pgsql, so that we can guarantee
the progress of running scripts?

Regards,

David

Apparently table locks are the key issue to see red flags

[Shaozhong SHI]

Apparently table locks are the key issue to see red flags.

As we observe, every time, red marking occurs, there must be some tables
are locked.

Can anyone shed light into this?

Regards,

David

Re: frequency of positive and negative numbers, sizes of numbers and frequency of alteration of polarity

[Shaozhong SHI]

On Thu, 17 Feb 2022 at 21:20, Thomas Munro  wrote:

> On Fri, Feb 18, 2022 at 9:11 AM Shaozhong SHI 
> wrote:
> > How to calculate frequency of positive and negative numbers and define
> and calculate frequency of alteration of polarity?
> >
> > Surely, we can use frequency of alteration of polarity and level of
> change (e.g., size of positive and negative numbers) to measure degree and
> frequency of alteration.
> >
> > Any ideas in doing so in postgres tables' columns full of positive and
> negative numbers?
>
> Window functions might be useful to detect polarity changes:
>
> postgres=# create table time_series (time int, value int);
> CREATE TABLE
> postgres=# insert into time_series values (1, -5), (2, -5), (3, 10), (4,
> -3);
> INSERT 0 4
> postgres=# select time,
>   value,
>   sign(lag(value) over (order by time)) != sign(value)
> as flipped
>  from time_series;
>  time | value | flipped
> --+---+-
> 1 |-5 |
> 2 |-5 | f
> 3 |10 | t
> 4 |-3 | t
> (4 rows)
>

Given 2 or more such columns, is there any measure that can be calculated
to tell which one alternates more than others?

Regards,
David

Re: frequency of positive and negative numbers, sizes of numbers and frequency of alteration of polarity

[Shaozhong SHI]

On Thursday, 17 February 2022, Thomas Munro  wrote:

> On Fri, Feb 18, 2022 at 9:11 AM Shaozhong SHI 
> wrote:
> > How to calculate frequency of positive and negative numbers and define
> and calculate frequency of alteration of polarity?
> >
> > Surely, we can use frequency of alteration of polarity and level of
> change (e.g., size of positive and negative numbers) to measure degree and
> frequency of alteration.
> >
> > Any ideas in doing so in postgres tables' columns full of positive and
> negative numbers?
>
> Window functions might be useful to detect polarity changes:
>
> postgres=# create table time_series (time int, value int);
> CREATE TABLE
> postgres=# insert into time_series values (1, -5), (2, -5), (3, 10), (4,
> -3);
> INSERT 0 4
> postgres=# select time,
>   value,
>   sign(lag(value) over (order by time)) != sign(value)
> as flipped
>  from time_series;
>  time | value | flipped
> --+---+-
> 1 |-5 |
> 2 |-5 | f
> 3 |10 | t
> 4 |-3 | t
> (4 rows)
>

Hot to get measures for frequency and magnitude of alternating?
Regards, David

frequency of positive and negative numbers, sizes of numbers and frequency of alteration of polarity

[Shaozhong SHI]

How to calculate frequency of positive and negative numbers and define and
calculate frequency of alteration of polarity?

Surely, we can use frequency of alteration of polarity and level of change
(e.g., size of positive and negative numbers) to measure degree and
frequency of alteration.

Any ideas in doing so in postgres tables' columns full of positive and
negative numbers?

Regards,

David

Is there a way to automatically scan a table and determine the format of data

[Shaozhong SHI]

Is there a way to automatically scan a table and report the format of data
for each column?
Regards,
David

Operator % and its meaning and use

[Shaozhong SHI]

Can anyone remind me of the meaning and use of operator %.

It works in one of scripts like  WHERE NOT (street_name % designatedname)

Regards,

David

Turn a json column into a table

[Shaozhong SHI]

There is a JSON column in a table.  It contains key value pairs, just like
a dictionary.  What is the best way to turn this column into a data table?

Regards,

David

Re: Regular Expression For Duplicate Words

[Shaozhong SHI]

Hi, Peter,  Interesting.

On Thu, 3 Feb 2022 at 19:48, Peter J. Holzer  wrote:

> On 2022-02-02 08:00:00 +0000, Shaozhong SHI wrote:
> > regex - Regular Expression For Duplicate Words - Stack Overflow
> >
> > Is there any example in Postgres?
>
> It's pretty much the same as with other regexp dialects: User word
> boundaries and a word character class to match any word and then use a
> backreference to match a duplicate word. All the building blocks are
> described on
>
> https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> and except for [[:<:]] and [[:>:]] for the word boundaries, they are
> also pretty standard.
>
> So
>
> [[:<:]]start of word
> ([[:alpha:]]+) one or more alphabetic characters in a capturing group
> [[:>:]]end of word
> \W+one or more non-word characters
> [[:<:]]start of word
> \1 the content of the first (and only) capturing group
> [[:>:]]end of word
>
> All together:
>
> select * from t where t ~ '[[:<:]]([[:alpha:]]+)[[:>:]]\W[[:<:]]\1[[:>:]]';
>
> Give a good example if you can.
>

Regards,

David

Re: Can Postgres beat Oracle for regexp_count?

[Shaozhong SHI]

Hi, David,

Many thanks.

I am investigating into transformation of data quality validation through
automation with application of Postgres/PostGIS.

Regards,

David

On Thu, 3 Feb 2022 at 13:00, David G. Johnston 
wrote:

>
>
> On Thursday, February 3, 2022, Shaozhong SHI 
> wrote:
>
>>
>> Is it correct to say that this ?: construction of a regex can be applied
>> for checking whether cell values meet specifications?
>>
>>>
>>>
> It does exactly what our examples shows it does.  I don’t understand what
> you mean above but if that helps you remember its purpose, great.
>
> David J.
>
>

Re: Can Postgres beat Oracle for regexp_count?

[Shaozhong SHI]

Many thanks, Tom,

select regexp_matches('My High Street', '(?:[A-Z][a-z]+[\s]*)+', 'g');
looks very interesting.

I did read the documentation, but found it is difficult to read.
Particularly, the documentation on the use ?: does not state clear sense.
There is only limited explanation on ?:.

Is it correct to say that this ?: construction of a regex can be applied
for checking whether cell values meet specifications?

Regards,

David

On Thu, 3 Feb 2022 at 05:59, Tom Lane  wrote:

> Shaozhong SHI  writes:
> > The following has been attempted but no luck.
>
> > select regexp_matches('My High Street', '([A-Z][a-z]+[\s]*)+', 'g')
> > It is intended to match 'My High Street, but it turned out only 'Street'
> > was matched.
>
> You've got the parentheses in the wrong place, ie inside not outside the
> "+" quantifier.  Per the fine manual [1], the result is determined by the
> last match of quantified capturing parens.
>
> You could avoid using any capturing parens, so that the result is
> the whole match:
>
> regression=# select regexp_matches('My High Street',
> '(?:[A-Z][a-z]+[\s]*)+', 'g');
>regexp_matches
> 
>  {"My High Street"}
> (1 row)
>
> or you could do
>
> regression=# select regexp_matches('My High Street',
> '(([A-Z][a-z]+[\s]*)+)', 'g');
>   regexp_matches
> ---
>  {"My High Street",Street}
> (1 row)
>
> but then you have two sets of capturing parens and you get results for
> both, so you might prefer
>
> regression=# select regexp_matches('My High Street',
> '((?:[A-Z][a-z]+[\s]*)+)', 'g');
>regexp_matches
> 
>  {"My High Street"}
> (1 row)
>
> In any case, there's no substitute for reading the manual.
>
> regards, tom lane
>
> [1]
> https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
>

Re: Can Postgres beat Oracle for regexp_count?

[Shaozhong SHI]

Hi, Tom, Lane,

On Wed, 2 Feb 2022 at 22:26, Tom Lane  wrote:

> "David G. Johnston"  writes:
> > Given we don't have a regexp_count function this isn't surprising...
>
> FYI, it's there in HEAD.
>
> In the meantime, you could possibly do something like
>
> =# select count(*) from regexp_matches('My High Street',
> '([A-Z][a-z]+[\s])', 'g');
>  count
> ---
>  2
> (1 row)
>
> (Note that 2 is the correct answer given that there's no space
> after the third word; I trust Oracle agrees.)
>
> Can the whole 3 or 4 or 5 to be matched as 1?
>

The following has been attempted but no luck.

select regexp_matches('My High Street', '([A-Z][a-z]+[\s]*)+', 'g')
It is intended to match 'My High Street, but it turned out only 'Street'
was matched.

Regards,  David

Can Postgres beat Oracle for regexp_count?

[Shaozhong SHI]

It has been found that regexp_count works brilliantly in Oracle.

However, it is not easy to replicate that in Postgres.  The following codes
have been experimented but without any luck.

select regexp_matches('My High Street', '([A-Z][a-z]+[\s])', 'g')

select regexp_matches('My High Street', '([A-Z][a-z]+[\s]*)', 'g')

County occurrences of 'My High Street' in one of the following strings:

'My High Street'1
'' 0
'My High Street My High Street'   2

Can anyone enlighten all of us?

Regards,

David

Regular Expression For Duplicate Words

[Shaozhong SHI]

This link is interesting.

regex - Regular Expression For Duplicate Words - Stack Overflow


Is there any example in Postgres?

Regards,

David

Re: Counting the number of repeated phrases in a column

[Shaozhong SHI]

On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI  wrote:

> There is a short of a function in the standard Postgres to do the
> following:
>
> It is easy to count the number of occurrence of words, but it is rather
> difficult to count the number of occurrence of phrases.
>
> For instance:
>
> A cell of value:  'Hello World' means 1 occurrence a phrase.
>
> A cell of value: 'Hello World World Hello' means no occurrence of any
> repeated phrase.
>
> But, A cell of value: 'Hello World World Hello Hello World' means 2
> occurrences of 'Hello World'.
>
> 'The City of London, London' also has no occurrences of any repeated
> phrase.
>
> Anyone has got such a function to check out the number of occurrence of
> any repeated phrases?
>
>
>
How about knock unique words into discrete joint up strings?  Then check
whether there is any repeated words?
Regards,
David

Re: Counting the number of repeated phrases in a column

[Shaozhong SHI]

On Thursday, 27 January 2022, Merlin Moncure  wrote:

> On Wed, Jan 26, 2022 at 5:23 PM Merlin Moncure  wrote:
> >
> > with s as (select 'Hello World Hello World' as sentence)
> > select
> >   phrase,
> >   array_upper(string_to_array((select sentence from s), phrase), 1) -
> > 1 as occurrances
> > from
> > (
> >   select array_to_string(x, ' ') as phrase
> >   from
> >   (
> > select distinct v[a:b]  x
> > from regexp_split_to_array((select sentence from s), ' ') v
> > cross join lateral generate_series(1, array_upper(v, 1)) a
> > cross join lateral generate_series(a + 1, array_upper(v, 1)) b
> >   ) q
> > ) q;
>
> Simplified to:
> select distinct array_to_string(v[a:b], ' ') phrase, count(*) as
> occurrences
> from regexp_split_to_array('Hello World Hello World', ' ') v
> cross join lateral generate_series(1, array_upper(v, 1)) a
> cross join lateral generate_series(a + 1, array_upper(v, 1)) b
> group by 1;
>
>  phrase  │ occurances
> ─┼
>  World Hello │  1
>  Hello World Hello   │  1
>  Hello World │  2
>  Hello World Hello World │  1
>  World Hello World   │  1
>
> merlin
>



How about knock unique words into discrete joint up strings?  Then check
whether there is any repeated words?
Regards,
David

Re: Counting the number of repeated phrases in a column

[Shaozhong SHI]

On Tue, 25 Jan 2022 at 17:10, Shaozhong SHI  wrote:

> There is a short of a function in the standard Postgres to do the
> following:
>
> It is easy to count the number of occurrence of words, but it is rather
> difficult to count the number of occurrence of phrases.
>
> For instance:
>
> A cell of value:  'Hello World' means 1 occurrence a phrase.
>
> A cell of value: 'Hello World World Hello' means no occurrence of any
> repeated phrase.
>
> But, A cell of value: 'Hello World World Hello Hello World' means 2
> occurrences of 'Hello World'.
>
> 'The City of London, London' also has no occurrences of any repeated
> phrase.
>
> Anyone has got such a function to check out the number of occurrence of
> any repeated phrases?
>
> Regards,
>
> David
>

Hi, All Friends,

Whatever.   Can we try to build a regex for   'The City of London London
Great London UK ' ?

It could be something like '[\w\s]+[\s-]+[a-z]+[\s-][\s\w]+'.
 [\s-]+[a-z]+[\s-] is catered for some people think that 'City of London'
is 'City-of-London' or 'City-of-London'.

Regards,

David

Re: Counting the number of repeated phrases in a column

[Shaozhong SHI]

On Tue, 25 Jan 2022 at 21:33, Ivan Panchenko 
wrote:

>
> On 26.01.2022 00:21, benj@laposte.net wrote:
> > Le 25/01/2022 à 18:10, Shaozhong SHI a écrit :
> >> There is a short of a function in the standard Postgres to do the
> >> following:
> >>
> >> It is easy to count the number of occurrence of words, but it is
> >> rather difficult to count the number of occurrence of phrases.
> >>
> >> For instance:
> >>
> >> A cell of value:  'Hello World' means 1 occurrence a phrase.
> >>
> >> A cell of value: 'Hello World World Hello' means no occurrence of any
> >> repeated phrase.
> >>
> >> But, A cell of value: 'Hello World World Hello Hello World' means 2
> >> occurrences of 'Hello World'.
> >>
> >> 'The City of London, London' also has no occurrences of any repeated
> >> phrase.
> >>
> >> Anyone has got such a function to check out the number of occurrence
> >> of any repeated phrases?
> >>
> >> Regards,
> >>
> >> David
> >
> > Don't know if it's exactly what you want, but you can replace all
> > occurence of the phrase in the text by empty string and compute the
> > diff between the initial and the result and next divide by the length
> > of your phrase.
> >
> > Example :
> > WITH x AS (SELECT 'toto like tata and toto like titi and toto like
> > tutu' , 'toto like' phrase)
> > SELECT (char_length(texte) - char_length(replace(texte, phrase, '')))
> > / char_length(phrase) AS nb_occurence
> > FROM x
> >
> This works if the user knows the phrase. As far as I understood, the
> phrase is not known, and user wants to count number of repeats of any
> phrases.
> Of course this can be done with recursive CTE. Split into words,
> generate all phrases (AFAIK requires recursion), then group and count.
>
> But probably in PL/Perl this could be done  more effectively.
>

Is there an example of using recursive CTE to split a text string into
words?

Regards,

David

Re: Counting the number of repeated phrases in a column

[Shaozhong SHI]

How about split up the value into individual words and keep their orders?
add words up to form individual phrase and ensure that each phrase only
consists unique/distinct words
count repeated phrases afterward

How about this?

Regards,

David

On Tue, 25 Jan 2022 at 17:22, Karsten Hilbert 
wrote:

> > There is a short of a function in the standard Postgres to do the
> following:
> >
> > it is easy to count the number of occurrence of words, but it is rather
> difficult to count the number of occurrence of phrases.
> >
> > For instance:
> >
> > A cell of value:  'Hello World' means 1 occurrence a phrase.
> >
> > A cell of value: 'Hello World World Hello' means no occurrence of any
> repeated phrase.
> >
> > But, A cell of value: 'Hello World World Hello Hello World' means 2
> occurrences of 'Hello World'.
> >
> > 'The City of London, London' also has no occurrences of any repeated
> phrase.
> >
> > Anyone has got such a function to check out the number of occurrence of
> any repeated phrases?
>
> For that to become answerable you may want to define what to
> do when facing ambiguity.
>
> Best,
> Karsten
>
>
>

Counting the number of repeated phrases in a column

[Shaozhong SHI]

There is a short of a function in the standard Postgres to do the following:

It is easy to count the number of occurrence of words, but it is rather
difficult to count the number of occurrence of phrases.

For instance:

A cell of value:  'Hello World' means 1 occurrence a phrase.

A cell of value: 'Hello World World Hello' means no occurrence of any
repeated phrase.

But, A cell of value: 'Hello World World Hello Hello World' means 2
occurrences of 'Hello World'.

'The City of London, London' also has no occurrences of any repeated phrase.

Anyone has got such a function to check out the number of occurrence of any
repeated phrases?

Regards,

David

Re: Robust ways for checking allowed values in a column

[Shaozhong SHI]

How about adding null as an alteration.

Would this be robust?

Regards,

David

On Tue, 25 Jan 2022 at 14:25, David G. Johnston 
wrote:

> On Tue, Jan 25, 2022 at 6:56 AM Shaozhong SHI 
> wrote:
>
>>  select form from mytable  where form ~
>> '^Canal$|^Drain$|^Foreshore$|^inlandRiver$|^Lake$|^lockOrFlightOfLocks$|^Marsh$|^Researvoir$|^Sea$|^tidalRiver$|^Transfer$'
>>
>
> You do not need to repeat the boundary metacharacters on each branch.  You
> can assert their presence just once and then use parentheses to group the
> alternations.
>
> form ~ '^(?Canal|Drain|etc...)$'
>
> David J.
>
>

Robust ways for checking allowed values in a column

[Shaozhong SHI]

I tried the following:

 select form from mytable  where form ~
'^Canal$|^Drain$|^Foreshore$|^inlandRiver$|^Lake$|^lockOrFlightOfLocks$|^Marsh$|^Researvoir$|^Sea$|^tidalRiver$|^Transfer$'

I used ^ and $ to ensure checking of allowed values.

However, 'Backyard' was selected.

Why is that?

Regards,

David

Re: Query on postgres_fdw extension

[Shaozhong SHI]

Any functional code to be tested to confirm?

Regards,

David

On Fri, 21 Jan 2022 at 15:55, Laurenz Albe  wrote:

> On Fri, 2022-01-21 at 14:33 +, Duarte Carreira wrote:
> > If we just create the 2 foreign tables, one complete and one without id,
> > you can simply insert into the table without id and it will work fine.
> > To select and show data, you use the "complete" table that has the id
> column.
> >
> > No need for trigger and view. If I understood correctly.
> >
> > I have this 2 table setup working.
> >
> > It's a workaround that quickly escalates out of hand though... with
> little added value.
>
> Sure, that will work.
>
> I just wanted to show how you can do without dealing with two different
> foreign tables explicitly.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>

Re: Can commands be typed in to view geometry in PgAdmin?

[Shaozhong SHI]

It would be nice to be able to write lines of scripts to tell PgAdmin to
show some visualisation of geographical features.

Regards,

David

On Fri, 21 Jan 2022 at 15:14, David G. Johnston 
wrote:

> It is amazing how much one can learn by reading documentation.  You are
> looking for a section that describes how to view, and possibly edit, data
> in pgAdmin4.
>
> David J.
>
>
> On Fri, Jan 21, 2022 at 5:52 AM Shaozhong SHI 
> wrote:
>
>> I just wonder whether commands can be typed in PgAdmin to view geometries.
>>
>> Regards,
>>
>> David
>>
>

Can commands be typed in to view geometry in PgAdmin?

[Shaozhong SHI]

I just wonder whether commands can be typed in PgAdmin to view geometries.

Regards,

David

PgAdmin is struggling and can we configure it so that it works better

[Shaozhong SHI]

Some time, PgAdmin freezes and its response is very slow.

Some time, it gives strange display of table content.  Perhaps, it is
related to the fact that PgAdmin is struggling with the amount of data it
thinks that it has to display.

I just wonder whether we can configure PgAdmin so that it will work better.

Regards,

David

Re: psql does not provide proper response

[Shaozhong SHI]

Added.  But only head of columns appeared.
Any way to visualise?
Regards, David

On Thursday, 20 January 2022, Rob Sargent  wrote:

> On 1/20/22 10:54, Shaozhong SHI wrote:
>
> I do not know what happened.
>
> psql does not provide proper response anymore.
>
> I typed the following and see nothing.
>
> user=# select * from boundaryline.scotland_and_wales_const_region
> user-#
>
> Can anyone enlighten me?
>
> Regards,
>
> David
>
> Add semi-colon return?
>

psql does not provide proper response

[Shaozhong SHI]

I do not know what happened.

psql does not provide proper response anymore.

I typed the following and see nothing.

user=# select * from boundaryline.scotland_and_wales_const_region
user-#

Can anyone enlighten me?

Regards,

David

How to schedule running of a script?

[Shaozhong SHI]

Can a script be scheduled to run within Postgres?

Regards,

David

Re: How can a Postgres SQL script be automatically run when a new table turns up?

[Shaozhong SHI]

Hi, Jay,

That looks interesting.  Is there an excellent example to do the following?

 How to create an event trigger in Postgres? When a user finished loading a
new table on to it, the trigger can start off an script 10 minutes after
the event?

Regards,

David

On Thu, 13 Jan 2022 at 10:50, Jayadevan M 
wrote:

>
>
>
> When a user load a new table in the Postgres System?  Can a script
>> automatically detect it and run?
>>
>> Are you looking for Even triggers?
> https://www.postgresql.org/docs/current/event-triggers.html
>
> Regards,
> Jay
>

How can a Postgres SQL script be automatically run when a new table turns up?

[Shaozhong SHI]

When a user load a new table in the Postgres System?  Can a script
automatically detect it and run?

Regards,

David

Re: How best to create and use associative array type in Postgres?

[Shaozhong SHI]

Have a look at this one.

GitHub - theory/kv-pair: A key/value pair data type for PostgreSQL
<https://github.com/theory/kv-pair>

There is no documentation on how to use it.

Regards,

David

On Wed, 5 Jan 2022 at 16:24, Tom Lane  wrote:

> Shaozhong SHI  writes:
> > What do you think this attempt by using create type and create a
> function?
> > Managing Key/Value Pairs in PostgreSQL (justatheory.com)
> > <https://justatheory.com/2010/08/postgres-key-value-pairs/>
>
> The date alone should suggest to you that there might be
> better ways to do it by now.
>
> regards, tom lane
>

Re: How best to create and use associative array type in Postgres?

[Shaozhong SHI]

What do you think this attempt by using create type and create a function?

Managing Key/Value Pairs in PostgreSQL (justatheory.com)
<https://justatheory.com/2010/08/postgres-key-value-pairs/>

Regards,

David

On Wed, 5 Jan 2022 at 14:54, Tom Lane  wrote:

> Shaozhong SHI  writes:
> > How best to create and use associative array type in Postgres?
>
> I think the closest thing you'd find to that is jsonb, or
> contrib/hstore if you'd like something with a bit less complexity.
> The notation is unlikely to look much like Oracle, but they
> both have the ability to store sets of key/value pairs.
>
> https://www.postgresql.org/docs/current/datatype-json.html
> https://www.postgresql.org/docs/current/hstore.html
>
> regards, tom lane
>

Re: Create and access a dictionary type

[Shaozhong SHI]

I just checked operators.  I could not find any operator to set a new value
given a key.

Regards,

David

On Wed, 5 Jan 2022 at 13:22, Tomas Vondra 
wrote:

> On 1/5/22 14:17, Shaozhong SHI wrote:
> > Any examples in Postgres to create a dictionary type to store and access
> > key value pairs?
> >
>
> I'd say JSONB can be used as an associative array, and e.g. in Python
> can map to dict data type.
>
>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Create and access a dictionary type

[Shaozhong SHI]

Any examples in Postgres to create a dictionary type to store and access
key value pairs?

Regards,

David

How best to create and use associative array type in Postgres?

[Shaozhong SHI]

In Oracle, one can create and use associative array.  For instance,
TYPE FID_MEASURE IS TABLE OF NUMBER INDEX BY VARCHAR2(38);
NODES_WAITING FID_SET;

How best to create and use associative array type in Postgres?

Or, what is the best/most efficient equivalent in Postgres?

Regards,

David

Re: Can we use sql language to create a function in Postgres?

[Shaozhong SHI]

Any online documentation or examples for using Oracle SQL in Postgres?

Regards,

David

On Wed, 5 Jan 2022 at 11:22, Shaozhong SHI  wrote:

> If so, can we use Oracle SQL script in DO block?
>
> Does this mean that all Oracle SQL can be used in Postgres?
>
> Regards,
>
> David
>
> On Wed, 5 Jan 2022 at 11:12, hubert depesz lubaczewski 
> wrote:
>
>> On Wed, Jan 05, 2022 at 11:04:34AM +, Shaozhong SHI wrote:
>> > I was given an Oracle script.  Can we use sql language to create a
>> function
>> > in Postgres?
>>
>> Sure:
>>
>> create function z() returns int4 language sql as $$
>> select 123;
>> $$;
>>
>> depesz
>>
>

Re: Can we use sql language to create a function in Postgres?

[Shaozhong SHI]

If so, can we use Oracle SQL script in DO block?

Does this mean that all Oracle SQL can be used in Postgres?

Regards,

David

On Wed, 5 Jan 2022 at 11:12, hubert depesz lubaczewski 
wrote:

> On Wed, Jan 05, 2022 at 11:04:34AM +0000, Shaozhong SHI wrote:
> > I was given an Oracle script.  Can we use sql language to create a
> function
> > in Postgres?
>
> Sure:
>
> create function z() returns int4 language sql as $$
> select 123;
> $$;
>
> depesz
>

Can we use sql language to create a function in Postgres?

[Shaozhong SHI]

I was given an Oracle script.  Can we use sql language to create a function
in Postgres?

Regards,

David

How best to turn select result into options like 'a|b|c''

[Shaozhong SHI]

Is there a way to turn select result into something like 'a|b|c' .
Regards,
David

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

[Shaozhong SHI]

I did make it to work and have been experimenting on a number of ways.  But
it just does not produce expected results.

Regards,

David

On Thu, 16 Dec 2021 at 21:25, David G. Johnston 
wrote:

> On Thu, Dec 16, 2021 at 2:11 PM Shaozhong SHI 
> wrote:
>
>> When I used SQL identifier, it stopped working.  The command line gets
>> interpreted as following:
>>
>> insert into stats select "1" as id, 'count of nulls in
>> "UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""")
>> from points_of_interest."pointx_v2_National_Coverage_Sep21" where
>> """UNIQUE_REFERENCE_NUMBER""" is null
>>
>
> By using the correct type specification at each variable insertion you can
> get this to work.
>
> Either do trial-and-error or actually reason through what is happening at
> each position and why it is either correct or wrong (build up the query in
> parts if that makes things easier).
>
> David J.
>

Re: How to ensure column names are double quoted while using execute format when building a stored procedure?

[Shaozhong SHI]

When I used SQL identifier, it stopped working.  The command line gets
interpreted as following:

insert into stats select "1" as id, 'count of nulls in
"UNIQUE_REFERENCE_NUMBER"' as checks, count("""UNIQUE_REFERENCE_NUMBER""")
from points_of_interest."pointx_v2_National_Coverage_Sep21" where
"""UNIQUE_REFERENCE_NUMBER""" is null

I used select count("UNIQUE_REFERENCE_NUMBER") from a_table where
"UNIQUE_REFERENCE_NUMBER"
is null in SQL.

It always worked.

This can not be replicated in Execute Format.

Regards,

David

On Thu, 16 Dec 2021 at 20:24, David G. Johnston 
wrote:

> On Thu, Dec 16, 2021 at 1:21 PM Shaozhong SHI 
> wrote:
>
>> The following command runs but does not produce results as expected.
>>
>> Execute Format('insert into stats select %L as id, %2$L as checks,
>> count(%3$s) from %4$s where %5$s is null', i, 'count of nulls in '||col,
>> col, t_name, col);
>>
>> All columns have got capital letters in.  How to ensure that the columns
>> are double-quote when they are fed in as variables.
>>
>>
> Quoting the relevant doc section:
>
>
> https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT
>
> type (required)
> The type of format conversion to use to produce the format specifier's
> output. The following types are supported:
>
> s formats the argument value as a simple string. A null value is treated
> as an empty string.
>
> I treats the argument value as an SQL identifier, double-quoting it if
> necessary. It is an error for the value to be null (equivalent to
> quote_ident).
>
> L quotes the argument value as an SQL literal. A null value is displayed
> as the string NULL, without quotes (equivalent to quote_nullable).
>
> David J.
>
>

How to ensure column names are double quoted while using execute format when building a stored procedure?

[Shaozhong SHI]

The following command runs but does not produce results as expected.

Execute Format('insert into stats select %L as id, %2$L as checks,
count(%3$s) from %4$s where %5$s is null', i, 'count of nulls in '||col,
col, t_name, col);

There should be a lot of nulls in columns, but it produces o count.

All columns have got capital letters in.  How to ensure that the columns
are double-quote when they are fed in as variables.

Regards,

David

Re: Detecting repeated phrase in a string

[Shaozhong SHI]

Hi, Peter,

How to define word boundary as either by using
^  , space, or $

So that the following can be done

fox fox is a repeat

foxfox is not a repeat but just one word.

Regards,

David

On Thu, 9 Dec 2021 at 13:35, Peter J. Holzer  wrote:

> On 2021-12-09 12:38:15 +0000, Shaozhong SHI wrote:
> > Does anyone know how to detect repeated phrase in a string?
>
> Use regular expressions with backreferences:
>
> bayes=> select regexp_match('foo wikiwiki bar', '(.+)\1');
> ╔══╗
> ║ regexp_match ║
> ╟──╢
> ║ {o}  ║
> ╚══╝
> (1 row)
>
> "o" is repeated in "foo".
>
> bayes=> select regexp_match('fo wikiwiki bar', '(.+)\1');
> ╔══╗
> ║ regexp_match ║
> ╟──╢
> ║ {wiki}   ║
> ╚══╝
> (1 row)
>
> "wiki" is repeated in "wikiwiki".
>
> bayes=> select regexp_match('fo wikiwi bar', '(.+)\1');
> ╔══╗
> ║ regexp_match ║
> ╟──╢
> ║ (∅)  ║
> ╚══╝
> (1 row)
>
> nothing is repeated.
>
> Adjust the expression within parentheses if you want to match somethig
> more specific than any sequence of one or more characters.
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

Detecting repeated phrase in a string

[Shaozhong SHI]

Does anyone know how to detect repeated phrase in a string?

Is there any such function?

Regards,

David

Use tsquery to check out occurrence of the same phrase in a cell

[Shaozhong SHI]

Are there good examples for using tsquery to check out occurrence of the
same phrase in a cell?

Regards,

David

Re: How to allow null as an option when using regexp_matches?

[Shaozhong SHI]

Hi, Karsten,

That sounds interesting.

Any good example?

Regards,  David

On Wed, 8 Dec 2021 at 12:10, Karsten Hilbert 
wrote:

> Am Wed, Dec 08, 2021 at 12:07:13PM + schrieb Shaozhong SHI:
>
> > We can do this:
> > select count(*) from regexp_matches('Great London', 'Great
> > London|Information Centre|Department for Transport', 'g');
> >
> > Is it possible to allow null as an option?  something like this
> > select count(*) from regexp_matches('Great London', 'null|Great
> > London|Information Centre|Department for Transport', 'g');
>
> You seem to want to apply coalesce() judiciously.
>
> Best,
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
>
>

How to allow null as an option when using regexp_matches?

[Shaozhong SHI]

We can do this:
select count(*) from regexp_matches('Great London', 'Great
London|Information Centre|Department for Transport', 'g');

Is it possible to allow null as an option?  something like this
select count(*) from regexp_matches('Great London', 'null|Great
London|Information Centre|Department for Transport', 'g');

Regards,

David

Re: How to reveal the codes of functions properly?

[Shaozhong SHI]

In what environment, that did not work.


On Tuesday, 30 November 2021, Rob Sargent  wrote:

> On 11/30/21 9:30 AM, Shaozhong SHI wrote:
>
> Any one can shed the light on this?
>
> Regards,
>
> David
>
> \sf+ function_name
>

How to reveal the codes of functions properly?

[Shaozhong SHI]

Any one can shed the light on this?

Regards,

David

Match 2 words and more

[Shaozhong SHI]

this is supposed to find those to have 2 words and more.

select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ ]+[:alpha:]+$';

But, it finds only one word as well.

It appears that regex is not robust.

Can anyone shed light on this?

Regards,

David

Merge into does not work

[Shaozhong SHI]

CREATE TABLE Stock(item_id int UNIQUE, balance int);

INSERT INTO Stock VALUES (10, 2200);
INSERT INTO Stock VALUES (20, 1900);


CREATE TABLE Buy(item_id int, volume int);

INSERT INTO Buy values(10, 1000);
INSERT INTO Buy values(30, 300);

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
 WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
 WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);

I am using Postgres 9.6.

Regards,

David

Re: Best examples of cardinality check and associated functions

[Shaozhong SHI]

Hi, Rob,

I am reviewing robust automation to do so and promote the best practice.

Regards,
David
D

On Thu, 25 Nov 2021 at 22:24, Rob Sargent  wrote:

>
>
> > On Nov 25, 2021, at 3:16 PM, Shaozhong SHI 
> wrote:
> >
> > 
> > I wonder whether the Postgres community has got the best examples of
> cardinality check and associated functions.
> >
>
> Are you looking for examples or opinions on the examples?
>
> > Regards,
> >
> > David
>

Best examples of cardinality check and associated functions

[Shaozhong SHI]

I wonder whether the Postgres community has got the best examples
of cardinality check and associated functions.

Regards,

David

function difference not found

[Shaozhong SHI]

Function difference not found.

Should an extension created for finding it?

Regards,

David

How to set alias data type?

[Shaozhong SHI]

select 'Total' as Total generate result that set Total as a column name
with unknown type

When trying to cast
select 'Total' as Total:: text

It simply does not work.

Regards,

David

Re: Regex for Word space Word space Word ....

[Shaozhong SHI]

Hi, David J,
Any good example of doing test on array?

Regards, David


On Tuesday, 23 November 2021, David G. Johnston 
wrote:

> On Tue, Nov 23, 2021 at 2:58 AM Shaozhong SHI 
> wrote:
>
>> Is there any regex for Word space Word space Word and more?
>>
>>
> What problem are you actually trying to solve?  You may find it easier to
> simply split your string on space and then do tests on elements of the
> resultant array.
>
> David J.
>

Re: Regex for Word space Word space Word ....

[Shaozhong SHI]

It only matches First Street from 'My First Street'.

I was trying to make it to match words starting capital letter only.

Regards,
David

On Tue, 23 Nov 2021 at 10:59, chlor  wrote:

> On Tue, Nov 23, 2021 at 11:51 AM Shaozhong SHI 
> wrote:
>
>> I tried nested regex  '[[A-Z][a-z] ]+[[A-Z][a-z]]' but it did not work.
>>
>
> [A-Z][a-z]+ +[A-Z][a-z]+
> will match 'Hello   World', but not 'Hello world'. Is that what you want?
>
> Try this instead
> [A-Za-z]+ +[A-Za-z]+
>
>
> And try also this editor to learn regex
> https://regex101.com/
>
> ./hans
>
>

Re: Regex for Word space Word space Word ....

[Shaozhong SHI]

I tried nested regex  '[[A-Z][a-z] ]+[[A-Z][a-z]]' but it did not work.

Regards,

David

On Tue, 23 Nov 2021 at 09:58, Shaozhong SHI  wrote:

> Is there any regex for Word space Word space Word and more?
>
> Regards,
>
> David
>

Regex for Word space Word space Word ....

[Shaozhong SHI]

Is there any regex for Word space Word space Word and more?

Regards,

David

Why swirling circles in pgAdmin?

[Shaozhong SHI]

Why there are swirling circles in pgAdmin and no expansion to view details?

Regards,

David

Why in pgAdmin an active session is marked/highlighted in Red

[Shaozhong SHI]

I never came across this before and wonder why?

Regards,

David

Regex for (A) and (B) to find in Bus Stop (A) or (B)

[Shaozhong SHI]

What is the regex for (A) and (B) to find in Bus Stop (A) or (B)?

Regards, David

Regex for (A) and (B) to find in Bus Stop (A) or (B)

[Shaozhong SHI]

What is the regex for (A) and (B) to find in Bus Stop (A) or (B)?

Regards, David

Re: DBeaver does not show all tables in a Schema

[Shaozhong SHI]

On Fri, 29 Oct 2021 at 14:53, Ryan Booz  wrote:

> In a recent update (not sure when), the default for DBeaver seems to have
> changed so that the navigator view is set to "simple", rather than
> "advanced" which shows all objects.
>
> Right-click the server -> edit connection -> Select "General" -> verify
> "Navigator View"
>
>
>>> I am afraid that this did not make a difference.

Regards,

David

Re: DBeaver does not show all tables in a Schema

[Shaozhong SHI]

It is a new installation with all permissions.  Nothing has done to it.

Regards,

David

On Fri, 29 Oct 2021 at 14:14, negora  wrote:

> Are you sure that you're not applying a filter in the "Database
> Navigator" panel of DBeaver?
>
> Sometimes, it occurs to me that I apply a filter for certain database, I
> forget to remove it, then open another database, and some or all tables
> do not appear in the navigator.
>
>
> On 29/10/2021 12:46, Shaozhong SHI wrote:
> > I used a DBeaver to connect to postgres but it does not show all
> > tables in a schema.
> >
> > Can anyone shed light on this?
> >
> > Regards,
> >
> > David
>
>
>

DBeaver does not show all tables in a Schema

[Shaozhong SHI]

I used a DBeaver to connect to postgres but it does not show all tables in
a schema.

Can anyone shed light on this?

Regards,

David

Re: Fault with initcap

[Shaozhong SHI]

On Tue, 12 Oct 2021 at 23:02, Adrian Klaver 
wrote:

> On 10/12/21 13:50, Shaozhong SHI wrote:
> >
> >
> > On Tue, 12 Oct 2021 at 20:34, Adrian Klaver 
> >
> > Which follows the definition here:
> >
> > https://www.postgresql.org/docs/14/functions-string.html
> > <https://www.postgresql.org/docs/14/functions-string.html>
> >
> > initcap ( text ) → text
> >
> > Converts the first letter of each word to upper case and the rest to
> > lower case. Words are sequences of alphanumeric characters separated
> by
> > non-alphanumeric characters.
> >
> > Hi, Adrian Klaver,
> >
> >
> > It looks like that you replicated the error.
>
> There is no error, initcap is doing what it is documented to.
>
> notemachine is not two words anymore then 'online', 'bluebell',
> 'network' are.
>
>
> >
> > There must be a way to do the following.
>
> Maybe, but as Karsten says it would involve an AI. One that understands
> the mutt language that is English.
>
> >
> > a column contains a list of words.  Only the first letter of each word
> > should be capitalised.  INITCAP can not do that.  How to create a
> > function just to capitalised each word (substring) in a list of
> > words/strings.  This will be very useful and create great impact.
>
>  From here:
>
> https://www.grammarly.com/blog/14-of-the-longest-words-in-english/
>
> uncopyrightable
>
> where would you split that into words?:
>
> Some 'words' I see:
>
> un
> unc
> copy
> copyright
> right
> table
> able
>
> >
> > Regards,
> >
> > David
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com


Hi, Adrian Klaver,

In Python, there is  a capwords.  Do we have an equivalent in Postgres?
Regards, David

Re: Fault with initcap

[Shaozhong SHI]

On Tue, 12 Oct 2021 at 20:34, Adrian Klaver 
wrote:

> On 10/12/21 09:31, Shaozhong SHI wrote:
> > I tried initcap and found a major problem with it.
>
> What Postgres version?
>
> In version 12 and 14 I get:
>
> >
> > Initcap of notemachine is NoteMachine.
>
> select initcap('notemachine');
> initcap
> -
>   Notemachine
>
> >
> > Initcap of Sainsbury's Bank is Sainsbury'S bank.
>
> select initcap('Sainsbury''s Bank');
>   initcap
> --
>   Sainsbury'S Bank
>
>
> Which follows the definition here:
>
> https://www.postgresql.org/docs/14/functions-string.html
>
> initcap ( text ) → text
>
> Converts the first letter of each word to upper case and the rest to
> lower case. Words are sequences of alphanumeric characters separated by
> non-alphanumeric characters.
>
> Hi, Adrian Klaver,
>

It looks like that you replicated the error.

There must be a way to do the following.

a column contains a list of words.  Only the first letter of each word
should be capitalised.  INITCAP can not do that.  How to create a function
just to capitalised each word (substring) in a list of words/strings.  This
will be very useful and create great impact.

Regards,

David

Fault with initcap

[Shaozhong SHI]

I tried initcap and found a major problem with it.

Initcap of notemachine is NoteMachine.

Initcap of Sainsbury's Bank is Sainsbury'S bank.

This is not expected.

Anyway to get around this problem?

Regards,

David

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

[Shaozhong SHI]

Hello, Adrian Klaver,
What is the robust way to upgrade Pandas?
Regards,
David

On Monday, 4 October 2021, Adrian Klaver  wrote:

> On 10/4/21 9:20 AM, Shaozhong SHI wrote:
>
>> Hello, Adrian Klaver,
>>
>> Pandas version is 0.23.0.
>>
>
> The reason the below does not work is method did not show up until pandas
> 0.24.0.
>
>
>> I used the following code:
>>
>> def psql_insert_copy(table, conn, keys, data_iter):
>>  # gets a DBAPI connection that can provide a cursor
>>  dbapi_conn = conn.connection
>>  with dbapi_conn.cursor() as cur:
>>  s_buf = StringIO()
>>  writer = csv.writer(s_buf)
>>  writer.writerows(data_iter)
>>  s_buf.seek(0)
>>
>>  columns = ', '.join('"{}"'.format(k) for k in keys)
>>  if table.schema:
>>  table_name = '{}.{}'.format(table.schema, table.name <
>> http://table.name>)
>>  else:
>>  table_name = table.name <http://table.name>
>>
>>  sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
>>  table_name, columns)
>>  cur.copy_expert(sql=sql, file=s_buf)
>> engine = create_engine('postgresql+psycopg2://:5432/postgres')
>> try:
>>  df.to_sql('test1', engine, schema='public', if_exists='append',
>> index=False, method=psql_insert_copy)
>>
>> I could not find obvious reasons.
>>
>
>
>
>> Regards,
>>
>> David
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

[Shaozhong SHI]

Hello, Adrian Klaver,

Pandas version is 0.23.0.

I used the following code:

def psql_insert_copy(table, conn, keys, data_iter):
# gets a DBAPI connection that can provide a cursor
dbapi_conn = conn.connection
with dbapi_conn.cursor() as cur:
s_buf = StringIO()
writer = csv.writer(s_buf)
writer.writerows(data_iter)
s_buf.seek(0)

columns = ', '.join('"{}"'.format(k) for k in keys)
if table.schema:
table_name = '{}.{}'.format(table.schema, table.name)
else:
table_name = table.name

sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
table_name, columns)
cur.copy_expert(sql=sql, file=s_buf)

engine = create_engine('postgresql+psycopg2://:5432/postgres')
try:
df.to_sql('test1', engine, schema='public', if_exists='append',
index=False, method=psql_insert_copy)

I could not find obvious reasons.

Regards,

David

On Mon, 4 Oct 2021 at 17:06, Adrian Klaver 
wrote:

> On 10/4/21 8:44 AM, Shaozhong SHI wrote:
> >
> > Has anyone tested this one?
> > A Fast Method to Bulk Insert a Pandas DataFrame into Postgres · Ellis
> > Valentiner
> > <
> https://ellisvalentiner.com/post/a-fast-method-to-insert-a-pandas-dataframe-into-postgres/
> >
> >
> > I tried psql_insert_copy method, but I got the following error message.
> >
> > to_sql() got an unexpected keyword argument 'method'
> >
> >
> > Can anyone shed light on this?
>
> method is a legitimate keyword:
>
>
> https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html?highlight=to_sql#pandas.DataFrame.to_sql
>
> So there must be something about how you used it.
>
> Pandas version?
>
> The actual code you used when the error occurred?
>
>
> >
> >
> > Regards,
> >
> >
> > David
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Testing of a fast method to bulk insert a Pandas DataFrame into Postgres

[Shaozhong SHI]

Has anyone tested this one?
A Fast Method to Bulk Insert a Pandas DataFrame into Postgres · Ellis
Valentiner


I tried psql_insert_copy method, but I got the following error message.

to_sql() got an unexpected keyword argument 'method'


Can anyone shed light on this?


Regards,


David

How to set up temporary path for starting up psql in any folder?

[Shaozhong SHI]

How to set up temporary path for starting up psql in any folder?

I do not want to disrupt existing settings of paths.

Regards,

David

Re: PostgreSQL CHECK Constraint

[Shaozhong SHI]

Hi, Christian,
Brilliant!
Some years ago, I did something similar and saved problematic ones for data
collector.
Now, I am reviewing to see whether there be any elegant way to automate
reporting and giving feedback.
Regards,
David

On Sunday, 3 October 2021, Christian Ramseyer  wrote:

> > The reported error looks like this:
> >
> > postgres@dellstore ERROR:  new row for relation "test_customers"
> > violates check constraint "check_age"
> ..
> >
> > This errors appears in the serverlog which has many format and
> > forwarding options, you can read about them here:
>
> On 03.10.21 20:16, Shaozhong SHI wrote:
> > Hi， Christian,
> > That is interesting.  Can errors be captured and saved as data with
> > scripting?
>
> Yes that works quite the same, e.g. in Python you can do
>
> try:
> cur.execute("insert into test_customers (firstname, lastname, age)
> values ( %s, %s, %s)", ("Bobby", "Tables", 10))
> except psycopg2.errors.CheckViolation as e:
> print(f"That didn't work: {e.cursor.query} failed")
> print(f"{e.pgerror}")
>
>
> HTH
>

Re: PostgreSQL CHECK Constraint

[Shaozhong SHI]

Hi, Kirsten,
That sounds brilliant.
Are there any examples on the web these days?
Regards,
David

On Sunday, 3 October 2021, Karsten Hilbert  wrote:

> Am Sun, Oct 03, 2021 at 07:16:32PM +0100 schrieb Shaozhong SHI:
>
> > That is interesting.  Can errors be captured and saved as data with
> > scripting?
>
> Depends on what the script does.
>
> If the script runs (or is written in) Python the canonical PG
> driver (psycopg2/3) will give you such data.
>
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
>
>

Re: PostgreSQL CHECK Constraint

[Shaozhong SHI]

Hi， Christian,
That is interesting.  Can errors be captured and saved as data with
scripting?
Regards,
David

On Sunday, 3 October 2021, Christian Ramseyer  wrote:

>
>
> On 03.10.21 09:31, Shaozhong SHI wrote:
> >
> > Has anyone got experience with data quality checking, validation and
> > reporting within PostgreSQL?
> >
> > How best to use PostgreSQL CHECK Constraint for data quality checking,
> > validation and reporting?
> >
> > Can we report on errors in a detailed and specific way?  For instance,
> > can we produce report on specific issues of erroneous cells in which row
> > and etc.?
> >
>
> Yes that's all possible. Given a constraint like
>
> alter table test_customers
>add constraint check_age check (age >= 18);
>
>
> The reported error looks like this:
>
> postgres@dellstore ERROR:  new row for relation "test_customers"
> violates check constraint "check_age"
>
> postgres@dellstore DETAIL:  Failing row contains (1, Jimmy, Schmoe, 15).
>
> postgres@dellstore STATEMENT:  insert into test_customers (firstname,
> lastname, age) values ( 'Jimmy', 'Schmoe', 15);
>
> This errors appears in the serverlog which has many format and
> forwarding options, you can read about them here:
>
> https://www.postgresql.org/docs/current/runtime-config-logging.html
>
> Cheers
> Christian
>
> --
> Christian Ramseyer, netnea ag
> Network Management. Security. OpenSource.
> https://www.netnea.com
>
>

PostgreSQL CHECK Constraint

[Shaozhong SHI]

Has anyone got experience with data quality checking, validation and
reporting within PostgreSQL?

How best to use PostgreSQL CHECK Constraint for data quality checking,
validation and reporting?

Can we report on errors in a detailed and specific way?  For instance, can
we produce report on specific issues of erroneous cells in which row and
etc.?

Regards,

David

Is it possible to compare a long text string and fuzzy match only phrases contained in?

[Shaozhong SHI]

We are looking for working examples of comparing a long text string and
fuzzy-matching multiple words (namely, phrases) contained in.

Any such work examples?

Regards,

David

Postgres as a service for supporting common application users

[Shaozhong SHI]

Dear All,

To further promote the use of PostgreSQL as a service, we need working
examples for common application users to understand what to do, and to
prepare data resources for them.
These users may use PowerBI, Excel, graphical packages and more specialist
applications such as ArcGIS.

Are there any information on how best to store image data, and enable
application users to simply add images into their applications.

Looking forward to hearing from you.

Regards,

Shao

Re: Most effective and fast way to load few Tbyte of data from flat files into postgresql

[Shaozhong SHI]

On Tue, 25 Aug 2020 at 12:24, Peter J. Holzer  wrote:

> On 2020-08-24 21:17:36 +, Dirk Krautschick wrote:
> > what would be the fastest or most effective way to load few (5-10) TB
> > of data from flat files into a postgresql database, including some 1TB
> > tables and blobs?
> >
> > There is the copy command but there is no way for native parallelism,
> > right? I have found pg_bulkload but haven't tested it yet. As far I
> > can see EDB has its EDB*Loader as a commercial option.
>
> A single COPY isn't parallel, but you can run several of them in
> parallel (that's what pg_restore -j N does). So the total time may be
> dominated by your largest table (or I/O bandwidth).
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>


This topic is interesting.  Any examples for parallel copy?

Regards,

SS