Re: psql output result
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
Re: Code of Conduct plan
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. This is somewhat at odds with your message here. https://www.postgresql.org/message-id/18630.1454960447%40sss.pgh.pa.us It's rather disappointing that discussion was effectively silenced based on the implication that there would be time for further discussions before the implementation stage, only to have consultation deferred until late on in the implementation itself. If we're going to move on from that (as I assume), as to the content of the CoC itself, can I echo others' comments that > engaging in behavior that may bring the PostgreSQL project into disrepute, is far too open to interpretation. Geoff
Re: Code of Conduct plan
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 draft was subsequently revised a good bit > as a result of that, and republished [2]. It's taken us (mainly meaning > core, not the exploration committee) way too long to agree to a final > draft from there, but claiming that there's been no public input is just > wrong. Fair; however I still maintain that there was no further consultation on whether one was required, which was the implication of your message, and which your latest email implied had occurred when it suggests that the wider community was consulted on whether it is required. However searching through the lists for concepts, rather than words, is pretty difficult, so it's quite possible that I missed the email asking for votes and as I said, I'm just going to drop that one. > In reality I suspect actions under that provision will be quite rare. > You'd need somebody to actually file a complaint, and then for the CoC > committee to agree that it's a good-faith complaint and not a form of > using the CoC as a weapon. Given reasonable people on the committee, > that seems like it'll be a fairly high bar to clear. But, given an > unambiguous case, I'd want the committee to be able to take action. I'm just worried that expressing a political (or other) opinion on (eg) twitter that some people find disagreeable could easily be considered to bring the community into disrepute. eg a patent lawyer might reasonably consider that a hypothetical core developer (let's call him Lon Tame :P ) making public statements on an ongoing patent dispute implying that the case is baseless could make patent lawyers look upon the PostgreSQL community less favourably, ie his actions have done damage to the reputation of PostgreSQL in the eyes of other patent lawyers. I'm pretty sure no-one here (or indeed on the committee) would think that that was reasonable but because of the wording a court might well disagree; I'm not a lawyer so I'm unsure whether you could leave yourself open to action in the event that the person bringing the complaint considers it was mishandled by the committee: by including this line there's a potential legal argument that you really don't need to be having. Geoff
manipulating NUMERIC values in C extension
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-numeric function that would be enough (although not the most efficient) but I can't see a way to get a string back in to a numeric value to return. 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 :( I _could_ return the result as a varchar and cast it back to numeric in the SQL, but that's not very clean. Accessing the numeric structure directly would work too but I'm assuming that's not recommended since it's not exposed in numeric.h. Any thoughts would be appreciated, Geoff
Re: manipulating NUMERIC values in C extension
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 = DatumGetNumeric(DirectFunctionCall3(numeric_in, CStringGetDatum(buf), 0, -1)); should do it, judging from https://api.pgxn.org/src/orafce/orafce-3.6.1/convert.c Geoff
Re: manipulating NUMERIC values in C extension
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"
#include "funcapi.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/numeric.h"
#include "catalog/pg_type.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(pgnumeric_x10);
Datum
pgnumeric_x10(PG_FUNCTION_ARGS) {
Numeric v;
char *r;
char mybuff[1000];
double f;
v=PG_GETARG_NUMERIC(0);
r=numeric_normalize(v);
f=atof(r)*10;
sprintf(mybuff, "%f", f);
v = DatumGetNumeric(DirectFunctionCall3(numeric_in,
CStringGetDatum(mybuff), 0, -1));
pfree(r);
PG_RETURN_NUMERIC(v);
}
Example of it running:
=# CREATE OR REPLACE FUNCTION pgnumeric_x10(NUMERIC) RETURNS NUMERIC
AS 'testpgnumchange.so', 'pgnumeric_x10' LANGUAGE C STRICT IMMUTABLE;
CREATE FUNCTION
Time: 0.811 ms
=# select pgnumeric_x10(132387.4823487::NUMERIC);
pgnumeric_x10
1323874.823487
(1 row)
Time: 0.593 ms
=#
For obvious reasons I wouldn't suggest using atof on a numeric, we
have our own functions for manipulating _Decimal128 which is what I'll
actually be using in the end version, but this is easier to compile as
an example :)
Geoff
Re: manipulating NUMERIC values in C extension
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 to the server. Thanks for taking the time to reply though, it's appreciated. Geoff
Re: Suggestion about logging only every n-th statement
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 introduce a randomizing element if you wanted to make it statistically valid. Logging every n'th statement could (depending on your usage pattern) be catastrophically inaccurate. Geoff
Re: Code of Conduct committee: call for volunteers
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
Re: Re: Allow Reg Expressions in Position 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.
> 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.
>
For some reason, substring() returns the parenthesised subexpression rather
than the top level.
The comment in testregexsubstr does say that it does this, but it's not
clear from the documentation at all, unless I'm missing where it says it.
You can work around this by putting parentheses around the whole
expression, because that way the first subexpression is the whole match.
db=# SELECT position(substring('https://www.webexample.com/s/help?' FROM
'(/(s|b|t)/)') IN 'https://www.webexample.com/s/help?');
position
--
27
Geoff
Re: Re: Re: Allow Reg Expressions in Position function
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 create overhead.
FWIW, you don't really appear to want position() to handle regexps at
all, rather a completely new function that returns any and all
matching positions of your regexp.
You can do a generalised regexp match with (say)
CREATE OR REPLACE FUNCTION mypositions(s varchar, r varchar) RETURNS
TABLE (c1 BIGINT) LANGUAGE SQL IMMUTABLE AS $$
WITH v AS (
SELECT unnest(arr[1:array_length(arr,1)-1]) AS res FROM
regexp_split_to_array(s, CONCAT('(?=', r, ')')) AS arr
)
SELECT sum(LENGTH(res)) OVER (rows between unbounded preceding and
current row) FROM v;
$$;
Example:
SELECT mypositions ('http://www.wibble.com/s/blah/b/blah', '/(s|b|t)/');
mypositions
-
21
28
(2 rows)
I'm not sure that suggesting a new builtin to provide what is a fairly
esoteric requirement and which can be done efficiently with a small
function (as above) is likely to gain much traction.
Geoff
Re: Why order by column not using index with distinct keyword in select clause?
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? > Bear in mind that index skip scans aren't yet implemented, which (unless I've missed the point) seems to be what you're expecting to help here. https://wiki.postgresql.org/wiki/Loose_indexscan Geoff
Re: Code of Conduct plan
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 go after them on Facebook and Twitter. >> >> these aren't a solution looking for a problem. If we just want to look >> at the clusterfuck that is happening in the reddis community right now >> we can see conversations spilling onto twitter and into ad hominem >> vitriol. >> > > You haven't established that this is both 1) the PG mailing list's problem > and that 2) this can't and won't be used to retaliate against those holding > unpopular viewpoints but aren't specifically harassing anyone. > This argument (whether or not PostgreSQL should have a CoC) was hashed out pretty heavily a year ago. In my opinion it wasn't really clear that any one side or another won the argument but the people who matter came down on the side of having one. It's pretty unlikely that re-running these arguments is going to make those people change their minds. Certainly posting obscenities to these open forums isn't going to do it, however strongly you might feel about it. Geoff
Re: Code of Conduct plan
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 group and that will be part of the CoC > committee will have had to have dedicated their life of pg? > No. The core developers get to decide the policy and who is best to enforce it. It seems fair that the people who have contributed so much get to decide what goes on in their name. > > Sure, they own the servers, they make the rules. I get it. I'm not > entirely opposed to it, even if I think it's silly to ram something down > the rest of the groups throats. > I agree with you. I'm just fed up with rerunning the same argument every 3 months every time a new CoC update comes out. PS: Also, what's with the personal replies? If you don't want to say what > you want to the whole group, I don't really have an interest in talking to > you personally. > Sorry what? I replied offlist to your offlist reply to my onlist post, since I assumed you had decided (correctly) that this was hardly the sort of discussion that we should be clogging up other people's mailboxes with. Geoff >
Re: Is my lecturer wrong about PostgreSQL? I think he is!
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 madrid fan and vice > versa; so "rational" decisions (at first, based on some reasoning) tend > to get loaded emotional feelings. Yeah, this. Bear in mind it's possible that having made a sweeping statement that he cannot back up and that he secretly knows was unfounded, your lecturer will be defensive and uncomfortable. Chances are after your conversation he will have gone away and done the same research you did and may well have modified his opinion but will be too embarrassed to admit that to you. Geoff
Re: SELECT returnig a constant
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 , > > project.proj_no , > > work_type.type , > > 'rate' 1 > > FROM employee > > CROSS JOIN project > > CROSS JOIN work_type; > > > > This statement works correctly, till I add the last " 'rate' 1 line, then it > > returns a syntax error. > > I don't think you can use a number as a column name. Give it a different > name and it should work: Well you can but only if you quote the column alias SELECT employee.id , project.proj_no , work_type.type , 'rate' "1" FROM employee CROSS JOIN project CROSS JOIN work_type; but I really wouldn't recommend it. That way madness lies. Geoff
Re: Is this a bug ?
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 SQL Error [102] [S0001]: Incorrect syntax near 'd'.
>
> Can't believe this bug was never found before. We ended up wasting lot of
> time to figure this out.
Simplify:
select 'a'
db-# 'b';
?column?
--
ab
(1 row)
This is not a bug.
https://www.postgresql.org/docs/9.2/sql-syntax-lexical.html
Two string constants that are only separated by whitespace with at
least one newline are concatenated and effectively treated as if the
string had been written as one constant.
Geoff
Re: Is this a bug ?
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 'a', 'b', 'cd' or 'e'. There is not one with 'cd', so the correct result is 3. Geoff
Re: Is this a bug ?
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 ignored standards either because they misunderstood them or because they wanted to lock in their customers, not for any reasons of altruism. 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'; although the advantage of it vs using a concat operator is slim. Geoff
Re: Is this a bug ?
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'; > > although the advantage of it vs using a concat operator is slim. As an aside, Postgres isn't the only DB to follow the standard here. mysql> select 'hello' -> ' there'; +-+ | hello | +-+ | hello there | +-+ Geoff
Re: CASE(?) to write in a different column based on a string pattern
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 case statements for each column, I think. SELECT pattern, CASE WHEN pattern LIKE 'foo%' THEN SUBSTR(pattern, 4) ELSE '' END AS foo CASE WHEN pattern LIKE 'bar%' THEN SUBSTR(pattern, 4) ELSE '' END AS bar CASE WHEN pattern LIKE 'baz%' THEN SUBSTR(pattern, 4) ELSE '' END AS baz FROM tbl; Geoff
Re: REINDEX VERBOSE unknown option
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 understand it). In the parameter list of REINDEX, it is > > still called VERBOSE (not "( VERBOSE )") and there's no info > > that parentheses are needed. [snip] > Mainly historical reasons. REINDEX VERBOSE has been added in 9.5. > EXPLAIN VERBOSE is around since at least 7.1. Using options within > parenthesis is preferred lately because it is much easier to make the > grammar more extensible for future purposes and it eases the option > parsing. All well and good (although personally I think it's arguable whether it's productive to have two different syntaxes for something that in the user's perspective does the same thing). But if the parentheses are part of the parameter, I think putting the parentheses in the parameter list might be a good compromise. Geoff
Re: REINDEX VERBOSE unknown option
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 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 understand it). In the parameter list of REINDEX, it is
> > > > still called VERBOSE (not "( VERBOSE )") and there's no info
> > > > that parentheses are needed.
> [...]
> > But if the parentheses are part of the parameter, I think putting the
> > parentheses in the parameter list might be a good compromise.
>
> The parentheses aren't part of the parameter. They are part of the
> syntax of the command.
Then at the very least draw attention to the fact that the parentheses
are required in the description of the parameter in the notes.
It's bad enough that you have the inconsistency that REINDEX VERBOSE
requires parentheses while the more recent REINDEX CONCURRENTLY does
not (presumably to match the syntax of CREATE INDEX CONCURRENTLY),
without insisting that the user parse the difference between { and (
in the manual (not the easiest difference to scan) before they can use
the command.
> How about this?
>
> * Terminals (stuff that has to be typed as shown) in bold.
>
> * Non-Terminals (stuff which has to be replaced) in italic.
>
> * Meta-characters ([, ], |, ...) in regular type.
Even if you do that you're still requiring the user to parse syntax
according to esoteric rules. I'm not sure that changing the rules
helps that much.
Geoff
Re: REINDEX VERBOSE unknown option
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" have been in wide-spread use for > decades. It wasn't meant to be insulting, I meant "esoteric" in the strict sense: that you need to have specific knowledge to parse them. My point was that modifying the rules (by making certain things bold or italic) wouldn't really solve the problem - if you don't know what the rules are, you're unlikely to be any better off if someone adds to them. Geoff
Re: REINDEX VERBOSE unknown option
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't understand it as insulting (why would I?), I've absolutely no idea, but I couldn't imagine why on Earth you would apparently take such exception to it otherwise. Maybe writing sarcastic bombast in response to something that hasn't annoyed you is just your thing. Oh well, you do you. > but don't think this > convention is "requiring ... knowledge that is restricted to a small > group" (Merriam-Webster). That's entirely the case here. I'd say the number of people able to understand something like BNF is vanishingly small in terms of the 7bn(?) world-population. > This specific convention for conveying grammar > rules is in my experience by far the most common (before BNF and > diagrams). Anybody who has read any documentation about any formal > language (e.g., a programming language, a query language, a markup or > configuration language) has very likely encountered it before. OK, but parentheses not being part of the rules, ie that they should be read literally, is something that is knowledge specific to postgresql, because "this specific convention" you blithely reference is only a convention, and there are several instances where programmers have their own version of this convention where parentheses are part of the grammar, not the syntax. > > My point was that modifying the rules (by making certain things bold > > or italic) wouldn't really solve the problem - if you don't know what > > the rules are, you're unlikely to be any better off if someone adds to > > them. > > people are very > likely to understand that > [ ( VERBOSE ) ] > means that "( VERBOSE )" must be typed as is, but is optional. Even if > they can't tell you the rules Depending on the font, I'm not at all confident that I could tell the difference between ( VERBOSE ) and ( VERBOSE ) unless they're actually next to each other. Geoff
Re: SQL Query Syntax help
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 table" with the value columns specified by a second query Geoff
combination join against multiple tables
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); CREATE TABLE t2 (a int, b int, c int); CREATE TABLE base (a int); INSERT INTO t1 (a, b, c) VALUES (1, 1, 111), (1,2,112), (2,1,121), (4,1,141); INSERT INTO t2 (a, b, c) VALUES (1, 1, 211), (2, 2, 222), (5,3,253); INSERT INTO base(a) SELECT * FROM GENERATE_SERIES(1,10); 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 results I would like: a | c | c +-+- 1 | 111 | 211 1 | 112 | 2 | 121 | 2 | | 222 3 | | 4 | 141 | 5 | | 253 6 | | 7 | | 8 | | 9 | | 10 | | At the moment I'm doing SELECT base.a, t1.c, t2.c FROM base CROSS JOIN (SELECT b FROM t1 UNION SELECT b FROM t2 UNION SELECT -1) tmpset LEFT JOIN t1 ON t1.a=base.a AND t1.b=tmpset.b LEFT JOIN t2 ON t2.a=base.a AND t2.b=tmpset.b WHERE t1.a IS NOT NULL OR t2.a IS NOT NULL OR (tmpset.b=-1 AND NOT EXISTS (SELECT FROM t1 WHERE t1.a=base.a) AND NOT EXISTS (SELECT FROM t2 WHERE t2.a=base.a) ); but this seems like a really convoluted way to do it. Is there a join style that will return the set I want without the pain? I should be clear that the real world data is much more complex than this, but it describes the basic problem. Thanks Geoff
Re: combination join against multiple tables
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 rows for each “a” is the larger row count of “b” > and “c” having the same “a”. Furthermore for the first “n” rows “b” and > “c” should be paired together by position. The smaller count column just > gets nulls for the extra rows. > > Probably the easiest way is to combine the matches for “b” and “c” into > arrays the jointly unnest those arrays in the final result - with in the > select list or 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). > > Thanks for the reply. Using array() hadn't occurred to me, I'll look at that. I actually came up with this: SELECT base.a, t1.c, t2.c FROM base LEFT JOIN (t1 FULL OUTER JOIN t2 ON t1.b=t2.b AND t1.a=t2.a) ON COALESCE(t1.a, base.a)=base.a AND COALESCE(t2.a, base.a)=base.a; which does solve the described problem; sadly I realise that I'd oversimplified my question: I haven't fully described the problem because in reality "t2" is joined to "base" with a different field, and I can't seem to get the join to do what I want without joining them together like this. Geoff
Re: Get rid of brackets around variable
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-01'')', i, i;
>> END LOOP;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>
> If I execute, this is the output:
>
> NOTICE: create table (logi_web)_y2020m01 PARTITION OF (logi_web) FOR
>> VALUES FROM ('2019-12-01') TO ('2020-01-01')
>> NOTICE: create table (logi_taustaprotsess)_y2020m01 PARTITION OF
>> (logi_taustaprotsess) FOR VALUES FROM ('2019-12-01') TO ('2020-01-01')
>> DO
>
>
You're returning rows from the cursor.
You need to use i.col1 instead of i.
Geoff
Re: Lock Postgres account after X number of failed logins?
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 mature, and (with the password policy overlay) should provide exactly the functionality the OP requested. Geoff
Re: Lock Postgres account after X number of failed logins?
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 of infrastructure. > > > > Is there an objection to openldap? > > LDAP-based authentication in PG involves passing the user's password to > the database server in the clear (or tunneled through SSL, but that > doesn't help if the DB is compromised), so it's really not a good > solution > If your DB is compromised then (if the LDAP server is only used for the db) what difference does it make to lose the passwords? I was (as per the thread) suggesting a simple way for small companies to achieve the OP's requirements without a large infrastructure investment and without involving the pg team undertaking the rediscovery of novel circular transportation-assisting devices. Any large company will have an AD or similar setup already, clearly I'm not suggesting using it in that situation. AIUI you can configure kerberos with openldap if that's more your thing, fwiw, but then IME the learning curve (and thus setup cost) increases exponentially. Geoff
Re: Order by lower(column-alias) doesn't work...
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_person p order by lower(fullname); > ERROR: column "fullname" does not exist > LINE 1: ... as fullname from onp_crm_person p order by lower(fullname); Wrap the original query in either a CTE or a temporary table. eg => SELECT REPLACE(name, '_', ' ') AS nm FROM subs ORDER BY lower(nm); ERROR: column "nm" does not exist => SELECT * FROM (SELECT REPLACE(name, '_', ' ') AS nm FROM subs) AS t ORDER BY lower(nm); [results] => WITH t AS (SELECT REPLACE(name, '_', ' ') AS nm FROM subs) SELECT * FROM t ORDER BY lower(nm); [results] Geoff
Re: Need help how to reproduce MySQL binary to PosgreSQL
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. Those functions _have_ been implemented long ago with other names, as per the answer to the link given. The functions you want are encode and decode, or to_hex if you want to encode integers. The only reason to use the CREATE FUNCTION shims as per the answer is if you don't want to change your code. You might find https://pgxn.org/dist/mysqlcompat/ helpful too. Geoff
Re: tcp keepalives not sent during long query
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-domain socket, this parameter is ignored and always reads as zero." Geoff
Re: Re[2]: Getting error while upgrading postgres from version 12 to 13
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-bindir /usr/lib/postgresql/13/bin > --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link > --check > Just in case I'm _not_ misreading this... are you saying you already ran this command once with postgres v12.6? If so, since you've used --link the original folder will no longer be version12 data. Geoff >
Re: Re[2]: Getting error while upgrading postgres from version 12 to 13
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 working with 1version 2.14 >> time /usr/lib/postgresql/13/bin/pg_upgrade --old-bindir >> /usr/lib/postgresql/12/bin --new-bindir /usr/lib/postgresql/13/bin >> --old-datadir /usr/pgdata/pg_data --new-datadir /usr/pgdata/pg_data2 --link >> --check >> > > Just in case I'm _not_ misreading this... are you saying you already ran > this command once with postgres v12.6? > > If so, since you've used --link the original folder will no longer be > version12 data. > No, I'm an idiot. Ignore me, I missed "--check". Geoff
Re: Using CTID system column as a "temporary" primary key
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 definition to add your own indexed BIGSERIAL value called "ROWID" to the rows and use that instead (assuming it will be large enough). Geoff
Re: psql is hanging
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 usage with a postgresql > process, so it > appears to be doing something. > > If I stop that command, then immediately rerun the rest of the script > starting from the command that "hung", > the "hung" command then completes in 5 seconds and the rest of the script > just continues on perfectly. I assume that JDBC doesn't put a transaction around your whole script if you send it in one hit? Geoff
Re: simple division
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 value - the potential for unexpected errors creeping into calculations is huge; however that's the standard and no-one's going to change it now. Having said that it's worth noting that those in the Other Place think that it's broken enough to go against the standard (they have a DIV b for integer divide and a/b for float). Geoff
Re: simple division
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". Where's the logical progression in step 3 here: 1 You asked the computer a question 2 The values you passed to it don't have decimal points ... 4 Ergo, you wanted an answer that was incorrect. Geoff
Re: Casting Integer to Boolean in assignment
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 describing why casts are chosen to be defined as implicit or not here https://www.postgresql.org/docs/9.5/typeconv-overview.html My own opinion is that non-0 should implicitly cast as true and 0 should cast as false. I just run UPDATE pg_cast SET castcontext = 'i' WHERE oid IN ( SELECT c.oid FROM pg_cast c inner join pg_type src ON src.oid = c.castsource inner join pg_type tgt ON tgt.oid = c.casttarget WHERE (src.typname ILIKE '%int%' AND tgt.typname ILIKE 'bool%') OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE '%int%') OR (src.typname ILIKE 'bool%' AND tgt.typname ILIKE 'bit%') OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE 'bool%') OR (src.typname ILIKE 'bit%' AND tgt.typname ILIKE '%int') ); when I install the system to solve this for my own uses. Geoff
Re: Casting Integer to Boolean in assignment
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 would make > such a query valid: Feel free. I said it's my own opinion and gave a way for someone who agrees with me to do the same as I do. If your objection is that someone can write a stupid query and it might go wrong, there are a million other things that should be addressed before implicit int::bool casts. Geoff
Re: Casting Integer to Boolean in assignment
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
Re: Casting Integer to Boolean in assignment
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 developer complaining today > might have their bacon saved tomorrow. I've missed off WHERE clauses on a live database (oops) in my time, and I'm happy to see work being done to safeguard against that (although I tend to be of the opinion that it's not something you ever do twice!) but I can confidently state that I've never once been caught out by being surprised that a number was treated as a boolean. How could you even write a query like the one Thomas posted? It doesn't even look remotely sensible. But I have been caught out by boolean vs int, enough that I bothered to search out that ALTER statement. And I'm a lazy person at heart, so if something irritated me enough to bother doing that, you can be sure it was _really_ irritating me. Geoff
Re: Casting Integer to Boolean in assignment
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 misunderstand me. I mean, how can one write a query like that by mistake? DELETE FROM WHERE ; What would you be thinking that that ought to do? G
Re: Casting Integer to Boolean in assignment
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 ever done, but at least I see how it's possible. *shrug* I should reiterate, it's just my opinion, I'm certainly not arguing for it to be changed, although I would be pretty upset if the existing ability to change the behaviour were removed. Geoff
Re: Casting Integer to Boolean in assignment
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 single boolean test at all
should be invalid.
CREATE TABLE mytest (myval char (1));
INSERT INTO mytest VALUES ('a'),('b'),('c'),('s'),('t');
DELETE FROM mytest WHERE 't';
SELECT * FROM mytest;
myval
---
(0 rows)
Geoff
Re: Casting Integer to Boolean in assignment
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 complete it. Missing out a clause altogether is understandable but writing half of one? Even if you weren't sure what the value was you would probably write WHERE myfield= and then have to go and look it up. > Like you say it is a matter of opinion. The projects opinion is here: > > https://www.postgresql.org/docs/11/datatype-boolean.html > > and it works for me. And you're welcome to it. I'm not arguing for it changing. I'm simply stating that I'm very pleased that the default behaviour can be changed, because in my opinion writing a bunch of explicit casts in a query is a surefire path to unreadable code. Geoff
Re: Forks of pgadmin3?
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
Re: multiple indexes on the same column
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?
Re: bigint out of range
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
Re: with and trigger
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, the insert won't happen (as far as the rest of the query is concerned) until it commits. Or have I misunderstood what you're saying? Geoff
Re: libpq and multi-threading
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 dynamic memory being > allocated using malloc(3) from two different threads simulaneously will > result in internal interference. > There's an interesting note here https://stackoverflow.com/a/45285877 TL;DR: glibc malloc does not cope well with threads created with clone(). Use pthread_create if you wish to use glibc malloc. Geoff
Re: TSQL To Postgres - Unpivot/Union All
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 results are going. Geoff
Re: TSQL To Postgres - Unpivot/Union All
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 INSERT statements, if only to make your own life easier, but especially because any later changes to the tables could end up shifting the position of the columns. Certainly it will be easier for anyone else trying to disentangle the SQL later. As an aside, can you clarify whether you mean temporal tables (and are you using a temporal tables extension)? Or should that read "temporary"? Geoff
Re: ERROR: stack depth limit exceeded
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
updating PGDG 12 devel on centos7 requires llvm5.0
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 >= 5.0 SCLO is enabled and it happily installs llvm7-toolset (and its various subs) from there. But yum install llvm5.0-devel simply returns "No package llvm5.0-devel available" There's suggestions in the mailing list archives that actually this is resolved by installing llvm7-devel package but it's not. # yum list installed | grep llvm llvm-toolset-7-clang.x86_64 5.0.1-4.el7 @sclo llvm-toolset-7-clang-libs.x86_64 5.0.1-4.el7 @sclo llvm-toolset-7-compiler-rt.x86_64 5.0.1-2.el7 @sclo llvm-toolset-7-libomp.x86_64 5.0.1-2.el7 @sclo llvm-toolset-7-llvm.x86_645.0.1-8.el7 @sclo llvm-toolset-7-llvm-devel.x86_64 5.0.1-8.el7 @sclo llvm-toolset-7-llvm-libs.x86_64 5.0.1-8.el7 @sclo llvm-toolset-7-runtime.x86_64 5.0.1-4.el7 @sclo llvm-toolset-7.0.x86_64 7.0.1-2.el7 @sclo llvm-toolset-7.0-clang.x86_64 7.0.1-1.el7 @sclo llvm-toolset-7.0-clang-libs.x86_647.0.1-1.el7 @sclo llvm-toolset-7.0-compiler-rt.x86_64 7.0.1-3.el7 @sclo llvm-toolset-7.0-libomp.x86_647.0.1-2.el7 @sclo llvm-toolset-7.0-lld.x86_64 7.0.1-2.el7 @sclo llvm-toolset-7.0-lld-libs.x86_64 7.0.1-2.el7 @sclo llvm-toolset-7.0-lldb.x86_64 7.0.1-1.el7 @sclo llvm-toolset-7.0-llvm.x86_64 7.0.1-4.el7 @sclo llvm-toolset-7.0-llvm-devel.x86_647.0.1-4.el7 @sclo llvm-toolset-7.0-llvm-libs.x86_64 7.0.1-4.el7 @sclo llvm-toolset-7.0-python2-lit.noarch 0.7.1-1.el7 @sclo llvm-toolset-7.0-runtime.x86_64 7.0.1-2.el7 @sclo I tried adding the EPEL repo just in case that was the issue but it hasn't made any difference. Thanks Geoff
Re: updating PGDG 12 devel on centos7 requires llvm5.0
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 > hasn't made any difference. In case anyone else hits the same issue, I had a dead network-local cache of the epel repo in yum.repos.d that, even though it was marked as disabled, was blocking the live repo that I added later. Geoff
Re: ON CONFLICT DO NOTHING ignored on bulk insert
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
Re: Faster distinct query?
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 not, why not?), then an EXISTS query, something like SELECT stations.name, ARRAY_AGG(channels.name) FROM stations, channels WHERE EXISTS (SELECT FROM data WHERE data.channels=channels.name AND data.station= stations.name) GROUP BY stations.name will usually be much faster, because it can stop scanning after the first match in the index. Geoff
Re: migrations (was Re: To all who wish to unsubscribe)
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 commercial product or service”. This is a discussion mailing list and as such does not come under the remit of CAN-SPAM. Geoff
Re: [GENERAL] Multiple key error .
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
Re: Re: PostgreSQL needs percentage function
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
ADD OR ALTER column
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 part of users. Geoff
Re: ADD OR ALTER column
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, while > potentially >saving a significant amount of effort on the part of users. > > what is your use case ? Not sure what benefit you are talking about. > Having one script to ensure the user has the latest version of a db, without a significant amount of to-and-fro effort. Actually I would probably rather have CREATE OR ALTER TABLE instead, with the syntax of CREATE TABLE but with an optional USING clause for each column for use if the column already existed and was of a non-implicitly-castable type, along with an optional WITH DROP COLUMNS [ * | name [,...] ] clause (to allow user to drop any (or specific) columns not in the new definition). That's likely to be rather more effort though, at first glance. Geoff
testing for DEFAULT insert value in ON CONFLICT DO UPDATE query
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 subclause for the columns that I don't want to set and use SET column=CASE WHEN EXCLUDED.column IS NULL THEN tablename.column ELSE EXCLUDED.column END (or COALESCE(), if you prefer); however for NOT NULL columns with a default, I don't know how I can do this. I was hoping for something like an "IS DEFAULT" test but that didn't work. I can't just test for the default value itself because there might be times when I want to update the value to the default, overriding an existing value. I also can't simply exclude the column from the insert because for some rows I _will_ be setting the value. Am I missing something obvious, or am I going to have to change the column to accept NULLs in order to make this work? Thanks Geoff
