a very primitive question about division

[Martin Mueller]

I have a very primitive question about division to which I can’t find an 
obvious answer in the documentation.

Given two values defined as integers, how do I divide one by the other and get 
an answer with two decimals, e.g 3 /4 = 0.75.

This is very simple in mysql and seems to be oddly contorted in postgres. I 
spent half an hour on the Web and with the documentation but couldn’t find a 
simple example.

Probably my stupidity.

Re: a very primitive question about division

[Martin Mueller]

Thanks.  So  round(before1550/colfreq::numeric, 2)  produces the desired result.

The explanations and examples of string functions in the Postgres documentation 
are a model of clarity. The explanations and examples of basic arithmetic 
operations  are not.  There is room for improvement there.

From: "David G. Johnston" 
Date: Wednesday, March 7, 2018 at 11:23 AM
To: Martin Mueller 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: Re: a very primitive question about division

On Wed, Mar 7, 2018 at 10:21 AM, Martin Mueller 
mailto:martinmuel...@northwestern.edu>> wrote:
Given two values defined as integers, how do I divide one by the other and get 
an answer with two decimals, e.g 3 /4 = 0.75.

​Case one of them to numeric.

​select 3/4::numeric
​
​David J.​

Re: a very primitive question about division

[Martin Mueller]

In Table 9.5  the division of 9 by 4 is indeed described clearly. But there is 
no example of 4/9 and the different ways of formatting it as a decimal fraction 
with different options for rounding or a percentage.  Two or three added 
examples would make life easier for folks who have not progressed much beyond 
8th grade math.

From: "David G. Johnston" 
Date: Wednesday, March 7, 2018 at 11:43 AM
To: Martin Mueller 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: Re: a very primitive question about division

On Wed, Mar 7, 2018 at 10:30 AM, Martin Mueller 
mailto:martinmuel...@northwestern.edu>> wrote:
Thanks.  So  round(before1550/colfreq::numeric, 2)  produces the desired result.

The explanations and examples of string functions in the Postgres documentation 
are a model of clarity. The explanations and examples of basic arithmetic 
operations  are not.  There is room for improvement there.


Possibly.  Patches (or even just email suggestions - though the former are 
generally more apt to get applied) detailing specific improvements to make are 
welcome.

The description "division (integer division truncates the result)" seems 
reasonably clear - if you don't want the result truncated, and have two 
integers, you have to make one of the inputs a non-integer.  Of the various 
options I tend to choose numeric though others are possible.

David J.

Re: a very primitive question about division

[Martin Mueller]

I see the logic of that, but I also think that this is not how many users think 
when they want to know how to get results for 4/9.  Note that division is a 
great stumbling block in middle school.  They want to know how to divide four 
by nine and don’t want to think about division and formatting as separate 
operations to be looked up in separate chapters. Perhaps they should think that 
way, but they don’t, and as Adenauer, modern Germany’s first chancellor,  
observed: you have to take people as they come because there are no others.

At the least the table 9.5 in 9.3  could have an NB about the problem of 
decimals in division, together with an example of how to do 4/9 in different 
formats, and a reference to the fuller discussion.

From: "David G. Johnston" 
Date: Wednesday, March 7, 2018 at 12:11 PM
To: Martin Mueller 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: Re: a very primitive question about division

On Wed, Mar 7, 2018 at 10:56 AM, Martin Mueller 
mailto:martinmuel...@northwestern.edu>> wrote:
But there is no example of 4/9 and the different ways of formatting it as a 
decimal fraction with different options for rounding or a percentage.

​That would be the responsibility of the "Data Type Formatting Functions" 
chapter and the "to_char" function it describes.

David J.
​

computing z-scores

[Martin Mueller]

You construct a z-score for a set of values by subtracting the average from the 
value and dividing the result by the standard deviation. I know how to do this 
in a two-step procedure. First, I compute the average and standard deviation. 
In a second run I use the formula and apply it to each value. 

Is there a way of doing this in a single-step procedure or can you chain the 
two parts together in one query?  This goes beyond my SQL competence. 

Martin Mueller

two instances of postgres on the same machine?

[Martin Mueller]

I made a stupid mistake with a backup and would be grateful for some advice on 
how to extricate myself.

I created a dumpfile from a 10.3 postgres installation on a Mac and tried to 
restore it on another Mac 1,000 miles away, but forgot that that machine runs 
Postgres 9.6.

The command

/Applications/Postgres.app/Contents/Versions/9.6/bin/psql -U postgres  
earlyprint2 

Re: two instances of postgres on the same machine?

[Martin Mueller]

Many thanks for your generous help, and on a Sunday afternoon as well. 

This is a wonderful listserv, and I am deeply indebted to it. 

MM
On 7/15/18, 2:17 PM, "Tom Lane"  wrote:

    Martin Mueller  writes:
> I created a dumpfile from a 10.3 postgres installation on a Mac and tried 
to restore it on another Mac 1,000 miles away, but forgot that that machine 
runs Postgres 9.6.

OK ...

> The command
> /Applications/Postgres.app/Contents/Versions/9.6/bin/psql -U postgres  
earlyprint2  generated a long error list  like this:
> ERROR:  syntax error at or near "toc"
> LINE 1: toc.datspelling character varying(150) NOT NULL,
> ^

I gather from the ".tar" filename that you used -Ft dump format; if so,
you need to feed it to pg_restore not plain psql.  The errors you're
showing here look somewhat consistent with the theory that you got that
wrong, although other explanations are certainly possible.

> I can’t tell from the error list whether it’s  a matter of a corrupted
> backup file or (more probably) a result of 9.6 not reading  a 10.3 dump
> file.

While the latter is possible, I think that typically it would only happen
if the 10.x installation is using SQL DDL features that 9.6.x doesn't
have.  I would try doing what you're doing, but with the right process...

> If the latter, what would be the safest way of cleaning up this mess?
> There is a side of me that would prefer installing a 10.3 on the same
> machine in addition, but I’m not sure whether I can do this in the
> environment of the Postgres App. I access the data via Aqua Studio that
> looks for the server on port 5432, which seems to be a standard port.
> Is there a safe way of installing a second server via a different port?

You can certainly install multiple PG servers on one machine; most of the
developers have several different PG versions laying about.  What you need
for that is separate install directories, separate data directories, and
separate port numbers for each server.  This is easy to achieve if
building from source, but may be nigh impossible if you are installing
somebody else's packaging and they didn't make provisions for it.  I don't
know much about the Postgres App packaging so I can't offer any advice
there.  I also don't know how hard it is to get the Aqua client you
mention to talk to a nonstandard port number, though I'd guess that it's
possible.

regards, tom lane

Re: Code of Conduct plan

[Martin Mueller]

I have followed this list for a couple of years, have benefited several times 
from quick and helpful advice,  and wonder whether all this code of conduct 
stuff is a solution in search of a problem. Or, if there is a problem now and 
then, whether an elaborate code does a better job than reminding offenders that 
they’ve crossed a line marked by common decency or common courtesy. I think a 
list manager should have the right to expel repeat offenders. I doubt whether 
‘proceduralizing’ offences against common decency or common courtesy makes it 
easier to police what is always a tricky boundary.

It is possible to spend a lot of time and energy designing bureaucratic 
solution that in the end does little good.  My grandchildren were taught that 
“please and thank you sound so nice  manners are important, be polite” sung 
to the tune of Frère Jacques. They don’t always remember it,  but a longer poem 
wouldn’t help.


From: James Keener 
Date: Friday, September 14, 2018 at 7:52 AM
To: "pgsql-general@lists.postgresql.org" , 
Chris Travers , "i...@dataegret.com" 

Cc: Tom Lane , Stephen Frost , 
"pgsql-generallists.postgresql.org" , 
"pgsql-hack...@lists.postgresql.org" , 
"pgsql-advoc...@lists.postgresql.org" 
Subject: Re: Code of Conduct plan

I find a lot of neo-con/trumpian political stances moronic, short-sighted, and 
anti-intellectual and therefore consider them offensive, an affront on my way 
of life, and a stain on my country.

1) Can I report anyone holding such views and discussing them on a 3rd party 
forum?

2) Could I be reported for saying the above on a 3rd party forum?

Obviously the pg mailing list isn't a place for such discussion, but is being a 
member of this community a deal with the devil to give up my right to free 
speech elsewhere?

Jim
On September 14, 2018 6:10:47 AM EDT, Chris Travers  
wrote:

On Fri, Sep 14, 2018 at 11:45 AM Ilya Kosmodemiansky 
mailto:i...@dataegret.com>> wrote:
On Fri, Sep 14, 2018 at 10:31 AM, Chris Travers 
mailto:chris.trav...@gmail.com>> wrote:
> I really have to object to this addition:
> "This Code is meant to cover all interaction between community members,
> whether or not it takes place within 
> postgresql.org
>  infrastructure, so long
> as there is not another Code of Conduct that takes precedence (such as a
> conference's Code of Conduct)."
>
> That covers things like public twitter messages over live political
> controversies which might not be personally directed.   At least if one is
> going to go that route, one ought to *also* include a safe harbor for
> non-personally-directed discussions of philosophy, social issues, and
> politics.  Otherwise, I think this is asking for trouble.  See, for example,
> what happened with Opalgate and how this could be seen to encourage use of
> this to silence political controversies unrelated to PostgreSQL.

I think, this point has nothing to do with _correct_ discussions or
public tweets.

If one community member tweets publicly and in a way which abuses
other community members, it is obvious CoC violation. It is hard to
imagine healthy community if someone interacts with others  correctly
on the list or at a conference because the CoC stops him doing things
which he will do on private capacity to the same people when CoC
doesnt apply.

If someone reports CoC violation just because other community member's
_correct_ public tweet or whatsoever  expressed different
political/philosophical/religious views, this is a quite different
story. I suppose CoC committee and/or Core team in this case should
explain the reporter the purpose of CoC rather than automatically
enforce it.

So first, I think what the clause is trying to do is address cases where 
harassment targeting a particular community member takes place outside the 
infrastructure and frankly ensuring that the code of conduct applies in these 
cases is important and something I agree with.

However, let's look at problem cases:

"I am enough of a Marxist to see gender as a qualitative relationship to 
biological reproduction and maybe economic production too."

I can totally imagine someone arguing that such a tweet might be abusive, and 
certainly not "correct."

Or consider:

"The effort to push GLBT rights on family-business economies is nothing more 
than an effort at corporate neocolonialism."

Which would make the problem more clear.  Whether or not a comment like that 
occurring outside 
postgresql.org
 infrastructure wou

Re: Code of Conduct plan

[Martin Mueller]

On 9/14/18, 12:50 PM, "Joshua D. Drake"  wrote:

On 09/14/2018 07:41 AM, James Keener wrote:
> > Community is people who joined it
>
> We're not a "community."

I do not think you are going to get very many people on board with that 
argument. As anyone who knows me will attest I am one of the most 
contrarian members of this community but I still agree that it is a 
community.

JD


As Bill Clinton said in another context, "it all depends on the meaning of 
'community'".  'Community' is a very tricky word with uncertain boundaries and 
variable degrees of belonging to it.  Moreover, it's reciprocal: 'you' and the 
'community' may have different ideas of whether or how you belong. Rules in 
communities are usually tacit. You might almost want to say that if you need to 
write rules you no longer have a community.  Writing community rules is a very 
and probably hopeless endeavor.

For quite a while the word 'community' has been grossly overused and has often 
been invoked as a way of creating a sense of community where there is reason to 
doubt whether the thing is there in the first place. 

'Civil' and 'civility' are more modest words with more modest goals that are 
perhaps easier to capture in language. When it comes to a code of civil 
conduct, less is more. If you use more than the words of the ten commandments 
you almost certainly have gone too far. I have yet to see a posting on this 
list that would suggest an urgent need for trying to regulate what contributors 
say or how they say it.  





-- 
Command Prompt, Inc. || 
https://urldefense.proofpoint.com/v2/url?u=http-3A__the.postgres.company_&d=DwICaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=RJwS1VI8elhlnCutR_Pulg0oUzeSh5KpHQs0EJSdr04&s=3RBPPMk6HiBPEHYfzKDsP-DZxFvRs5NCYc9LKGXjpdE&e=
 || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: 
https://urldefense.proofpoint.com/v2/url?u=https-3A__postgresconf.org&d=DwICaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=RJwS1VI8elhlnCutR_Pulg0oUzeSh5KpHQs0EJSdr04&s=ZiPaHw5gfja9OJeMGlTHieS-paSoyTHYC35rTgkwv_U&e=
* Unless otherwise stated, opinions are my own.   *

Re: Code of Conduct plan

[Martin Mueller]

What counts as foul language has changed a great deal in the last two decades.  
You could always tie it to what is printable in the New York Times, but that 
too is changing. I could live with something like “Be considerate, and if you 
can’t be nice, be at least civil”.

From: Melvin Davidson 
Date: Saturday, September 15, 2018 at 11:12 AM
To: Tom Lane 
Cc: Bruce Momjian , Chris Travers , 
James Keener , Steve Litt , 
"pgsql-generallists.postgresql.org" 
Subject: Re: Code of Conduct plan

How about we just simplify the code of conduct to the following:
Any member in the various PostgreSQL lists is expected to maintain
respect to others and not use foul language. A variation from
the previous sentence shall be considered a violation of the CoC.

On Sat, Sep 15, 2018 at 11:51 AM Tom Lane 
mailto:t...@sss.pgh.pa.us>> wrote:
Bruce Momjian mailto:br...@momjian.us>> writes:
> There is a risk that if we adopt a CoC, and nothing happens, and the
> committee does nothing, that they will feel like a failure, and get
> involved when it was best they did nothing.  I think the CoC tries to
> address that, but nothing is perfect.

Yeah, a busybody CoC committee could do more harm than good.
The way the CoC tries to address that is that the committee can't
initiate action of its own accord: somebody has to bring it a complaint.

Of course, a member of the committee could go out and find a "problem"
and then file a complaint --- but then they'd have to recuse themselves
from dealing with that complaint, so there's an incentive not to.

regards, tom lane


--
Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!

Re: Code of Conduct plan

[Martin Mueller]

That is quite true: the very high quotient of helpful prose and very low 
quotient of inappropriate language is striking--much like the TEI list of which 
I long have been a member, and unlike the MySQL list, which has a non-trivial 
(though not serious)  boorish component. 

Which makes me say again "Where is the problem that needs solving?"

On 9/15/18, 11:32 AM, "Bruce Momjian"  wrote:

On Sat, Sep 15, 2018 at 04:24:38PM +, Martin Mueller wrote:
> What counts as foul language has changed a great deal in the last two 
decades. 
> You could always tie it to what is printable in the New York Times, but 
that
> too is changing. I could live with something like “Be considerate, and if 
you
> can’t be nice, be at least civil”.

I have to admit I am surprised how polite the language is here,
considering how crudely some other open source projects communicate.

-- 
  Bruce Momjian  
https://urldefense.proofpoint.com/v2/url?u=http-3A__momjian.us&d=DwIDaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=TJILWn2nTs3E72LB1XpPNrNBCTYdMYWcTUevA54MIgM&s=jP360tfk8zSE3PhzhCJ5PSD_h8HnzqLCs4jFe5nUddE&e=
  EnterpriseDB 
https://urldefense.proofpoint.com/v2/url?u=http-3A__enterprisedb.com&d=DwIDaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=TJILWn2nTs3E72LB1XpPNrNBCTYdMYWcTUevA54MIgM&s=EHp2yUxMzSrJsO0jCYJM4dq7m35j69Aec87OEBfXaP8&e=

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: Code of Conduct plan

[Martin Mueller]

As long as subscribers to the list or attendants at a conference do not violate 
explicit or implicit house rules, what business does Postgres have worrying 
about what they do or say elsewhere?  Some version of an 'all-of-life' clause 
may be appropriate to the Marines or  federal judges, but it strikes me as 
overreach for a technical listserv whose subject is a particular relational 
database. The overreach is dubious on both practical and theoretical grounds. 
"Stick to your knitting " or the KISS principle seem good advice in this 
context. 

On 9/16/18, 7:08 AM, "Stephen Cook"  wrote:

On 2018-09-16 00:00, Mark Kirkwood wrote:
> On 15/09/18 08:17, Tom Lane wrote:
>> Yeah, this.  The PG community is mostly nice people, AFAICT.  I'll be
>> astonished (and worried) if the CoC committee finds much to do.  We're
>> implementing this mostly to make newcomers to the project feel that
>> it's a safe space.
> 
> Agreed. However I think the all-of-life clause gives an open door to
> potential less than well intentioned new members joining up to extend a
> SJW agenda. So in fact the unintended consequence of this may be a
> *less* safe place for some existing members - unless all of their social
> media utterances are agreeable to the angry militant left.

This is my only concern, there are some very sensitive people out there
just looking for scandal / publicity. No reason to give them a larger
attack surface. Maybe that sounds paranoid but look around, there are
folks that want to spread the US culture war to every front, including
open source projects on the internet.

This sentence in the CoC should be worded to exclude things that are not
directed harassment when outside of the community spaces. For example,
some "incorrect opinion" on Twitter should have little bearing if it
wasn't meant as an "attack". Maybe for extreme cases there could be a
"hey you're making us look bad and scaring people away, chill with the
hate speech or leave" clause, but that should only apply if it is
someone whose name is publicly associated with Postgres and they are
saying really terrible things. I feel there is a big difference between
keeping it civil/safe in the lists and conferences, and making people
afraid to say anything controversial (in the USA) anywhere ever.

Maybe the way the committee is set up, it will handle this fairly. But
it's better to be explicit about it IMO, so as not to attract
professional complainers.


-- Stephen

metadata about creation and size of tables

[Martin Mueller]

I’ve looked at the documentation to find where to find some data that are very 
to find Mysql:  the creation and modification data of a table and the size of 
particular tables.

Where do I find an answer to the question “which is the last table I created” 
or “when did I last modify this table?”  In the data directory, tables seem to 
have numbers, but there doesn’t seem to be an easy mapping of those numbers to 
the table names.

I thumbed through the documentation, but didn’t see any heading that was likely 
to have that information.  Is there some where  a “table of tables” that lets 
you look up various metadata?

Drop a primary

[Martin Mueller]

I created a primary key with the following commands

Add id serial
Add primary key (id)

I cannot figure out from the documentation how to drop that column.

Finding out about the dates of table modification

[Martin Mueller]

 I've moved happily from MySQL to Postgres but miss one really good feature of 
MYSQL: the table of tables that let you use SQL queries to find out metadata 
about your table. Thus looking at the table of tables and sorting it by last 
change, lets you quickly look at the most recently modified table. Which is 
really useful if you have a bad memory, as I do, and can't remember the name of 
a particular table that I worked on last Wednesday. 

Are those capabilities hidden somewhere in Postgres?  There isn't an obvious 
section in the documentation. At least I can't find it. 

Martin Mueller
Professor emeritus of English and Classics
Northwestern University

Re: MS Access Frontend

[Martin Mueller]

Leaving aside the question of money, a frontend like AquaData Studio or the 
equivalent program by Jetbrains would be a much better solution. I used to work 
with Access a lot and quite liked it. But AquaData Studio is not any more 
difficult to learn and gives you access to everything Postgress can do. 

I haven't used the Jetbrains version, which is cheaper. 

On 11/30/19, 8:53 AM, "Adrian Klaver"  wrote:

On 11/30/19 3:15 AM, Tim Clarke wrote:
> On 29/11/2019 17:30, Adrian Klaver wrote:
>> On 11/29/19 9:23 AM, Jason L. Amerson wrote:
>>> I am trying to setup MS Access as a frontend so that it would be
>>> easier on my wife and children to interact with PostgreSQL. I looked
>>> online for some tutorials but the ones I found are out-date or only
>>> pick up after Access is connected to PostgreSQL. I was wondering if
>>> someone knew of some updated material that they could point me to or
>>> maybe walk me through it. I have used Access quite a bit years ago
>>> and things have changed since then. I know I must install the ODBC
>>> drivers, which I have already done. I have already setup the DSN and
>>> I clicked on test and it says everything is fine. I know that my next
>>> step has something to do with Linked Tables in Access, but I am not
>>> sure how to set it up. I guess that is where I start to need help.
>>> The client computers using the frontend will be running Windows 10
>>> and Office 365, both are updated to the latest versions.
>>
>> This might help:
>>
>> 
https://urldefense.proofpoint.com/v2/url?u=https-3A__support.office.com_en-2Dus_article_Manage-2Dlinked-2Dtables-2D1d9346d6-2D953d-2D4f85-2Da9ce-2D4caec2262797&d=DwIC-g&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=q2mFiQLD3Q0WGNcvV6_A-Jva78TI-_O-TJQkuVHqU_Y&s=n5gUTTvGiefsNhxsv4WNKTOok9pQSRe9TVdcXwWPjbI&e=
 
>>
>>
>>
>>>
>>> Thank you,
>>>
>>> Jason L. Amerson
>>>
>>
>>
> That will help you manage once you have created some linked tables, but
> to create them:

Which is covered in the above:


https://urldefense.proofpoint.com/v2/url?u=https-3A__support.office.com_en-2Dus_article_Manage-2Dlinked-2Dtables-2D1d9346d6-2D953d-2D4f85-2Da9ce-2D4caec2262797-23bkmk-5Fadd&d=DwIC-g&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=q2mFiQLD3Q0WGNcvV6_A-Jva78TI-_O-TJQkuVHqU_Y&s=pDhZ2CyGXAtJ2gOO7qDxiTFYvLGWbW1y-b8KRfDaK5E&e=
 

> 
> 1) Click "External Data" from the top Access menu, then "New data
> source" on the left
> 
> 2) Pick "From other sources" at the bottom and as you correctly
> identified "ODBC Database".
> 
> 3) From the dialogue box, change the default of the two options to the
> bottom one "Linkby creating linked table"
> 
> 4) Next pick your created DSN from the "Select data source" dialog
> 
> 5) You should then see the list of tables etc from your Postgres database
> 
> Troubleshooting; make sure you have an ODBC 32 or 64 bit version
> matching the 32 or 64 bit MS Access installed.
> 
> If you use any security at the MS Access level you will need to set up a
> simultaneously shared central system.mdw file in some repository. Don't
> try to get more than 6-7 people using this at the same time, it breaks.
> 
> Good luck.
> 
> 
> --
> Tim Clarke
> IT Director
> Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420
> 
> 
> 
> Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | 
Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 
503 2848
> Web: 
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.manifest.co.uk_&d=DwIC-g&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=q2mFiQLD3Q0WGNcvV6_A-Jva78TI-_O-TJQkuVHqU_Y&s=yyQ5fbd8EgtdPmHpuM9zaCn-ZVfurKRUUMinpN3JQuI&e=
 
> 
> 
> 
> Minerva Analytics Ltd - A Solactive Company
> 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United 
Kingdom
> 
> 
> 
> Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here 
https://urldefense.proofpoint.com/v2/url?u=https-3A__www.manifest.co.uk_legal_&d=DwIC-g&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=q2mFiQLD3Q0WGNcvV6_A-Jva78TI-_O-TJQkuVHqU_Y&s

Re: Oracle vs. PostgreSQL - a comment on Mysql

[Martin Mueller]

On the topic of what other databases do better: I much prefer Postgres to Mysql 
because it has better string functions and better as well as very courteous 
error messages. But MySQL has one feature that sometimes makes me want to 
return it: it stores the most important metadata about tables in a Mysql table 
that can be queried as if it were just another table.  That is a really 
feature. I makes it very easy to look for a table that you edited most 
recently, including a lot of other things.

Why doesn’t Postgres have that feature? Or is there a different and equally 
easy way of getting at these things that I am just missing?

From: Andreas Joseph Krogh 
Date: Wednesday, June 3, 2020 at 12:54 PM
To: Chris Travers 
Cc: "pgsql-generallists.postgresql.org" 
Subject: Re: Oracle vs. PostgreSQL - a comment

På onsdag 03. juni 2020 kl. 20:07:24, skrev Chris Travers 
mailto:chris.trav...@gmail.com>>:
[...]

Regardless of what Oracle does, I agree this would be a huge step in the right 
direction for pg-DBAs.
I have absolutely no clue about how much work is required etc., but I think 
it's kind of strange that no companies have invested in making this happen.

I manage database clusters where the number of databases is a reason not to do 
logical replication based upgrades, where pg_upgrade is far preferred instead.

If this were to be the case, I would be very concerned that a bunch of things 
would have to change:
1.  Shared catalogs would have txid problems unless you stay with global txids 
and then how do local wal streams work there?
2.  Possibility that suddenly streaming replication has the possibility of 
different databases having different amounts of lag
3.  Problems with io management on WAL on high throughput systems (I have 
systems where a db cluster generates 10-20TB of WAL per day)

So I am not at all sure this would be a step in the right direction or worth 
the work.

I agree these are all technical issues, but nevertheless - "implementation 
details", which DBAs don't care about. What's important from a DBA's 
perspective is not whether WAL is cluster-wide or database-wide, but whether 
it's possible to manage backups/PITR/restores of individual databases in a more 
convenient matter, which other RDBMS-vendors seem to provide.

I love PG, have been using it professionally since 6.5, and our company depends 
on it, but there are things other RDBMS-vendors do better...

--
Andreas Joseph Krogh

Re: editable spreadsheet style interface

[Martin Mueller]

I have used Aqua Data Studio for several years. Jetbrains recently released a 
similar product. Academic licensing is affordable (~ $200 a year) and very 
cheap if considered in terms of the time it saves you.

From: David Gauthier 
Date: Tuesday, October 30, 2018 at 2:06 PM
To: "pgsql-gene...@postgresql.org" 
Subject: editable spreadsheet style interface

I think I know the answer to this one but I'll ask anyway...

Is there a spreadsheet style interface to a PG DB where users can...
- lock records
- edit records
- submit changes (transaction)

Is there any after-market tool for PG that does something like this ?

simple division

[Martin Mueller]

I have asked this question before and apologize for not remembering it.  How do 
you do simple division in postgres and get 10/4 with decimals?

This involves cast and numeric in odd ways that are not well explained in the 
documentation. For instance, you’d expect an example in the Mathematical 
Functions. But there isn’t.

The documentation of string functions is exemplary. The documentation of 
mathematical less so. Remember that it may be used by folks like me whose math 
is shaky. The MySQL documentation is better on this simple operation.


-
Martin Mueller
Professor emeritus of English and Classics
Northwestern University

Re: simple division

[Martin Mueller]

I didn't formulate my question properly, because the query went like
 "select alldefects /wordcount" 
where alldefects and wordcount are integers.   But none of the different ways 
of putting the double colon seemed to work. 

The Postgres notation of this simple procedure is very unintuitive. I haven't 
been able to remember several times, and most people think of me as a person 
with a reasonably good memory. There is no obvious place in the documentation 
to look this up.


On 12/4/18, 2:45 PM, "David G. Johnston"  wrote:

On Tue, Dec 4, 2018 at 1:38 PM Joshua D. Drake  
wrote:
> I may be misunderstanding the question but:
Indeed...
> select cast(x/y as numeric(10,4));

Your answer is 2.0 instead of the correct 2.5 - you need to cast
before the division, not after.

David J.

Re: simple division

[Martin Mueller]

It worked, and I must have done something wrong. I'm probably not the only 
person who would find something like the following helpful:


division (integer division truncates the result)10/33
division (with decimal results) 10/3::numeric   3.
division (rounded)  round(10/3::numeric, 2) 3.33

From an end user's the question "how do I divide two integers and limit the 
number of decimals" is surely a common one. And  if you look it up somewhere, 
division is probably the search word.  Now you could argue that the user should 
already know about formatting and rounding.  But some don't.

If you wanted to look up a rare wordform in a famous early 20th century 
dictionary of Old English, you had to know the root form of the word. If you 
already knew the root form, there is a good chance that you didn't need to look 
it up in the first place. If you didn't know the root form, the dictionary was 
no use. 

In this, single stop shopping for the three most common problems of simple 
division makes life easier for users.







On 12/4/18, 3:06 PM, "David G. Johnston"  wrote:

On Tue, Dec 4, 2018 at 1:57 PM Martin Mueller
 wrote:
>
> I didn't formulate my question properly, because the query went like
>  "select alldefects /wordcount"
> where alldefects and wordcount are integers.   But none of the different 
ways of putting the double colon seemed to work.

IDK...the first thing that came to mind was to just stick it at the
end of the expression:

select x/y::numeric from (values (10,4)) vals (x,y)

And it worked...

If you want to propose a concrete documentation patch more power to
you but this doesn't come up enough to think that what we have is
materially deficient.  I'm sorry you are having trouble with it but
the lists do provide quick and customized answers for situations like
this.

David J.

Re: simple division

[Martin Mueller]

I take the point that two decades of backward compatibility should and will 
win. That said,  it's an easy enough thing to right the balance for novices and 
put in a really obvious place in the documentation what you should do if you 
want to divide two integers and get the results with the number of decimals of 
your choice. I made one suggestion how this could be done. A better way might 
be a short paragraph like

A note on division:  if you divide two constants or variables defined as 
integers, the default will be an integer. If you want the result with decimals, 
add "::numeric".  If you want to limit the decimals, use the round() function:
Select 10/3:3
Select 10/3::numeric3.3
Round(select 10/3::numeric, 3)  3.333
For more detail see the sections on ...





`
On 12/5/18, 9:23 AM, "Tom Lane"  wrote:

Geoff Winkless  writes:
> IMO it's fundamentally broken that SQL doesn't cast the result of a
> divide into a numeric value - the potential for unexpected errors
> creeping into calculations is huge; however that's the standard and
> no-one's going to change it now.
> Having said that it's worth noting that those in the Other Place think
> that it's broken enough to go against the standard (they have a DIV b
> for integer divide and a/b for float).

Well, this isn't really blame-able on the SQL standard; it's a
Postgres-ism.  What the spec says (in SQL99, 6.26 ) is

 1) If the declared type of both operands of a dyadic arithmetic
operator is exact numeric, then the declared type of the
result is exact numeric, with precision and scale determined
as follows:

a) Let S1 and S2 be the scale of the first and second operands
  respectively.

b) The precision of the result of addition and subtraction is
  implementation-defined, and the scale is the maximum of S1
  and S2.

c) The precision of the result of multiplication is
  implementation-defined, and the scale is S1 + S2.

d) The precision and scale of the result of division is
  implementation-defined.

 2) If the declared type of either operand of a dyadic arithmetic
operator is approximate numeric, then the declared type of the
result is approximate numeric. The precision of the result is
implementation-defined.

Postgres' integer types map onto the standard as exact numerics with
scale 0.  (The precision aspect is a bit squishy, since their maximum
values aren't powers of 10, but let's disregard that.)  Postgres'
integer division operator meets the spec with the stipulation that
the "implementation-defined" scale of the result is 0.  Other SQL
implementations can and do define that differently --- if they even
have an "integer" data type, which some do not.

Anyway, the bottom line here is that we're balancing surprise factor
for novices against twenty-plus years of backwards compatibility,
and the latter is going to win.

regards, tom lane

crosstab function

[Martin Mueller]

I run Postgres 10.5.  I understand that there is something called tablefunc and 
it includes a crosstab function. On Stack Overflow I learn that you import this 
function. But from where and how?  The Postgres documentation is quite clear 
and intelligible to a retired English professor like me, but there is nothing 
in the Postgres documentation about how to do the import, and a search for 
‘import modules’ yields nothing.

I tried to emulate a Stack overflow query that does what I want to do, but got 
an error message saying that the crosstab function doesn’t exist. I tried

CREATE EXTENSION IF NOT EXISTS tablefunc;

but it did nothing. It doesn’t seem to work  as import statements in Python do
-
Martin Mueller
Professor emeritus of English and Classics
Northwestern University

Re: crosstab function

[Martin Mueller]

Thank you for the prompt and clear answer. I work with a Mac (OS 10.4.3) and I 
used the PostgresApp to install it. I am a very primitive user of Postgres and 
think of it as Microsoft Access on Steroids. I access it via Aqua Data, Studio, 
but I don't think that makes any difference

On 2/26/19, 8:04 PM, "Adrian Klaver"  wrote:

On 2/26/19 5:51 PM, Martin Mueller wrote:
> I run Postgres 10.5.  I understand that there is something called 
> tablefunc and it includes a crosstab function. On Stack Overflow I learn 
> that you import this function. But from where and how?  The Postgres 
> documentation is quite clear and intelligible to a retired English 
> professor like me, but there is nothing in the Postgres documentation 
> about how to do the import, and a search for ‘import modules’ yields 
> nothing.

tablefunc is a contrib module so we need to know something information 
about your Postgres setup:

1) Your OS ?

2) How did you install Postgres?

> 
> I tried to emulate a Stack overflow query that does what I want to do, 
> but got an error message saying that the crosstab function doesn’t 
> exist. I tried
> 
> CREATEEXTENSION IFNOTEXISTStablefunc;
> 
> but it did nothing. It doesn’t seem to work  as import statements in 
> Python do

The above depends on the module code actually existing where CREATE 
EXTENSION can find it. Whether the module exists and where it exists 
depends on the answers to the questions above.

> 
> -
> 
> Martin Mueller
> Professor emeritus of English and Classics
> 
> Northwestern University
> 


-- 
Adrian Klaver
adrian.kla...@aklaver.com

a simple-minded question about updating

[Martin Mueller]

I work with Postgres and wonder whether for my purposes there is a good-enough 
reason to update one of these days.

I’m an editor working with some 60,000 Early Modern texts, many of them in need 
of some editorial attention. The texts are XM encoded documents. Each word is 
wrapped in a  element with attributes for various linguistic metadata. 
Typically a type of error occurs several or many times, and at the margins they 
need individual attention. I use Python scripts to extract stuff from the main 
corpus—sometimes dozens, sometimes thousands or millions—turn them into keyword 
in contexts and import them into Postgres. I basically use Postgres as a giant 
spreadsheet.  Its excellent string-handling routines make it relatively easy to 
to perform search and sort operations that identify tokens in need of 
correction. Once they corrections are made in Postgres—typically as batch 
updates-- I move them as a data frame into Python, and from Python I move them 
back into the texts.

I do this on a recent Mac with 64 GB of memory and a 6 cor i& processor.  I use 
Data Studio as an editing interface.

Unless a more recent version of Postgress has additional string handling 
routines, or indexing routines that speed up working with tables with rows in 
the low millions, or other features that are likely to speed up operations, I 
don’t see any reasons to update.

I could imagine a table that has up to 40 million rows.  That would be pretty 
sluggish on my current equipment, which handles up to 10 million rows quite 
comfortably.

A I right in thinking that given my tasks and equipment it would be a waste of 
time to update? Or is there something I’m missing?

Martin Mueller
Professor emeritus of English and Classiccs
Northwestern University

Re: a simple-minded question about updating

[Martin Mueller]

I currently work with Postgres 13. I forgot to mention that. From which I 
gather that around version 18 it would be time to upgrade. But in the interim 
I’d be OK.
Right?

From: Adrian Klaver 
Date: Thursday, May 18, 2023 at 11:21 PM
To: Martin Mueller , 
pgsql-general@lists.postgresql.org 
Subject: Re: a simple-minded question about updating
On 5/18/23 21:08, Martin Mueller wrote:
> I work with Postgres and wonder whether for my purposes there is a
> good-enough reason to update one of these days.

Since you have not mentioned the Postgres version you are on now, there
is really no definitive way to answer this.

Though as a rule keeping up with minor updates to whatever major version
you running is a good idea. Should be noted that at some point ~5 years
from a version's initial release the minor updates will stop. That is
when community support ends. Then the answer to any problem you have
will start with; you should upgrade.

>
> Martin Mueller
>
> Professor emeritus of English and Classiccs
>
> Northwestern University
>

--
Adrian Klaver
adrian.kla...@aklaver.com

a really dumb password question

[Martin Mueller]

I have a very stupid password question. I don’t know whether a postgres 
database on my Mac has a pass word or not.

I access the database via the Aqua Data Studio frontend as the user postgres. I 
don’t think I ever added a password, and on the authentication panel the 
password box is empty but the Remember Password box is checked.

However on the user panel there is a sequence of black dots for the Password 
and Password confirmation. This suggests that I did set a password, but I don’t 
remember what it is.

How can I undo the damage? Is there some super user way in which I can undo the 
current password and set a new one. The database itself has several hundred 
tables, and I need to find a way to fix my stupid error.

I have a dump of the database.  I don’t know whether that is subject to the 
password or not. If it isn’t, I suppose I could start over from scratch.

I’ll be grateful for advice.

Martin Muelleer
Professor emeritus of English and Classics
Northwestern Univerrsity

update from 13 to16

[Martin Mueller]

I am thinking of upgrading from version 13 to version 16.

I think I can do this by leaving the data alone and just replacing the 
software.  My data are extremely simple and consist of several hundred  tables, 
some with close to ten million records.  But their structure is simple.  I 
access the data via Aqua Data Studio, as if they were very large spreadsheets.

Is that a correct analysis?  I just want to make sure.

Martin Mueller
Professor  emeritus of English and Classsics
Northwestern University

A simple question about text fields

[Martin Mueller]

Are there performance issues with the choice of 'text' vs. varchar and some 
character limit?  For instance, if I have a table with ten million records and 
text fields that may range in length from 15 to 150, can I expect a measurable 
improvement in response time for using varchar(150) or will textdo just or 
nearly as well. 

If the latter is the case, using text across the board is a simpler choice  

a very naive question about table names in Postgres

[Martin Mueller]

I have a very naïve question and don’t know where tp  look for the answer.  I 
use Postgres via AquaData Studio as a giant spreadsheet.  I create tables and 
give them names. I see the table names  and know how to manipulate them with 
simple SQL queries.

I know that on my Mac the tables are  kept in the  data directory 
/Users/martinmueller/Library/Application Support/Postgres/var-13.  If I go 
there I see that internally the tables have numbers.  Somewhere in the system 
there must be a table that maps the name I gave to table X  to the numerical  
inside the database.

Where is that mapping and how can I query it?  On what page of the 
documentation do I find the answer?

I much prefer Postgres to Mysql for  a variety of reasons, but mostly for its 
elegant string functions. But in Mysql it seems to be much easier to keep track 
of tables.

Martin Mueller
Professor emeritus of English and Classics
Northwestern University

a question about oddities in the data directory

[Martin Mueller]

 Apologies if I asked this question before.

I’m a newcomer to Postgres, having migrated from Mysql.  Information about 
tables seems harder to get at in Postgres. That data directory on my machine is 
suspiciously large—well over 100 GB.  The directory Postgres/var-9.5/base/ 
contains a number of subdirectories with Arabic numerals. Directory 16385 has a 
subdirectory 17463 with a size of 1.07 GB. But there are also 17 subdirectories 
with names from 17463.1 to 17.463.17.  There are also other entries with 
similar forms of duplication and suspiciously identical file sizes of 1.07GB.

Is this normal behavior?  Where in the postgres documentation do I read up on 
this? Postgres strikes me as superior to MySQl, especially with regard to 
string functions and regular expressions, but it’s harder to look under the 
hood. How, for instance, do I figure out what number corresponds to the table 
that I know as ‘earlyprinttuples

With thanks in advance for any advice

Martin Mueller
Professor emeritus of English and Classics

a back up question

[Martin Mueller]

Are there rules for thumb for deciding when you can dump a whole database and 
when you’d be better off dumping groups of tables? I have a database that has 
around 100 tables, some of them quite large, and right now the data directory 
is well over 100GB. My hunch is that I should divide and conquer, but I don’t 
have a clear sense of what counts as  “too big” these days. Nor do I have a 
clear sense of whether the constraints have to do with overall size, the number 
of tables, or machine memory (my machine has 32GB of memory).

Is 10GB a good practical limit to keep in mind?

Re: a back up question

[Martin Mueller]

Time is not really a problem for me, if we talk about hours rather than days.  
On a roughly comparable machine I’ve made backups of databases less than 10 GB, 
and it was a matter of minutes.  But I know that there are scale problems. 
Sometimes programs just hang if the data are beyond some size.  Is that likely 
in Postgres if you go from ~ 10 GB to ~100 GB?  There isn’t any interdependence 
among my tables beyond  queries I construct on the fly, because I use the 
database in a single user environment

From: "David G. Johnston" 
Date: Tuesday, December 5, 2017 at 3:59 PM
To: Martin Mueller 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: Re: a back up question

On Tue, Dec 5, 2017 at 2:52 PM, Martin Mueller 
mailto:martinmuel...@northwestern.edu>> wrote:
Are there rules for thumb for deciding when you can dump a whole database and 
when you’d be better off dumping groups of tables? I have a database that has 
around 100 tables, some of them quite large, and right now the data directory 
is well over 100GB. My hunch is that I should divide and conquer, but I don’t 
have a clear sense of what counts as  “too big” these days. Nor do I have a 
clear sense of whether the constraints have to do with overall size, the number 
of tables, or machine memory (my machine has 32GB of memory).

Is 10GB a good practical limit to keep in mind?


​I'd say the rule-of-thumb is if you have to "divide-and-conquer" you should 
use non-pg_dump based backup solutions.  Too big is usually measured in units 
of time, not memory.​

Any ability to partition your backups into discrete chunks is going to be very 
specific to your personal setup.  Restoring such a monster without constraint 
violations is something I'd be VERY worried about.

David J.

Re: a back up question

[Martin Mueller]

On 12/6/17, 4:39 AM, "karsten.hilb...@gmx.net"  wrote:

On Tue, Dec 05, 2017 at 09:52:28PM +0000, Martin Mueller wrote:

> Are there rules for thumb for deciding when you can dump a
> whole database and when you’d be better off dumping groups of
> tables?

It seems to me we'd have to define the objective of "dumping" first ?

Regards,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


The objective is to create a  backup from which I can restore any or all tables 
in the event of a crash. In my case,  I use Postgres for my own scholarly 
purposes. Publications of whatever kind are not directly made public via the 
database. I am my only customer, and a service interruption, while a nuisance 
to me, does not create a crisis for others. I don’t want to lose my work, but a 
service interruption of a day or a week is no big deal. 

rookie question about upgrade from 13 to 17

[Martin Mueller]

I would like to update my current version of Postgres13 on a Mac mini to the 
latest version, but I can’ t find anything in the general document that 
explains the procedure in terms readily understand by retired professors of 
English…

I understand that there are three items involved:

  1.  The installed instance of version 13
  2.  The not yet installed instance of version 17
  3.  The tables (several hundred and adding up to 30 GB when  backed  with 
pg_dump)

I also understand that there is a pg_upgrade command that picks up the old 
tables and presents them to the later version in a somewhat different format

But I don’t understand how the different steps in the upgrade process interact. 
What are the steps and in what order  that wil

  1.  Install version 17
  2.  Upgrade the data so that they will in version 17
  3.  Get rid of the no longer needed version 13

There is a Postgres app for the Mac, but upgrading from one version  to another 
doesn’t seem to be part of it.

I use postgres exclusively in a single user fashion with a GUI front end, as if 
it were a more powerful version of Microsoft Access.

I’ll gladly volunteer to write up the process in English intelligible to others 
like me, and there may be some.

With thanks in advance


Martin Mueller
Professor emeritus of English and Classics
Northwestern University

an error message that I don't understand

[Martin Mueller]

I get this error message:

table tid from new index tuple (32586,21) overlaps with invalid duplicate tuple 
at offset 120 of block 4398 in index "aacorrections_tcpreading_idx"

from an update query of the
update table 1 set columnx = ‘yes’
where table1.xmlid in (select xmlid from table2)

I have no idea what is going on or how to deal with it and will be grateful for 
advice