], array[null,4]);
> tt
> ---
> {3,4}
> (1 row)
>
Plain SQL variant:
SELECT array_agg(COALESCE(a, b))
FROM (
SELECT
unnest(ARRAY[null, 2]::int[]),
unnest(ARRAY[1,null]::int[])
) vals (a, b);
Even if they aren't the same length the above should work, I think, as
extra rows for the shorter array will contribute padded nulls.
David J.
; and therefore can be only marked as Stable and not Immutable?
>
Yes
Forever means beyond the lifetime of a single transaction and thus it is
possible for the changing of the table contents to impact the return value
of the function.
David J.
On Tue, Jun 25, 2019 at 2:58 PM David Gauthier
wrote:
> I need to create a constraint on a column of a table such that it's value
> is found in another table but may not be unique in that other table.
> Example...
>
This requires a trigger
>
> Let's say the DB is a
ined as being STRICT (null on null input).
You need to COALSESCE SQL NULL to JSON 'null' to obtain the result you
desire. This is a general truth when dealing with the JSON type in
PostgreSQL.
select jsonb_set('{"foo": 1}'::jsonb, '{bar}',
coalesce(to_jsonb(null::int), 'null'), true)
David J.
>
>
Sql null poisons the function call which immediately returns sql null
> > select to_jsonb('null'::text);
> ┌──┐
> │ to_jsonb │
> ├──┤
> │ "null" │
> └──┘
>
>
Json null
> I'm sharing Thomas's confusion…
>
>
Sql null and json null are represented differently; strict functions with
sql null inputs yield sql null output without even executing the function
David J.
On Thu, Jul 4, 2019 at 2:09 PM David G. Johnston
wrote:
> On Thursday, July 4, 2019, Gianni Ceccarelli
> wrote:
>
>>
>> > select to_jsonb('null'::text);
>> ┌──┐
>> │ to_jsonb │
>> ├──┤
>> │ "null" │
>&g
back from
> the database.
>
>
There isn't anything special about a failed transaction compared to any
other transaction that you leave open.
Might help to describe what the application does with the connection
subsequent to the point of attempted commit.
David J.
On Mon, Apr 24, 2023 at 12:56 PM David Wheeler
wrote:
>
>
> On 25 Apr 2023, at 1:47 am, David G. Johnston
> wrote:
>
>
> There isn't anything special about a failed transaction compared to any
> other transaction that you leave open.
>
>
> Now I’m cu
#x27;t, that is a server-side configuration.
"Specifies the dynamic shared memory implementation that the server should
use."
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY
David J.
So is this possible?
>
The output of a set-returning function (srf) is a single tabular result.
If you want to produce multiple tabular results you would need to either
serialize them (say into jsonb) or assign a cursor name to each and then
reference them by name.
David J.
On Thu, May 4, 2023 at 7:04 AM Justin wrote:
>
>
> On Thu, May 4, 2023 at 9:49 AM DAVID ROTH wrote:
>
>> 1) Can I create a trigger on a view?
>> 2) Do triggers cascade?
>>
>> Say I have an insert trigger on a table.
>> And, I have an insert trigger on
age (Load-and-Go) Format
>
>
>
IIUC (I haven't formally studied compilers), none of the above, or, rather,
not applicable.
I can conjure up an analogy that says the assembly language artifact has a
similar relationship to the operating system kernel as the compiled plan
has to the database executor.
David J.
; 9 | SgrA*
>
> Notice that 12 is missing in the list.
> Even with x as (select distinct on(symb) * from stars) select * from x
> where nb = 12 order by nb;
>
nb = 12 is a duplicate with np = 34
Since your DISTINCT ON *subquery* doesn't specify an ordering which of
those two are chosen as the representative record for M31 is
non-determinstic.
If you want to ensure the lowest valued nb is chosen you need to sort the
*subquery*. The first record the DISTINCT encounters is the one selected
to represent.
Sorting in the outer/main query happens after the DISTINCT and so the
record is already gone.
David J.
tly installed version of what, the server or the client?
>
It's an SQL Command, no specific client can/should be presumed.
David J.
>
mposite_type.column1
I don't know why I'd want to give up the expressiveness of writing the
column name.
David J.
ape their
query level, and if you then try to cast the concrete record to some other
concrete type a cast needs to exist.
David J.
ility to change row
> structure on the fly making the cast possible? In what way would the query
> calling get_row() be critical?
>
Row() is pure syntax. It distinguishes (col) vs. row(col) where the first
is just a column in parentheses and the second is a composite with one
column member.
David J.
re that this
row/relationship continues to exist, you may change other attributes".
David J.
6
> 7
> 8
> 9
> 10
> 11
> 12
> 13
> 14
> 15
> 16
> 17
> 18
> 19
> 20
> 21
> Time: 0.518 ms
>
> My question is, why postgres didn't print the 22 to 25?
> Can someone give some advice?
> Thanks in advance!
>
Some kind of visual interaction between psql, the pager, and your terminal?
David J.
gt;
> Can someone give a short SQL syntax hint ?
>
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS
David J.
these things you may
have larger model design and query writing concerns to deal with in
addition to being able to identify the problems specific error messages are
pointing out and trying to fix them.
David J.
https://gcc.gnu.org/onlinedocs/gcc-4.1.2/gcc/Gcov-Data-Files.html
Seems like a reasonable answer to "what it is".
David J.
t guaranteed ordered output you must place the order by in the
outermost query level (i.e., before your limit 50). Trying to do that for
dynamic SQL where you don't actually know what query you are working with
is going to be a challenge - maybe force the user to have the order by
column first in their query then just say "ORDER BY 1" in the wrapper query
you are adding? Basically have them write "row_number() over (order by)"
for their query and you then order by row number.
David J.
when using copy. Either the cast for a given single setting produces the
correct result or it doesn't. If you need a custom cast like this you have
to get away from COPY first. Usually that is best done after importing
data to a temporary table as text.
David J.
tom triggers if you need something other than this.
David J.
tify function
that can be parameterized and handles the SQL-injection stuff for you.
David J.
“1234:567”. This
> seems to match positive and then fails due to not being an actual number in
> the subsequent cast.
>
>
>
Works (returns false) here:
select '1234:567' ~ '^([0-9]+[.]?[0-9]*)$';
https://mail.google.com/mail/u/0/?tab=mm&zx=y3hfqt48pyg7#all/FMfcgzGsnLNNXcRVCVNpjQhGknMSVLKn
David J.
On Thu, Jun 29, 2023 at 7:42 PM wrote:
> Trying to write a script that will run on Linux, Windows, and Mac.
>
This seems impossible on its face unless you use WSL within the Windows
environment. And if you are doing that, then the pathing would be WSL
pathing, not native Windows.
David J.
On Thursday, July 6, 2023, Lorusso Domenico wrote:
>
> returning bt_info into _bt_info;
>
>
I think it’s “returning (bt_info).* into _bt_info;”
David J.
malformed data situations while preventing something
that is basically impossible to encounter in real life. Especially if you
also have separate individual indexes to make searching for specific subset
of the database faster (i.e., everything in a state).
David J.
ica-mode?
>
>
Relying on external search_path for system executed objects is
problematic. Don’t do it. Either attach a SET to the function or
schema-qualify references.
David J.
ession they are created in. Hence,
you get multiple temporary schemas if you have concurrent sessions using
temporary objects.
David J.
.
>
>
The first time a session needs a temporary schema it is assigned one which
is then immediately cleared out.
David J.
"changing the volatility marker" to be
prohibited just like we prohibit changing the return type.
David J.
arch_path even exists. Unless you
are writing custom operators, and even then, consider search_path to be
evil.
David J.
le # 1 - implied), the "raise" stuff goes
to stderr (file # 2)
IIRC you can do:
psql -af test.sql > test.out 2>&1
(order matters, left-to-right)
But you can search online for "output redirection in Linux" or some such if
you want to learn the Linux command line better.
David J.
ng the format function:
...
for rec in select
format('alter user %I with password %L', usename, 'newpassword')
from pg_user
loop
...
David J.
other matter.
David J.
hat was previously undefined is now defined. It is not a bug
to choose to not implement something.
David J.
t the privileges of the user that executes them by
> default. So if the user is read-only (i.e. has just SELECT privilege),
> then the function can't do any writes either.
>
>
>
By definition any function marked stable or immutable is read-only though
the system doesn't enforce that user-specified label.
David J.
se assist?
>
You need to provide more info - specifically the input data mentioned
above. Try making a reproducer on this fiddle site.
https://dbfiddle.uk/btGcOH30
David J.
On Thursday, July 20, 2023, Les wrote:
> I try to execute this on a very simple table, in a production database:
>
Please provide version information and any extensions you may have
installed.
David J.
e permission to do so is
granted to the role on the schema, not the database.
David J.
y don't know how to do it, wrtite a extension?
>
> Can someone give me some advice?
>
>
A formal extension seems like overkill. PostgreSQL provides an ability to
write customer user-space functions in C. You ought to be able to leverage
that for this specific task.
https://www.postg
bdevdb TO
> cbdevdbadmin;".
>
>
I'm quite happy that such a command doesn't go ahead and grant read, write,
and execute privileges on every table, function, and view in the database.
The thing that does what you describe is called SUPERUSER.
David J.
SET ROLE before creating a
database."
David J.
ld
be "postgres".
David J.
On Tue, Aug 1, 2023 at 2:27 AM jacktby wrote:
> Hi, I’m trying to develop a new grammar for pg, can you give me a code
> example to reference?
>
Use git blame or a repo history viewer.
David J.
system.
https://www.postgresql.org/docs/current/ddl-priv.html
And yes, this is a usability vs secure-by-default that hasn't seen enough
complaint to take on changing the default.
David J.
xplain automatically produces the explain output of a query that is
running for reals. The effect is identical to running explain analyze
except your output here is whatever the query would produce instead of the
explain output, which instead goes into the log.
David J.
g I am missing?
>
createuser has to login to the server to do its work. Whatever credentials
or method you use to connect via psql (usually via sudo and the postgres
o/s user) you need to use here as well.
David J.
ql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Otherwise, the solution is to do as you did: write the output to a file,
> trim out any extraneous lines, and then use that as a script.
>
Or in psql execute it using the \gexec meta-command instead of a semi-colon.
David J.
>
ges.html
> and list their tables.
>
Requires being connected to the database being inspected.
> My understanding is that this ability is inherited from the public role
> (could use confirmation of this)?
>
Yes, public is what gets the default connection grant to newly created
databases.
David J.
find the need.
David J.
red catalog column is that of the system bit type.
Creating a user bit domaim is going to have absolutely zero impact on this
situation.
If you really want to make this work and are willing to risk and deal with
side-effects of manual catalog updates you could maybe make the existing
cast implicit.
David J.
in a unique index, the CREATE INDEX command will fail
but leave behind an “invalid” index.
"Problem" includes you forcibly killing it while it is running.
https://www.postgresql.org/docs/current/sql-createindex.html
David J.
On Thu, Aug 17, 2023 at 8:43 PM Stefan Knecht
wrote:
> But that "invalid" index is being used by queries
>
>>
>>
Please don't top-post.
If it is used by queries it isn't invalid and thus its existence shouldn't
be surprising. So I'm not sure what you are saying.
David J.
On Fri, Aug 18, 2023 at 6:36 PM Justin Clift wrote:
> If the minor version is already tracked somewhere as well, that
> would be extremely useful for my use case.
>
>
The data directory doesn't have a concept of "minor version". Only the
installed libraries and binaries do.
David J.
blem?
>
Use the type appropriate getter, not getString, to retrieve the value of
the underlying real typed column.
Otherwise, I agree this seems like a bug, probably in the JDBC driver,
though one pertains to style as opposed to semantics since both answers are
technically correct.
David J.
execution plan you wish to see.
> >>>
>
> seems you can use it with SELECT INTO.
>
> explain (ANALYZE, BUFFERS, TIMING, VERBOSE ON, BUFFERS ON, WAL ON,SETTINGS
> ON)
> select count(*) as cnt INTO s from tenk1;
>
>
We try not to encourage such things. And CTAS is listed.
David J.
+---+
> 57 *** | (4,5) |
> 58 *** +---+
> 59 *** (1 row)
> 60 ***
>
The above (and the type definition...) is all that is relevant for the bug
report. Once you've gotten a value of some type nothing else in the system
that is simply passed that value is going to reevaluate the constraints.
David J.
>
ginates in order to know your options for removing it. In this case the
grant to the public group that all roles are a member of.
David J.
conf
>
> test rootcce
>
>
> and login pg via root user , it doesnt work
>
You are saying root os user can login as pg role cce but that line of the
pg_hba.conf doesn’t permit cce to login. In short, the mapping is useless.
David J.
On Sunday, August 27, 2023, pan snowave wrote:
> Hi
>
> "Show your psql command that is failing."
>
> [root@~ pg]# /usr/local/pgsql/bin/psql -p5432 -d db1 -h127.0.0.1
> psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:
> Ident authentication failed for user "root"
ou can specify a db role name via the
cli or libpq)
psql -U cce -h 127.0.0.1 -d db1
Keep in mind you could very well have two lines in your pg_ident.conf file:
test root cce
test root ddf
David J.
- start from scratch
with the software and then restore your backups.
I have no idea how you’d expect the software to run if you remove its
configuration file. You should undo that.
David J.
command is executed within a block, I would like to know if
> the event trigger fires when the line is executed or at commit time.
>
https://www.postgresql.org/docs/current/event-trigger-definition.html
There is nothing there about event execution being able to be deferred.
David J.
f that it is
documented to do are only doable by it in many cases, and in all cases are
done locally, not by the server. You cannot send those meta-commands to
the server, it has no clue what to do with them. And since you are using
JDBC you by definition aren’t using psql.
David J.
and wasn’t a syntax, or
runtime, error? Plus, the error is syntax, usually when you try something
that exists but is disallowed the system gives you some kind of invalid
state exception at runtime,
David J.
ranscript, your description is unclear.
David J.
ot all that familiar with them but they
were designed for non-atomic data values.
David J.
lue of a sequence is not an error.
>
The output of SELECT queries cannot be quieted, only redirected.
David J.
t is producing, I doubt it for the
reason mentioned. It isn't difficult to add "> /dev/null" to the command
line if that is what you want - no need to touch scripts given a capable
enough shell.
David J.
ns are black-boxes, it is not possible to
establish dependencies between them. This limitation is why many of the
rules I allude to above exist.
You have also not mentioned what version you are working with.
David J.
d be trying harder to isolate this down to a reproducible
test case and thus be able to provide more information without it being too
much.
David J.
tion also suggests that maybe the v14 instance has altered
default privileges setup that maybe the v15 doesn't have.
David J.
On Wed, Sep 20, 2023 at 2:48 PM Michael Corey
wrote:
> How can I check the default privileges?
>
\ddp
https://www.postgresql.org/docs/current/catalog-pg-default-acl.html
David J.
in, I would discourage you to fork psql. Would you be able to
> maintain the new upcoming versions in the future?
>
>
The OP seems to be used the term fork in a process sense, not forking the
source code. Process execution from within another program. “Launch” or
“execute” psql would be a better choice of wording here.
David J.
SQL prompts for a password when not using these mechanism,
> so does my tool.
>
Once you have the password you should utilize the PGPASSWORD environment
variable to get it passed to psql. It doesn’t matter in the least how you
obtained that password in the first place.
David J.
ec"d
> tool.
>
> Much safer than plain-text passwords floating around env-vars or
> temp-files. --DD
>
Sure, though maybe just some kind of “—password-on-stdin” option and then
the next input read from stdin is interpreted as the password, would be
more readily accomplished. Scripts should be sent via “—file” in that
usage but that seems desirable anyway.
David J.
/row access ordering, and figure
out where you have the same pairs of tables being accessed but in different
orders.
David J.
cation
> failed for user "my_group_worker**"*
>
> *** What am I doing wrong?
>
postgres != my_group_worker and you haven’t setup an ident mapping to
associate the two
David J.
ld from source?
>
>
https://yum.postgresql.org/news/rhel7-postgresql-rpms-end-of-life/
David J.
ng “the fast
> option” so adding the column which can be regenerated is overhead.
This question and statement makes zero sense to me. If you specify the
column name you’d get the values of that column like always.
I’m good, though, with the columns you can choose being a strict subset of
those that are output when you do not list any. Writing a select query to
get a faithful reproduction of the entire table’s contents is fine for an
API.
David J.
esn't make sense to specify data
for inbound generated data. So while we do have a POLA violation here the
desirability to now fix it years later is basically zero. And the current
behavior is at least defensible and consistent. And there is a very easy
way to get the desired output making any change that much harder a sell.
The error message maybe could use some help though, and if there isn't a
hint maybe add one.
David J.
On Friday, October 6, 2023, Tom Lane wrote:
> "David G. Johnston" writes:
> >> On 10/6/23 08:45, Ron wrote:
> >>> Nah. "The programmer -- and DBA -- on the Clapham omnibus" quite
> >>> reasonably expects that COPY table_name TO (output
t you need to use a different tool. Ideally you can just get the JSON
into whatever client software you are writing with and export it from
there. Doing it in psql is possible but a bit tricky. Doing it within the
server usually isn't worth the hassle.
David J.
valuated. Nothing the
optimizer does will change that. The planner for the function internals
does not know whether px will or will not be null on any given invocation.
David J.
r good
reasons. Stick with relational models in a relational database.
Also of note:
https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_varchar.28n.29_by_default
I'm tempted to add "Don't use inheritance" to that page...but fortunately
it doesn't come up that often.
David J.
On Tue, Oct 17, 2023 at 3:33 PM Jeff Laing
wrote:
> “Don’t use table inheritance” IS on that page
>
>
>
Indeed - oddly under "tool usage"...along with rules and, an actual tool,
psql -W
David J.
ossible to give
advice without knowing what you told it to do. You should put more effort
into showing others what your app is doing and how you are debugging it and
the database.
Killing sessions without changing anything else is unlikely to result in a
change of behavior.
David J.
gives the minimum as clarified in the prose. There is room in the
table to include more detail and it probably should.
David J.
nsert into t values (1);
> INSERT 0 1
> postgres=# begin;
> BEGIN
>
Session one can see this row and modify it just fine…
David J.
autocommit on and incorporate explicit begin/commit
commands into the script if you want to demonstrate concurrency behavior.
David J.
cluster
from scratch.
David J.
.conf entry added to permit those connections. All trust connections
in pg_hba.conf should be removed from it as quickly as possible.
David J.
you start the PgAmin?
>
>
Test it with a view that takes seconds to run.
David J.
ups, would simply be assumed better off done with a
generic plan involving an index scan (maybe based upon a table size check)
derived from the initial custom plan.
David J.
of. You can find stuff that, once you've executed the
script you can compare that database to some other and find differences.
David J.
>
>
e an
optimization to attempt.
David J.
1601 - 1700 of 2422 matches
Mail list logo
