e contents of the portal being lost
due to the perform but that behavior isn't evident from the the section you
quote.
David J.
..)
SELECT
current_timestamp AS ct,
current_timestamp AS mt,
vals.last_name,
vals.first_name,
vals.country,
(SELECT id ) AS school_id,
vals.last_name || ', ' || vals.first_name
FROM (VALUES (%s, %s, %s)) vals (first_name, last_name, country)
And I'd probably rely on defaults for the timestamp columns and only do:
INSERT INTO lib_author (last_name, first_name, country, school_id, name)
SELECT ...
David J.
On Tue, May 8, 2018 at 7:44 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, May 8, 2018 at 7:17 PM, tango ward wrote:
>
>> I am trying to concatenate the value of column firstname and lastname
>> from source DB to name column of destination DB
n-time environment I need with the behavior the script provides - merging
that I find I need more often than not and don't miss the added overhead in
the few cases where it is unnecessary.
David J.
he first
> line of any file referenced & executed via the -f argument will be ignored
> if and only if it starts with a shebang (#!). If the first line of the file
> does not start with a shebang, it is processed normally as are all
> subsequent lines.
>
>
Don't forget the \i and \ir meta commands.
David J.
er for #1 doesn't seem worthwhile - or maybe is a feature in
its own right. Something like:
--@ client-version >= 9.6
--@ server-version >= 9.4
If those comments are found in a file psql is evaluating it should error
out if the condition doesn't match. That should apply regardless of
invocation method.
David J.
On Wed, May 9, 2018 at 8:05 PM, Chandru Aroor wrote:
> Yes, the service shows as running. But I don't have a Server to connect
> to!
>
I have to imagine you can add a server to pgAdmin...try host: localhost
and port: 5432
David J.
uld possibly make the "fail to
tell the server that the timezone is ET" impossible using a custom type.
This seems to be what you want though I'd question whether it is worth the
cost.
I'm not sure how binary timestamp values being sent to the server in a BIND
command plays into this...
David J.
sure about composites but if you do store a text representation it will
work.
David J.
gt; foo_text on foo_t the gist works.
>
You should ifnd it works even without the existence of the foo_text
function.
David J.
d question - the post-installation instructions that are part of
the news releases seem like something that should be part of the release
notes...but they are not.
David J.
On Monday, May 14, 2018, tango ward wrote:
>
> May I ask an advice on how to approach this?
>
I can't make heads nor tails of your description...but there isn't IF in
SQL. But you may get some mileage out of simple joins.
David J.
f the expression to "name".
Or just do the rename in python, not sql.
David J.
On Monday, May 14, 2018, tango ward wrote:
> Noted Sir Adrian. The course name for the ones that are blank are not
> match with the ones in the profile_program table. I am writing a CASE
> Statement righ
\i myquery.sql to 'output.csv'
>
Maybe try something like:
\o output.csv
COPY (
\i myquery.sql
) TO stdout;
\o
You'll need to use COPY if you want to mix in \i with a query that would
otherwise operate as a standard select query.
David J.
e you had multiple rows...this time you have multiple columns...
David J.
hat would be helpful.
While that seems to work it is definitely obscure. You should just use
concatenation.
’+63' || mobilenumber
The pattern is a zero-length string, matched once, which first matches the
start of the input text. Not sure what happens when the input is the empty
string...
David J.
gt; for before making a bottom-post?
>
>
You remove quoted material that isn't relevant and then type your response
at the bottom of the email (after the stuff being quoted), like I'm doing
here.
David J.
turn exactly 1 row, never 0. This is a
feature as returning zero rows is problematic in many ways; a 0-row result
in a select-list will cause that row to be removed from the result when is
most cases people would rather be told whatever they were trying to compute
for the row simply didn't compute - via a NULL.
David J.
https://www.pgadmin.org/support/list/
You should also be checking the pgAdmin docs, not PostgreSQL proper (the
version you indicate is an ancient and unsupported PostgreSQL release).
Using pure SQL you'd do something like:
col ~ '\\\d{6}\\\s+\\' (regular expression match operator)
depending upon what exactly you mean by "like this"...
David J.
for the product to be useful initdb is itself not really part of
the core installation routine. IOW it is outside of what the make routine
is responsible for and, as documented, it doesn't deal with permissions
explicitly nor does it create parent directories.
David J.
the current supported release for
that 9.6 version is 9.6.9 (more generally, the 5 releases that are shown
on the home page).
David J.
gt; first ROW_COUNT (after SELECT) without performing it twice?
>
Not directly, no. You should execute the inner query to a temporary table
than perform your counting and json_agg from that.
David J.
On Tuesday, May 22, 2018, Jayadevan M wrote:
> pg_basebackup ... | tee -- Also, the output from pg_basebackup does not
> get logged in $logfile even on those days when the backup works fine.
>
I would conclude that pg-basebackup is placing its output in stderr instead
of stdout then.
LIMITER ',' CSV HEADER;
>
you cannot put "[1]" there. The csv file value has to be an array input
literal with only one element. i.e., something like: {(1,2)}
There is no first element as such when you are creating a new record.
There will be however many elelements to supply to the array input.
David J.
t the nature of the relational database by not normalizing
your data model. Converting your array of composites to a table is going
to make life considerably easier if you expect to have to performs searches
like you requrest help for here.
David J.
On Wed, May 23, 2018 at 7:03 AM, a <372660...@qq.com> wrote:
> Thank you so much, did you mean the section 8.15.6??
Yes.
ng as much technical normalization as
you can to your model and assume that years of modelling theory is going to
be a better guide than inexperienced gut instinct. Starting from a
normalized position you can selectively de-normalize and add abstraction
layers later when you come across actual problems that you wish to solve.
David J.
teger);
INSERT INTO test_t
SELECT '1', '2', 3 WHERE false; --where false causes the data to
effectively "not exist"
As for ON CONFLICT: conflicts can only happen between things that exist.
David J.
give me error but it doesn't insert data either.
>
>
I'm doubting your assertion that it doesn't error. How do you run that
query such that age and name are recognized given the main query doesn't
have a from clause?
David J.
If you are going to post so many messages can you please observe the
bottom-post and trim convention used of this mailing list.
On Wednesday, May 23, 2018, tango ward wrote:
> Tried it, but it still I am not inserting data into the table.
>
tried what?
David J.
me is ALWAYS true and so as long as there is at least
one record in my_table the exists returns true, and the not inverts it to
false and the main select returns zero rows. You have successfully
inserted a record that doesn't exist (i.e., you've inserted nothing just
like you observe).
David J.
On Wednesday, May 23, 2018, tango ward wrote:
>
> On Thu, May 24, 2018 at 9:09 AM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wednesday, May 23, 2018, tango ward wrote:
>>
>>>
>>>
>>> curr.pgsql.execute('
On Wednesday, May 23, 2018, Adrian Klaver wrote:
>
> INSERT INTO my_table(%s, %s)
> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %s)
>
INSERT doesn't have a where clause...
David J.
ed, you have three parameter markers(%s) and two
> parameter values. Not only that two of the markers are for identifiers.
>
>
The count is indeed off but the two markers after the main select are
literals, not identifiers. As is the one being compared to name.
SELECT 'Scott', 23;
is a valid query.
David J.
symbol, and cannot ever because it is already a valid operator symbol.
David J.
nctions provide the easiest means to apply aggregated values to
individual rows.
SELECT v, (v - (AVG(v) OVER ()) / (stddev(v) OVER ())) AS z_v
FROM (
VALUES (1),(2),(3)
) vals (v);
//-1, 0, 1
https://www.postgresql.org/docs/10/static/tutorial-window.html
David J.
my SQL competence.
>>
>
> What about this?
>
> SELECT value, (value - AVG(value))/stddev(value) as zvalue
> FROM sometable
> WHERE some conditions
> GROUP by value
>
That is syntactically correct but doesn't provide the correct answer.
David J.
On Thursday, May 24, 2018, David G. Johnston
wrote:
> On Thu, May 24, 2018 at 8:15 AM, Martin Mueller <
> martinmuel...@northwestern.edu> wrote:
>
>> You construct a z-score for a set of values by subtracting the average
>> from the value and dividing the result by the
time should the audit data be needed though.
>
> 2. Use UNLOGGED in the log table creates.
>
Seriously? For audit tables?
David J.
query string here. I'd just
avoid them and use regular expressions.
David J.
On Sunday, May 27, 2018, Stuart McGraw wrote:
> Is there some way to to test if a savepoint of a given name
> exists? Or better yet, the number of stacked savepoints of
> that name?
>
A scan of the documentation doesn't show any commands or functions that
would provide this in
ing that where failure is
simply bad - usually by bulk loading with fewer constraints and then
ensuring that future queries don't attempt to do something illegal like
insert duplicates.
David J.
u haven't explained the pattern well
enough, if there is one, to know for sure. Frankly, you'll probably spend
more time figuring out the elegant way than just copy-paste-change so
unless you need to leverage this elsewhere I'd say just brute-force it.
David J.
an
assumed bullet-resistant solution.
David J.
On Wednesday, May 30, 2018, tango ward wrote:
>
> Okay I will try it.
>>
>
> When I tried it, I am getting an error: Invalid input syntax for UUID:
> uuid_generate_v4(),
>
Avoid references to "it" and just show the code you tried to run.
David J.
On Wed, May 30, 2018 at 9:32 PM, tango ward wrote:
>
> On Thu, May 31, 2018 at 12:18 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wednesday, May 30, 2018, tango ward wrote:
>>>
>>> Okay I will try it.
>>&
ing nulls
though in my particular usage I still have them since I'm basically
implementing a key-value json document and so have a record for the ID in
every table even if no data is present. The advantage is reducing model
complexity by adding meta-data to all of the fields contained fields.
David J.
>
, as
noted above, such a policy doesn't need the community at-large's approval:
it's a document that constrains those that wrote it.
> I am completely in favor of civil discourse and behavior, but I am not
> in favor of unenforcible red tape.
>
The core team does have enforcement tools at its disposal. They are at
least being open about the circumstances and extents under which they would
leverage those tools.
David J.
On Mon, Jun 4, 2018 at 7:06 AM, Adrian Klaver
wrote:
> On 06/03/2018 09:21 PM, David G. Johnston wrote:
>
>>
>> That's pretty much par for the public dynamic of this community. And, as
>> noted above, such a policy doesn't need the community at-large&#
On Mon, Jun 4, 2018 at 6:44 AM, pavan95 wrote:
> The main reason for my requirement is to find the time swing between server
> stop and start.
>
Not all server stops are logged/evented (i.e., crashes), though by
definition all successful starts are (or at least can be).
David J.
o 9.4.18 and see if any of the
various vacuum related bug fixes solves your problem. That's about the
extent of my useful advice for this...
David J.
here
things stand today and could be continued on with until such time as a
specific need for a committee is felt.
David J.
including multiple times,
without issue. Have you tried it?
Now, if you are using Row-by-Row mode that likely varies a bit: but your
example doesn't show that to be the case.
David J.
y is documented but only on the underlying catalog, not the view.
https://www.postgresql.org/docs/10/static/catalog-pg-statistic.html
David J.
On Wednesday, June 6, 2018, David Gauthier wrote:
> Hi:
>
> Is there a way to do the equivalent of a "\set foo 1" through perl dbi ?
> I tried...
> $dbh->do("\\set foo 1");
> and got a syntax error
>
> Of course, I'd also have to be a
ers long-term
or is it mainly transient distribution and only individual's personal email
accounts, with whatever hosting provider they choose, hold the messages
long-term?
David J.
o a sane state. Though ANALYZE by itself should clear up
the statistical discrepancy.
But, I may be missing something, my experience and skill here is somewhat
limited.
David J.
vacuum analyze commands into
your application at appropriate times are probably necessary.
David J.
values, will be assigned
according to the order by.
David J.
>
Likely it means the open source product built directly from the source code
published here (or packages derived there-from). As opposed to say AWS RDS
or EnterpriseDB or various other forks of the product available in the wild.
David J.
zero value you were
directed to use is what causes everything to be logged.
David J.
area being worthwhile though.
Renaming is impossible and an alias is probably just going to confuse
matters even further by giving people one more thing they need to
understand.
David J.
On Tuesday, June 19, 2018, Louis Battuello
wrote:
> Is it possible to drop default privileges
>
https://www.postgresql.org/docs/10/static/sql-alterdefaultprivileges.html ?
David J.
On Tue, Jun 19, 2018 at 11:31 AM, Pavan Teja
wrote:
> Once I tried finding the list of default privileges, but left with no clue.
>
Start here:
https://www.postgresql.org/docs/10/static/catalog-pg-default-acl.html
David J.
ol to be generally helpful in this area -
though I'm thinking it doesn't do what you want here.
http://csvkit.readthedocs.io/en/1.0.3/scripts/csvsql.html
David J.
cute those at high volume.
Or at minimum give an example of the output you would want from this
unknown program...
David J.
tgresql.org/docs/10/static/sql-createeventtrigger.html
Your scenario suggests you may wish to avail yourself of the Listen and
Notify commands as well.
David J.
https://lists.postgresql.org/unsubscribe/
On Tuesday, June 19, 2018, Asif Ali wrote:
> please just tell me the site i will do it right away and i have marked it
> junked so many times , i will keep spamming it until my email address is
> removed from the list
>
> Bye
>
> ---
y so that
knowing the signed on user you would also know who they are in any
environment that uses the same authentication source - and if that source
supplies their Windows identity you are golden. The specific setups
involved here are outside my experience, though.
David J.
nd that your email is indeed a core
PostgreSQL issue please just submit it to one of the available lists. If
you are unsure pgsql-general is a safe choice. For this email enough
information has been provided that our official pgsql-bugs email list (or
form) would be acceptable and indeed preferred.
David J.
unction returning an
"event_trigger" pseudo-type: but I cannot locate an official statement
that doing so is valid. I was expecting a paragraph at [1] but it is not
there. Adding one and linking it to the overall event trigger chapter as
well as the event trigger section of the pl/pgsql chapter seems warranted.
[1]
https://www.postgresql.org/docs/10/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS
David J.
On Wed, Jun 20, 2018 at 3:34 PM, Fabrízio de Royes Mello <
fabri...@timbira.com.br> wrote:
> And use some external service like pastebin.com to send long SQL
> statements.
>
Or just attach a text file - those are allowed on these lists.
David J.
ackup and cannot be used for
point-in-time-recovery."
I like adding "cold backup" here to help contrast and explain why a base
backup is considered a "hot backup". The rest is style to make that flow
better.
David J.
script that creates the table,
inserts a single record, and updates that record. Present that for
consideration along with a description or capture of the results of running
the script on your machine.
David J.
esults of running
>> the script on your machine.
>>
>>
> But not all the records fail the update...
>
But you are able to determine which ones do fail, yes? Use one (or more)
of those...
Your problem is a mis-understanding of the data being operated on/with.
Figure out a way to show us the problem with real data.
David J.
For a user it's simply the order that rows are passed into the function.
If the state transition aspect retains order this is how you can control
it. The function itself doesn't really ever see the parameter, it just
sees ordered data.
David J.
is question two weeks ago...why are you starting a new
thread instead of continuing that one?
David J.
tion that (whose
name/signature) might already exist; CREATE already assumes one doesn't
exist.
David J.
ndeed you have nit defined a relevant
constraint in your schema, just indexes.
David J.
ided the row. In
short, the OP cannot do what they thought they needed to do but hasn't
really provided any info for others to make alternative suggestions.
David J.
On Thursday, July 5, 2018, Rafal Pietrak wrote:
>
> W dniu 04.07.2018 o 00:55, David G. Johnston pisze:
> > On Tuesday, July 3, 2018, Rafal Pietrak > <mailto:ra...@ztk-rp.eu>> wrote:
> >
> >
> > ERROR: there is no unique constraint matching given
o this
is basically impossible. The interface for such a behavior would have to
be a function. You one someone already wrote elsewhere or you can write
your own. There isn't one in core that I see.
David J.
On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot wrote:
> Hi, David,
>
> On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
> wrote:
> > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote:
> >>
> >>
> >> I presume threre is a query which check for the fu
On Thu, Jul 5, 2018 at 9:58 AM, David Gauthier
wrote:
> Postgres 9.5.2 on linux
>
> Given that I am not superuser, but DO have createrole...
> Can I grant some other role the ability to grant access to a table I
> created ?
>
> For Example: I create a table called foo. As
On Thu, Jul 5, 2018 at 10:07 AM, Igor Korot wrote:
> On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston
> wrote:
>
> > You can, and depending on how often you intend to execute said code, it
> is
> > probably the better way. It also requires pl/pgsql while CREATE OR
&
in its rights to do so.
David J.
On Thu, Jul 5, 2018 at 2:36 PM, Rafal Pietrak wrote:
>
>
> W dniu 05.07.2018 o 23:04, David G. Johnston pisze:
> > On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak > <mailto:ra...@ztk-rp.eu>>wrote:
> >
> > I was thinking, that when "add constraint&q
ot; statement creates a row inside the
> information_schema.tables
>
Given that information_schema.tables is a view, not a table, it doesn't
make a valid hook point for the trigger regardless of the previous point.
David J.
gestion will be welcome.
>
>
Consider views and functions too.
David J.
aren't doing that manually there will be a different kind of problem (bad
stats) preventing the query from being executed efficiently. But in terms
of the "barrier", no, they are no different than any other table added to a
query FROM list.
David J.
iggers chapter should
link directly to the Triggers section under pl/pgsql instead of the top of
the chapter...
David J.
On Wednesday, July 11, 2018, David Gauthier
wrote:
> Hi:
>
> I would like to get the utc timestamp, 24-hr clock (military time),
> without the time zone suffix.
>
> Below commands were run nearly at the same time...
>
> sqfdev=> select now()::tim
On Wednesday, July 11, 2018, David Gauthier
wrote:
> OK, the "to_char" gets rid of the timezone extension. But the times still
> don't make sense.
>
> When I go to store this in a DB, I want to store the UTC time. How d I do
> that ?
>
Use the data
On Wednesday, July 11, 2018, David Gauthier
wrote:
>
> I want to store the current UTC date/time in the DB. Does PG
> unconditionally store something like UTC, then let the queries figure out
> how they want to look at it (with "at time zone" and "to_char()&q
On Wednesday, July 11, 2018, David Gauthier
wrote:
>
> This won't work...
> update tv set greatest = greatest(tcfg1,tcfg2,tcfg3)
> ...because it thinks 1.0.9 is greater than 1.0.10
>
> Is there a way to get this to work right ?
>
>
Haven't used it personally
parsing)
you've decided are not viable for your need. Thus you've seemingly
eliminated all viable options and you now need to make a business decision.
David J.
p.s. Please remove the excess quoted material before hitting send. At
minimum anything after the last text that you write.
need to record time stamps for the relevant events and use
them to decide what is new and what is old.
David J.
On Sunday, July 15, 2018, David G. Johnston
wrote:
> On Sunday, July 15, 2018, Anto Aravinth
> wrote:
>>
>> I'm not sure, how to get started with this. Read about NOTIFY:
>> https://www.postgresql.org/docs/current/static/sql-notify.html
>>
>>
>>
es
> backups", "5 things to NEVER do with Postgres". I imagine we would sort it
> by version (9.6/10.0 etc...) as well as break it down via type
> (Administration, Tuning, Gotchas) etc...
>
> What do we think?
>
Politely tell them to buy some of the many well written books that are
available on these very topics...
David J.
On Mon, Jul 16, 2018 at 3:19 PM, Joshua D. Drake
wrote:
> On 07/16/2018 03:14 PM, David G. Johnston wrote:
>
>
> What does the community think about a community run, community organized,
>> sub project for USER documentation? This type of documentation would be
>> t
701 - 800 of 2416 matches
Mail list logo
