Re: Implementing pgaudit extension on Microsoft Windows

. > It is strange - can you try same setup on linux? > > Regards Niels > > > > > > > > > > > > *Fra:* Pavel Stehule > *Sendt:* 12. februar 2019 09:01 > *Til:* Niels Jespersen > *Cc:* Tom Lane ; pgsql-general@lists.postgresql.org > *Emne:* R

Re: Where **not** to use PostgreSQL?

Hi čt 28. 2. 2019 v 12:47 odesílatel Thomas Güttler < guettl...@thomas-guettler.de> napsal: > Hi experts, > > where would you suggest someone to **not** use PostgreSQL? > > Why would you do this? > > What alternative would you suggest instead? > Don't use Postgres like cache, don't use Postgres

Re: query has no destination for result data

I believe language plpgsql is not considered part of the function body > so it is not included in the line count: > > https://www.postgresql.org/docs/10/plpgsql-structure.html > > When tracking a line number down I usually do: > > \ef some_function line_number > > which counts the line in the funct

Re: Tools to migrate data from Json files to PostgreSQL DB.

Hi čt 7. 3. 2019 v 22:21 odesílatel github kran napsal: > Hello PostgreSQL Team, > > Are there are any tools to migrate data present in the json files ? to the > postgreSQL database. > We have data in flat files about 2 billion records across multiple files. > > 1) What is the easiest way I can

Re: How to parse XML in Postgres newer versions also

Hi ne 17. 3. 2019 v 12:11 odesílatel Andrus napsal: > Hi! > > In Postgres 9.1.2 script below produces proper results: > > 1.34 > 5.56 > > In Postgres 11 it produces wrong results: > > null > null > > How to make it also to work in newer versions on Postgres ? > > create temp table t(x xml, nsa t

Re: How to parse XML in Postgres newer versions also

ne 17. 3. 2019 v 14:49 odesílatel Pavel Stehule napsal: > Hi > > ne 17. 3. 2019 v 12:11 odesílatel Andrus napsal: > >> Hi! >> >> In Postgres 9.1.2 script below produces proper results: >> >> 1.34 >> 5.56 >> >> In Postgres 11 it pro

Re: How to parse XML in Postgres newer versions also

ne 17. 3. 2019 v 15:19 odesílatel Andrus napsal: > Hi! > > >You can use XMLTABLE function > >select xmltable.* > > from t, > > lateral > > xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' > as > > ns), > > > > '/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/.' pass

Re: How to parse XML in Postgres newer versions also

ne 17. 3. 2019 v 15:11 odesílatel Andrus napsal: > Hi > > >This variant is working > >postgres=# SELECT > >(xpath('/ns:Ntry/ns:Amt/text()', x,nsa))[1]::text::numeric AS > tasusumma > >FROM ( > >SELECT > unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry', > >x,nsa)) as x

Re: Logical replication - DDL sub transactions for script executed in single transaction?

pá 22. 3. 2019 v 10:49 odesílatel Aleš Zelený napsal: > Hello, > > I've learned that logical replication might have performance problem if > there are lot of sub transactions within transaction (at least because it > enforces spill files in pg_replslot and if there are many - like 80mio, > EXT4 d

Re: assembling PGresults from multiple simultaneous queries (libpq, singlerowmode)

Hi ne 7. 4. 2019 v 20:47 odesílatel Konstantin Izmailov napsal: > Hi, > I'm experimenting with Postgres 10 and protocol v3. I noticed that the > Postgres allows executing multiple queries simultaneously (I basically > commented out a check that prevents sending another query in libpq while > pre

Re: Unable to Vacuum Large Defragmented Table

po 8. 4. 2019 v 7:57 odesílatel Igal Sapir napsal: > David, > > On Sun, Apr 7, 2019 at 8:11 PM David Rowley > wrote: > >> On Mon, 8 Apr 2019 at 14:57, Igal Sapir wrote: >> > However, I have now deleted about 50,000 rows more and the table has >> only 119,688 rows. The pg_relation_size() still

Re: Getting error while running the pg_basebackup through PGBOUNCER

po 8. 4. 2019 v 15:42 odesílatel Raghavendra Rao J S V < raghavendra...@gmail.com> napsal: > Hi All, > > We are using PGBOUNCER(connection pool mechanisam). PGBOUNCER uses port > 5433. > > Postgres database port number is 6433. By using port 5433 PGBOUNCER is > connecting to postgres port 6433 dat

Re: Unable to Vacuum Large Defragmented Table

po 8. 4. 2019 v 17:22 odesílatel Igal Sapir napsal: > Pavel, > > On Sun, Apr 7, 2019 at 11:22 PM Pavel Stehule > wrote: > >> >> po 8. 4. 2019 v 7:57 odesílatel Igal Sapir napsal: >> >>> David, >>> >>> On Sun, Apr 7, 2019 at 8:

Re: orafce error

po 22. 4. 2019 v 13:21 odesílatel Prakash Ramakrishnan < prakash.ramakrishnan...@nielsen.com> napsal: > > Hi Team, > > While am creating the orafce extension we are getting below error. > > ==> ll > total 728 > -rw-r--r--. 1 postgres postgres 7863 Mar 27 18:54 aggregate.c > -rw-r--r--. 1 postgre

Re: Migrating an application with Oracle temporary tables

Hi What I am not clear on is what the rules are as to when a > function/procedure is effectively recompiled. Is there a danger that. > assuming the temporary table is created for a session that one session > might see another session's data due to the procedure having effectively > compiled the t

Re: Migrating an application with Oracle temporary tables

pá 3. 5. 2019 v 8:19 odesílatel Laurenz Albe napsal: > On Thu, 2019-05-02 at 16:55 +, Mark Zellers wrote: > > I thought I needed the prototype table to be able to define functions > and procedures that refer to the temporary table but do not create it. > > > > Perhaps my assumption that I nee

Re: Oracle Migration Approach (Open source vs Vendor Specific)

good work on automatic migration from PL/SQL to PL/pgSQL. Regards Pavel Stehule > > Regards, > Sandeep >

Re: Strange performance degregation in sql function (PG11.1)

čt 23. 5. 2019 v 23:38 odesílatel Alastair McKinley < a.mckin...@analyticsengines.com> napsal: > Hi Andrew, > > Thanks for your in-depth response. I found that adding the stable > qualifier didn't solve the issue unfortunately. I actually encountered the > same issue (or at least extremely simil

Re: Feature request (or at least discussion): enable autovaccum on temp tables

Hi pá 31. 5. 2019 v 17:26 odesílatel Ivan Voras napsal: > Hello, > > The reason why we are using temp tables is to allow concurrent runs on > some very large reports which involve creating and heavily churning dozens > of very large tables. > > The problem we're facing is that if we DON'T use te

Re: psql \copy

7;;' CSV" > > Error: relation "public.badenwuerttemberg_20181011" does not exist. > > What am I Doing wrong? > looks like case sensitive identifier is used. try "\copy public.""BadenWuerttemberg_20181011"" ... Regards Pavel Stehule > > The

Re: Requirement PA-DSS 1.1.4

Hi čt 6. 6. 2019 v 1:23 odesílatel Jan Bilek napsal: > Hi team, > > anyone? Please let me know if this is not a correct group to ask, I'll > move it somewhere else. > this question, proposal is much more related to pgsql-hackers forum. Currently Postgres doesn't support any feature like this.

Re: how to concat/concat_ws all fields without braces

Hi so 15. 6. 2019 v 8:20 odesílatel Jean Louis napsal: > Hello, > > I have tried doing something like: > > SELECT concat_ws(' ', table.*) FROM table; > > and if I do that way, it is essentially same as > > SELECT concat(table.*) FROM table; > > and I get the items in braces like (1,something). >

Re: how to concat/concat_ws all fields without braces

so 15. 6. 2019 v 16:20 odesílatel Jean Louis napsal: > Dear Pavel, > > Ahoj. > > * Pavel Stehule [2019-06-15 11:37]: > > you can write own function that will do what you want > > > > create or replace function rec_concat_fields(record, text) > > return

Re: function signature allow "default" keyword

Hi út 16. 5. 2023 v 5:11 odesílatel jian he napsal: > > > On Mon, May 15, 2023 at 5:00 PM Thomas Kellerer wrote: > >> jian he schrieb am 15.05.2023 um 10:33: >> > >> > function idea. >> > allow function calling using the default keyword for any of the input >> arguments. >> > >> > example: htt

Re: Packed raster data in postgresql?

Hi út 16. 5. 2023 v 19:09 odesílatel Ron napsal: > On 5/16/23 10:03, Elstermann, Mike wrote: > > Hello all, > > > > is it possible to store also packed raster data (e.g. jpg, lzw tiff, ...) > in PostgreSQL? > > > You can store any binary data you want in columns of type "bytea". > or you can us

Re: Records, Types, and Arrays

Hi pá 19. 5. 2023 v 8:59 odesílatel Raymond Brinzer napsal: > Sorry, I should have noted this as well: > > "One should also realize that when a PL/pgSQL function is declared to > return type record, this is not quite the same concept as a record > variable, even though such a function might use

Re: Profiling a function call

ne 21. 5. 2023 v 13:30 odesílatel Jan Wieck napsal: > On 5/20/23 00:36, Tiffany Thang wrote: > > Hi, > > I have a function that has been executing for a long time and not > > returning any results. Wait event=NULL so it seems like it is still > > executing and not waiting on any specific resource

Re: 2 master 3 standby replication

Hi pá 23. 6. 2023 v 10:37 odesílatel Atul Kumar napsal: > Hi, > > Please help me with the query I raised. > > Currently there is not any community based multi master solution. Regards Pavel Stehule > > Regards. > > On Fri, 23 Jun 2023, 00:12 Atul Kumar, wrote: &g

Re: How to show current schema of running queries in postgresql 13

Hi po 26. 6. 2023 v 8:39 odesílatel 陈锡汉 napsal: > Hello,I use multi-schemas in one database in Postgres,such as > > ``` > Postgres(instance) > MyDB >public >MySchema1 > table1 > table2 >MySchema2 > table1 > table2 >MySchema3 > table1 > table2 > ```

Re: How to show current schema of running queries in postgresql 13

po 26. 6. 2023 v 9:19 odesílatel Pavel Stehule napsal: > Hi > > > > no, there is nothing for this purpose. > > you can use application_name > > so user can do > > SET search_path=MySchema; > SET application_name = 'MySchema'; > SELECT *

Re: How to show current schema of running queries in postgresql 13

> 回复的原邮件 ---- > 发件人 Pavel Stehule > 日期 2023年06月26日 17:51 > 收件人 陈锡汉 > 抄送至 pgsql-general@lists.postgresql.org > 主题 Re: How to show current schema of running queries in postgresql 13 > > > po 26. 6. 2023 v 9:19 odesílatel Pavel Stehule > napsal: > >> H

Re: function to_char(unknown) is not unique at character 8

Hi čt 6. 7. 2023 v 11:19 odesílatel gzh napsal: > Hi, > > > I upgraded the version of PostgreSQL from 12.6 to 12.13, > > when I execute the sql below , the to_char function caused the following > error. > > > ---SQL-- > > select TO_CHAR('100'); > > > ERROR: funct

Re: function to_char(unknown) is not unique at character 8

his issue. On second thought, the behavior can be a little bit different than before. I have a question. Why do you use the to_char(string) function? Instead to_char('text') you can write only 'text'. > > > At 2023-07-06 19:21:24, "Pavel Stehule" wrote: >

Re: function to_char(unknown) is not unique at character 8

čt 6. 7. 2023 v 16:16 odesílatel gzh napsal: > Thank you very much for taking the time to reply to my question. > I added oracle to search_path, but it didn't work. > > postgres=# show search_path; > search_path > - > "$user", public, oracle, pg_c

Re: function to_char(unknown) is not unique at character 8

function to_char > > CREATE OR REPLACE FUNCTION oracle.to_char(text) > RETURNS text AS $$ > SELECT $1 > $$ LANGUAGE sql IMMUTABLE STRICT; > > This version will be preferred and fix this issue. On second thought, the > behavior can be a little bit different than before.

Re: Using "exit" to bring "goto" functionality.

Hi > As it happens, Oracle's PL/SQL has a "goto" statement. But PL/pgSQL does > not. (I assume that this is because "goto" is considered a bad thing.) But > PL/SQL programmers do use it. However, the doc section: > The reason why PL/pgSQL has not "goto" statement is mainly technological. PL/pgSQ

Re: PostgreSQL and GUI management

Hi út 15. 8. 2023 v 17:09 odesílatel Jason Long napsal: > Hello, > Does PostgreSQL have a graphical environment for management or is it only > managed through CLI? > https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools Regards Pavel > > Thank you. >

Re: PostgreSQL and GUI management

út 15. 8. 2023 v 17:16 odesílatel Pavel Stehule napsal: > Hi > > út 15. 8. 2023 v 17:09 odesílatel Jason Long napsal: > >> Hello, >> Does PostgreSQL have a graphical environment for management or is it only >> managed through CLI? >> &

Re: PostgreSQL and GUI management

út 15. 8. 2023 v 20:38 odesílatel Adrian Klaver napsal: > On 8/15/23 08:08, Jason Long wrote: > > Hello, > > Does PostgreSQL have a graphical environment for management or is it > > only managed through CLI? > > There are, but make your life easier and learn to use psql: > > https://www.postgresq

Re: Re: Fatal Error : Invalid Memory alloc request size 1236252631

Hi čt 17. 8. 2023 v 16:48 odesílatel Karsten Hilbert napsal: > > Even I used postgreSQL Large Objects by referring this link to store and > retrieve large files (As bytea not working) > https://www.postgresql.org/docs/current/largeobjects.html > > But even now I am unable to fetch the data at on

Re: The same prepared query yield "-1" the first six times and then "-1.0"

po 21. 8. 2023 v 19:52 odesílatel Edoardo Panfili < edoardo.panf...@iisgubbio.edu.it> napsal: > > > > Il giorno 21 ago 2023, alle ore 18:45, Tom Lane ha > scritto: > > > > "David G. Johnston" writes: > >> Otherwise, I agree this seems like a bug, probably in the JDBC driver, > >> though one pert

Re: Accessing system information functions

Hi po 25. 9. 2023 v 19:51 odesílatel Rob Sargent napsal: > > > On Sep 25, 2023, at 5:05 AM, Johnson, Bruce E - (bjohnson) < > john...@pharmacy.arizona.edu> wrote: > >  Environment: Ubuntu 22.04 lts, postgres 15 installed via postgres.org > repository > > > I am attempting to use the system inf

Re: Emitting JSON to file using COPY TO

Hi po 27. 11. 2023 v 14:27 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Monday, November 27, 2023, Dominique Devienne > wrote: > >> There's even a JSON mode. >> By miracle, would the JSON output mode recognize JSON[B] values, and >> avoid the escaping? >> > > I agree t

Re: Add support for data change delta tables

Hi po 15. 1. 2024 v 11:00 odesílatel PavelTurk napsal: > Hello all, > > > Currently PostgreSQL doesn't support data change delta tables. For example, > it doesn't support this type of query: > > SELECT * FROM NEW TABLE ( > INSERT INTO phone_book > VALUES ( 'Peter Doe', '555-2323' ) >

Re: Add support for data change delta tables

po 15. 1. 2024 v 11:11 odesílatel PavelTurk napsal: > > On 1/15/24 12:05 PM, Pavel Stehule wrote: > > Hi > > po 15. 1. 2024 v 11:00 odesílatel PavelTurk > napsal: > >> Hello all, >> >> >> Currently PostgreSQL doesn't support data change d

Re: Add support for data change delta tables

po 15. 1. 2024 v 11:27 odesílatel PavelTurk napsal: > > On 1/15/24 12:17 PM, Pavel Stehule wrote: > > > > po 15. 1. 2024 v 11:11 odesílatel PavelTurk > napsal: > >> >> On 1/15/24 12:05 PM, Pavel Stehule wrote: >> >> Hi >> >> po 15. 1

Re: Nested-Internal Functions

Hi čt 18. 1. 2024 v 13:31 odesílatel Rossana Ocampos napsal: > Effectively I had to create the function externally, I am in the process > of migrating from Oracle to Postgresql and I have many cases of > encapsulated functions and transactions. > Thank you very much for the return. > Rossana Oca

Re: Fastest way to clone schema ~1000x

Hi po 26. 2. 2024 v 7:28 odesílatel Emiel Mols napsal: > Hello, > > To improve our unit and end-to-end testing performance, we are looking to > optimize initialization of around 500-1000 database *schemas* from a > schema.sql file. > > Background: in postgres, you cannot change databases on > ex

Re: Fastest way to clone schema ~1000x

'minimal' > > - linux perf report comparing schema-per-test vs database-per-test: > https://ibb.co/CW5w2MW > > - Emiel > > > On Mon, Feb 26, 2024 at 1:36 PM Pavel Stehule > wrote: > >> Hi >> >> po 26. 2. 2024 v 7:28 odesílatel Emiel Mols napsal

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

čt 7. 3. 2024 v 16:59 odesílatel Christophe Pettus napsal: > > > > On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud < > a.mantz...@cloud.gatewaynet.com> wrote: > > So, I ask, have there been any efforts to bring PL/PGSQL to the terminal? > > Strictly speaking, of course, you can use PL/pgSQL

Re: Pgxs - How to reference another extension

Hi po 11. 3. 2024 v 14:48 odesílatel Michał Kłeczek napsal: > > > On 11 Mar 2024, at 14:08, Artur Zakirov wrote: > > On Mon, 11 Mar 2024 at 13:26, Michał Kłeczek wrote: > > > > On 11 Mar 2024, at 11:41, Michał Kłeczek wrote: > > Hi, > > I am trying to create an extension that delegates some c

Re: PL/pgSQL techniques better than bash for dynamic DO?

út 9. 4. 2024 v 18:33 odesílatel Ron Johnson napsal: > PG 9.6.11, if relevant, migrating to PG 14 Real Soon Now. > > I must purge the oldest X period of records from 70 tables, every Sunday. > The field name, interval (X days or months) and date (CURRENT_DATE or > CURRENT_TIMESTAMP) varies for ea

Re: Need some assistance on stored procedures execution using libpq in C

Hi čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh napsal: > Hi PostgreSQL Team, > > I'm trying to execute the stored procedure(details along with the program > in the attachment) to fetch the records from the table for the inputs given > in the code as well. I have already created the procedur

Re: Need some assistance on stored procedures execution using libpq in C

rvers). You can check code there https://github.com/postgres/postgres/blob/master/contrib/dblink/dblink.c Regards Pavel > > > Regards, > Sasmit Utkarsh > +91-7674022625 > > > On Thu, Apr 25, 2024 at 8:26 PM Pavel Stehule > wrote: > >> Hi >> >> čt 25.

Re: search_path wildcard?

st 22. 5. 2024 v 19:54 odesílatel Ron Johnson napsal: > On Wed, May 22, 2024 at 12:53 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, May 22, 2024, 10:36 Ron Johnson wrote: >> >>> This doesn't work, and I've found nothing similar: >>> ALTER ROLE foo SET SEARCH_PATH = '*

Re: search_path wildcard?

st 22. 5. 2024 v 21:13 odesílatel Ron Johnson napsal: > On Wed, May 22, 2024 at 1:58 PM Tom Lane wrote: > >> Ron Johnson writes: >> > That would be a helpful feature for administrators, when there are >> multiple >> > schemas in multiple databases, on multiple servers: superusers get ALTER >> >

Re: search_path and SET ROLE

st 22. 5. 2024 v 21:38 odesílatel Ron Johnson napsal: > On Wed, May 22, 2024 at 2:02 PM Isaac Morland > wrote: > >> On Wed, 22 May 2024 at 13:48, Ron Johnson >> wrote: >> >> As a superuser administrator, I need to be able to see ALL tables in ALL >>> schemas when running "\dt", not just the one

Re: Can a long running procedure detect when smart shutdown is pending?

so 6. 7. 2024 v 1:36 odesílatel Dennis White napsal: > Thanks for responding. > This will be a stored procedure written in plpgsql that's kicked off by > pg_cron. > I wasn't sure a normal smart shutdown would stop it. > shutdown try to cancel any query. The plpgsql routine should be canceled wit

Re: Can a long running procedure detect when smart shutdown is pending?

so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios < a.mantz...@cloud.gatewaynet.com> napsal: > Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε: > > > > so 6. 7. 2024 v 1:36 odesílatel Dennis White > napsal: > >> Thanks for responding. >> This will be a sto

Re: Can a long running procedure detect when smart shutdown is pending?

so 6. 7. 2024 v 8:19 odesílatel Pavel Stehule napsal: > > > so 6. 7. 2024 v 8:06 odesílatel Achilleas Mantzios < > a.mantz...@cloud.gatewaynet.com> napsal: > >> Στις 6/7/24 08:39, ο/η Pavel Stehule έγραψε: >> >> >> >> so 6. 7. 2024 v 1:36 od

Re: Declaring a field that is also an out parameter in a function

ne 7. 7. 2024 v 0:14 odesílatel Tom Lane napsal: > Michael Nolan writes: > > Shouldn't declaring a field that is also an OUT parameter throw an error? > > No. The DECLARE is a block nested within the function, > and the parameter is declared at function scope. > So this is a standard case of an

Re: Declaring a field that is also an out parameter in a function

ne 7. 7. 2024 v 9:31 odesílatel Pavel Stehule napsal: > > > ne 7. 7. 2024 v 0:14 odesílatel Tom Lane napsal: > >> Michael Nolan writes: >> > Shouldn't declaring a field that is also an OUT parameter throw an >> error? >> >> No. The DECLARE

Re: Declaring a field that is also an out parameter in a function

ne 7. 7. 2024 v 15:01 odesílatel Michael Nolan napsal: > On Sun, Jul 7, 2024 at 4:13 AM Pavel Stehule > wrote: > > > > but looks so there are false alarms related to using an alias. It is > interesting so I have not any report about this issue, so probably using > a

Re: Declaring a field that is also an out parameter in a function

ne 7. 7. 2024 v 16:48 odesílatel Adrian Klaver napsal: > On 7/7/24 07:42, Pavel Stehule wrote: > > I'm not sure why there's a warning about using an alias. 43.3.1 says > > to use them for improved readability. > > > > > > it is obsolete - alia

Re: Declaring a field that is also an out parameter in a function

ne 7. 7. 2024 v 16:37 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > (2024-07-07 09:27:14) postgres=# select * from > > plpgsql_check_function('test_function'); > > ┌───┐ > > │

Re: Declaring a field that is also an out parameter in a function

ne 7. 7. 2024 v 17:00 odesílatel Adrian Klaver napsal: > On 7/7/24 07:53, Pavel Stehule wrote: > > > > > > ne 7. 7. 2024 v 16:48 odesílatel Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> napsal: > > > > On 7/7/24 07:42, Pavel Stehule wro

Re: Detecting PostgreSQL client library

Hi st 10. 7. 2024 v 18:07 odesílatel Igor Korot napsal: > Hi, ALL, > Is there an explicit autotools macro that can tell > the presence of PostgreSQL client library? > And that can be used with "configure" script. > > Or I should be using AC_CHECK_MODULE() one? > I use I use https://github.com/

Re: Planet Postgres and the curse of AI

st 17. 7. 2024 v 19:22 odesílatel Greg Sabino Mullane napsal: > I've been noticing a growing trend of blog posts written mostly, if not > entirely, with AI (aka LLMs, ChatGPT, etc.). I'm not sure where to raise > this issue. I considered a blog post, but this mailing list seemed a better > forum

Re: Debugging set up for Postgres?

Hi čt 8. 8. 2024 v 19:16 odesílatel James Creasy napsal: > Hi all, > > We've spent about 12 hours trying to set up a working debugger for psql > functions without success, trying both PGAdmin 4 v8.3 and DBeaver. > > The online searches turn up multiple instructions for Postgres 12 or > older, ho

Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

Hi po 8. 3. 2021 v 19:20 odesílatel Alexander Farber < alexander.far...@gmail.com> napsal: > Good evening, > > in PostgreSQL 13.2 I have a custom stored function: > > CREATE OR REPLACE FUNCTION words_join_new_game( > in_uid integer, > in_bid integer >

Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 9:53 odesílatel Frank Millman napsal: > > On 2021-03-19 10:29 AM, Thomas Kellerer wrote: > > Frank Millman schrieb am 19.03.2021 um 09:19: > >> This may be a non-issue, and I don't want to waste your time. But > perhaps someone can have a look to see if there is anything obvious

Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 10:22 odesílatel Frank Millman napsal: > > On 2021-03-19 10:56 AM, Pavel Stehule wrote: > > > > pá 19. 3. 2021 v 9:53 odesílatel Frank Millman > napsal: > >> >> On 2021-03-19 10:29 AM, Thomas Kellerer wrote: >> > Frank Millman schrie

Re: SELECT is faster on SQL Server

pá 19. 3. 2021 v 11:58 odesílatel Frank Millman napsal: > > On 2021-03-19 12:00 PM, Pavel Stehule wrote: > > > In this query the most slow operation is query planning. You try to do > tests on almost empty tables. This has no practical sense. You should test > queries on tab

Re: pass non-formated query to PL function

Hi pá 2. 4. 2021 v 11:35 odesílatel Joao Miguel Ferreira < joao.miguel.c.ferre...@gmail.com> napsal: > Hello all, > > Is it possible, in PL/pgSQL, to pass an argument to a function which is > actually a "query skeleton" that the method will "fill in the blanks" and > execute it or return it to th

Re: pgadmin4 SRPM

út 25. 5. 2021 v 14:56 odesílatel Ron napsal: > > The pgAdmin mailing list is a better place to ask: > https://www.pgadmin.org/support/list/ > > Postgres community repository has pgadmin too https://ftp.postgresql.org/pub/repos/yum/srpms/common/redhat/rhel-6.7-x86_64/ you can download srpms fil

Re: Need to omit time during weekends from age calculations

po 7. 6. 2021 v 21:17 odesílatel Ron napsal: > On 6/7/21 2:12 PM, David Gauthier wrote: > > Hi: > > I suspect I'm not the first to ask about this but couldn't find anything > after googling for a bit. So here goes > > I'd like to get the "age" difference between two times which span either >

Re: CONCAT function adding extra characters

Hi út 15. 6. 2021 v 20:56 odesílatel AI Rumman napsal: > I am using Postgresql 10 and seeing a strange behavior in CONCAT function > when I am concatenating double precision and int with a separator. > > select concat('41.1'::double precision,':', 20); >> Result: >> 41.1014:20 > > >

Re: CONCAT function adding extra characters

út 15. 6. 2021 v 21:07 odesílatel Tom Lane napsal: > AI Rumman writes: > > I am using Postgresql 10 and seeing a strange behavior in CONCAT function > > when I am concatenating double precision and int with a separator. > > > select concat('41.1'::double precision,':', 20); > >> Result: > >> 41.

Re: Greatest of a list of columns?

čt 1. 7. 2021 v 15:26 odesílatel Ron napsal: > Postgresql 12.5 > > > What's the canonical Postgresql method for doing, for example, this? > SELECT relname, MAXOF(last_vacuum, last_autovacuum) > FROM pg_stat_user_tables; > > Seeing both last_vacuum and last_autovacuum is useful, of course, but > s

Re: Greatest of a list of columns?

čt 1. 7. 2021 v 15:27 odesílatel Pavel Stehule napsal: > > > čt 1. 7. 2021 v 15:26 odesílatel Ron napsal: > >> Postgresql 12.5 >> >> >> What's the canonical Postgresql method for doing, for example, this? >> SELECT relname, MAXOF(last_vacuu

Re: Formating psql query output

po 19. 7. 2021 v 21:07 odesílatel Rich Shepard napsal: > On Mon, 19 Jul 2021, Rob Sargent wrote: > > > Can we see on line of the csv output? The field with commas should be in > > quotes, no? You’ll have write a “real” csv importer. awk =F”\”*,*\”” > > might, heavy on the might. > > Rob, > > Here

Re: Formating psql query output

po 19. 7. 2021 v 21:12 odesílatel Pavel Stehule napsal: > > > po 19. 7. 2021 v 21:07 odesílatel Rich Shepard > napsal: > >> On Mon, 19 Jul 2021, Rob Sargent wrote: >> >> > Can we see on line of the csv output? The field with commas should be in >> &g

Re: version 0 calling convention

čt 22. 7. 2021 v 18:21 odesílatel Adrian Klaver napsal: > On 7/22/21 9:18 AM, Mark Lybarger wrote: > > i have some sql functions written in c code using version 0 calling > > convention. it's working fine on postgresql 9.6, but i'm migrating to > > v13. has this calling convention been removed?

Re: PostgreSQL reference coffee mug

po 26. 7. 2021 v 8:56 odesílatel Matthias Apitz napsal: > > Hello, > > Nearly 20 years ago, I ordered some 50 vi-reference coffee mugs like this > one here (not exactly the same, but to give you an idea): > > https://www.getdigital.eu/vi-reference-mug.html > > for our vi-lovers in-house and the a

Re: PostgreSQL reference coffee mug

po 26. 7. 2021 v 13:44 odesílatel Rob Sargent napsal: > > > On Jul 26, 2021, at 1:06 AM, Pavel Stehule > wrote: > >  > > Thanks in advance for any hints. >> > > I have this in czech language - maybe google translator > https://translate.google.c

Re: PHP: query with parameter for SET

Hi út 27. 7. 2021 v 19:10 odesílatel Ray O'Donnell napsal: > Hi everyone, > > Using PHP (or indeed maybe more generally), is it possible to issue a > SET TIME ZONE statement with a parameter for the new runtime setting? > > In a PHP application I'm issuing a SET TIME ZONE command just after the

Re: Series of 10 questions about the use of postgresql, generally.

Hi pá 6. 8. 2021 v 6:46 odesílatel A Z napsal: > I have been going through the free online book LEARNING postgresql book, > that has been compiled by Stack Overflow contributors. I have gotten to the > point where I have the following series of unanswered questions: > > >1. Are there free sc

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

po 9. 8. 2021 v 21:41 odesílatel Bryn Llewellyn napsal: > *The problem that I report here seems to be known and seems, too, to > astonish and annoy users. It's a bare "computer says No". **It's hard to > find anything of ultimate use with Google search (either constrained to the > PG doc or uncon

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

po 9. 8. 2021 v 23:13 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > Some errors like this, but not this can be detected by plpgsql_check > > https://github.com/okbob/plpgsql_check - probably the heuristic for type > > check is not complete. > > STRICTMULTIA

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

> > > > It now seems to me to be odd, in the light of the explanations for why the > naïve (PL/SQL-style) syntax doesn't work in PL/pgSQL, that assigning a > scalar subquery to a variable of the composite type in question _does_ > work! But don't take that as a question. I'm going to regard this as

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

út 10. 8. 2021 v 19:48 odesílatel Bryn Llewellyn napsal: > pavel.steh...@gmail.com wrote: > > t...@sss.pgh.pa.us wrote: > > *pavel.steh...@gmail.com wrote:* > > Some errors like this, but not this can be detected by plpgsql_check > https://github.com/okbob/plpgsql_check > probably the heuristic

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

> > >> I can get the reference by field name that I prefer with a schema-level >> type: >> >> create type tmp as (b text, t type1); >> >> and by declaring "r" with this data type. But this is a greater >> discomfort than using the dynamically shaped "record" because it needs you >> to create a dedi

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

po 9. 8. 2021 v 23:13 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > Some errors like this, but not this can be detected by plpgsql_check > > https://github.com/okbob/plpgsql_check - probably the heuristic for type > > check is not complete. > > STRICTMULTIA

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

út 10. 8. 2021 v 21:25 odesílatel Pavel Stehule napsal: > >>> I can get the reference by field name that I prefer with a schema-level >>> type: >>> >>> create type tmp as (b text, t type1); >>> >>> and by declaring "r" with

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

> > r := (select (b, t)::type1 -- it is composite with labels again > > > postgres=# do $$ declare r record; begin r := (select (10,20)); raise notice '%', to_json(r); end; $$; NOTICE: {"f1":10,"f2":20} DO postgres=# do $$ declare r record; begin r := (select (10,20)::footype); raise notic

Re: ERROR: control reached end of function without RETURN, except, it is not a function it is a procedure or a nameless block.

Hi po 13. 9. 2021 v 13:23 odesílatel Herwig Goemans napsal: > Hi Support, > > > I get an error in a stored procedure - not a stored function mind you - > control reached end of function without RETURN > > The error occurs when doing an exit in a stored procedure. Now > I cannot give all of the

Re: Using XMLNAMESPACES with XMLEMENT

Hi pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis napsal: > Hi All, > > > > I am attempting to port the following statement from DB2z to Postgres: > > > > SELECT e.empno, e.firstnme, e.lastname, > > XMLELEMENT ( NAME "foo:Emp", > > XMLNAMESPACES('http://www.foo.com' AS "f

Re: Using XMLNAMESPACES with XMLEMENT

Hi ne 26. 9. 2021 v 21:48 odesílatel Garfield Lewis napsal: > Thx @Pavel Stehule , I’ll see if I can figure > this out… ☺ > > > > Regards, > > Garfield > > > > *From: *Pavel Stehule > *Date: *Friday, September 24, 2021 at 11:33 PM > *To: *Garfield Le

Re: type bug?

st 6. 10. 2021 v 5:50 odesílatel napsal: > Hello, > > there is a bug with types in functions. Here is an example: > > --drop type xyz; > create type xyz as ( x numeric, y numeric, z numeric ); > > --drop table test_xyz ; > create table test_xyz ( a int, b xyz, c xyz ); > insert into test_x

Re: psql syntax for array of strings in a variable?

Hi pá 29. 10. 2021 v 19:21 odesílatel Philip Semanchuk < phi...@americanefficient.com> napsal: > Hi, > I would appreciate help with the syntax for querying an array of strings > declared as a psql variable. Here's an example. > > \set important_days ARRAY['monday', 'friday'] > > select 1 where 'm

<    1   2   3   4   5   >