Re: Issue with a query while running on a remote host

On Tuesday, August 31, 2021, Ninad Shah wrote: > Hi Karsten, > > I apologize for the delayed response. > > There is no script-related transfer happening here. It creates an issue > while using "bash@" inside a column. > >> >> That wasn’t what was meant. Ignore the “why” for the moment, the

Re: Using a TRIGGER with window functions.

On Sun, Aug 15, 2021 at 1:24 AM Pól Ua Laoínecháin wrote: > > ERROR: window functions are not allowed in UPDATE LINE 2: SET > sort_order = activity_id - FIRST_VALUE(activity_id) > > Why are window functions now allowed in UPDATEs > You can get it to work via a subquery/FROM clause computation.

Selecting table row with latest date

On Thursday, August 19, 2021, Rich Shepard wrote: > On Thu, 19 Aug 2021, Rich Shepard wrote: > > Please point me to the proper place in the docs where I can learn how to do >> this. >> > > If I use DISTINCT ON would this produce the most recent date for each > person_nbr? > Yes, I mis-read your

Re: How to detach a database

On Thursday, August 19, 2021, sivapostg...@yahoo.com wrote: > > Is there any way to make a PG database either in-active or switch to > single-user mode ? We want to avoid DELETE database process. > Probably can do it via changing the pg_hba.conf file. David J.

Re: Selecting table row with latest date

On Thursday, August 19, 2021, Rich Shepard wrote: > I have a table of contacts (PK is the person_nbr and contact_date) > Select distinct on (person_nbr) ….. order by person_nbr, contact_date desc; David J.

Re: string_agg distinct order by

On Thu, Aug 19, 2021 at 9:09 AM Markhof, Ingolf < ingolf.mark...@de.verizon.com> wrote: > > > string_agg(distinct status,',' order by status) as list > > but this is not what I want: 'green' would get first, red second, yellow last... > > I could also drop the distinct and say: > >

Re: Selecting table row with latest date

On Thursday, August 19, 2021, Rich Shepard wrote: > On Thu, 19 Aug 2021, Adrian Klaver wrote: > > So take David Johnston's query: >> Select distinct on (person_nbr) ….. order by person_nbr, contact_date >> desc; >> > > Adrian, > > contact_date -- > 2021-08-17 What’s your point?

Re: jsonpath duplication result

On Thu, Sep 2, 2021 at 6:06 AM Charles Paperman wrote: > I also wonder if it is problematic to have simple small query like that > that can be design to make the database work endlessly? > > No, because the "simple small" part of that doesn't matter. A user with access has various ways to

Re: Upgrade 9.5 cluster on Ubuntu 16.04

On Thursday, September 2, 2021, Vano Beridze wrote: > I just want to upgrade to the OS and Postgres that is supported. > If I just stop the service on both VMs and upgrade Ubuntu, then start > Postgres, will it work or I should go with clean install and dump/restore? > >> >> Personally I find

Re: Query takes around 15 to 20 min over 20Lakh rows

On Thu, Sep 2, 2021 at 3:16 PM Shubham Mittal wrote: > Hi , > > *Please help in optimizing this query. I need to actually generate reports > daily using this query.. It takes almost 15 to 20 min to execute this query > due to joins.. * > *Here common_details is a jsonB column.* > > SELECT

Re: How to observe plan_cache_mode transition from custom to generic plan?

On Sunday, September 5, 2021, Richard Michael wrote: > > Based on the documentation, I expected the first planned query text to > be: `SELECT 10 AS data`, since it should be a custom plan with > substituted values. However, the query text always contains a parameter > symbol: `SELECT $1 AS

Re: update non-indexed value is slow if some non-related index/fk are enabled

On Friday, September 3, 2021, Philippe Doussot wrote: > > One UPDATE of one boolean value which is non-indexed take some time ( for > many tuples .. 3 Millions ). > > But if we disable all indexes/fk the UPDATE is 10x faster and do 10x less > shared hit . > > I don't understand why disabling all

Re: How does postgres behave if several indexes have (nearly) identical conditions?

On Wednesday, September 8, 2021, Koen De Groote wrote: > > > create index index_001 on my_table using btree (a001,a002,a003) where > a001=true and a002=false; > > create index index_002 on my_table using btree (a003) where a001=true and > a002=false; > > Now take this query: > > select * from

Re: How does postgres behave if several indexes have (nearly) identical conditions?

On Wednesday, September 8, 2021, Koen De Groote wrote: > And initial setup is wrong. There should be no 'and a002=false' in the > indexes. > > >>> create index index_001 on my_table using btree (a001,a002,a003) where >>> a001=true and a002=false; >>> >>> create index index_002 on my_table using

Re: How does postgres behave if several indexes have (nearly) identical conditions?

On Wednesday, September 8, 2021, Koen De Groote wrote: > Forgot to mention, this is on Postgres 11.2 > You should stop worrying about performance and indexes and instead focus on system stability and security - i.e., upgrade to a supported version. David J.

Re: PostgreSQL starting with a corrupted database or other fatal issues

On Friday, September 10, 2021, Keith Christian wrote: > > Where would I find details about this situation in the PG docs?. > > As far as PostgreSQL is concerned if startup fails it tries to log that fact then stops. If doesn’t care whether it’s being started by hand, via systemd, or even cron,

Re: update non-indexed value is slow if some non-related index/fk are enabled

On Monday, September 6, 2021, Philippe Doussot wrote: > I whas hopping the same optimisation as you: Write in place. > > > How exactly would you expect “update-in-place” to work given the nature of MVCC? David J.

Re: autocommit for multi call store procedure

On Thursday, September 16, 2021, Trang Le wrote > > > Could you double check it? > We’re testing your failing code, not that PostgreSQL is working as intended. Suggest you get rid of all the stuff that doesn’t produce errors and focus on trying to supply a minimal script that produces the

Re: Basic Questions about PostgreSQL.

On Wednesday, September 15, 2021, A Z wrote: > -In PostgreSQL, what is the difference between Roles, Groups > > There is none, aside from convention. Roles are generic, groups don’t get login rights while users do. You might want to clarify what you mean by policy since referencing the “create

Re: is there any memory leak with postgres version(12.x) - https://stackoverflow.com/questions/3849543/force-postgresql-to-release-allocated-memory

On Thursday, September 2, 2021, M Tarkeshwar Rao < m.tarkeshwar@ericsson.com> wrote: > > Is there any memory leak in Postgres version 12.7 or any other 12.x? > Most likely the answer is no, or if there are the amount is so small as to be practically unmeasurable. It is much more likely that

Re: Need to check each element of an array satisfies a foreign key constraint

On Tuesday, July 13, 2021, David Gauthier wrote: > > > I suppose I could write a stored procedure to do this and call it in a > check constraint. But I was wondering if there is something more elegant. > > You cannot use a check constraint here as the behavior is not immutable. You can use a

Re: PostgreSQL 9.2 high replication lag

On Tue, Jul 13, 2021 at 8:01 PM Lucas wrote: > According to the documentation, max_standby_streaming_delay is a configuration > parameter determining how long a > standby server should wait before canceling queries that conflict with > pending WAL entries

Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

On Mon, Aug 2, 2021 at 12:20 PM Dhanush D wrote: > It doesn't show up twice based on these checks (Is there any other way I > can check if the seq is showing up twice?). > > SELECT * FROM pg_class WHERE relkind = 'S' AND relname ~ 'permission'; I made the "relname" a bit broad, you should see

Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

On Mon, Aug 2, 2021 at 11:42 AM Dhanush D wrote: > Multiple "Create sequence" and "Drop sequence". However the drop sequence > doesn't seem to drop the sequence cleanly hence failing the restore process. > It shouldn't be dropping at all - it assumes it is starting from an empty slate. Same

Re: Automatic backup in windows

On Sunday, August 1, 2021, obi reddy wrote: > Hello everyone, I want to schedule the automatic backup in windows > postgresql platform. > > I have attached the script, if any changes is required please correct and > send back. This is my humble request. > Actually I don't know to write the

Re: Logical Replication - Different Primary Key on Source Table and Destination Table

On Thu, Aug 5, 2021 at 12:22 AM Avi Weinberg wrote: > Is it permitted to have different primary key on source and destination > tables? > Sure, but you are still going to want a unique index on the source table that includes the site ID to act as a replica identity. Which of the two unique

PostgreSQL general set of Questions.

On Sunday, August 8, 2021, A Z wrote: > > 2) How may I get PostgreSQL to output the create table statement(s) for > one or more tables inside one database, without issuing instructions via > the command line, but only inside a database login, as a query or pl/sql? > If not possible, what can you

Re: archive_command / single user mode

On Mon, Aug 9, 2021 at 4:17 PM Christophe Pettus wrote: > Does archive_command run in single user mode? This isn't an actual > situation at the moment, but for runbook / disaster planning purposes, I > was curious. > > Nope. If I follow the source code correctly...main.c calls PostgresMain

Re: Serious List of PostgreSQL usage questions.

As this is your third email of this form, with mostly the same questions, I would suggestion you send a single email, with an appropriate subject line, per question to facilitate discussion and avoid subjecting people to looking at multiple bulk threads to see what has and hasn't been said. Then,

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

On Thu, Aug 5, 2021 at 9:46 PM A Z wrote: > >1. Are there free script for CREATE TYPE (native type) or types out >there, online, free for commercial use? With function support, too? Can >someone reply with a link or a suggestion? > > Look at the PostgreSQL source code, contrib

Re: psql's default database on connect (our internal ref. SRP-30861)

On Friday, August 6, 2021, Guillaume Lelarge wrote: > If unset, it will use the PostgreSQL user name also as the database name. > This is true, but its not what the docs says: psql usage doc: “The default user name is your operating-system user name, as is the default database name.”

Re: php connection failure

On Saturday, August 7, 2021, Tom Lane wrote: > ourdiaspora writes: > > psql -d cpacweb -h 127.0.0.1 -U cpaca > > psql: could not connect to server: Connection refused > > Is the server running on host "127.0.0.1" and accepting > > TCP/IP connections on port 5432? > > Hm --- this

Re: php connection failure

On Saturday, August 7, 2021, ourdiaspora wrote: > > SHOW hba_file; > /etc/postgresql/9.6/main/pg_hba.conf > > Please post the contents of this file. The one shown at the link is invalid. David J.

Re: php connection failure

On Saturday, August 7, 2021, ourdiaspora wrote: > > On Sunday, August 8th, 2021 at 12:50 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > > > On Saturday, August 7, 2021, ourdiaspora > wrote: > > > > > SHOW hba_file; > > >

Re: postgres vacuum memory limits

On Saturday, July 31, 2021, Ayub M wrote: > But when default_statistics_target is increased to 3000, the session usage > is 463mb > IIUC, the analyze process doesn’t consult maintenance_work_mem. It simply creates an array, in memory, to hold the random sample of rows needed for computing the

Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

On Friday, July 30, 2021, Dhanush D wrote: > > > pg_restore: error: could not execute query: FATAL: terminating connection > due to administrator command > > Did you control-C or something, maybe remote connection dropped? > pg_restore: creating SEQUENCE "schema99.sequence2" > pg_restore:

Re: Using functions in regexp replace captures

On Wednesday, August 4, 2021, Tim Uckun wrote: > I want to do something like this > > SELECT REGEXP_REPLACE('some_string','(.*) > (.*)',some_function_that_returns_string('\2',' \1')); > > Is this possible at all? > > Generally I’d say yes, it is possible to combine multiple subqueries together

Re: Using functions in regexp replace captures

On Wednesday, August 4, 2021, David G. Johnston wrote: > > But no, you cannot directly write: f(x, y, g(a)) where a is the > replacement string because you don’t know what a is when the inner function > g is evaluated first. You need: f(x, y, g(h(x, y))) where h is the > match

Re: Timestamp with vs without time zone.

On Tuesday, September 21, 2021, Peter J. Holzer wrote: > > These two values are completely indistinguishable. That's good for a > timestamp (they are the same time after all). But they are not a > "timestamp with time zone". The time zone is not part of the value but > of the environment. > No,

Re: Surprising results from tests intended to show the difference in semantics between transaction_timestamp(), statement_timestamp(), and clock_timestamp()

On Tuesday, September 21, 2021, Bryn Llewellyn wrote: > I'm surprised by the results that I describe below. Please help me > interpret them. > > Briefly, why does transaction_timestamp() report a later value > than statement_timestamp() when they're both invoked in the very first > statement

Re: Get COUNT results from two different columns

On Thu, Sep 23, 2021 at 6:37 AM Clive Swan wrote: > Greetings, > > I have two separate queries that work individually, returning a count from > each column. > > > > I want to subtract New(COUNT) from Old(Count) > > > I get an error when trying to run UNION? > While you finally did provide this

Re: pg_class.oid at 4B

On Wednesday, October 20, 2021, Radoslav Nedyalkov wrote: > Hello all, > We have a busy database that has oid increasing with 50-100M per month. > There is no oid type across user attributes anywhere nor user tables with > oids; > Not sure where this generation comes from. If you can hint me,

Re: Looking for a doc section that presents the overload selection rules

On Thursday, October 21, 2021, Bryn Llewellyn wrote: > > This causes the 42883 error, "function f(date) does not exist". I might've > expected the system to have done an implicit conversion to "text" because > this conversion is supported, thus: > Yes, implicit casting to text is bad. > > For

Re: Where is the tsrange() function documented?

On Monday, October 18, 2021, Bryn Llewellyn wrote: > > > Where is it? > TOC - Data Types - Range Types: https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN David J.

Re: JSON query

On Monday, October 18, 2021, Scott Ribe wrote: > > "containing only keys from this list of keys" > > (jsonb - text[]) = ‘{}’::jsonb …? Combine with (jsonb ?& text[]) if all tested keys need to be present as well. David J.

Where is the tsrange() function documented?

On Monday, October 18, 2021, Bryn Llewellyn wrote: > > I can't find anything, neither on the page in question here on Range Types > nor in the doc on the overlaps operator, about the semantics for when a > duration collapses to an instant. Am I missing this too? > > Same page:

Re: connecting multiple INSERT CTEs to same record?

On Tuesday, October 19, 2021, Assaf Gordon wrote: > > Sadly, I can't assume the student name is unique, so I can't "join" on it. > > Instead of assigning a unique identifier to student after inserting it into the table, assign the identifier first. Generally this is done by using “nextval()”

Re: "two time periods with only an endpoint in common do not overlap" ???

On Thu, Oct 14, 2021, 16:38 Bryn Llewellyn wrote: > . This means for instance that two time periods with only an endpoint in > common do not overlap. > > A range has two endpoints. The one at the later (end) of the range and the one at the earlier (start). I suppose rewording it to say

Re: "two time periods with only an endpoint in common do not overlap" ???

On Friday, October 15, 2021, Ron wrote: > > Prima facie, if you were told "numbers in the range 0-10", would you > really think, "ah, they *really* mean 0 through 9"? > > I would indeed default to both endpoints of the range being inclusive. I also begin counting at one, not zero. I’ve long

Re: "two time periods with only an endpoint in common do not overlap" ???

On Friday, October 15, 2021, Ron wrote: > > The numeric ranges 0-10 and 10-19 overlap, just as the time ranges > 00:01:00-00:00:02:00 overlaps 00:02:00-00:03:00. > > *It's the programmer's responsibility* to say what s/he really means, not > for "the system" to make that choice. > The system

Re: Faster distinct query?

On Wed, Sep 22, 2021 at 1:21 PM Michael Lewis wrote: > In the future, please share the plan returned by explain analyze, and some > data about how many rows in the involved tables, > I believe we consider it acceptable to link to an explain viewer, which is what the OP did. Reading explain

Re: Faster distinct query?

On Wed, Sep 22, 2021 at 1:05 PM Israel Brewster wrote: > To work around the issue, I created a materialized view that I can update > periodically, and of course I can query said view in no time flat. However, > I’m concerned that as the dataset grows, the time it takes to refresh the > view will

Re: Check constraint failure messages

On Sun, Oct 3, 2021, 14:39 zim wrote: > An error message that includes the table name and column name would also > be helpful for non-bulk inserts: > This is not limited to domain types and comes up fairly often. In general there is agreement that the current behavior is not great but

Re: PostgreSQL - Ordering Table based of Foreign Key

On Sun, Oct 3, 2021, 00:48 FOUTE K. Jaurès wrote: > Hello, > > I want to order tables based on the foreign key so that I can delete > tables one by one without facing "ERROR: update or delete on table > "table" violates foreign key constraint. DETAIL: Key is still referenced > from table" > As

Re: type bug?

On Tuesday, October 5, 2021, wrote: > > create function test_xyz3() returns table ( b xyz) as > $$ > declare > bb xyz; > cc xyz; > begin > select b, c into bb, cc from test_xyz ; > return bb ; > end; > $$ language plpgsql ; > -- -- > > select * from test_xyz3() ; -- compiling error

Re: type bug?

On Tuesday, October 5, 2021, wrote: > > select * from test_xyz2() ; -- 3 columns, but 1 should be correct? > select b from test_xyz2() ; -- error > select x from test_xyz2() ; -- never declared column All three of these stem from:

Re: Faster distinct query?

On Wednesday, September 22, 2021, David Rowley wrote: > I think writing the query in such a way > that allows it to be parallelised is likely going to result in some > quite good performance improvements. i.e: > Agreed, though if the query author needs to do that here we’ve violated the spirit

Re: create query with date values

On Friday, December 31, 2021, ourdiaspora wrote: > Recipients, > > How to create a query such that when a date value is entered, the date may > be associated with the result of another query. > > The scenario is that a date value is entered (the plan is these dates > should be a separate table),

Re: Visibility of data from table inherits function

On Sunday, January 2, 2022, ourdiaspora wrote: > > > Please could someone explain why the data in the table 'exampletable' is > not visible from the query using the child table? > > Does not 'INHERIT' function apply to the data of the precedent parent > table? > > Apparently not…and you can

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On Wed, Dec 22, 2021 at 6:27 PM Bryn Llewellyn wrote: > > * update t set t.v = p.v where t.k = p.k;* > At run-time, p() terminates with an obscurely worded error: > > 42703: column "t" of relation "t" does not exist. > > "set t.v" is simply invalid SQL and the error has nothing with this

Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

On Wed, Dec 22, 2021 at 1:54 PM Bryn Llewellyn wrote: > > Is this model not used for the PostgreSQL system? > It is not. Basically we have an "Proposed Patches" tracker and they either get committed, or they do not. Once committed, new features become available at the next annual release. >

Re: csv copy error

On Wednesday, December 29, 2021, Adrian Klaver wrote: > On 12/29/21 13:08, ourdiaspora wrote: > >> >> " >> \copy exampletable from '/local/path/to/examplefile.csv'; >> " >> > > \copy exampletable from '/local/path/to/examplefile.csv with csv'; > > Right idea though that won’t execute for three

Re: Default values in functions

On Wednesday, December 29, 2021, Michael Lewis wrote: > > > If I can somehow pass "use default for this parameter" to functions like I > can for insert statements, then that would be great to know. > > There is not. > > Generally, the use case I am looking at is having a function with a few >

Re: How to confirm the pg_hba.conf service is correctly working

Please don't top post; the convention here is to inline or bottom post and trim unnecessary quoting. On Tue, Dec 21, 2021 at 9:24 PM shing dong wrote: > > - remove > yum remove postgresql* > > --- install > yum -y install >

Re: storing zipped SQLite inside PG ?

On Tue, Dec 21, 2021 at 10:06 PM David Gauthier wrote: > I'll have to read more about sqlite_fdw. Thanks for that Steve ! > > Each SQLite isn't that big (billions of records), more like 30K records or > so. But there are lots and lots of these SQLite DBs which add up over time > to perhaps

storing zipped SQLite inside PG ?

On Tuesday, December 21, 2021, David Gauthier wrote: > > OK, you get the picture. I'm all ears :-) And thanks in advance for any > suggestions ! > This is basically a variant of “should I store images in the database” but the content sizes involved are insanely high compared to most images.

Re: Update concurrency

On Monday, December 20, 2021, Sylvain Déve wrote: > > To summarize, how can I allow for multiple updates of the same attributes, > one after another, even when these updates are requested almost at the same > time from independent, uncontrollable client requests? > Most people use an

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

On Mon, Dec 20, 2021 at 3:24 PM Ray O'Donnell wrote: > On 20 December 2021 22:10:19 Shaozhong SHI wrote: > >> Is there a way to turn select result into something like 'a|b|c' . >> Regards, >> David >> > > On my phone so hard to reply properly, but if a, b and c are in separate > rows then you

Find missing data in a column

On Wednesday, December 29, 2021, john polo wrote: > > I understand this means there is a problem with one or more rows in the > column referenced. This is a column of text. How do I find the error(s) in > question? > It doesn’t actually mean anything…you cannot use the copy command to import

Re: Error when rewriting a query into multiple queries

On Friday, November 19, 2021, Huang Wang wrote: > Is there any way to do it on the server or it's better to do it in the > application? > Application; and for me at least modifying the server to do this is an extremely bad idea that goes against its fundamental design. David J.

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

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

Re: PQexecParams, placeholders and variable lists of params

On Tue, Nov 23, 2021 at 7:21 AM wrote: > Makes sense. Problem is, that, again, the application would be > responsible of making sure the individual values don't contain nasty > stuff (for example, if they are strings) before consolidating them to > one PostgreSQL array literal. > > So long as

Re: Execute command in PL/pgSQL function not executing

On Fri, Nov 19, 2021 at 3:37 PM Abdul Mohammed wrote: > I read somewhere that you could have a table as a return type. I, > therefore tried using the pivot table alias as the return type but got an > error along lines of the alias not being recognized. > A table in that context is something

Re: Execute command in PL/pgSQL function not executing

On Thu, Nov 18, 2021 at 4:24 AM Abdul Mohammed wrote: > The second part tries to use the string to build a pivot table using the > crosstab function. > Aside from the simple learning curve on how to write functions in PostgreSQL you also are dealing with the fact that you are dealing with a

Re: Are all unlogged tables in any case truncated after a server-crash?

On Thu, Nov 11, 2021 at 11:39 AM wrote: > After > that all unlogged tables remain completely unchanged (no > DML-/DDL-Statements). Hence all of my huge unlogged, "static" tables get > never "unclean" and should not be truncated after a server crash. > The server cannot make this assumption so

Re: I added a √ operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

ly, but it will > affect the existing functions. > > I don’t know why this happened. Please forgive me for not clarifying my > focus. > > > ------ 原始邮件 -- > *发件人:* "David G. Johnston" ; > *发送时间:* 2021年11月12日(星期五) 中午12:23 > *收件人:* &quo

Re: I added a √ operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

On Thursday, November 11, 2021, 孤傲小二～阿沐 <2903807...@qq.com> wrote: > Hello, I think what you said is right, it should be the problem. But I > don't know what to do in the lexical analysis system of plpgsql to solve > this problem. > > “To match” means keep two copies identical, in this case

Re: I added a √ operator, the sqrt function is still used internally, but now there is a problem, it affects the := and .. operators of the database

On Thu, Nov 11, 2021 at 8:42 PM 孤傲小二～阿沐 <2903807...@qq.com> wrote: > I added a √ operator to scan.l. > Why? > The sqrt function is still used internally, but there is a problem now, > which affects the := and .. operators of the database. > Someone else will have to volunteer their time to

Re: Match 2 words and more

On Sat, Nov 27, 2021 at 5:27 PM Shaozhong SHI wrote: > this is supposed to find those to have 2 words and more. > > select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ > ]+[:alpha:]+$'; > > But, it finds only one word as well. > > It appears that regex is not robust. > > Can anyone shed

Re: Merge into does not work

As evidenced by the lack of such a command in our documentation this doesn't exist. We do offer similar functionality directly as part of the INSERT command via its ON CONFLICT clause. David J.

insert column monetary type ver 2

On Sunday, November 21, 2021, Ron wrote: > On 11/21/21 7:33 PM, Adrian Klaver wrote: > >> On 11/21/21 17:08, Ron wrote: >> >>> On 11/21/21 10:27 AM, Tom Lane wrote: >>> Ron writes: >>> >> The contrary argument is that people tend not to like "best guess" behavior when working

Re: Account privileges

On Tuesday, November 16, 2021, Prathima Mulpuri wrote: > > Please share any ideas and solutions for my requirement. > Drop the “function or a stored procedure” requirement, use an external application language instead. Node.js for example. Separate the listing of extra/missing privileges from

Re: function difference not found

On Thu, Nov 25, 2021 at 9:13 AM Shaozhong SHI wrote: > Function difference not found. > > Should an extension created for finding it? > > It's an operator (there may be a backing function that isn't documented)... SELECT 5 - 2; -- yields 3, the difference of 5 and 2. David J.

Re: Best examples of cardinality check and associated functions

On Thu, Nov 25, 2021, 16:17 Shaozhong SHI wrote: > Hi, Rob, > > I am reviewing robust automation to do so and promote the best practice. > > Maybe show what you've come up with so far, regardless of whether it's best practice or not, so to help overcome the apparent communication barrier. David

Re: jsonb: unwrapping text

On Wed, Oct 27, 2021 at 11:58 AM wrote: > > I've found out that one can treat a string singleton as if it > were an array: > > foo=# select '"foo"'::jsonb ->> 0; >?column? > -- >foo > (1 row) > > which conveniently returns the right type. My question: can I rely > on that,

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

On Friday, October 29, 2021, Philip Semanchuk wrote: > 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'] Not sure why the single quotes are getting stripped out but

Re: Determining if a table really changed in a trigger

On Tue, Oct 26, 2021 at 12:05 AM Mitar wrote: > > But this fails if the table contains a JSON field with the error: > > could not identify an equality operator for type json > Thus it is not possible to use whole row comparisons. You will need to write the code to manually check equality on

Re: How to copy rows into same table efficiently

On Tue, Oct 26, 2021 at 2:06 AM Ron wrote: > Anyway, for millions of rows, I might use COPY instead of INSERT > (depending > on how many millions, how many indices, how large the rows, how fast the > machine, etc. > > I don't imagine using COPY TO to write the data to a file and then COPY FROM

Re: Looking for a doc section that presents the overload selection rules

On Friday, October 22, 2021, Bryn Llewellyn wrote: > > There could, so easily, have been three “to_char()” overloads for these > three data types that honored the spirit of the “::text” typecast by > rendering only what’s meaningful, despite what the template asks for. > Even if we added

Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value

On Tue, Nov 9, 2021 at 7:50 AM Avi Weinberg wrote: > Was this done on purpose, that default value for new column is not copied > for existing data? Does this mean that on destination side we must also > define the table with default value? > Logical replication has two modes, initial

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

On Wednesday, November 3, 2021, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, November 3, 2021, Shaozhong SHI > wrote: > >> What is the regex for (A) and (B) to find in Bus Stop (A) or (B)? >> > > Not tested… > > ^Bus\sStop\s\(

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

On Wednesday, November 3, 2021, Shaozhong SHI wrote: > What is the regex for (A) and (B) to find in Bus Stop (A) or (B)? > Not tested… ^Bus\sStop\s\((\w)\)\sor\((\w)\)$ The \s can just written as a space though the above seems clearer in email (though it allows for non-space whitespace too)

Re: CTE Materialization

On Thursday, December 2, 2021, Paul van der Linden < paul.doskabou...@gmail.com> wrote: > Hi, > > when switching to postgres 14 (from 11) I'm having some slow queries > because of inlining of CTE's. > I know I can get the same result as with PG11 when adding MATERIALIZED to > the cte, but the

Re: INSERT ... ON CONFLICT doesn't work

On Wed, Dec 1, 2021 at 12:52 PM Tom Lane wrote: > It's also possible to qualify the name in the ON CONFLICT clause, > although I think you have to parenthesize it to do so: > > ... ON CONFLICT (("Lockers"."Uuid")) > > This really needs to be confirmed and, if so, better documented on the INSERT

Re: INSERT ... ON CONFLICT doesn't work

On Wed, Dec 1, 2021 at 12:21 PM Jenda Krynicky wrote: > > CREATE OR REPLACE FUNCTION public.findorcreatelocker( > lockeruuid text, > ipaddress text) > RETURNS TABLE("Id" integer, "Created" timestamp without time > zone, "Uuid" text, "State" integer, "ConnectionStatus"

Re: INSERT ... ON CONFLICT doesn't work

On Wed, Dec 1, 2021 at 12:44 PM Jenda Krynicky wrote: > How's that a variable for gawd's sake? It's a column name too! A > column name in the definition of the resulting table. > The columns of the returns table are provided to the function as variables so that one can write: output_column1 :=

Re: INSERT ... ON CONFLICT doesn't work

On Wed, Dec 1, 2021 at 12:59 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Dec 1, 2021 at 12:52 PM Tom Lane wrote: > >> It's also possible to qualify the name in the ON CONFLICT clause, >> although I think you have to parenthesize it to do s

Re: INSERT ... ON CONFLICT doesn't work

On Wed, Dec 1, 2021 at 1:04 PM Jenda Krynicky wrote: > I did not create the table and I did not forget the quotes. I removed > them in one of many attempts to appease PostgreSQL. I've already > learned about those braindead rules. > > Nothing is perfect, especially something that started decades

Re: CTE Materialization

On Tue, Dec 7, 2021 at 6:40 PM Дмитрий Иванов wrote: > I beg your pardon. > The problem is more or less clear to me, but the solution is not. What > does the "hack is to add an "offset 0" to the query" suggest? Thank you. > > A subquery with a LIMIT clause cannot have where clause expressions in

Re: locks within select

On Monday, December 13, 2021, Marc Millas wrote: > > I was wondering if for example, within a join, some kind of lock may be > acquired so that the dataset concerned is not changed during execution ? > (for example a delete then autovacuum ??) > Read this primer on MVCC from the docs:

<    4   5   6   7   8   9   10   11   12   13   >