On Thu, 7 Sept 2023 at 08:45, gzh wrote:
> but is there a good way to find out which SQL statements have issues without
> having to run all SQL statements, as it would be too expensive?
Does your postgresql server log not contain the error with the
statement at issue?
Geoff
On Thu, 20 Jul 2023 at 15:28, Anthony Apollis wrote:
> I am attaching my TSQL and Postgres SQL:
You're still missing some CREATEs, for example for temp_FieldFlowsFact.
Even assuming your columns list is correct, I would still (and as a
matter of habit) include the target column list in your INSE
On Thu, 20 Jul 2023 at 13:17, Anthony Apollis
wrote:
> The Postgres i used: INSERT INTO temp_FieldFlowsFact
> SELECT "Account", "Calendar day", "Financial year", "Period",
>
> [snip]
At the very least, include a column list in your INSERT statement. We have
no way of checking where any of your r
On Wed, 3 May 2023 at 12:11, Michael J. Baars <
mjbaars1977.pgsql.hack...@gmail.com> wrote:
> The shared common address space is controlled by the clone(2) CLONE_VM
> option. Indeed this results in an environment in which both the parent and
> the child can read / write each other's memory, but dy
On Tue, 28 Mar 2023 at 10:28, Sebastien Flaesch
wrote:
> Is the CTID a good choice?
>
I think if you're honest with yourself you already know the answer to this
question. The only real solution is to update the legacy code to use the
primary key, or (if that's not possible) change the table defi
On Tue, 21 Mar 2023 at 16:06, Geoff Winkless wrote:
> On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy <
> shashidharreddy...@gmail.com> wrote:
>
>> Actually I was using the below command to check the compatibility, it
>> worked without any issue with 12.6 but it is not
On Tue, 21 Mar 2023 at 10:29, shashidhar Reddy
wrote:
> Actually I was using the below command to check the compatibility, it
> worked without any issue with 12.6 but it is not working with 1version 2.14
> time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir
> /usr/lib/postgresql/12/bin --new-
On Thu, 15 Dec 2022 at 07:31, Willy-Bas Loos wrote:
>
> wbloos=# set tcp_keepalives_idle=120;
> SET
> wbloos=# show tcp_keepalives_idle;
> tcp_keepalives_idle
> -
> 0
Are you connected in this psql session via tcp or unix domain socket?
"In sessions connected via a Unix-dom
On Wed, 22 Sept 2021 at 21:05, Israel Brewster
wrote:
> I was wondering if there was any way to improve the performance of this
> query:
>
>
> SELECT station,array_agg(distinct(channel)) as channels FROM data GROUP BY
> station;
>
> If you have tables of possible stations and channels (and if no
On Tue, 25 May 2021 at 08:18, Andrus wrote:
> Looking for a method to do bulk insert ignoring product foreign key
> mismatches.
>
ON CONFLICT only works with unique constraints, it's not designed for what
you're trying to use it for.
Geoff
On Mon, 8 Mar 2021 at 16:15, I wrote:
> Tried running yum update on my centos7 box. Get the following:
>
> Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (postgresql_12)
>Requires: llvm5.0-devel >= 5.0
> I tried adding the EPEL repo just in case that was the issue but it
>
Hi
I'm clearly missing something obvious but it's passed me by what that
might be, so a pointer would be appreciated...
Tried running yum update on my centos7 box. Get the following:
Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (postgresql_12)
Requires: llvm5.0-devel >=
On Sun, 9 Aug 2020 at 12:49, Condor wrote:
> Yea, I checked it, but because is 3 years old solution I expect these
> functions to be implemented long ago and just have another names or to
> have similar functions that do the same functionality. It's seems not,
> Okay will use the solution from sof
On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh wrote:
> This works:
> select p.firstname, p.lastname, p.firstname || p.lastname as fullname from
> onp_crm_person p order by fullname;
>
> But this doesn't:
> select p.firstname, p.lastname, p.firstname || p.lastname as fullname from
> onp_crm_
On Wed, 6 May 2020, 14:28 Stephen Frost, wrote:
> Greetings,
>
> * Geoff Winkless (pgsqlad...@geoff.dj) wrote:
> > On Wed, 6 May 2020 at 00:05, Tim Cross wrote:
> > > Where Tom's solution fails is with smaller companies that cannot afford
> > > this level
On Wed, 6 May 2020 at 00:05, Tim Cross wrote:
> Where Tom's solution fails is with smaller companies that cannot afford
> this level of infrastructure.
Is there an objection to openldap? It's lightweight (so could
reasonably be run on the same hardware without significant impact),
BSD-ish and mat
On Wed, 5 Feb 2020 at 10:48, Raul Kaubi wrote:
>
> DO $$
>> DECLARE
>> cur cursor for
>> select * from (values('logi_web'), ('logi_taustaprotsess')) as q (col1);
>> BEGIN
>> for i in cur LOOP
>> RAISE NOTICE 'create table %_y2020m01 PARTITION OF % FOR VALUES FROM
>> (''2019-12-01'') TO (''2020-01
On Fri, 31 Jan 2020 at 15:25, David G. Johnston
wrote:
> On Friday, January 31, 2020, Geoff Winkless wrote:
>
>> Now the problem is that I would like to return all the rows from a, but
>> with a single row where t2.b and t1.b match.
>>
>
> So, the final,number of r
Hi
I have a query involving multiple tables that I would like to return in a
single query. That means returning multiple sets of the data from the first
base table, but that's acceptable for the simplicity in grabbing all the
data in one hit.
An example set:
CREATE TABLE t1 (a int, b int, c int)
On Wed, 22 Jan 2020 at 11:00, srikkanth
wrote:
> Can you please help me in writing the syntax for the below mentioned table.
>
Suggest looking at the crosstab function.
https://www.postgresql.org/docs/current/tablefunc.html
crosstab(text source_sql, text category_sql)
Produces a "pivot
On Thu, 21 Nov 2019 at 15:32, Peter J. Holzer wrote:
> On 2019-11-21 09:43:26 +0000, Geoff Winkless wrote:
> > It wasn't meant to be insulting, I meant "esoteric" in the strict
> > sense: that you need to have specific knowledge to parse them.
>
> I didn'
On Wed, 20 Nov 2019 at 22:48, Peter J. Holzer wrote:
>
> On 2019-11-19 11:37:04 +, Geoff Winkless wrote:
> > Even if you do that you're still requiring the user to parse syntax
> > according to esoteric rules.
>
> Oh, please. Those "esoteric rules" hav
On Mon, 18 Nov 2019 at 22:24, Peter J. Holzer wrote:
>
> On 2019-11-18 12:24:40 +, Geoff Winkless wrote:
> > On Mon, 18 Nov 2019 at 11:46, Michael Paquier wrote:
> > > On Mon, Nov 18, 2019 at 10:27:24AM +0100, Josef Šimánek wrote:
> > > > This is clear once
On Mon, 18 Nov 2019 at 11:46, Michael Paquier wrote:
> On Mon, Nov 18, 2019 at 10:27:24AM +0100, Josef Šimánek wrote:
> > This is clear once you understand what does it mean. I was aware of VERBOSE
> > option of EXPLAIN and tried to use it without needed parentheses (the same
> > way EXPLAIN can u
On Wed, 13 Nov 2019 at 16:24, Moreno Andreo wrote:
> |foo |bar |baz |
> 1234
> 5678
> 9012
> (hoping text formatting is ok... 1234 should go in column foo, 568 in
> bar and 9012 in baz)
>
> Is it possible?
Simplest way in plain SQL would be individual ca
On Wed, 23 Oct 2019 at 17:20, Geoff Winkless wrote:
> For what it's worth, I can see a value to having
>
> SELECT 'this is quite a long string'
>'which I've joined together '
>'across multiple lines';
>
&g
On Wed, 23 Oct 2019 at 17:09, Ron wrote:
> As much as I hate to say it, MSFT was right to ignore this bug in the
> standard.
Standards are standards for a reason. It is almost never correct to
deliberately ignore them. If you don't like them, then revise the
standard.
Historically Microsoft ig
On Wed, 23 Oct 2019 at 16:55, Ron wrote:
> Then -- since the 'e' is separated from 'd' by a comma, the result should be
> "4", not "3".
>
> No doubt: it's a bug, no matter what the Pg devs say.
I'm confused why you consider that to be true. The count is checking
for columns containing the strings
On Wed, 23 Oct 2019 at 16:42, Ravi Krishna wrote:
> select count(*) from bugtest where fld1 in ('a','b','c'
> 'd','e');
>
> Note the missing comma after 'c'.
>
> PG takes it a syntactically right SQL and gives 3 as output.
>
> In SQLServer it errors out
On Tue, 15 Oct 2019 at 14:35, Ray O'Donnell wrote:
>
> On 15/10/2019 14:28, stan wrote:
> > I used to be able to return a constant value in a SELECT statement in
> > ORACLE. I need to populate a table for testing, and I was going to do so
> > like this:
> >
> > SELECT
> > employee.id ,
>
On Thu, 10 Oct 2019 at 09:31, Wim Bertels wrote:
> sometimes people are really a fan of certain product,
> sometimes in combination with the thought that all other products are
> bad; i don't know if this is the case, you could compare it with
> soccer, a barcalona fan will never become a real mad
On Wed, 29 May 2019 at 12:52, PegoraroF10 wrote:
> This trigger will not work because Master record was not inserted yet.
>
That seems reasonable. Since the transaction is meant to be atomic any
select within the query should return data from tables as they are at the
start of the transaction, t
On Thu, 16 May 2019 at 16:31, Daulat Ram wrote:
> Hello team ,
>
> We are getting ERROR: bigint out of range. Please help on this.
>
>
>
Bigint is -9223372036854775808 to 9223372036854775807.
https://www.postgresql.org/docs/current/datatype-numeric.html
On Fri, 12 Apr 2019 at 11:54, Tiffany Thang wrote:
> Can you provide a scenario where creating multiple indexes on the same
> column would be beneficial?
>
When you have too much disk space?
When your table writes are too fast?
On Fri, 22 Mar 2019 at 16:25, wrote:
> I know that I can do this in psql but it’s not handy with many columns.
>
I know this doesn't solve your root problem but for this issue you might
find pspg helpful.
https://github.com/okbob/pspg
Geoff
On Thu, 24 Jan 2019 at 16:00, Adrian Klaver wrote:
> People are going to make mistakes that is a given. Eliminating a boolean
> test is not going to change that.
I still think that if you've got to the point where you're actually
part-way through writing a clause you're unlikely to forget to
comp
On Thu, 24 Jan 2019 at 15:40, Adrian Klaver wrote:
> delete from delete_test where
>
> and then forget the 'field =' part. Though my more common mistake along
> that line is:
>
> delete from delete_test;
>
> At any rate, if it can be done it will be done.
If you follow that logic, then having a s
On Thu, 24 Jan 2019 at 15:32, Geoff Winkless wrote:
> DELETE FROM WHERE ;
>
> What would you be thinking that that ought to do?
To be fair, I suppose that accidentally missing out a test but
including an integer field
DELETE FROM WHERE ;
could do this. Not something I've eve
On Thu, 24 Jan 2019 at 15:28, Adrian Klaver wrote:
> On 1/24/19 7:21 AM, Geoff Winkless wrote:
> > How could you even write a query like the one Thomas posted? It
> > doesn't even look remotely sensible.
> delete from delete_test where 1::boolean;
*chuckle*
You misunde
On Thu, 24 Jan 2019 at 15:11, Tom Lane wrote:
> People don't generally post to the lists after a type-mismatch error
> catches a typo for them. So it's pretty hard to tell about "how
> many" developers would find one behavior more useful than the other.
> It is safe to say, though, that the same
On Thu, 24 Jan 2019 at 14:28, David G. Johnston
wrote:
> To assist developers in avoiding the writing of buggy queries.
Amazing how many of these developers find this a hindrance. If only
they could see how helpful we're being to them.
Geoff
On Thu, 24 Jan 2019 at 12:17, Thomas Kellerer wrote:
> Geoff Winkless schrieb am 24.01.2019 um 12:45:
> > My own opinion is that non-0 should implicitly cast as true and 0
> > should cast as false.
>
> I strongly disagree - that would mimic MySQL's idiosyncrasies and
On Thu, 24 Jan 2019 at 11:04, Alexandre GRAIL
wrote:
>
> And added to this weirdness is the fact that '1' or '0' (with quote) is OK.
>
The reason for that at least is that '1' and '0' are valid boolean values.
https://www.postgresql.org/docs/9.5/datatype-boolean.html
There's additional text desc
On Wed, 5 Dec 2018 at 12:45, Gavin Flower wrote:
> If you divide one integer by another, then it is logical to get an
> integer as as the answer.
Hmm. It might fit with what a computer scientist might expect (or
rather, not be surprised about), but I don't think you can say that
it's "logical".
On Wed, 5 Dec 2018 at 09:13, Gavin Flower wrote:
> SELECT ceil(10/4.0);
>
> Is what you want for that example.
Except that implies that "number of people who can fit in a car" is a
real number, not a whole.
IMO it's fundamentally broken that SQL doesn't cast the result of a
divide into a numeric
On Fri, 30 Nov 2018 at 15:53, John Smith wrote:
> We have a long script of sql that we run, several thousand lines of sql. If I
> execute the script
> from start to finish, somewhere in the middle of it, one sql command will
> hang and take 2 to
> 3 hours. During this time, "htop" shows 100% cpu
On Fri, 14 Sep 2018, 15:55 James Keener, wrote:
>
>
> Yes. They can. The people who make the majority of the contributions to
>> the software can decide what happens, because without them there is no
>> software. If you want to spend 20 years of your life
>>
>
> So everyone who moderates this gro
On Fri, 14 Sep 2018 at 15:10, James Keener wrote:
> I understand the concern, however, if you look at how attacks happen
>
>> it is frequently through other sites. Specifically under/poorly
>> moderated sites. For specific examples, people who have issues with
>> people on Quora will frequently g
On Tue, 11 Sep 2018 at 13:56, Arup Rakshit wrote:
> I have define a simple B Tree index on column *country* for users table. I
> don’t understand why the order by column not using the index scan when
> using *distinct* keyword in the select clause. Can anyone explain what is
> happening here?
>
On Mon, 20 Aug 2018 at 14:46, Nick Dro wrote:
> My specific issue is alrady solved.
> For the greater good I sent the email requesting to allow reg exp in the
> position functions.
> Not sure if you will implement it... Just wanted to let you know that the
> limited capabilities of this function
On Mon, 20 Aug 2018 at 09:22, Nick Dro wrote:
>
> This incorrect.
> SELECT position(substring('https://www.webexample.com/s/help?' FROM
> '/(s|b|t)/') IN 'https://www.webexample.com/s/help?');
>
> Gives 5. It's wrong.
>
On Mon, 20 Aug 2018 at 09:22, Nick Dro wrote:
>
> This incorrect.
> SELE
On Wed, 27 Jun 2018 at 12:11, Raymond O'Donnell wrote:
> On 27/06/18 10:44, ERR ORR wrote:
[snip moronic ranting]
> Is someone running unit tests on the CoC? :-)
LOL.
More like false-flagging, IMO.
Geoff
On Wed, 20 Jun 2018 at 12:51, Janning Vygen wrote:
> But for analyzing usage patterns it would be very nice to have this
> combined with a sample_rate for logging.
>
>logging_sample_rate = n
>
> So each n-th statement will get logged regardless of execution time.
>
I think you would need to
On Fri, 8 Jun 2018 at 13:44, John McKown wrote:
> Have you considered the standard C library functions: "atoi()", "atof()",
> "atol()", and "atoll()" ?
Hi John
My issue wasn't so much how to get a number out of the string, rather
how to get that value back into a NUMERIC object to return back t
On Fri, 8 Jun 2018 at 13:47, Geoff Winkless wrote:
> Answering my own question, looks like
And just in case anyone googling the question comes across this, this
example code works.
#include "postgres.h"
#include
#include "fmgr.h"
#include "utils/geo_decls.h&qu
On Fri, 8 Jun 2018 at 13:27, Geoff Winkless wrote:
> numeric_in looks like it might do what I want but to do that I would
> have to build a FunctionCallInfo struct to do that, and I'm not 100%
> clear how to do that either :(
Answering my own question, looks like
res = D
Hi
I'd like to be able to perform some manipulation on NUMERIC values in
a C function; however the exposed functionality in numeric.h is pretty
restrictive.
I can see numeric_normalize will return a pointer to a string
representation, which is workable, and if there were an equivalent
string-to-n
On Tue, 5 Jun 2018 at 01:18, Tom Lane wrote:
> I think you're forgetting the sequence of events. That was posted in
> Feb 2016. In May 2016 we posted a draft CoC which was open for public
> discussion, and was discussed extensively at a public meeting at PGCon
> in that same month [1], and the d
On Sun, 3 Jun 2018 at 22:47, Tom Lane wrote:
> In any case, we went over all these sorts of arguments at excruciating
> length in 2016. It's quite clear to the core team that a majority of
> the community wants a CoC. I don't think any useful purpose will be
> served by re-litigating that point.
On 15 March 2018 at 14:57, Tiffany Thang wrote:
> Is there a way to output the SQLs and DDLs so that I could easily identify
> what statements were executed?
>
>
-a, --echo-all echo all input from script
Geoff
Hi
Is there any way to tell if a conflicting row in an multi-line INSERT
used the DEFAULT directive?
I would like to be able to upsert a bunch of rows and only UPDATE the
conflicting rows where the value set was not new - the way I do this
for NULLable columns is to just write NULL in the INSERT
On 12 January 2018 at 13:56, Rakesh Kumar wrote:
> >I'm probably missing something, but can anyone see a reason why adding an
> ADD OR >ALTER COLUMN syntax to the ALTER TABLE command isn't either a)
> achievable or b) >desirable?
> >It seems to me to be eminently useful and not overly difficult,
I'm probably missing something, but can anyone see a reason why adding an
ADD OR ALTER COLUMN syntax to the ALTER TABLE command isn't either a)
achievable or b) desirable?
It seems to me to be eminently useful and not overly difficult, while
potentially saving a significant amount of effort on the
On 18 December 2017 at 16:13, Nick Dro wrote:
> Can you give a good reason why absolute value has a build in function while
> percentage is not?
ABS is an ansi-standard SQL function.
Geoff
On 23 November 2017 at 11:37, Szymon Lipiński wrote:
> table can have only one primary key. And most probably you already have one.
To clarify, you can have multiple UNIQUE constraints on a table, but
only one PRIMARY.
Geoff
On 22 November 2017 at 14:19, Vick Khera wrote:
>
> This sounds to me like you're going to run afoul of CAN-SPAM unsubscribe
> rules. You should re-think this workflow.
CAN-SPAM only applies to commercial email, "the primary purpose of
which is the commercial advertisement or promotion of a com
66 matches
Mail list logo
