sands of identical tables...) can either
reduce the need for them or make setting them up considerably easier,
respectively. IOW, starting with a higher level use case will produce more
useful responses.
David J.
over-engineering. If you really
want the count you'd need to add it to the table but the client can count
the records in the table easily enough. Errors can be done separately via
RAISE and likewise let the client deal with that as usual.
Otherwise I've found JSON to be useful for non-tabular results.
David J.
can find resources and then point people to those that exist
today next time questions come up (or have them asked on -general).
David J.
iven that no one is willing to make the field work as named)
to be the correct one (by a not-wide margin).
David J.
On Monday, July 30, 2018, Alvaro Aguayo Garcia-Rada
wrote:
>
> As far as I know, it's not currently possible.
>
That would be incorrect, you just need to change server startup commands.
https://www.postgresql.org/docs/10/static/runtime-config-file-locations.html
David J.
On Thursday, August 2, 2018, Suresh Raja wrote:
> yes ... how can i pass variable * to the function has_schema_privilege(*,
> 'schema-of-interest', 'usage');
>
You cannot...you must execute the function once for every user, hence the
original query's from clause.
David J.
till
access the data but would be challenged to make sense of it,
Usually DBAs are tasked with backups which requires read access to all
relevant data.
David J.
On Tue, Aug 7, 2018 at 12:09 PM, Day, David wrote:
> EXECUTE format(' UPDATE admin.user SET (%I) = ( SELECT %I FROM $1 ) WHERE
> id = $2)', USER_SETTING, USER_SETTING )
> USING NEW, NEW.id;
>
> When this executes my exception handler generates "
e in the languages
themselves) to mention what the check covers for each of them. At least
distinguishing between syntax and semantics for each.
David J.
provides that ability though you possibly could work up something using
dynamic sql.
David J.
rsion you care about to learn it's limits.
David J.
d tables but I want to manipulate tables while
transforming from the original input.
David J.
.5 and pgAdmin 4 version
> 3.2 .
>
https://www.postgresql.org/docs/10/static/index.html
Section VII is internals - the "structural dba tables" are termed "System
Catalogs" in PostgreSQL.
David J.
ured to accommodate
this use case as well as many other configurations people may think up.
https://www.postgresql.org/docs/10/static/auth-methods.html#AUTH-PAM
David J.
" can be established between the
two in pg_hba.conf.
David J.
might or might not be planning/doing in
this area before the heat death of the universe (a reasonably close
approximation of "ever")".
Since most things are theoretically possible a flat out - "no that will
never happen is unlikely".
David J.
On Tuesday, August 28, 2018, Shaun Savage wrote:
> I want to dynamically return a column from a function.
>
Variable object identifiers requires executing dynamic SQL. See:
https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
David J.
r design is unahappy - against to ideas of relations
> databases. So any native tools will be impossible.
>
+1
I don’t know for sure that what is desired is not possible but only because
the specification and model are more than I wish to untangle at the
moment...
David J.
a single
backslash command.”
https://www.postgresql.org/docs/10/static/app-psql.html
David J.
On Wednesday, August 29, 2018, saurabh shelar
wrote:
> Hi David,
>
> Thank you for the swift response.
>
> However, could you please confirm if the below scenario is expected.
>
> - included the alias in the file.
> - And passed the file with psql and it worked.
On Wed, Aug 29, 2018 at 8:31 AM, saurabh shelar
wrote:
> Hi David,
>
> Thank you for the help.
>
> I was just got confused with the below line mentioned in the document.
> However, it seems it is still the same behaviour *(i.e --no-psqlrc)*.
>
> *"Before PostgreSQL
at the
next time you run your calling query it indeed fails. I'm suspecting that
the code you are iterating over is not the same code that is being executed
(search_path dynamics probably).
That or you are looking in from another session without committing the
first one.
David J.
atic/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE
I was going for a conceptual communication, not syntax.
Though if you indeed get a syntax error then that precludes the "wrong
object" theory.
David J.
esn't have so many columns. Otherwise
generating dynamic SQL via the for,at function and loops and such is your
best bet. Can be down in pl/pgsql or in your preferred programming
language. Psql variables can maybe be useful too.
David J.
AS loc, fld
FROM schema.tbl2
WHERE fld = 'value'
...]
David J.
On Wednesday, September 12, 2018, Arup Rakshit wrote:
> IN is OR, I want the AND logic. Select posts which has tag 1, 2 and 3 (
> tag ids )
>
Build arrays and then use the “contains” operator.
David J.
uspect) such as locks, caching,
physical data locality.
It seems more useful to log actual execution times and look for trends. If
you are writing a query odds are it needs to be run regardless of how
efficient it may be - or used in a relative comparison to an alternate
query.
David J.
>
>From the docs:
"VACUUM cannot be executed inside a transaction block."
As it is non-transactional any work it performs is live immediately and
irrevocably as it occurs.
David J.
entence
should be, IMO, adjusted to loosen the "where" while tightening the "who".
Beyond that I don't object to writing out explicitly the option to consider
"external" activity - I doubt it will matter in practice and if the
situation is severe enough that it does then core could do what they want
anyway and deal with the fallout whether a CoC exists or whatever its
contents. I do not believe that, for the typical community member with a
low profile, this will ever come into play.
David J.
erything must be typed. It is not casting the first or
intermediate jsonb results to text. The final output is text because of
the ->> operator.
:: binds more tightly than the other operators.
Jsonb->('somedata'::text)
David J.
On Tue, Sep 18, 2018 at 11:37 AM, VENKTESH GUTTEDAR <
venkteshgutte...@gmail.com> wrote:
> Can we replace the for loop with something else?
>
A query probably - depending on what the function does and, in particular,
whether dynamic SQL is required.
David J.
On Wed, Oct 3, 2018 at 9:25 AM, David Gauthier
wrote:
> Is that how you detect if nothing was updated
>
https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
Given your concerns and examples you might also want to look into
serializable isolati
On Thursday, October 4, 2018, Tim Uckun wrote:
> can I refer to a pg_service entry in the pgpass file? It seems silly to
> repeat all the information in the pgpass just to add the password.
>
No
> Alternatively can I put the user password in the pg_service file?
>
Yes
David J.
WHERE added > TO_TIMESTAMP(1539100913);
>
>
SELECT array_agg(words) FROM (
SELECT hashed FROM words_nouns
UNION ALL
SELECT hashed FROM words_verbs
) AS src
David J.
ecurity. But yes it is generally sufficient enough to simply
allow usage on scheme without much thought while ensuring contained objects
are sufficiently secured.
David J.
n (cell) - you will still encounter a physical limit to the number
of bytes able to be stored in a single cell when using text.
David J.
Yes, deeply nesting views is a maintenance concern. It works this way so
the view remains stable (there may be others, the reasoning no longer
really matters...). If you really want dynamic SQL you will need to use a
function.
David J.
ly, use // to look for the attribute anywhere
David J.
r.
But, yes, objects saved to the database should usually have schema
qualifications (which gets a bit messy with custom operators). search_path
reliance should probably be reserved to interactive use or at worse client
supplied queries.
David J.
On Tue, Jul 9, 2019 at 8:48 AM Igal @ Lucee.org wrote:
> David,
>
> On 7/9/2019 7:49 AM, David G. Johnston wrote:
>
> On Tue, Jul 9, 2019 at 7:41 AM Igal @ Lucee.org wrote:
>
>> search_path is not set int he config, but rather with ALTER DATABASE SET
>> search_p
> So, due to the lack of examples in general and the sparse documentation
> about it, any help will be appreciate.
>
As Ian noted, contrib is usually the recommended source for up-to-date
coding examples.
David J.
ed in people deciding (or
defaulting) to not spend any effort in that area. Incremental maintenance
and refresh seem considerably less useful when only the current session can
see the table. Temp views and temp tables seem to provide sufficient
options in the session lifetime space.
David J.
decide what amount of internal data validation you
can live with. It also depends on whether you have expected numbers based
upon the data being restored.
David J.
mmended in cross-version cases, as it
can prevent problems arising from varying reserved-word lists in different
PostgreSQL versions.
David J.
On Wed, Jul 17, 2019 at 11:39 AM Perumal Raj wrote:
> Thanks Adrian, David,
>
> Basically , i want to upgrade few 9.X/8.X version DBs to some stable
> version ( 10.X / 11.X ), At the same time with less down time.
> So want to understand whether direct upgrade possible or no
sql command that can do those things and
the syntax does not provide a way to combine the two into a single
executable command.
Out of curosity, why do you ask?
David J.
ition in the version number became
the patch release version so both 11.2 and 11.4 are the same major version
(v11) and 11.2 is guaranteed to be more buggy than 11.4. If you wish to be
cautious then you should upgrade to the v10 series which is presently at
10.9
David J.
you expect
the drop to succeed.
Would it have less locking than simply altering the column?
>
I doubt anything will improve upon simply altering the column. You have to
perform a full table rewrite in either case which is going to be the main
resource consumer.
David J.
On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari wrote:
> On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn wrote:
>
> > B.t.w., I noticed that “set transaction isolation level serializable”
> must be the very first statement after “rollback” (or “commit”). Even an
> invocation of txid_current() after t
argument (has_email_field boolean) and when attaching the function to the
trigger attach it with either true/false depending on whether the target
table has an email field.
David J.
On Saturday, August 10, 2019, stan wrote:
> Sorry, I got the list address wrong the first time, and when I corected it,
> I forget to fix the subject line.
>
>
This subject line isn’t materially better...subjects should reflect the
technical content of the message, not its sender.
David J.
meaningful way.
>
Sure you can, at least generally, with Window Functions/Expressions (i.e.,
OVER)
David J.
oin against.
In any case to directly solve this in the full join form you probably want
to do something like:
SELECT ...
FROM tbl1
FULL JOIN (tbl2 AS tbl2alias (proj2_id) FULL JOIN tbl3 AS tbl3alias
(proj3_id) ON proj2_id = proj3_id) AS tbl23
ON (tbl23.proj2_id = tbl1.proj_id OR tbl23.proj3_id = tbl1
e. Am I missing
> something?
>
>
Nope. You need to create the table separately - or find a tool that will
do that creation for you.
David J.
>> use the header from a CSV file to define a new table. Am I missing
>> something?
>>
>
> Data types. Sure, you've got column names from the header line, but what
> types do you assign them?
Text. I’m gonna post-process anyway, casting to better types isn’t a
problem.
David J.
ing a scenario where running reindex or vacuum
arbitrarily at session end would be a useful thing.
David J.
single quotes) is always treated as
literal content.
David J.
er"*
>
> Do I need to drop the trigger and alter the table column to modify the
> datatype of the columns. Am I correct?
>
Seems like a reasonable course of action, and simple enough to just
experiment with on a test relation.
> or is there any other way to resolve it.
>
Doubtful
David J.
On Wed, Sep 25, 2019 at 3:08 PM David Salisbury wrote:
> Thanks,
>
> Unfortunately I believe I need to include a postgres module to get the
> "<@" operator, which I have no power to do. This is what I get with that
> operator..
>
> select name from table_n
ignore and return
the original object" or "add the key with a json null scalar value" is
debatable but either is considerably more useful than returning SQL NULL.
David J.
n the
rare case it is needed. Current behavior is unsafe with minimal or no
redeeming qualities. Change it so passing in null raises an exception and
make the user decide their own behavior if we don’t want to choose one for
them.
David J.
subjective - with no obvious reasonable default, and I agree that
"return a NULL" while possible consistent is probably the least useful
behavior that could have been chosen. We should never have allowed an SQL
NULL to be an acceptable argument in the first place, and can reasonably
safely and effectively prevent it going forward. Then people will have to
explicitly code what they want to do if their data and queries present this
invalid unknown data to the function.
David J.
robably the same thing - though I'd accept having the input json being
null result in the output json being null as well.
David J.
or to the caller using normal mechanisms
for "SQLException" propogation.
David J.
u added the schema prefix "sidecar_link" to
the table name when you issued \d above.
>
>
>
>
>
>
> *=> \det List of foreign tables Schema | Table | Server
> +---+(0 rows)*
>
David J.
ry about changing (though moving
to exception is safe) but a policy choice now could at least pave the way
to avoid this situation when the next new datatype is implemented. In many
functions we do provoke exceptions when known invalid input is provided -
supplying a function with a primary/important argument being undefined
should fall into the same "malformed" category of problematic input.
David J.
On Wed, Oct 23, 2019 at 4:42 AM Laurenz Albe
wrote:
> David G. Johnston wrote:
> > Now if only the vast majority of users could have and keep this level of
> understanding
> > in mind while writing complex queries so that they remember to always
> add protections
> > t
gt; I don't see a primary key defined, so I'm presuming I need to issue an ADD
> CONSTRAINT command against the foreign table to reflect what is actually
> true in the foreign table? Is that correct?
>
The documentation says doing what you suggest won't work...
David J.
clear, the new feature has some
rough edges (limitations) that you are hitting with your desired model.
David J.
shark','blue','blue fish');
> > replace
> >
> > blue shark
> > (1 row)
> >
> > Thanks a lot
> >
> > Antonio Olinto
>
> does this help?
> select replace (a.col, ‘value’, ’new value’) where a.col = ‘value’;
>
>
I’d probably do something like:
Select case when a.col = ‘value’ then ‘new value’ else a.col end from a;
I suspect adding where a.col = ‘value’ isn’t viable. If it is then:
Select ‘new value’ from a where a.col = ‘value’;
David J.
y use WINDOW functions to accomplish your
goal - count(...) OVER (PARTITION BY ))
David J.
The "outer" one doesn't have a
FROM clause and so doesn't have access to columns. The "outer" SELECT
project_cost_category_key is thus invalid.
David J.
On Tuesday, December 31, 2019, Ron wrote:
>
> But how do you issue a ROLLBACK to a different pid?
>
You cannot. At that point you need to start from scratch.
pg_terminate_backend(*pid int*)
And let the problematic app deal with losing its database connection
however it will.
David J.
On Tuesday, January 14, 2020, João Haas wrote:
>
> SELECT tb.*, array_agg(conn.child_id), array_agg(conn.kind)
>
>
Create a custom type (using row(...) might work...?) with the relevant
fields and “...array_agg((child_id, kind)::custom_type order by ...”?
David J.
On Wed, Jan 15, 2020 at 11:36 AM bhargav kamineni
wrote:
> Any workaround to make it work ?
>
Convert both to text and join on that? Curious choice making relid
numeric...
David J.
; and
> c.relnamespace::regnamespace::text='rpx_reporting_stage' and
> u.captured_dt::date=current_date - interval '1 days'
> ;
>
>
> ERROR: OID out of range
>
>>
>>
Yeah, the join isn't the problem, the error is casting to OID, not from...
Pretty sure your problem is:
pg_relation_size(u.table_size)
David J.
cally it is not a "log" in the transaction sense and its only shows
current session data.
David J.
https://www.postgresql.org/docs/12/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS
David J.
gt; After applying the logic i need to take this data into the table.
>
> can you please help me how to write the syntax in this case.
>
Cast everything to text?
https://www.postgresql.org/docs/12/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS
David J.
maybe as part,of a lateral join, not sure without
experimentation.
Otherwise you can add “row_number” to “b” and “c” and then left join on (a,
row_number).
David J.
along all of the validation information
and so can make up values for any parameters you might wish to check so
that they look like, i.e., your application as far as the authentication
code is concerned.
David J.
e it will be large enough to matter.
You can always make changes later as new requirements are added or
constraints such as size become more important.
David J.
On Wed, Feb 5, 2020 at 7:55 PM Vik Fearing wrote:
> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>
+1
gle physical
table. To the benefit of those who don't use the other product you may
wish to say exactly what you want to do instead of comparing it to
something that many people likely have never used.
David J.
On Friday, February 21, 2020, stan wrote:
>
> How can I get the name of the table that fired a trigger, in the function
> called by that trigger?
>
Language specific. Read the language docs. For pl/pgsql:
https://www.postgresql.org/docs/12/plpgsql-trigger.html
David J.
;)
> ])
>
> Here, c is a function that constructs a card type. Card is a ROW(varchar,
> varchar).
>
> So: how do I cross-join three identical arrays of my card type?
>
Something like:
Select f1.a, f2.b
>From (select * from unnest(arr)) as f1 (a)
Cross join (select * From unnest(arr)) as f2 (b)
Etc...Maybe with parentheses...
There may be a more succinct way to write this but going verbose until it
works minimizes the amount of syntax you need to deal with. Though I
personally encourage writing intentional cross join (really, all joins)
using join syntax instead of comma-separated from items.
David J.
constraints to the table to describe and enforce the
inter-field relationships that are present.
David J.
ommendations to start
there and avoid rules if at all possible.
David J.
On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver
wrote:
> On 3/3/20 3:06 PM, David G. Johnston wrote:
> > On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > The link was for automatically updateable views. If you
e.?
>
Not sure on an additive suggestion but you probably can get considerable
mileage by removing Excel from the equation and bring the external data
into PostgreSQL and use joins (i.e., SQL's version of VLOOKUP).
David J.
oose different names for the new stuff then remove the old stuff
separately.
David J.
USING liberally.
CREATE FUNCTION perform_update()...
AS $outer$
BEGIN
drop_sql := $inner$ DROP FUNCTION ...; $inner$
EXECUTE drop_sql;
END;
$outer$;
David J.
On Wed, Mar 4, 2020 at 3:55 PM David G. Johnston
wrote:
> On Wed, Mar 4, 2020 at 3:48 PM Rory Campbell-Lange <
> r...@campbell-lange.net> wrote:
>
>> Any thoughts on how to wrap pl/pgsql function dropping and recreation code
>> within a wrapper pl/pgsql function?
&
ld and new
software to continue working normally. The second commit then removes the
functionality the older software versions are using - after they've been
phased out.
David J.
column.
Then measure performance and decide whether a generic routine is performant
enough. If not you might try creating custom function dynamically using
the catalogs as input.
David J.
t. It
also likely has at least an empty public schema...
David J.
ome suggestions for how to request help that is actionable:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
Start specific and then generalize.
David J.
.
>
> What am I missing, here?
>
>
OLD and/or NEW?
https://www.postgresql.org/docs/12/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER
David J.
On Thu, Mar 5, 2020 at 8:50 AM David Gauthier
wrote:
> Hi:
>
> How does one reformat the output of the "age" function to always be in
> terms of hours:mins.
>
>
>
Custom function.
Use justify_hours(interval) to normalize the input in terms of days
Use extract(
t to unexpected
databases via the pg_hba.conf file.
Why does userA need create database privileges?
You could just have them log into an admin database and run a function that
creates the database for them using function owner privileges then you can
add whatever special logic you want to that function.
David J.
801 - 900 of 2416 matches
Mail list logo
