Re: PG SQL and LIKE clause

[John W Higgins]

Is this a possibility?

From
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
-

~ 'ali[ ]*$' matches strings ending in ali that have zero of more spaces
after ali

this would match

'bali'
'ali'
'ali '
'bali '

If you need full string then it would be like this

~ '^ali[ ]*$' matches string only containing ali plus zero or more spaces
after ali

this would match

'ali'
'ali '

but not match

'bali'
'bali '

Also switching ~* for ~ makes if case insensitive if necessary.


John


On Thu, Sep 12, 2019 at 10:29 PM Matthias Apitz  wrote:

>
> Hello,
>
> We're porting a huge Library Management System, written using all kind
> of languages one can think of (C, C++, ESQL/C, Perl, Java, ...) on Linux
> from the DBS Sybase to PG, millions of lines of code, which works also
> with DBS Oracle and in the past with INFORMIX-SE and -ONLINE.
>
> We got to know that in CHAR columns with trailing blanks a
>
> SELECT ... FROM ... WHERE name LIKE 'Ali'
>
> does not match in 'name' having 'Ali '.
>
> I glanced through our code with grep pipelines and found some hundred
> places which would be affected by this problem. I'm not interested in a
> religious discussion if or if not this behaviour of PG is correcter or
> better than in Sybase. It's just different to Sybase.
>
> Any hints to address this problem? Or is there any compile time option
> for the PG server to address this?
>
> Thanks
>
> matthias
> --
> Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/
> +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
>
> Mientras haya voluntad de lucha habrá esperanza de vencer.
>
>
>

Re: Extend inner join to fetch not yet connected rows also

[John W Higgins]

On Sun, Sep 22, 2019 at 6:30 AM Arup Rakshit  wrote:

>
>
> SELECT
> craftsmanships.id,
> craftsmanships.name,
> CASE WHEN contractor_skills.user_id IS NULL THEN
> FALSE
> ELSE
> TRUE
> END AS has
> FROM
> "craftsmanships"
> LEFT JOIN "contractor_skills" ON
> "contractor_skills"."craftsmanship_id" = "craftsmanships"."id"
> LEFT JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
> WHERE (contractor_skills.user_id = 8
> OR contractor_skills.user_id IS NULL)
> ORDER BY
> "craftsmanships"."id”;
>
> Gives correct result. Not sure if still this query has bug in it.
>
>
If you do not understand the query - then it's wrong on its face. You
should never run something which you do not understand.

So one should take a step back - make smaller pieces and then combine
smaller pieces of logic together to form an answer. If at some point in the
future there is a performance issue - then deal with that then - but do not
make some fancy multi join query that you do not fully understand.

So in that vein,

Piece 1 = A list of craftsmanship_id for a particular user
Piece 2 - Take piece 1 and compare to the full list of craftsmanship_id

Putting piece 1 into a CTE you end up with something like this.

with UserSkills as (
SELECT
  craftsmanship_id
FROM
  contractor_skills
WHERE
  user_id = 3
)
SELECT
  craftsmanships.id,
  craftsmanships.name,
  CASE WHEN UserSkills.ctraftsmanship_id IS NULL THEN FALSE
  ELSE TRUE as has
FROM
  craftsmanships
LEFT JOIN
  UserSkills
ON
  craftsmanships.id = UserSkills.craftsmanship_id

So you take the two pieces and combine then. Yes you can drop the CTE into
the main body - but unless you are certain you are doing it correctly -
there is no point doing that. The query parser will do the work for you -
so why bother making your life more difficult then it need be.

John W Higgins

Re: Thoughts on a cosntraint ?

[John W Higgins]

On Sun, Sep 29, 2019 at 12:40 PM stan  wrote:

> I have a table that consists of 3 columns.
>
> vendor_key
> mfg_key
> preferred (boolean)
>
> The idea is that a given vendor is the vendor we want to use for each
> manufacturer for a given project.
>
> I need to constrain such that  only on row of mfg, vendor and project can
> be set to TRUE.
>
>
Partial unique index

Example 3 on this page
https://www.postgresql.org/docs/current/indexes-partial.html is exactly
what you are looking for.

John W Higgins

>
>

Re: Is this a bug ?

[John W Higgins]

On Wed, Oct 23, 2019 at 8:56 AM Ravi Krishna  wrote:

> >
> > 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
>
> Shouldn't the output be 4 in that case since it is looking
> for('a','b','cd','e') and it should find all but cd.
>
>
And that means there are 3 options on the table = a, b, and e - where is
the 4th item?

cd is not in the table so it cannot be found.

John

Re: How to handle CASE statement with PostgreSQL without need for typecasting

[John W Higgins]

Good Morning,


>
> NOTE:  From my research online, I found that typecasting works and also
> the error from the database suggests typecasting.
>
> This statement works:
>
> UPDATE t_update SET F1 = (CASE WHEN (?::timestamp(6) IS NULL ) THEN
> (?::timestamp(6) ) ELSE (?::timestamp(6) ) END)
>
>
There is no option to convert the text parameter to a timestamp - you need
to cast it - or use a parsing function or something else - but a text value
cannot drop directly into a timestamp column. But it's not the case
statement that is the issue - but rather the update - so you could shorten
the statement a little with this.

UPDATE t_update SET F1 = (CASE WHEN (? IS NULL ) THEN (?) ) ELSE (?) ) END)
::timestamp(6)

You don't need a timestamp until you place in in the column.

You also probably don't want a case statement here - not the standard
option for this

UPDATE t_update SET F1 = COALESCE(?, ?)::timestamp(6) with the first ?
being the when above and the second being the else above. See here [1]

John

[1] - https://www.postgresql.org/docs/current/functions-conditional.html

>

Re: Could postgres12 support millions of sequences? (like 10 million)

[John W Higgins]

On Fri, Mar 20, 2020 at 8:13 PM pabloa98  wrote:

>
> I hope I described the problem completely.
>
>
1) What is a group - does it exist prior to records being inserted? How
many groups are you working with? How long do they live for?
2) #1 but for element
3) How many records per second per group per element are you anticipating?
Are their spikes?
4) How long does a group/element live for? You can put 10 record per second
over 3 years and still be under 100 million records (the size of your
seq_number)
5) How quickly do you need the seq_number - if you batch created them once
a minute would that work? Does it need to be quicker than that? Slower than
that? Or is it an immediate need?

That's a starting point.

John W Higgins

Re: Bug on version 12 ?

[John W Higgins]

On Fri, May 15, 2020 at 9:38 AM PegoraroF10  wrote:

> Ok Tom but then you cannot go back and forth, like this ...
>
> select to_timestamp(jsonb_build_object('mydate',
> current_timestamp)->>'mydate', '-MM-DD HH24:MI:SS');
>

>From here [1] - there are 2 green boxes on the page marked "Tip" - the
second one is of interest here.

Apparently the portable format for your need would be

select to_timestamp('2020-04-02T18:26:50.941531-03:00',
'-MM-DDtHH24:MI:SS');

That works on both PG 11 and PG 12.

John W Higgins

[1] - https://www.postgresql.org/docs/12/functions-formatting.html

Re: UUID or auto-increment

[John W Higgins]

On Mon, Aug 10, 2020 at 1:45 PM Israel Brewster 
wrote:

>
>
> > On Aug 10, 2020, at 12:06 PM, Peter J. Holzer  wrote:
> >
> > On 2020-08-10 09:10:00 -0800, Israel Brewster wrote:
> >> I would point out, however, that using a V1 UUID rather than a V4 can
> >> help with this as it is sequential, not random (based on MAC address
> >> and timestamp + random).
> >
> > If I read the specs correctly, a V1 UUID will roll over every 429
> > seconds. I think that as far as index locality is concerned, this is
> > essentially random for most applications.
>
> According to wikipedia, the time value in a V1 UUID is a 60-bit number,
> and will roll over "around 3400AD”, depending on the algorithm used, or
> 5236AD if the software treats the timestamp as unsigned. This timestamp is
> extended by a 13 or 14-bit “uniqifying" clock sequence to handle cases of
> overlap, and then the 48bit MAC address (constant, so no rollover there) is
> appended. So perhaps that 13 or 14 bit “uniqifying” sequence will roll over
> every 429 seconds, however the timestamp *as a whole* won’t roll over for
> quite a while yet, thereby guaranteeing that the UUIDs will be sequential,
> not random (since, last I checked, time was sequential).
>
>
Except the time portion of a V1 UUID is not written high to low but rather
low then middle then high which means that the time portion is not
expressed in a sequential format and the left 8 chars of a V1 UUID
"rollover" every 429 seconds or so.

For example a V1 UUID right around now looks like

7db3f2ba-db4f-11ea-87d0-0242ac130003

Less than a second later

7db534cc-db4f-11ea-87d0-0242ac130003

So that looks sequential but in roughly 429 seconds it will look like

7db3f2ba-db4f-11ea-87d1-0242ac130003

More importantly in other roughly 300 seconds it would be something like

6ab3f2ba-db4f-11ea-87d2-0242ac130003

Note the move from 87d0 to 87d1 and 87d2 in the middle but the left 8 bytes
"rollover".

That's not quite sequential in terms of indexing.

John

Re: TO_DATE function between PostgreSQL 8.2 and 9.4

[John W Higgins]

Good Morning,

On Tue, May 17, 2022 at 8:55 AM gzh  wrote:

> Hi,
>


>
> 
>
> ERROR:  function to_date(timestamp with time zone, unknown) does not exist
>
> LINE 1: SELECT to_date(now() + '-7day', '-MM-DD')
>
> ^
>
> ** Error **
>
>
> Of course, the most reliable way to deal with it is to
>
> rewrite the application or SQL to handle types strictly,
>
> but the application is large and rewrite is a terrible job.
>
>
> Is there any easy way to maintain compatibility?
>
>
Just create the missing function

create function to_date(timestamp with time zone, text) RETURNS text AS
   'SELECT to_date($1::text, $2)::text'
   LANGUAGE SQL
   IMMUTABLE;

(see https://www.db-fiddle.com/f/w5WYLgsiFLv3jm7hhJ7dsH/0 for a working
example)

John

Re: Window function?

[John W Higgins]

On Sat, Jun 4, 2022 at 1:18 AM Robert Stanford  wrote:

> Hi,
>


> From this:
> Input   Start End
> 5   2022-06-04 09:09:00   2022-06-04 09:09:29
> 4   2022-06-04 09:08:50   2022-06-04 09:09:00
> 4   2022-06-04 09:08:10   2022-06-04 09:08:50
> 4   2022-06-04 09:07:47   2022-06-04 09:08:10
> 17  2022-06-04 09:06:47   2022-06-04 09:07:47
> 4   2022-06-04 09:06:37   2022-06-04 09:06:47
> 4   2022-06-04 09:06:29   2022-06-04 09:06:37
> 4   2022-06-04 09:06:17   2022-06-04 09:06:29
> 4   2022-06-04 09:05:53   2022-06-04 09:06:17
> 16  2022-06-04 09:04:33   2022-06-04 09:05:53
>
> To this:
> Input   Start End
> 5   2022-06-04 09:09:00   2022-06-04 09:09:29
> 4   2022-06-04 09:07:47   2022-06-04 09:09:00
> 17  2022-06-04 09:06:47   2022-06-04 09:07:47
> 4   2022-06-04 09:05:53   2022-06-04 09:06:47
> 16  2022-06-04 09:04:33   2022-06-04 09:05:53
>

lag is indeed your friend here - assuming times is your table name

with times_cte as (select *, lag(input, 1) over () from times)
select input, start, end from times_cte where input != coalesce(lag, -1);

The coalesce to -1 is needed at the end to get the first row which has null
for the lag value because it's the first row.

John

Re: Limitting full join to one match

[John W Higgins]

On Wed, Dec 5, 2018 at 4:34 PM Phil Endecott <
spam_from_pgsql_li...@chezphil.org> wrote:

> Dear Experts,
>
> I have a couple of tables that I want to reconcile, finding rows
> that match and places where rows are missing from one table or the
> other:
>
> ...


> So my question is: how can I modify my query to output only two rows,
> like this:?
>
> +++++
> |date| amount |date| amount |
> +++++
> | 2018-01-01 |  10.00 | 2018-01-01 |  10.00 |
> | 2018-02-01 |   5.00 |||
> ||| 2018-03-01 |   8.00 |
> | 2018-04-01 |   5.00 | 2018-04-01 |   5.00 |
> | 2018-05-01 |  20.00 | 2018-05-01 |  20.00 |  1
> | 2018-05-01 |  20.00 | 2018-05-01 |  20.00 |  2
> +++++
>
>
Evening Phil,

Window functions are your friend here. I prefer views for this stuff - but
subqueries would work just fine.

create view a_rows as (select *,
   row_number() OVER (PARTITION BY date, amount) AS pos
from a);
create view b_rows as (select *,
   row_number() OVER (PARTITION BY date, amount) AS pos
from b);

select
  a_rows.date,
  a_rows.amount,
  a_rows.pos,
  b_rows.date,
  b_rows.amount,
  b_rows.pos
from
  a_rows full join b_rows using (date,amount,pos);

Example here - http://sqlfiddle.com/#!17/305d6/3

John

>
> Any suggestions anyone?
>
>
> The best I have found so far is something involving EXCEPT ALL:
>
> db=> select * from a except all select * from b;
> db=> select * from b except all select * from a;
>
> That's not ideal, though, as what I ultimately want is something
> that lists everything with its status:
>
> ++++
> |date| amount | status |
> ++++
> | 2018-01-01 |  10.00 |   OK   |
> | 2018-02-01 |   5.00 | a_only |
> | 2018-03-01 |   8.00 | b_only |
> | 2018-04-01 |   5.00 |   OK   |
> | 2018-05-01 |  20.00 |   OK   |
> | 2018-05-01 |  20.00 |   OK   |
> ++++
>
> That would be easy enough to achieve from the JOIN.
>
>
> Thanks, Phil.
>
>
>
>

Re: Query help

[John W Higgins]

On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin 
wrote:

> Sorry if this is too basic a question for this list, but I don't fully get
> how to use aggregates (sum()) and group-by together. I'm trying to get a
> list of transactions where the total for a given account exceeds a given
> number. I'm not sure an example is needed, but if so, consider this
> simplified data:
>
> accountid.   name
> 1  bill
> 2. james
> 3  sarah
> 4  carl
>
> transaction
> id. amount.  accountid. name
> 1.  50.  1   bill
> 2.  25.  2   james
> 3   35   4   carl
> 4.  75.  1   bill
> 5   25.  1   bill
> 6   50   3   sarah
>
> results wanted-all transactions where account total >= 50
>
> id. amount.  accountid.name
> 1.  50.  1   bill
> 3.  75.  1   bill
> 4   25.  1   bill
> 5   50   3   sarah
>
>
You have 2 concepts here - identify the accounts with a total over 50 and
then show the transactions for those accounts. I prefer CTEs here because
they allow for better understanding (to me) of the steps involved. A
subquery would work here as well.

with accounts_over_total as (
select accountid from transactions where sum(amount) >= 50 group by
accountid)
select transactions.* from transactions join accounts_over_total on
transactions.accountid = accounts.accountid

John


> I've tried to understand how to use GROUP BY and HAVING, but the penny
> won't drop. I keep getting errors saying that all columns in the SELECT
> have to also be in the GROUP BY, but nothing I've done seems to produce the
> correct results. I think because the GROUP BY contains multiple columns, so
> each row is treated as a group. It also is difficult to parse out since in
> the real world, many more tables and columns are involved.
>
> Chuck Martin
> Avondale Software
>

Re: Question about the new PostgreSQL 16 availability on Ubuntu

[John W Higgins]

On Wed, Oct 25, 2023 at 8:36 AM Momchil Milev 
wrote:

> Hello,
>
> I am trying to install PostgreSQL 16 on Ubuntu 18 and added your
> repository *deb https://apt.postgresql.org/pub/repos/apt
>  bionic-pgdg main* but it seems
> that version 16 is not available. Tle latest available version is 15. Is
> there some problem with the new version and why is it marked as *beta* in
> your wiki https://wiki.postgresql.org/wiki/Apt since it is already
> released?
>

18.04 is EOL so my guess would be that they are no longer creating packages
for that release.

As to the wiki (and/or any other references), small number of people, doing
way too many things - some stuff doesn't get updated as quickly as one
would like.

John

Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

[John W Higgins]

On Mon, Aug 9, 2021 at 12:41 PM Bryn Llewellyn  wrote:

>
> *https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter
> *
>
> *Question 1.*
> *---*
> *Where, in the PL/pgSQL doc, does it state that "select col into var" when
> col is a user-defined type doesn't work—and where is **the viable
> approach **shown?*
>
>
The first paragraph of the SO answer completely explains why this occurs.

However, the following 2 locations explain how we get here

1) https://www.postgresql.org/docs/current/rowtypes.html

Opening sentence of that page.

"A *composite type* represents the structure of a row or record;"

2) https://www.postgresql.org/docs/current/plpgsql-statements.html -
Section 42.5.3

"The result of a SQL command yielding a single row (possibly of multiple
columns) can be assigned to a record variable, row-type variable, or list
of scalar variables."

You did not provide a scalar variable - you provided a composite type -
which equates to a record/row-type variable and therefore, as described,
the engine tried to place each column returned into a column of your
composite type. Therefore the first column of the select result is placed
in the first column of your composite type - and you get an error.

It would seem rather clear that a sentence discussing composite types is
very much an option here in 42.5.3 to clarify it further given your
confusion today.


> *Question 2.*
> *---*
> *If I can easily re-write a failing approach by hand (once I know that I
> must) so that it works, why cannot the PL/pgSQL compiler do this under the
> covers?*
>

First, there is no need to not write the select almost the way you
initially tried. The following works just fine.

 select (r1).h, (r1).w
  into r
  from t1
  where k = 1;

The engine sees a composite type as the receiver and places the data in it
as it is supposed to.

As another example, switch out your do with the following and it works
fine. It's not user-defined types - but rather how they work.

create type rect_bucket as (r rect);

do $body$
declare
  r rect_bucket;
begin
  select r1
  into r
  from t1
  where k = 1;
end;

$body$;

Since the composite type is a single column of rect type - the select into
works - as does the variant I showed earlier.

John