Re: user privileges

ed access for a certain time. as the user is created with login privileges then what is the use such user ? See above. Where such users having no login privileges can be used ? Yes, you use it as a group. -- Charles Clavadetscher Spitzackerstrasse 9 CH - 8057 Zürich https://w

Re: About to know the info of foreign table reference used in any of call ,views,functions

the result of the first query. If you have overloaded functions you may need to extract more information to identify them correctly, such as the list of parameters. The documentation is very helpful in this context. Hope this helps. Regards Charles -- Charles Clavadetscher Spitzackerstrasse

Re: hstore each() function - returned order??

and stored on NIWA's IT systems Links: -- [1] https://www.niwa.co.nz [2] https://www.facebook.com/nzniwa [3] https://www.linkedin.com/company/niwa [4] https://twitter.com/niwa_nz [5] https://www.instagram.com/niwa_science -- Charles Clavadetscher Swiss PostgreSQL Users Group Treasurer

Re: Table sizes

Hello On 2020-09-30 15:54, luis.robe...@siscobra.com.br wrote: De: "Charles Clavadetscher" Para: "luis.roberto" Cc: "pgsql-general" Enviadas: Quarta-feira, 30 de setembro de 2020 10:46:39 Assunto: Re: Table sizes Hello On 2020-09-30 14:11, luis.robe...@si

Re: Table sizes

from pg_tables; You need to schema qualify the tables. Additionally, if you happen to have table names that have a mix of capital and non capital letters or contain other characters that might be problematic, you need to enclose the table name in double quotes. Regards Charles -- Charles Cl

Re: Slow SELECT

On 2020-05-26 11:10, Charles Clavadetscher wrote: Hello On 2020-05-26 10:38, Frank Millman wrote: On 2020-05-26 9:32 AM, Olivier Gautherot wrote: Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman <mailto:fr...@chagford.com>> wrote: Hi all I have a SELECT that ru

Re: Slow SELECT

Hello On 2020-05-26 10:38, Frank Millman wrote: On 2020-05-26 9:32 AM, Olivier Gautherot wrote: Hi Frank, On Tue, May 26, 2020 at 9:23 AM Frank Millman > wrote: Hi all I have a SELECT that runs over 5 times slower on PostgreSQL compared with Sql Serv

Re: FDW and RLS

Hello On 2020-05-25 15:50, Laurenz Albe wrote: On Fri, 2020-05-22 at 08:02 -0500, Ted Toth wrote: Will RLS be applied to data being retrieved via a FDW? ALTER FOREIGN TABLE rp_2019 ENABLE ROW LEVEL SECURITY; ERROR: "rp_2019" is not a table Doesn't look good. Yours, Laurenz Albe Actually

Re: How to get the OID of a view

Hello -- > On 22.05.2020, at 18:15, stan wrote: > > I am trying to write a query to return the names, and data types of all the > columns in a view. It has been pointed out to me that the best approach > would be using pg_catalog. OK, so I found pg_view, which I can get the names > o

Re: Exportacion por lotes

Hola --- Charles Clavadetscher Spitzackerstrasse 9 CH - 8057 Zürich Tel: +41-79-345 18 88 - > On 03.03.2020, at 20:21, Hernan Jesus Gonzalez Carmona > wrote: > > Estimados antes que todo me presento, mi nomb

Re: Help : Removal of leading spaces in all the columns of a table

, regexp_replace(upper(trim(txt2)),'[ ]{2,}', ' ', 'g')); Content of the table after the update. select * from test; txt | i | txt2 ---+---+--- ABC | 1 | DE F ABC | 2 | D E F A B C | 3 | D E F A B C | 4 | DEF (4 rows) Regards Charles On F

Re: Help : Removal of leading spaces in all the columns of a table

ces in the string. 'g' means global). The order of the calls is not really relevant for the result. Use UPDATE test SET txt instead of a select if you want to update your table at once. I usually prefer to see the result before I act on the data ;-) Bye Charles Thanks, Srikanth

Re: POLL: Adding transaction status to default psql prompt

Please answer +1 if you want or don't mind seeing transaction status by default in psql or -1 if you would prefer to keep the current default. +1 -- Charles Clavadetscher Swiss PostgreSQL Users Group Treasurer Spitzackerstrasse 9 CH - 8057 Zürich http://www.swisspu

Re: Trigger

information_schema.triggers; https://www.postgresql.org/docs/12/infoschema-triggers.html However you won't get there the function body. Regards Charles -- Charles Clavadetscher Swiss PostgreSQL Users Group Treasurer Spitzackerstrasse 9 CH - 8057 Zürich http://www.swisspu

Re: Variable constants ?

On 2019-08-16 14:50, Rich Shepard wrote: On Fri, 16 Aug 2019, Charles Clavadetscher wrote: Another way to keep a history is using a daterange instead of two columns for start and end date. Something like create table labor_rate_mult ( rate real primary_key, validity

Re: Variable constants ?

On 2019-08-15 23:27, Rich Shepard wrote: On Thu, 15 Aug 2019, stan wrote: I need to put a few bossiness constants, such as a labor rate multiplier in an application. I am adverse to hard coding these things. The best plan i have come up with so far is to store them in a table, which would hav

RE: Potentially undocumented behaviour change in Postgres 11 concerning OLD record in an after insert trigger

Hello From: Kristjan Tammekivi Sent: Freitag, 4. Januar 2019 11:46 To: pgsql-gene...@postgresql.org Subject: Potentially undocumented behaviour change in Postgres 11 concerning OLD record in an after insert trigger Hi, I've noticed a change in the behaviour in triggers / hstores in P

RE: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

> > On 19/11/18 3:27 μ.μ., Stephen Frost wrote: > > Greetings, > > > > * Charles Clavadetscher (clavadetsc...@swisspug.org) wrote: > >> > https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2018 > >> > >> As mentioned there, the s

RE: pgconf eu 2018 slides entry missing from https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

Hello Achilleas > -Original Message- > From: Achilleas Mantzios > Sent: Sonntag, 18. November 2018 12:23 > To: pgsql-gene...@postgresql.org > Subject: pgconf eu 2018 slides entry missing from > https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentatio > ns > > Hello, >

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

An addition On 01.10.2018 05:18:15, Charles Clavadetscher (SwissPUG) wrote: Hello On 01.10.2018 05:00:02, Carl Sverre wrote: Thank you for the detailed report Charles. I think you may be missing the “returning id” clause in the insert. Can you verify it works when you use “returning id

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

On Sun, Sep 30, 2018 at 7:57 PM Charles Clavadetscher (SwissPUG) mailto:clavadetsc...@swisspug.org]> wrote: Hello On 30.09.2018 23:31:32, Adrian Klaver mailto:adrian.kla...@aklaver.com]> wrote: On 9/30/18 1:13 PM, Carl Sverre wrote: > Thanks for the initial results. Can you check that you

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

is then I will have to confirm/deny my suspicion. > > Also yes, I forgot to add the policy names, sorry about that. > On Sun, Sep 30, 2018 at 1:34 AM Charles Clavadetscher (SwissPUG) > > wrote: > > Hello -- Adrian Klaver adrian.kla...@aklaver.com [Charles] : I also made the

Re: Postgres trigger side-effect is occurring out of order with row-level security select policy

Hello On 29.09.2018 20:24:45, Adrian Klaver wrote: On 9/28/18 11:35 PM, Carl Sverre wrote: > *Context* > I am using row-level security along with triggers to implement a pure > SQL RBAC implementation. While doing so I encountered a weird behavior > between INSERT triggers and SELECT row-level s

Re: Executing a Function with an INSERT INTO command fails

Hi > On 29.08.2018, at 19:45, TalGloz wrote: > > >> This is very strange, even if I comment all the loops in the function and >> leave only the INSERT INTO command the insert still doesn't happen. > > Now If I execute the function locally in a query window like this: > > SELECT public.seal_d

RE: Executing a Function with an INSERT INTO command fails

Hi > -Original Message- > From: TalGloz [mailto:glozman...@gmail.com] > Sent: Mittwoch, 29. August 2018 13:22 > To: pgsql-gene...@postgresql.org > Subject: RE: Executing a Function with an INSERT INTO command fails > > Charles Clavadetscher wrote > > Do

RE: Executing a Function with an INSERT INTO command fails

Hi > -Original Message- > From: TalGloz [mailto:glozman...@gmail.com] > Sent: Mittwoch, 29. August 2018 11:12 > To: pgsql-gene...@postgresql.org > Subject: Executing a Function with an INSERT INTO command fails > > Hello, > > I have this function that executes a C extention function in i

RE: extracting the sql for a function

Hello > -Original Message- > From: Paul Tilles [mailto:paul.til...@noaa.gov] > Sent: Donnerstag, 23. August 2018 19:18 > To: pgsql-gene...@postgresql.org > Subject: extracting the sql for a function > > Using postgres Version 9.5 > > I can extract the sql for a table using > > pg_dump

Re: JSONB filed with default JSON from a file

Hi --- Charles Clavadetscher Neugasse 84 CH - 8005 Zürich Tel: +41-79-345 18 88 - > On 13.08.2018, at 19:40, mrcasa bengaluru wrote: > > All, > > I'm new to JSONB datatype. We would like to store a nest

RE: How to revoke privileged from PostgreSQL's superuser

Hello From: bejita0...@yahoo.co.jp [mailto:bejita0...@yahoo.co.jp] Sent: Montag, 6. August 2018 11:49 To: pgsql-ad...@lists.postgresql.org; pgsql-general@lists.postgresql.org Subject: How to revoke privileged from PostgreSQL's superuser Hello, I am a newbie DBA. I have a request fo

RE: how to install pgcrypto

CCing list. From: Charles Clavadetscher [mailto:clavadetsc...@swisspug.org] Sent: Freitag, 3. August 2018 17:20 To: 'Ze Victor Harry' Subject: RE: how to install pgcrypto Hi From: Ze Victor Harry [mailto:assayab...@gmail.com] Sent: Freitag, 3. August 2018 17:11 T

RE: how to install pgcrypto

Hi From: Ze Victor Harry [mailto:assayab...@gmail.com] Sent: Freitag, 3. August 2018 10:54 To: pgsql-gene...@postgresql.org Subject: how to install pgcrypto hello again I have a small problem here can someone tell me briefly how to do it? I am getting this error

RE: Read only to schema

ials <https://www.packtpub.com/all?search=PostgreSQL+Administration+Essentials&availability_list%5BAvailable%5D=Available&offset=&rows=&sort=> &availability_list%5BAvailable%5D=Available&offset=&rows=&sort= On Fri, Jul 27, 2018 at 11:31 AM, Charles Clavadetsche

RE: Read only to schema

evoked CREATE (s. example in last mail). That means public still has USAGE on schema public and obviously you are on one of “any user”. I hope I could explain that somehow. If you still have questions just get back on the list. Bye Charles Best, Jacek sob., 14 lip 2018 o 08:23 C

RE: Read only to schema

An addition to my previous post (marked as [addition]). From: Charles Clavadetscher [mailto:clavadetsc...@swisspug.org] Sent: Samstag, 14. Juli 2018 08:23 To: 'Łukasz Jarych' ; pgsql-gene...@postgresql.org Subject: RE: Read only to schema Hello From: Łukasz Jarych [ma

RE: Read only to schema

Hello From: Łukasz Jarych [mailto:jarys...@gmail.com] Sent: Freitag, 13. Juli 2018 16:39 To: pgsql-gene...@postgresql.org >> PG-General Mailing List Subject: Re: Read only to schema I found something like this: CREATE ROLE readonly_user WITH LOGIN ENCRYPTED PASSWORD '

RE: Read only to schema

Hello From: Łukasz Jarych [mailto:jarys...@gmail.com] Sent: Freitag, 13. Juli 2018 07:00 To: pgsql-gene...@postgresql.org >> PG-General Mailing List Subject: Read only to schema Hi Guys, Yesterday i tried all day to figure out system to read only schemas. I want to : Here is

RE: How to remove elements from array .

Hi From: Brahmam Eswar [mailto:brahmam1...@gmail.com] Sent: Freitag, 6. Juli 2018 09:50 To: pgsql-general ; pgsql-hack...@postgresql.org Subject: How to remove elements from array . Hi , I tried to use array_remove to remove elements from an array but it's saying function doesn't exis

Re: Not able to update some rows in a table

> On 02.07.2018, at 17:59, Marco Fochesato wrote: > > >> >> I would suggest writing a self-contained script that creates the table, >> inserts a single record, and updates that record. Present that for >> consideration along with a description or capture of the results of running >> the s

Re: Not able to update some rows in a table

>> On 02.07.2018, at 17:59, Marco Fochesato wrote: >> >> >>> >>> I would suggest writing a self-contained script that creates the table, >>> inserts a single record, and updates that record. Present that for >>> consideration along with a description or capture of the results of running >>

RE: Question on disk contention

Hi Melvin From: Melvin Davidson [mailto:melvin6...@gmail.com] Sent: Donnerstag, 31. Mai 2018 15:53 To: Charles Clavadetscher Cc: pgsql-generallists.postgresql.org Subject: Re: Question on disk contention On Thu, May 31, 2018 at 1:13 AM, Charles Clavadetscher mailto:clavadetsc

Question on disk contention

Hi Melvin As an answer to a previous post you wrote: "Also, your main problem is that when you have two exact same queries executing at the same time, they will cause contention in the disk, and neither one will make much progress." Could you elaborate a little more on the meaning of "contentio

RE: RE: How do I select composite array element that satisfy specific conditions.

Hello From: a [mailto:372660...@qq.com] Sent: Mittwoch, 23. Mai 2018 14:23 To: Charles Clavadetscher ; pgsql-general Subject: Re: RE: How do I select composite array element that satisfy specific conditions. Thanks for your reply... Honestly I do not use java and don't r

RE: How do I select composite array element that satisfy specific conditions.

Hi > -Original Message- > From: a [mailto:372660...@qq.com] > Sent: Mittwoch, 23. Mai 2018 11:43 > To: pgsql-general > Subject: How do I select composite array element that satisfy specific > conditions. > > Hi, say if I have composite type and table > > create type A as( > x floa

RE: help to query json column

Hello > -Original Message- > From: Arup Rakshit [mailto:aruprakshit1...@outlook.com] > Sent: Mittwoch, 7. März 2018 05:41 > To: pgsql-general@lists.postgresql.org > Subject: help to query json column > > Hi, > > I would like to select only rows where signature has a non null value. My >

Re: How to avoid trailing zero (after decimal point) for numeric type column

Hello > On 28.02.2018, at 13:33, pkashimalla wrote: > > Hello Team, > > We have recently migrated our database from Oracle > And there are few of my tables with numeric column type. > > In below example > I did insertion from java program with below code snippet > > Double object = 10.0; > St

RE: Remove default privilege from DB

Hi From: Durumdara [mailto:durumd...@gmail.com] Sent: Donnerstag, 15. Februar 2018 12:41 To: Charles Clavadetscher Cc: Postgres General Subject: Re: Remove default privilege from DB Dear Charles! 2018-02-12 10:03 GMT+01:00 Charles Clavadetscher mailto:clavadetsc...@swisspug.org

Re: How do I get rid of database test-aria

Hi > On 13.02.2018, at 21:38, Sherman Willden wrote: > > development platform: HP Compaq 6710b > postgresql 9.6 > Operating System: Ubuntu 17.10 > > It probably doesn't matter since there is nothing in the database. I don't > remember how I created the database. It appears that postgresql doe

RE: Remove default privilege from DB

Hi From: Durumdara [mailto:durumd...@gmail.com] Sent: Montag, 12. Februar 2018 09:32 To: Postgres General Subject: Remove default privilege from DB Hello! I need to remove default privileges from a Database. After that some of them remains. Default access privileges

RE: Set role dynamically from proc

Hello From: Durumdara [mailto:durumd...@gmail.com] Sent: Mittwoch, 22. November 2017 14:56 To: pgsql-gene...@postgresql.org Subject: Set role dynamically from proc Hello! May you know the way how to set role dynamically. DO $$ DECLARE act_dbowner varchar(100); BEGIN