Re: alter system appending to a value

I would do smth like: SELECT format( 'ALTER SYSTEM SET shared_preload_libraries = %L;', setting ) FROM pg_settings WHERE name = 'shared_preload_libraries' \gexec Of course, you should add new value to the existing setting, making sure there are no duplicates and the format is correct. -- Victor Yegorov

Re: change JSON serialization for BIGINT?

> But I want it to do this (note that "500" is quoted): > > select row_to_json(row(500::bigint)); > row_to_json > - > {"f1":"500"} > Will this work? select row_to_json(row(500::text)); -- Victor Yegorov

Re: Unexpected results from CALL and AUTOCOMMIT=off

eems not to. I assume there's smth to do with implicit BEGIN issued in non-AUTOCOMMIT mode and non-atomic DO block behaviour. -- Victor Yegorov

Unexpected results from CALL and AUTOCOMMIT=off

f_print_x() >> x=0 DO I don't understand why CALL statement is not seeing an updated record. With AUTOCOMMIT=on, all goes as expected. I tried to examine snapshots and xids (commented lines), but they're always the same. Can you explain this behavior, please? Is it expected? -- Victor Yegorov

scalar plpgsql functions and their stability flags

Good day experts... Question on scalar plpgsql functions stability flags (immutable, stable) regarding how it works in sql queries. It is clear that for immutable/stable functions with constant parameters, query planner could/should calculate value in a parse time and use it directly in query, o

Re: right way of using case-expressions in plpgsql functions

Thank you. I'll take it. пн, 16 окт. 2023 г. в 00:20, Ron : > On 10/15/23 11:19, Victor Dobrovolsky wrote: > > [snip] > > The documentation states that after some executions of such functions the > plan should become generic. > What is a generic plan for such a

Re: right way of using case-expressions in plpgsql functions

of the case-expressions in particular. пн, 16 окт. 2023 г. в 00:15, Tom Lane : > Victor Dobrovolsky writes: > > From that - "short and dirty translation" - point of view - should I > prefer > > to divide that > > $$ > > Select > > case $1 > >

Re: right way of using case-expressions in plpgsql functions

First of all, thanks everyone for the answers. вс, 15 окт. 2023 г. в 20:08, Tom Lane : > "David G. Johnston" writes: > > On Sunday, October 15, 2023, Victor Dobrovolsky > > wrote: > >> select (case when px is not null then pf = px > >> else pf is

right way of using case-expressions in plpgsql functions

I understood from documentation that case expression can be 1) optimized by planner 2) may compute its subexpressions in advance, in case of presence aggregation functions in them, for example. The question is - how it is combined with generic prepared plans in pl/pgsql. How can I deduct - when us

Re: Is there a good way to handle sum types (or tagged unions) in PostgreSQL?

On Fri, 19 May 2023 at 12:44, Dominique Devienne wrote: > On Thu, May 18, 2023 at 2:28 PM Victor Nordam Suadicani < > v.n.suadic...@gmail.com> wrote: > >> Is there any nice way to handle sum types (aka tagged unions) in a >> PostgreSQL database? [...] >> >

Re: Is there a good way to handle sum types (or tagged unions) in PostgreSQL?

ata types, this feels like a "missing link" in the type system. I'm not sure why SQL or the underlying relational model has never addressed this deficiency. Would greatly appreciate any insight anyone may have. On Thu, 18 May 2023 at 16:35, Adrian Klaver wrote: > On 5/18/23 05:

Is there a good way to handle sum types (or tagged unions) in PostgreSQL?

be extended with capabilities for this? I have no idea how this would be done in practice though. Perhaps SQL itself is just unsuited for data of this kind? I don't really see why it should be though. Thanks, Victor Nordam Suadicani

Re: CSV From Oracle with timestamp column getting errors

I:SS" ALTER SESSION SET nls_date_format='-MM-DD HH24:MI:SS'; Otherwise, you have to load this CSV file in a table, that has `text` type for the column and do a post-processing, smth like: INSERT INTO permanent_tab SELECT *, to_timestamp(col, 'DD-MON-YY HH12.MI.SS.S AM') FROM temp_table; Hope this helps. -- Victor Yegorov

Re: Code of Conduct: Russian Translation for Review

сб, 27 февр. 2021 г. в 01:51, Stacey Haysler : > If you have any comments or suggestions for the translation, please bring > them to our attention no later than 5:00 PM PST on Friday, March 5, 2021. > Greetings. I looked through the text and made some comments. -- Victo

Re: Performance penalty during logical postgres replication

witch, any remaining entries can be moved from previous to the live table (shouldn't be necessary if switch is done properly, although might be tricky in a presence of a long transactions) - previous table is TRUNCATEd In your case, you can do `VACUUM FULL` between replicating each batch of tables. -- Victor Yegorov

Query to retrieve the index columns when a function is used.

Hello I'm testing on Postgresql 12.1 and I have a index like this: "check_dates_gist" EXCLUDE USING gist (id_test1 WITH =, id_test2 WITH =, tsrange(valid_from::timestamp without time zone, valid_to::timestamp without time zone) WITH &&) When I run this query: "select pc.relname, pi.indisuni

Re[2]: Enabling extensions on a compiled instance of postgresql 12.1

It works. Thank you, thank you, thank you. -- Original Message -- From: "Julien Rouhaud" To: "Sterpu Victor" Cc: pgsql-gene...@postgresql.org Sent: 2020-02-13 3:48:08 PM Subject: Re: Enabling extensions on a compiled instance of postgresql 12.1 On Thu, Feb 13

Enabling extensions on a compiled instance of postgresql 12.1

Hello I compiled from source postgresql 12.1 and all went fine but when I try to restore my DB I can see that I have 3 extensions missing: uuid-ossp, btree_gist, tablefunc. I tried to run: CREATE EXTENSION "uuid-ossp"; and the error is ERROR: could not open extension control file "/usr/local

Re: POLL: Adding transaction status to default psql prompt

чт, 6 февр. 2020 г. в 04:55, Vik Fearing : > 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 -- Victor Yegorov

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 When I give ant fresh_install it gives error PostgreSQL 'pgcrypto' extension installed /up to date? False (not installed) Create extension pgcrypto

JSONB arrays

Hey people, I ended up with the following to check (or similar to return) the intersection of two list of values on a JSONB object: array_length( array( select jsonb_array_elements_text(col1) intersect select jsonb_array_elements_text(col2) ), 1) > 0 Is there a better way? Thanks

Re: Drop Default Privileges?

; If you see `ALTER DEFAULT PRIVILEGES … REVOKE …` and want to undo it, you will have to GRANT corresponding privilege. -- Victor Yegorov

Re: Drop Default Privileges?

that will show you internal queries used for displaying this information, or you can `\set ECHO_HIDDEN on` with the same effect. Also, you can do `pg_dumpall -s | grep -E 'DEFAULT PRIVILEGE|\\connect' and it'll produce a list of all entries for all databases, along with database name. -- Victor Yegorov

Why standby restores some WALs many times from archive?

cessful execution in 'restore_command' script and realized that it's really happens: some WALs are recovering again and again. And this is always connected with 'unexpected pageaddr' log message. Some statistics of how many 'same WALs' have been restored in 3 minutes:

Re: Intersection or zero-column queries

2017-12-22 2:03 GMT+02:00 David G. Johnston : > On Thu, Dec 21, 2017 at 4:53 PM, Victor Yegorov > wrote: > >> postgres=# select except select; >> -- >> (2 rows) >> postgres=# select intersect all select; >> -- >> (2 rows) >

Intersection or zero-column queries

postgres=# select intersect all select; -- (2 rows) Why is it so? Should this be reported as a bug?.. ;) -- Victor Yegorov