How to remove elements from array .

[Brahmam Eswar]

Hi ,

I tried to use array_remove to remove elements from an array but it's
saying function doesn't exist . I'm able to use other array functions.

1) Capture the results with multiple columns into array .
2)  if ay results exist then loop through an array to find out the record
with col1='Y'
3)  If col1='Y' then get the respective value of Col2 (10) and delete the
similar records of col2 if exist.

Col1Col2
 Y 10
 N 20
N  10

Need to delete record1 and record3.To delete the array records i'm using
array_remove but it says doesn't exist.

Version pgadmin4 .






Snippet :-

CREATE or REPLACE FUNCTION FUNC1
(
<< List of elements >>
) AS $$

DECLARE

TEST_CODES record1 ARRAY;
 TEMP_REF_VALUE VARCHAR(4000);

BEGIN
IS_VALID := 'S';

  SELECT ARRAY
   (SELECT ROW(Col1,Col2,COl3,Col4) ::record1
FROM table1  INTO TEST_CODES
IF array_length(TEST_CODES, 1) > 0 THEN
FOR indx IN array_lower(TEST_CODES, 1)..array_upper(TEST_CODES, 1) LOOP
 IF TEST_CODES[indx].COL1 = 'Y' THEN
TEMP_REF_VALUE:=TEST_CODES[indx].Col2;
TEST_CODES := array_remove(TEST_CODES,TEMP_REF_VALUE);
END IF;
   END Loop;
END IF;


-- 
Thanks & Regards,
Brahmeswara Rao J.

RE: How to remove elements from array .

[Charles Clavadetscher]

Hi

 

From: Brahmam Eswar [mailto:brahmam1...@gmail.com] 
Sent: Freitag, 6. Juli 2018 09:50
To: pgsql-general ; pgsql-hack...@postgresql.org
Subject: How to remove elements from array .

 

Hi ,

 

I tried to use array_remove to remove elements from an array but it's saying 
function doesn't exist . I'm able to use other array functions.

 

1) Capture the results with multiple columns into array . 

2)  if ay results exist then loop through an array to find out the record with 
col1='Y'

3)  If col1='Y' then get the respective value of Col2 (10) and delete the 
similar records of col2 if exist.

 

Col1Col2 

 Y 10

 N 20

N  10 

 

Need to delete record1 and record3.To delete the array records i'm using 
array_remove but it says doesn't exist.

 

Version pgadmin4 .

 

 

 

 

 

 

Snippet :-

 

CREATE or REPLACE FUNCTION FUNC1

(

  << List of elements >>

) AS $$

 

DECLARE



  TEST_CODES record1 ARRAY;

  TEMP_REF_VALUE VARCHAR(4000);



BEGIN

IS_VALID := 'S';



  SELECT ARRAY 

 (SELECT ROW(Col1,Col2,COl3,Col4) ::record1

  FROM table1  INTO TEST_CODES

  

  IF array_length(TEST_CODES, 1) > 0 THEN

  

  FOR indx IN array_lower(TEST_CODES, 
1)..array_upper(TEST_CODES, 1) LOOP

   IF TEST_CODES[indx].COL1 = 'Y' THEN

  TEMP_REF_VALUE:=TEST_CODES[indx].Col2;

  TEST_CODES := 
array_remove(TEST_CODES,TEMP_REF_VALUE);

  END IF;

 END Loop;

END IF; 




 

-- 

Thanks & Regards,
Brahmeswara Rao J.

 

I am not so in clear why you are using arrays in a function for that.

A solution with SQL would be:

 

CREATE TABLE tst (

  col1 text,

  col2 integer

);

 

INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);

 

SELECT * FROM tst;

 

col1 | col2

--+--

Y|   10

N|   20

N|   10

(3 rows)

 

DELETE FROM tst t

USING (SELECT * FROM tst

   WHERE col1 = 'Y') AS x

WHERE t.col2 = x.col2;

 

SELECT * FROM tst;

 

col1 | col2

--+--

N|   20

(1 row)

 

Regards

Charles

Re: How to remove elements from array .

[Pavel Stehule]

Hi

2018-07-06 9:49 GMT+02:00 Brahmam Eswar :

> Hi ,
>
> I tried to use array_remove to remove elements from an array but it's
> saying function doesn't exist . I'm able to use other array functions.
>
> 1) Capture the results with multiple columns into array .
> 2)  if ay results exist then loop through an array to find out the record
> with col1='Y'
> 3)  If col1='Y' then get the respective value of Col2 (10) and delete the
> similar records of col2 if exist.
>
> Col1Col2
>  Y 10
>  N 20
> N  10
>
> Need to delete record1 and record3.To delete the array records i'm using
> array_remove but it says doesn't exist.
>
> Version pgadmin4 .
>
>
>
>
In this case, unnesting can be solution

postgres=# select * from foo;
+++
| c1 | c2 |
+++
| t  | 10 |
| f  | 20 |
| f  | 20 |
+++
(3 rows)

postgres=# do $$
declare a foo[] default array(select foo from foo);
begin
  a := array(select (c1,c2)::foo from unnest(a) g(c1,c2) where g.c1 = true);
  raise notice 'a=%', a;
end;
$$;
NOTICE:  a={"(t,10)"}
DO

Regards

Pavel



>
>
>
> Snippet :-
>
> CREATE or REPLACE FUNCTION FUNC1
> (
> << List of elements >>
> ) AS $$
>
> DECLARE
>
> TEST_CODES record1 ARRAY;
>  TEMP_REF_VALUE VARCHAR(4000);
>
> BEGIN
> IS_VALID := 'S';
>
>   SELECT ARRAY
>(SELECT ROW(Col1,Col2,COl3,Col4) ::record1
> FROM table1  INTO TEST_CODES
> IF array_length(TEST_CODES, 1) > 0 THEN
> FOR indx IN array_lower(TEST_CODES, 1)..array_upper(TEST_CODES, 1) LOOP
>  IF TEST_CODES[indx].COL1 = 'Y' THEN
> TEMP_REF_VALUE:=TEST_CODES[indx].Col2;
> TEST_CODES := array_remove(TEST_CODES,TEMP_REF_VALUE);
> END IF;
>END Loop;
> END IF;
>
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.
>

Re: How to remove elements from array .

[Pavel Stehule]

Hi

2018-07-06 10:19 GMT+02:00 Charles Clavadetscher :

> Hi
>
>
>
> *From:* Brahmam Eswar [mailto:brahmam1...@gmail.com]
> *Sent:* Freitag, 6. Juli 2018 09:50
> *To:* pgsql-general ;
> pgsql-hack...@postgresql.org
> *Subject:* How to remove elements from array .
>
>
>
> Hi ,
>
>
>
> I tried to use array_remove to remove elements from an array but it's
> saying function doesn't exist . I'm able to use other array functions.
>
>
>
> 1) Capture the results with multiple columns into array .
>
> 2)  if ay results exist then loop through an array to find out the record
> with col1='Y'
>
> 3)  If col1='Y' then get the respective value of Col2 (10) and delete the
> similar records of col2 if exist.
>
>
>
> Col1Col2
>
>  Y 10
>
>  N 20
>
> N  10
>
>
>
> Need to delete record1 and record3.To delete the array records i'm using
> array_remove but it says doesn't exist.
>
>
>
> Version pgadmin4 .
>
>
>
>
>
>
>
>
>
>
>
>
>
> Snippet :-
>
>
>
> CREATE or REPLACE FUNCTION FUNC1
>
> (
>
>   << List of elements >>
>
> ) AS $$
>
>
>
> DECLARE
>
>
>
>   TEST_CODES record1 ARRAY;
>
>   TEMP_REF_VALUE VARCHAR(4000);
>
>
>
> BEGIN
>
> IS_VALID := 'S';
>
>
>
>   SELECT ARRAY
>
>  (SELECT ROW(Col1,Col2,COl3,Col4) ::record1
>
>   FROM table1  INTO TEST_CODES
>
>
>
>   IF array_length(TEST_CODES, 1) > 0 THEN
>
>
>
>   FOR indx IN array_lower(TEST_CODES,
> 1)..array_upper(TEST_CODES, 1) LOOP
>
>IF TEST_CODES[indx].COL1 = 'Y' THEN
>
>   TEMP_REF_VALUE:=TEST_CODES[indx].Col2;
>
>   TEST_CODES :=
> array_remove(TEST_CODES,TEMP_REF_VALUE);
>
>   END IF;
>
>  END Loop;
>
> END IF;
>
>
>
>
> --
>
> Thanks & Regards,
> Brahmeswara Rao J.
>
>
>
> I am not so in clear why you are using arrays in a function for that.
>
> A solution with SQL would be:
>

I don't understand to the request too.


>
>
> CREATE TABLE tst (
>
>   col1 text,
>
>   col2 integer
>
> );
>
>
>

Attention - temp table are expensive in Postgres (mainly for higher load),
so what can be done simply with arrays should be done with arrays.

Regards

Pavel


> INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);
>
>
>
> SELECT * FROM tst;
>
>
>
> col1 | col2
>
> --+--
>
> Y|   10
>
> N|   20
>
> N|   10
>
> (3 rows)
>
>
>
> DELETE FROM tst t
>
> USING (SELECT * FROM tst
>
>WHERE col1 = 'Y') AS x
>
> WHERE t.col2 = x.col2;
>
>
>
> SELECT * FROM tst;
>
>
>
> col1 | col2
>
> --+--
>
> N|   20
>
> (1 row)
>
>
>
> Regards
>
> Charles
>

RE: Unable to Connect to DB Instance (SOLVED)

[Boblitz John]

Good Morning,

First and foremost - thank you for the info.

While we still cannot determine that cause of the missing files (I see no 
indication of them being deleted)
I was able to use a combination of touch and the "blunderbuss" to fully 
recreate the two databases.  While it may not
always be useful - here is what I did

1) Run psql
2) Connect to db1
3) Run REINDEX SYSTEM db1 (I'm on 9.1 so options may vary)
4) Wait -> message some file not found - 
5) in second session touch the file (pay attention to the directory)
6) goto 3) 

This until no error, then same procedure for REINDEX DATABASE

There were files missing from base, global and pg_tblspc!

Rinse and repeat for the second DB.  This obviously took quite a bit of time - 
but in the end, I got it to work.

Again, this worked for me and I had a backup of the postgres instance itself, 
so I could take the chance.


Regards,

John Boblitz



> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Mittwoch, 4. Juli 2018 21:37
> To: Boblitz John 
> Cc: pgsql-gene...@postgresql.org
> Subject: Re: Unable to Connect to DB Instance
> 
> Boblitz John  writes:
> > Thanks - I get "pg_db_role_setting" as a response.
> 
> Hm ... not its index?  If the table itself is gone, it's surprising that you 
> can get
> through session startup.
> 
> > I have already attempted to reindex system but get:
> 
> I had in mind just reindexing the specific table you're having trouble with 
> ...
> but this:
> 
> > ERROR:  could not open file "base/11919/11680": No such file or
> > directory
> 
> shows that there's another table that also has a problem, and there may be
> more :-(.  I don't know what the odds are that you can get out of this
> completely.  I would NOT recommend "reindex system" as a blunderbuss
> solution.  You do not know how much is corrupted and there's a significant
> chance of making things worse by tromping over the whole database using
> catalogs of uncertain reliability.
> 
> Did you identify which table 11680 is?
> 
> In the case of pg_db_role_setting, a possible solution is to "touch" the
> missing file so it exists; it'll be empty, which means that you'll have lost 
> any
> ALTER DATABASE/ROLE SET settings, but that's better than not being able to
> dump at all.  (You might then need to REINDEX pg_db_role_setting to get its
> indexes in sync with it being empty.)
> 
> Whether an equally drastic answer is tolerable for your other missing
> table(s) depends on what they are...
> 
>   regards, tom lane

Re: How to remove elements from array .

[Brahmam Eswar]

Hi All,

My request is simple,

Just browse the results from a table into an array and loop through array
results to find out to unnecessary records and delete them based on certain
business conditions and print the rest of the records.

Below are the array results from table.

 {"(20310,https://google.com,AP,BR,,Y)","(20310,https://google.com
,AP,,,N)","(20311,https://google.com,AP,,,N)"}

Tried to apply the Unnest on array results but giving an error  at "https://
" .

Can we iterate over unnest records?

On Fri, Jul 6, 2018 at 1:56 PM, Pavel Stehule 
wrote:

> Hi
>
> 2018-07-06 10:19 GMT+02:00 Charles Clavadetscher <
> clavadetsc...@swisspug.org>:
>
>> Hi
>>
>>
>>
>> *From:* Brahmam Eswar [mailto:brahmam1...@gmail.com]
>> *Sent:* Freitag, 6. Juli 2018 09:50
>> *To:* pgsql-general ;
>> pgsql-hack...@postgresql.org
>> *Subject:* How to remove elements from array .
>>
>>
>>
>> Hi ,
>>
>>
>>
>> I tried to use array_remove to remove elements from an array but it's
>> saying function doesn't exist . I'm able to use other array functions.
>>
>>
>>
>> 1) Capture the results with multiple columns into array .
>>
>> 2)  if ay results exist then loop through an array to find out the record
>> with col1='Y'
>>
>> 3)  If col1='Y' then get the respective value of Col2 (10) and delete the
>> similar records of col2 if exist.
>>
>>
>>
>> Col1Col2
>>
>>  Y 10
>>
>>  N 20
>>
>> N  10
>>
>>
>>
>> Need to delete record1 and record3.To delete the array records i'm using
>> array_remove but it says doesn't exist.
>>
>>
>>
>> Version pgadmin4 .
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Snippet :-
>>
>>
>>
>> CREATE or REPLACE FUNCTION FUNC1
>>
>> (
>>
>>   << List of elements >>
>>
>> ) AS $$
>>
>>
>>
>> DECLARE
>>
>>
>>
>>   TEST_CODES record1 ARRAY;
>>
>>   TEMP_REF_VALUE VARCHAR(4000);
>>
>>
>>
>> BEGIN
>>
>> IS_VALID := 'S';
>>
>>
>>
>>   SELECT ARRAY
>>
>>  (SELECT ROW(Col1,Col2,COl3,Col4) ::record1
>>
>>   FROM table1  INTO TEST_CODES
>>
>>
>>
>>   IF array_length(TEST_CODES, 1) > 0 THEN
>>
>>
>>
>>   FOR indx IN array_lower(TEST_CODES,
>> 1)..array_upper(TEST_CODES, 1) LOOP
>>
>>IF TEST_CODES[indx].COL1 = 'Y' THEN
>>
>>   TEMP_REF_VALUE:=TEST_CODES[indx].Col2;
>>
>>   TEST_CODES :=
>> array_remove(TEST_CODES,TEMP_REF_VALUE);
>>
>>   END IF;
>>
>>  END Loop;
>>
>> END IF;
>>
>>
>>
>>
>> --
>>
>> Thanks & Regards,
>> Brahmeswara Rao J.
>>
>>
>>
>> I am not so in clear why you are using arrays in a function for that.
>>
>> A solution with SQL would be:
>>
>
> I don't understand to the request too.
>
>
>>
>>
>> CREATE TABLE tst (
>>
>>   col1 text,
>>
>>   col2 integer
>>
>> );
>>
>>
>>
>
> Attention - temp table are expensive in Postgres (mainly for higher load),
> so what can be done simply with arrays should be done with arrays.
>
> Regards
>
> Pavel
>
>
>> INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);
>>
>>
>>
>> SELECT * FROM tst;
>>
>>
>>
>> col1 | col2
>>
>> --+--
>>
>> Y|   10
>>
>> N|   20
>>
>> N|   10
>>
>> (3 rows)
>>
>>
>>
>> DELETE FROM tst t
>>
>> USING (SELECT * FROM tst
>>
>>WHERE col1 = 'Y') AS x
>>
>> WHERE t.col2 = x.col2;
>>
>>
>>
>> SELECT * FROM tst;
>>
>>
>>
>> col1 | col2
>>
>> --+--
>>
>> N|   20
>>
>> (1 row)
>>
>>
>>
>> Regards
>>
>> Charles
>>
>
>


-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: How to remove elements from array .

[Pavel Stehule]

2018-07-06 11:45 GMT+02:00 Brahmam Eswar :

> Hi All,
>
> My request is simple,
>
> Just browse the results from a table into an array and loop through array
> results to find out to unnecessary records and delete them based on certain
> business conditions and print the rest of the records.
>
> Below are the array results from table.
>
>  {"(20310,https://google.com,AP,BR,,Y)","(20310,https://google.com
> ,AP,,,N)","(20311,https://google.com,AP,,,N)"}
>
> Tried to apply the Unnest on array results but giving an error  at
> "https://"; .
>
> Can we iterate over unnest records?
>

sure - it is relation like any other.

Can you send test case?



> On Fri, Jul 6, 2018 at 1:56 PM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>> 2018-07-06 10:19 GMT+02:00 Charles Clavadetscher <
>> clavadetsc...@swisspug.org>:
>>
>>> Hi
>>>
>>>
>>>
>>> *From:* Brahmam Eswar [mailto:brahmam1...@gmail.com]
>>> *Sent:* Freitag, 6. Juli 2018 09:50
>>> *To:* pgsql-general ;
>>> pgsql-hack...@postgresql.org
>>> *Subject:* How to remove elements from array .
>>>
>>>
>>>
>>> Hi ,
>>>
>>>
>>>
>>> I tried to use array_remove to remove elements from an array but it's
>>> saying function doesn't exist . I'm able to use other array functions.
>>>
>>>
>>>
>>> 1) Capture the results with multiple columns into array .
>>>
>>> 2)  if ay results exist then loop through an array to find out the
>>> record with col1='Y'
>>>
>>> 3)  If col1='Y' then get the respective value of Col2 (10) and delete
>>> the similar records of col2 if exist.
>>>
>>>
>>>
>>> Col1Col2
>>>
>>>  Y 10
>>>
>>>  N 20
>>>
>>> N  10
>>>
>>>
>>>
>>> Need to delete record1 and record3.To delete the array records i'm using
>>> array_remove but it says doesn't exist.
>>>
>>>
>>>
>>> Version pgadmin4 .
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Snippet :-
>>>
>>>
>>>
>>> CREATE or REPLACE FUNCTION FUNC1
>>>
>>> (
>>>
>>>   << List of elements >>
>>>
>>> ) AS $$
>>>
>>>
>>>
>>> DECLARE
>>>
>>>
>>>
>>>   TEST_CODES record1 ARRAY;
>>>
>>>   TEMP_REF_VALUE VARCHAR(4000);
>>>
>>>
>>>
>>> BEGIN
>>>
>>> IS_VALID := 'S';
>>>
>>>
>>>
>>>   SELECT ARRAY
>>>
>>>  (SELECT ROW(Col1,Col2,COl3,Col4) ::record1
>>>
>>>   FROM table1  INTO TEST_CODES
>>>
>>>
>>>
>>>   IF array_length(TEST_CODES, 1) > 0 THEN
>>>
>>>
>>>
>>>   FOR indx IN array_lower(TEST_CODES,
>>> 1)..array_upper(TEST_CODES, 1) LOOP
>>>
>>>IF TEST_CODES[indx].COL1 = 'Y' THEN
>>>
>>>   TEMP_REF_VALUE:=TEST_CODES[indx].Col2;
>>>
>>>   TEST_CODES :=
>>> array_remove(TEST_CODES,TEMP_REF_VALUE);
>>>
>>>   END IF;
>>>
>>>  END Loop;
>>>
>>> END IF;
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Thanks & Regards,
>>> Brahmeswara Rao J.
>>>
>>>
>>>
>>> I am not so in clear why you are using arrays in a function for that.
>>>
>>> A solution with SQL would be:
>>>
>>
>> I don't understand to the request too.
>>
>>
>>>
>>>
>>> CREATE TABLE tst (
>>>
>>>   col1 text,
>>>
>>>   col2 integer
>>>
>>> );
>>>
>>>
>>>
>>
>> Attention - temp table are expensive in Postgres (mainly for higher
>> load), so what can be done simply with arrays should be done with arrays.
>>
>> Regards
>>
>> Pavel
>>
>>
>>> INSERT INTO tst VALUES ('Y', 10), ('N', 20), ('N', 10);
>>>
>>>
>>>
>>> SELECT * FROM tst;
>>>
>>>
>>>
>>> col1 | col2
>>>
>>> --+--
>>>
>>> Y|   10
>>>
>>> N|   20
>>>
>>> N|   10
>>>
>>> (3 rows)
>>>
>>>
>>>
>>> DELETE FROM tst t
>>>
>>> USING (SELECT * FROM tst
>>>
>>>WHERE col1 = 'Y') AS x
>>>
>>> WHERE t.col2 = x.col2;
>>>
>>>
>>>
>>> SELECT * FROM tst;
>>>
>>>
>>>
>>> col1 | col2
>>>
>>> --+--
>>>
>>> N|   20
>>>
>>> (1 row)
>>>
>>>
>>>
>>> Regards
>>>
>>> Charles
>>>
>>
>>
>
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.
>

Re: Split daterange into sub periods

[Alban Hertroys]

On 5 July 2018 at 16:16, hmidi slim  wrote:
> In fact I'm trying to split a period in sub periods. Following this example
> :
> If I have a period =[2018-01-01, 2018-01-31] and two other periods
> [2018-01-04, 2018-01-06] and [2018-01-08, 2018-01-08].
> If I split the base period '[2018-01-01, 2018-01-31]' by the other two
> periods '[2018-01-04, 2018-01-06]' and '[2018-01-08, 2018-01-08]' I will got
> such a result:
> [2018-01-01, 2018-01-03]
> [2018-01-07, 2018-01-07]
> [2018-01-09, 2018-01-31].

What about a recursive CTE?

What about a recursive CTE?

with recursive
period as (select '[2018-01-01, 2018-01-31]'::daterange as range)
,exclude as (
select range
  from (values
  ('[2018-01-01, 2018-01-03]'::daterange)
,('[2018-01-07, 2018-01-07]'::daterange)
,('[2018-01-09, 2018-01-31]'::daterange)
  ) v(range)
)
,available (lo, hi, exclude, available) as (
select
lower(p.range), upper(p.range)
,x.range
,p.range - x.range
  from period p,exclude x
  where not exists (
  select 1
  from exclude x2
 where lower(x2.range) < lower(x.range)
   and lower(x2.range) >= lower(p.range)
  )

 union all

 select
upper(x.range), hi
,x.range
,daterange(upper(x.range), hi)
  from available a, exclude x
 where a.lo <= a.hi
   and lower(x.range) > lo
   and not exists (
select 1
  from exclude x2
 where lower(x2.range) < lower(x.range)
   and lower(x2.range) > lo
 )
)
select * from available;

 lo | hi | exclude |available
++-+-
 2018-01-01 | 2018-02-01 | [2018-01-01,2018-01-04) | [2018-01-04,2018-02-01)
 2018-01-08 | 2018-02-01 | [2018-01-07,2018-01-08) | [2018-01-08,2018-02-01)
 2018-02-01 | 2018-02-01 | [2018-01-09,2018-02-01) | empty
(3 rows)

It can probably be optimized a bit, I haven't played with ranges much yet.

Regards,
Alban Hertroys
-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

How to create StartsWith function for char(n) type with ending space

[Andrus]

Hi!

I need to create startswith function which returns true if  char(n) database
column starts with some characters which
may can contain space at end.
Spaces should treated like other characters.

Using sample data below

startswith( test, 'A')
startswith( test, 'A  ')
StartsWith(test, rpad('A',19) )

shuld return true

but

startswith( test, RPAD( 'A', 20))  should return false  since there is extra
space in end of check string

Database contains test column which has char(20) type column and this cannot
changed.

I tried code below but it returns false.

How to fix this so that it returns true?
Using Postgres starting from 9.1

Andrus.

CREATE or replace FUNCTION public.likeescape( str text )
--
https://stackoverflow.com/questions/10153440/how-to-escape-string-while-matching-pattern-in-postgresql
RETURNS text AS $$
SELECT replace(replace(replace($1,'^','^^'),'%','^%'),'_','^_') ;
$$ LANGUAGE sql IMMUTABLE;

CREATE or replace FUNCTION public.StartWith( cstr text, algusosa text )
RETURNS bool AS $$
SELECT $2 is null or $1 like likeescape($2) ||'%' ESCAPE '^' ;
$$ LANGUAGE sql IMMUTABLE;

create temp table test ( test char(20) ) on commit drop;
insert into test values ('A' );

select StartWith(test, 'A ' ) from test

posted also in

https://stackoverflow.com/questions/51206529/how-to-create-startswith-function-for-charn-type-with-ending-space 

Role problem in Windows

[Moreno Andreo]

Hi,
Running 9.1 on Windows 10, upgrading to 10 with pg_upgrade.

"Once upon a time" there was a bug in our automatic role creation 
procedure that did not mask vowels with accent (used in Italian 
language), like "ò, è" and the result was a role with an empty name.
We are now upgrading to 10, and pg_dumpall exits complaining with this 
role, showing its name (with mis-encoded UTF-8 accented vowel) as an 
invalid utf-8 character.


Trying to get rid of the role, that can't be deleted with a drop role 
because of the empty name, I did

delete from pg_authid where oid = 

Role disappeared from role list.

At the next execution of the pg_upgrade it complains that role "" 
does not exist while dumping a trigger function. I tried remove the 
privilege from function ACL, but "role n does not exists".


Is there a way to recreate the deleted role, either as a dummy, so I can 
finish upgrade?

Is there another way to bypass the problem?

Any help would be appreciated.

Cheers,
Moreno.-

Re: Role problem in Windows

[Melvin Davidson]

On Fri, Jul 6, 2018 at 10:01 AM, Moreno Andreo 
wrote:

> Hi,
> Running 9.1 on Windows 10, upgrading to 10 with pg_upgrade.
>
> "Once upon a time" there was a bug in our automatic role creation
> procedure that did not mask vowels with accent (used in Italian language),
> like "ò, è" and the result was a role with an empty name.
> We are now upgrading to 10, and pg_dumpall exits complaining with this
> role, showing its name (with mis-encoded UTF-8 accented vowel) as an
> invalid utf-8 character.
>
> Trying to get rid of the role, that can't be deleted with a drop role
> because of the empty name, I did
> delete from pg_authid where oid = 
>
> Role disappeared from role list.
>
> At the next execution of the pg_upgrade it complains that role "" does
> not exist while dumping a trigger function. I tried remove the privilege
> from function ACL, but "role n does not exists".
>
> Is there a way to recreate the deleted role, either as a dummy, so I can
> finish upgrade?
> Is there another way to bypass the problem?
>
> Any help would be appreciated.
>
> Cheers,
> Moreno.-
>
>
>
>Is there a way to recreate the deleted role, either as a dummy, so I can
finish upgrade?
I can't really suggest how to recreate the dummy role, but I do have an
alternate solution.
Most probably pg_dump is complaining that role 'xxx' owns some tables. So
you can use the
attached script and add 'AND a.rolname = 'xxx' to the WHERE clause.
Then as a superuser you can use ALTER TABLE xyz OWNER TO new_owner for each
table found.

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
SELECT c.relname as table, 
   a.rolname as owner,
   c.relacl as permits
  FROM pg_class c
  JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE relname NOT LIKE 'pg_%' 
  AND relname NOT LIKE 'information%' 
  AND relname NOT LIKE 'sql_%' 
  AND relkind = 'r'
  --AND position('cp' in ARRAY_TO_STRING(c.relacl, '') )> 0
ORDER BY relname;

correcting tablespaces inside data folder

[Duarte Carreira]

Hello.

Yes I'm one of those guys who only recently realized the mess of having
tablespaces inside the data directory... now I want to use pg_upgrade and
it will make things even worse...

Does anyone have a best approach to this problem? Fastest/safest?

pg 9.3.x on win

Thanks,
Duarte

Re: Role problem in Windows

[Moreno Andreo]

  
  
Il 06/07/2018 17:34, Melvin Davidson ha
  scritto:


  

  On Fri, Jul 6, 2018 at 10:01 AM,
Moreno Andreo 
wrote:
Hi,
  Running 9.1 on Windows 10, upgrading to 10 with
  pg_upgrade.
  
  "Once upon a time" there was a bug in our automatic role
  creation procedure that did not mask vowels with accent
  (used in Italian language), like "ò, è" and the result was
  a role with an empty name.
  We are now upgrading to 10, and pg_dumpall exits
  complaining with this role, showing its name (with
  mis-encoded UTF-8 accented vowel) as an invalid utf-8
  character.
  
  Trying to get rid of the role, that can't be deleted with
  a drop role because of the empty name, I did
  delete from pg_authid where oid = 
  
  Role disappeared from role list.
  
  At the next execution of the pg_upgrade it complains that
  role "" does not exist while dumping a trigger
  function. I tried remove the privilege from function ACL,
  but "role n does not exists".
  
  Is there a way to recreate the deleted role, either as a
  dummy, so I can finish upgrade?
  Is there another way to bypass the problem?
  
  Any help would be appreciated.
  
  Cheers,
  Moreno.-
  
  

  
  
  >Is there a way to recreate the deleted role, either as a
  dummy, so I can finish upgrade?
  

I can't really suggest how to recreate
  the dummy role, but I do have an alternate solution.
Most probably pg_dump is complaining
  that role 'xxx' owns some tables.
  

IIRC the complain was about "role  does not exist"
In the meantime I was able to pg_dump single databases (5 in total,
one of them complaining about the role not existing but dumped with
all data in its place) and, with my surprise (since I was convinced
that pg_dump was working inside a single transaction) I found all
roles (all but the "failing" one) at their place in the new server.
So, lesson learned: don't mess with system catalogs before RTFM
:-))


  
 So you can use the 

attached script and add 'AND a.rolname
  = 'xxx' to the WHERE clause.
Then as a superuser you can use ALTER
  TABLE xyz OWNER TO new_owner for each table found.

  

I'll keep it, so if something similar happens maybe it can come in
hand.

Thanks for your time
Moreno.-
  

Re: correcting tablespaces inside data folder

[Magnus Hagander]

On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira 
wrote:

> Hello.
>
> Yes I'm one of those guys who only recently realized the mess of having
> tablespaces inside the data directory... now I want to use pg_upgrade and
> it will make things even worse...
>
> Does anyone have a best approach to this problem? Fastest/safest?
>
> pg 9.3.x on win
>
>
If you can afford to shut the server down, the easiest is to shut it down,
move the tablespaces (with mv, and as long as you stay within the partition
it should be almost instant), update the symlinks to point to the new
location, and start it up again.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: correcting tablespaces inside data folder

[Duarte Carreira]

Magnus,
You mean changing the symlinks inside pg_tblspc?


On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander  wrote:

> On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira 
> wrote:
>
>> Hello.
>>
>> Yes I'm one of those guys who only recently realized the mess of having
>> tablespaces inside the data directory... now I want to use pg_upgrade and
>> it will make things even worse...
>>
>> Does anyone have a best approach to this problem? Fastest/safest?
>>
>> pg 9.3.x on win
>>
>>
> If you can afford to shut the server down, the easiest is to shut it down,
> move the tablespaces (with mv, and as long as you stay within the partition
> it should be almost instant), update the symlinks to point to the new
> location, and start it up again.
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/ 
>  Work: https://www.redpill-linpro.com/ 
>

Re: correcting tablespaces inside data folder

[Magnus Hagander]

(please don't top-post. It makes it hard to follow discussions)

On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira 
wrote:

> Magnus,
> You mean changing the symlinks inside pg_tblspc?
>
>
Yes. As long as the server is shut down, you can modify those symlinks.



>
> On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander 
> wrote:
>
>> On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira 
>> wrote:
>>
>>> Hello.
>>>
>>> Yes I'm one of those guys who only recently realized the mess of having
>>> tablespaces inside the data directory... now I want to use pg_upgrade and
>>> it will make things even worse...
>>>
>>> Does anyone have a best approach to this problem? Fastest/safest?
>>>
>>> pg 9.3.x on win
>>>
>>>
>> If you can afford to shut the server down, the easiest is to shut it
>> down, move the tablespaces (with mv, and as long as you stay within the
>> partition it should be almost instant), update the symlinks to point to the
>> new location, and start it up again.
>>
>> --
>>  Magnus Hagander
>>  Me: https://www.hagander.net/ 
>>  Work: https://www.redpill-linpro.com/ 
>>
>


-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: correcting tablespaces inside data folder

[Duarte Carreira]

On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander  wrote:

> (please don't top-post. It makes it hard to follow discussions)
>
> On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira 
> wrote:
>
>> Magnus,
>> You mean changing the symlinks inside pg_tblspc?
>>
>>
> Yes. As long as the server is shut down, you can modify those symlinks.
>

Ok, I'll try on my laptop and see how it goes.
Thanks.


>
>
>
>>
>> On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander 
>> wrote:
>>
>>> On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira 
>>> wrote:
>>>
 Hello.

 Yes I'm one of those guys who only recently realized the mess of having
 tablespaces inside the data directory... now I want to use pg_upgrade and
 it will make things even worse...

 Does anyone have a best approach to this problem? Fastest/safest?

 pg 9.3.x on win


>>> If you can afford to shut the server down, the easiest is to shut it
>>> down, move the tablespaces (with mv, and as long as you stay within the
>>> partition it should be almost instant), update the symlinks to point to the
>>> new location, and start it up again.
>>>
>>> --
>>>  Magnus Hagander
>>>  Me: https://www.hagander.net/ 
>>>  Work: https://www.redpill-linpro.com/ 
>>>
>>
>
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/ 
>  Work: https://www.redpill-linpro.com/ 
>

Re: User-defined function with anyrange[]

[Tom Lane]

Paul A Jungwirth  writes:
> I want to make an aggregate function range_agg(anyrange) that returns
> anyrange[]. But when I try to define it, Postgres tells me it doesn't
> know what an anyrange[] is. I get this error:

Yeah, there is no such thing as anyrange[], nor arrays over any other
pseudo-type either.

> I also tried taking an anyrange and returning an anyarray, which does
> let me define the function, but upon calling it I get an error. For
> example:

> paul=# CREATE OR REPLACE FUNCTION range_agg4(anyrange)
> RETURNS anyarray
> AS $$
> BEGIN
>   RETURN ARRAY[$1];
> END;
> $$
> LANGUAGE plpgsql;

The trouble with this is that anyarray and anyrange are both implicitly
related to the "anyelement" pseudo-type, which represents their element
type.  So if anyrange is associated with daterange in a particular
function call, then anyelement is associated with date, and then anyarray
is associated with date[] not daterange[].

I don't think there's any way to get what you want using a single
polymorphic function, at least not without some sort of extension to
the polymorphism rules.  However, you can use overloading to define
several functions of the same name, and just write out one for each
range type you actually need this functionality for.  I haven't really
seen applications that need so many range types that this'd be
intolerable.

regards, tom lane