normal? How can I simply resolve the problem?
Calculate the dat column in a subquery in the outer-level query.
Something like:
FROM (select *,to_char(dat, 'MM') as strdat from table1) table1
JOIN table2 USING (num_poste)
then use strdat in all the places you're currently using to_char(dat, 'MM')
David
On Tuesday, April 29, 2025, Tim Starling wrote:
>
>
> This is a public interface and there may be callers in code that I don't
> have access to.
>
You might help your cause by sharing examples of how client code uses your
driver to perform upsert that runs into this limitation.
David J.
to force
everyone to rewrite their RETURNING statements when upgrading to v18.
The moral of that story is, UPDATE isn't exactly consistent already
about when it requires column qualifications. Maybe it's weird to
insist that users qualify columns with their ON CONFLICT UPDATE SET
when RETURNING is happy to assume you must have meant NEW.
David
ocabulary word "jovial" if that word is
not already in the dictionary, or if it is already in the dictionary, it
increments the counter. The "count+1" expression could also be written as
"vocabulary.count". PostgreSQL requires the second form, but SQLite accepts
either.”
https://sqlite.org/lang_upsert.html
David J.
the broader
world. I am curious as to whether we are in strict adherence to the SQL
Standard on this point though. Makes deviation a bit tougher to justify.
It does seem that project policies would prevent back-patching such a
change.
David J.
t's possible that you can use an event trigger [1]
that triggers on CREATE TABLE to get what you want here. If the docs
don't give you enough information, then maybe [2] will have something
you can work with. I've not used them much, so I don't have any
recipes for you.
David
to output the query against that table?
David J.
rmatively for the tuples and error cases
and have a final else should the status be something unexpected.
David J.
l PQclear() on every iteration of the loop?
>
Would make processing more than a single row impossible if you throw away
the result after processing one row.
David J.
On Fri, 25 Apr 2025 at 03:06, Laurenz Albe wrote:
>
> On Fri, 2025-04-25 at 01:41 +1200, David Rowley wrote:
> > The 79.3 seconds is the total time spent doing reads for all parallel
> > workers. 52.6 seconds is the wall clock time elapsed to execute the
> > query.
>
&g
; I/O Timings: read=79368.246 write=11.486
>
> So, the total execution time is 52655 ms ok
> and the total time for i/o is...79368 ms
>
> how ???
The 79.3 seconds is the total time spent doing reads for all parallel
workers. 52.6 seconds is the wall clock time elapsed to execute the
query.
David
On Wednesday, April 23, 2025, Igor Korot wrote:
>
> The question is more about the default value...
>
0 or 1, determined at server compilation time. You quoted the
documentation that says this…
David J.
If they try and it errors, it doesn’t
support it.
David J.
same can be sad about
> https://www.postgresql.org/docs/current/runtime-config-
> query.html#GUC-RANDOM-PAGE-COST
Costs can’t be negative and no reasonable positive value is going to exceed
the data type limit, which is communicated via the data type specification.
David J.
igm, you can’t
remove columns from existence on a per-row basis.
David J.
You can do what you want if you directly insert into the individual
partitions, using the partitioned table as a read-only interface.
It looks like this:
https://dbfiddle.uk/Xc3cUHSO
David J.
I don't find that the information is hard to understand so no, I'm not
going to spend my time summarizing it in an email.
David J.
On Sat, Apr 19, 2025 at 6:06 AM sivapostg...@yahoo.com <
sivapostg...@yahoo.com> wrote:
> By any chance, if I get that statement, what should I do?
>
Read it.
What are the Steps (or documentation) to correct this issue?
>
>
Impossible to say until the statement is read.
David J.
of your
databases UUID names and ensure that non-superusers must be told the
databases they are allowed to connect to.
But feel free to work out a design and add it to the ToDo list for the v4
protocol. The use case seems reasonable and doable (on the basis of the
replication protocol works).
https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes_.2F_v4_Protocol
David J.
n or experience that they are
"postgres", "template1" and "template0".
Pick whichever of "system" or "convention" or some other word makes you
happy to categorize those three databases. Though I suggest "the initdb"
databases since neither of the other terms is used anywhere in the
documentation.
David J.
On Tue, Apr 15, 2025 at 11:20 AM Adrian Klaver
wrote:
> On 4/15/25 09:48, David G. Johnston wrote:
> > On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> >
>
> >
> > Agreed.
> >
> > The
On Tue, Apr 15, 2025 at 9:31 AM Adrian Klaver
wrote:
> On 4/15/25 09:21, Igor Korot wrote:
> >
> >
>
> > Hi, David,
> >
> > On Tue, Apr 15, 2025 at 9:56 AM David G. Johnston
> > mailto:david.g.johns...@gmail.com>> wrote:
> >
> >
On Tuesday, April 15, 2025, Igor Korot wrote:
> Hi, ALL,
> Is there a field in the pg_databases table which indicates that particular
> DB is a system one?
>
What is a system database?
David J.
version that just confused
the issue. Why point out exclude blobs if you know they are using bytea?
David J.
On Friday, April 11, 2025, sivapostg...@yahoo.com
wrote:
>
>
> bytea field also included in the backup.
>
Bytea typed columns are completely separate things than large objects. You
cannot exclude individual columns using pg_dump.
David J.
On Friday, April 11, 2025, Igor Korot wrote:
> Hi, David,
>
> On Fri, Apr 11, 2025 at 9:04 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Fri, Apr 11, 2025 at 6:49 PM Igor Korot wrote:
>>
>>> Hi, ALL,
>>> On the http
P-Gist:
fillfactor
GIN:
fastupdate
gin_pending_list_limit
BRIN:
pages_per_range
autosummarize
David J.
volatility of the used functions and compare
them to the volatility of the UDF.
David J.
unction volatility match the most volatile function you internally
call - constant input arguments don’t change this.
There is no reason to perform number formatting immutably - function call
results involving table data are not memoized.
David J.
olution setup this might impose a
non-negligible
+performance penalty.
This parameter can only be set in the
postgresql.conf
file or on the server command line.
Both the connection logging routine and log_line_prefix %h / %r simply
report the "identifier of the host making the connection".
David J.
y because \df+ does show this information as well, so at least one
doesn't have to go write the catalog query themself.
David J.
IMMUTABLE.
>
So, the punishment for lying about the volatility of one's function is to
prohibit it from being inlined even in a case where had you been truthful
about the volatility it would have been inlined.
David J.
>>>>>>
> PSQL should not have asked for the password.
> Thanks
> Danny
>
Suggests there are environment variables coming into play making
non-default choices for you. Namely, making you not use local as the
connection method.
David J.
On Wed, Apr 9, 2025 at 9:20 AM Abraham, Danny wrote:
> Fail
>
Failures include messages indicating why. You should always share such
messages. Showing the command that produced the failure is also advised.
David J.
els if doing this check on
large bloated tables is what your existing choices have led to.
David J.
On Thu, Mar 20, 2025 at 2:56 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thu, Mar 20, 2025 at 11:54 AM Tom Lane wrote:
> >> I think it's a mistake to suppose that pg_type_d.h is the only
> >> place where there's a risk of confusi
On Sunday, March 23, 2025, David G. Johnston
wrote:
>
> Maybe IOS helps though I do wonder whether a sequential scan skips over
> known all-dead pages making that relative benefit go away.
>
Well, no, since it tracks known visible, not known non-visible, though for
something like a
On Sat, 29 Mar 2025 at 06:00, Evgeny Morozov
wrote:
>
> On 23/03/2025 2:35 pm, David Rowley wrote:
> >> alter table entity_2 add column new_column text;
> > Is this just an example command? You can't add a column to a
> > partition directly.
>
> Yes, it was
of assuming an even
distribution, you use that minimum value to tell you what percentage
of the index must be read before a match is found. The stored maximum
position value would do the same job for backward index scans.
David
y is
positional information of where certain values are within indexes
according to an ordered scan of the index. I don't quite know how we'd
represent that exactly, but if we knew that a row matching col_a >
4996 wasn't until somewhere near the end of idx_col_a_btree index,
then we'd likely not want to use that index for this query.
David
pose.
There is a way to get a different interpretation for IST to be recognized
but I'd have to find it or wait for others to chime in.
David J.
from the manual
because people are bad at predicting the future. Just document what is and
don't string people along. We never go back to old releases and say "This
was fixed in v17."
David J.
rnative approach for that use
case could be suggested.
David J.
g in the relevant spot in [1].
David
[1]
https://postgr.es/m/CAApHDvogvzANoTOCyXUWgEuPFx1nT6S63aAN0bDRSJ=tagb...@mail.gmail.com
alues you are actually going to be filtering on. So low cardinality
booleans can be highly selective in usage if you are looking for the rare
false in a sea of trues but low selectivity if looking through those trues.
David J.
On Fri, Mar 28, 2025 at 10:02 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> Selectivity is evaluating cardinality with an eye on the frequency of
> the values you are actually going to be filtering on. So low cardinality
> booleans can be highly selective in u
On Sunday, March 23, 2025, Igor Korot wrote:
>
> 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
> version?
>
No. You have to drop the trigger if it does exist and then create the new
one.
David J.
On Sunday, March 23, 2025, David G. Johnston
wrote:
> On Sunday, March 23, 2025, Igor Korot wrote:
>
>>
>> 2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
>> version?
>>
>
> No. You have to drop the trigger if it does exist and then cr
On Sunday, March 23, 2025, Igor Korot wrote:
>
> CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague
> WHEN new.current_rank IS NULL
>
>
When the syntax shows parentheses you are required to write them.
[ WHEN ( *condition* ) ]
David J.
without exists #1 returns either an empty set
or an , never NULL.
Always returning a Boolean seems like a better API choice though; but
standardizing on that final transform doesn’t change the base comparison.
David J.
'server_version_num')::int > =14 as v14
>
IOW, you can’t use >13 because that will match v13.1 which is 130001 in
integer format.
David J.
not a true group role I suspect inherit/noinherit doesn’t
apply. (You also cannot SET to it, nor admin it - not tested.) Losing the
execute privilege on every built-in function would be way too annoying.
David J.
LAIN ANALYZE.
It is possible to determine the number of partitions which were
removed during this phase by observing the “Subplans Removed” property
in the EXPLAIN output."
Perhaps something like. "It's important to note that any partitions
removed by the partition pruning done
On Saturday, March 22, 2025, Igor Korot wrote:
>
>
>> Is it actually running in psql?
>>
>
> Yes, i run "psql - d draft -a -f
>
Then read the psql docs. Your version has \if and you’ll find server
version listed as the available client variables.
David J.
t code" would seem mostly interested in these
OIDs and not stuff like the attribute numbers of the columns in pg_type. I
get a distinct feel of one file serving multiple use cases.
> As for CASHOID and LSNOID, surely those have been deprecated long
> enough that we could just remove them?
>
>
I'd probably just leave them.
David J.
e invested in the outcome you have more motivation than probably anyone
else to dive into it and make concrete suggestions for change.
All that said, a comment at the top of what is probably the most important
section of the header seems warranted. Even if it is just mostly
formality. Mentioning the constant-ness of the integers should be part of
that.
David J.
m PUBLIC. You have to revoke a granted privilege.
David J.
ssumption is that at moderate to high reuse volumes it is quite
probable that a generic plan will win or at least be acceptable in 999 in
1000 or more executions, not 4 in 5.
David J.
'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid =
>>> 'U-523596'::text))
>>> Heap Fetches: 1
>>> Planning Time: 0.084 ms
>>> Execution Time: 0.043 ms
>>>
>>
>>
>> To return one row takes 43ms is not optimal
>>
>
You are off by a factor of 1000 in your claimed performance. It’s 0.043ms
David J.
wn IDs as part of the test data specification.
David J.
null);
>
> Any suggestions would be appreciated.
>
>
Using "equals" or "not equals" when one of the inputs can be null is not
usually what you want to do. The comparison evaluates to NULL which the
constraint allows.
David J.
rent/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT
See: client_min_messages
David J.
EN CREATE UNIQUE INDEX
> \"abcatc_x\" ON \"abcatcol\"(\"abc_tnam\" ASC, \"abc_ownr\" ASC,
> \"abc_cnam\" ASC); END IF; END;" );
> [/code]
>
> What am I missing?
>
“ ERROR: unterminated dollar-quoted string at or near‘
The terminator for your dollar-quoted string.
David J.
logical images of the database so applying WAL
on top of pg_restore is technically invalid - but it does effective convey
the idea. It’s like saying pg_dump and pg_basebackup are similar. Sure,
in some ways that is true - but the logical vs. physical distinction cannot
be ignored fully.
David J.
t is only possible of you’ve
backed up the data files at some point in the past and use that backup.
David J.
On Saturday, March 1, 2025, Krishnakant Mane wrote:
>
> Can you tell me how exactly should the syntax be?
>
https://www.postgresql.org/docs/current/ddl-schemas.html
> Is my function definition wrong?
>
It isn’t a function definition, it is a function call.
David J.
tputs from the above three commands in your response.
>
> See this issue for why this is different now.
https://github.com/sraoss/pg_ivm/pull/116
Then deal with the fact it is no longer installed to pg_control but instead
pgivm. With usual schema qualifications or search_path techniques.
David J.
e
feature isn't driving its development the odds of it getting worked on is
fairly low.
David J.
s you have no way to know what the
caller has specified as the return data type in order to write the inner
generic function call correctly. Maybe you can convert the record result
to jsonb and return that?
David J.
source; or use
something like pgbouncer.
David J.
lumn, for all existing rows, when it was executed. While the value
of the substitute is equal to the non-volatile default specified for the
column it is an independent thing. Subsequently dropping or changing the
default does not impact this substitute value. There is no way to impact
the substitute value via SQL that I know of.
David J.
nything in this case.
> So I don't see how these recommendations are relevant to this particular
> case. --DD
>
>From “drop owned”:
Any privileges granted to the given roles on objects in the current
database or on shared objects (databases, tablespaces, configuration
parameters) will also be revoked.
So, the command does more than the name suggests.
David J.
le. Does the combination of reassign
and drop owned not fulfill the requirements?
https://www.postgresql.org/docs/current/role-removal.html
Also, you had to know the role you wanted to drop so you already figured
out the grantor.
David J.
n. Why isn't it?
>
This has nothing to do with power/permissions. It is about not specifying
“granted by” in your SQL command and thus failing to fully and correctly
specify the single permission you want to revoke.
David J.
On Thu, Feb 20, 2025 at 9:05 AM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thursday, February 20, 2025, Dominique Devienne
> > wrote:
> >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
> >> did nothing, even with
On Thursday, February 20, 2025, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thursday, February 20, 2025, Dominique Devienne
> wrote:
>
>> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
>> did nothing, even with CASCADE, when I
gt;
> It doesn't like that reference to "d.dvm_id) in that subquery.
You could use LATERAL before the subquery in the FROM clause, or you
could adjust the subquery by removing the "where dvm_id=d.dvm_id"
replacing it with GROUP BY dvm_id and adding that column to the SELECT
list and include that in the join condition between the tables.
David
needs is a pointer to represent the future data, not the data itself. And
PERFORM will never resolve that pointer by itself - so as Tom said your
query would need to force pointer resolution by computing on the data.
David J.
his is our contrib module so this seems like the expected place to ask
such a question. It’s neither a bug nor a topic for -hackers. FTR, Robert
Haas is the author from 2013. Not sure he monitors -general though.
David J.
s the pg_prewarm call fail [1] in the above scenario?
>
> [1] https://github.com/postgres/postgres/blob/master/contrib
> /pg_prewarm/pg_prewarm.c#L108-L110
>
It fails because AFAICS there is no way for it to work on an index, only
tables.
David J.
st doesn't make sense as stated.
David J.
f space I mentioned earlier, in the config text literal, is fine.
David J.
nstants [2] require single quotes or the dollar quoting
format and 5min isn't a valid number.
David
[1] https://www.postgresql.org/docs/17/sql-set.html
[2]
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS
SET
> idle_in_transaction_session_timeout = 1min
> 2025-02-10 04:17:19.845 GMT [2467575] ERROR: trailing junk after numeric
> literal at or near "1m" at character 43
> 2025-02-10 04:17:19.845 GMT [2467575] STATEMENT: SET
> idle_in_transaction_session_timeout = 1min
> ...
>
David J.
ey is the usage side.
David J.
to reverse
the effects of a previous per-schema GRANT.
David J.
ith the correct matching values showing up
in the UI?
David J.
e business should probably
be considered part of its primary key - they don't announce "under new
ownership/management" just for fun - the new owner wants to keep the brand
recognition but discard historical opinions that are likely no longer true.
David J.
On Tuesday, February 4, 2025, Rich Shepard wrote:
> On Tue, 4 Feb 2025, David G. Johnston wrote:
>
> The point of a lookup table is to provide a unique list of authoritative
>> values for some purpose. Kinda like an enum. But having the label serve as
>> the unique value is
ve
values for some purpose. Kinda like an enum. But having the label serve
as the unique value is reasonable - we only add surrogates for optimization.
David J.
On Tuesday, February 4, 2025, Ron Johnson wrote:
> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tuesday, February 4, 2025, Rich Shepard
>> wrote:
>>
>>> Should lookup tables have a numeric FK co
nd is a
smaller value.
Lots of alter tables and update queries.
David J.
On Thu, Jan 30, 2025 at 3:46 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Jan 30, 2025 at 3:44 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Thu, Jan 30, 2025 at 3:38 PM Rich Shepard
>> wrote:
>>
>
On Thu, Jan 30, 2025 at 3:44 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Jan 30, 2025 at 3:38 PM Rich Shepard
> wrote:
>
>> On Thu, 30 Jan 2025, Rich Shepard wrote:
>>
>> > Thank you. I'll look into using the \set command.
>>
; Please point me to a reference where I can learn how to get the user input
> string into the script.
>
That is what \prompt is for. You have the correct meta-command, you were
capturing user input just fine. Read about how to use variables in queries
for the part you are missing.
David J.
On Thursday, January 30, 2025, Rich Shepard
wrote:
>
>
> What's the correct syntax for the \prompt?
>
Prompt isn’t your issue. Prompt stores the value into a variable. Read
how to reference variables in a psql script.
David J.
hat does that + symbol mean in this context?
>
The value has a line break break character sequence at that point (I forget
if it has any awareness of CR and CRLF differences…)
David J.
nce.
>
The description was correct even though using probably imprecise
terminology. The basic goal is to delete childless parents.
David J.
On Friday, January 17, 2025, Brent Wood wrote:
>
> I want to concatenate the hstores,
>
>
There are no hstore aggregate functions. You’ll want to convert them
to,json first then use the json_objectagg aggregate function.
David J.
;. We maybe could do a bit more work to initialise those
more lazily as we do for INSERT statements, but I'd be surprised if it
was a problem for 64 partitions, especially so for an update statement
that might be touching 3 months of data. Nothing about these existing
in the "Update on" portion of the EXPLAIN output means that that
partition will be scanned by the UPDATE statement, rest assured.
David
1 - 100 of 1792 matches
Mail list logo
