Re: How to reply to an existing bug?

[Thomas Kellerer]

Erwin Brandstetter schrieb am 28.05.2018 um 02:00:
> I found an existing bug report and have something to add to it.
> 
> What's the best way to reply to it? Just using a browser, with no newsreader 
> installed.
> 
> This one:
> https://www.postgresql.org/message-id/flat/20170925084522.1442.32786%40wrigleys.postgresql.org#20170925084522.1442.32...@wrigleys.postgresql.org
> 
> I want to add a related question on stackoverflow.com 
> 
> https://stackoverflow.com/questions/50553558/postgresql-add-serial-column-if-not-exists-still-creating-sequences/50557433
> 
> And that the same bug has been carried over to IDENTITY columns.
> 
> https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ac978b262727fa842aa1a71349a23767

I think that warrants a new bug report (maybe with a reference to the previous 
one). 

Re: Help with SQL

[Rob Sargent]

> On May 27, 2018, at 6:42 PM, anand086  wrote:
> 
> Hi,
> 
> I have a requirement of writing plpgsql function to create partial indexes
> on child tables if it exists on parent table. The function will have
> schemname, childtablename, tableowner as input.
> 
> I am using the below code to identify the indexname and index definition
> from parent table --
> 
> ```
> with idx as 
>(select indexrelid::regclass indexname, indisunique,
> indisprimary from pg_index where indrelid in 
>(select oid from pg_class where relname in (select
> tablename from pg_indexes where tablename='test_booking')
>) 
>   and indpred is not null
>) 
>select idxs.tablename, idxs.indexname, idxs.indexdef,
> idx.indisunique from pg_indexes as idxs join idx on
> idxs.indexname=split_part(idx.indexname::text , '.' ,2)
> ```
> 
> Suppose the indexdef is on parent table is --
> 
> CREATE UNIQUE INDEX uniq_test_booking_1 ON demo.test_booking USING btree
> (col1,col2 ,col3, col4,col5, col6) WHERE ((col4 IS NOT NULL) AND (col6 IS
> NOT NULL))
> 
> Now, what I am trying to achieve is to create and execute the below sql,
> wherein I replace the  indexname with uniq__
> and tablename with the childtablename part of function input.
> 
> CREATE UNIQUE INDEX uniq_test_booking_20180527_gdhsd ON
> demo.test_booking_20180527 USING btree (col1,col2 ,col3, col4,col5, col6)
> WHERE ((col4 IS NOT NULL) AND (col6 IS NOT NULL))
> 
> Using substring I am trying to break the SQL statement in 2 and then later
> concatenate it.
> The first part is substring(idxrec.indexdef from 0 for 21);  --> output is
> "create unique index " statement.
> and for the 2nd part, starting for USING until the end. But I am unable to
> get the 2nd part of sql.
> 
> 
> ```
>if idxrec.indisunique='t' then
>   SELECT substr(concat(md5(random()::text), md5(random()::text)), 
> 0,
> 7) into var;
>   idxname:='uniq_'||idxrec.tablename||'_'||var;
>   raise notice 'Index name will be %', idxname;
>   createStmts1:=substring(idxrec.indexdef from 0 for 21);  --> 
> gives me
> the "create unique index"
>   raise notice 'String1 %', createStmts1;
>   createStmts2:=
>   raise notice 'String2 %', createStmts2;
> ```
> 
> Is this the correct way? Any better suggestion? 
> 
> How can I achieve this?
> 
> 
Is using a stored procedure a required?  To do so would require “dynamic sql” 
but imho that’s not the best use of stored procedures.  Perhaps you could use 
your “with ids” sql to get the list of required indices with names, columns 
etc, place those values (sed, awk, perl, python, even sql) into versions of 
your create index code and stick those generated files in hopefully the same 
source code repository which has the definition of the parent and children 
tables.  They will likely all need maintenace at the same time.

Help with SQL

[anand086]

Hi,

I have a requirement of writing plpgsql function to create partial indexes
on child tables if it exists on parent table. The function will have
schemname, childtablename, tableowner as input.

I am using the below code to identify the indexname and index definition
from parent table --

```
with idx as 
 (select indexrelid::regclass indexname, indisunique,
indisprimary from pg_index where indrelid in 
 (select oid from pg_class where relname in (select
tablename from pg_indexes where tablename='test_booking')
 ) 
and indpred is not null
 ) 
select idxs.tablename, idxs.indexname, idxs.indexdef,
idx.indisunique from pg_indexes as idxs join idx on
idxs.indexname=split_part(idx.indexname::text , '.' ,2)
```

Suppose the indexdef is on parent table is --

CREATE UNIQUE INDEX uniq_test_booking_1 ON demo.test_booking USING btree
(col1,col2 ,col3, col4,col5, col6) WHERE ((col4 IS NOT NULL) AND (col6 IS
NOT NULL))

Now, what I am trying to achieve is to create and execute the below sql,
wherein I replace the  indexname with uniq__
and tablename with the childtablename part of function input.

CREATE UNIQUE INDEX uniq_test_booking_20180527_gdhsd ON
demo.test_booking_20180527 USING btree (col1,col2 ,col3, col4,col5, col6)
WHERE ((col4 IS NOT NULL) AND (col6 IS NOT NULL))

Using substring I am trying to break the SQL statement in 2 and then later
concatenate it.
The first part is substring(idxrec.indexdef from 0 for 21);  --> output is
"create unique index " statement.
and for the 2nd part, starting for USING until the end. But I am unable to
get the 2nd part of sql.


```
if idxrec.indisunique='t' then
SELECT substr(concat(md5(random()::text), md5(random()::text)), 
0,
7) into var;
idxname:='uniq_'||idxrec.tablename||'_'||var;
raise notice 'Index name will be %', idxname;
createStmts1:=substring(idxrec.indexdef from 0 for 21);  --> 
gives me
the "create unique index"
raise notice 'String1 %', createStmts1;
createStmts2:=
raise notice 'String2 %', createStmts2;
```

Is this the correct way? Any better suggestion? 

How can I achieve this?




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

How to reply to an existing bug?

[Erwin Brandstetter]

I found an existing bug report and have something to add to it.

What's the best way to reply to it? Just using a browser, with no
newsreader installed.

This one:
https://www.postgresql.org/message-id/flat/20170925084522.1442.32786%40wrigleys.postgresql.org#20170925084522.1442.32...@wrigleys.postgresql.org

I want to add a related question on stackoverflow.com
https://stackoverflow.com/questions/50553558/postgresql-add-serial-column-if-not-exists-still-creating-sequences/50557433

And that the same bug has been carried over to IDENTITY columns.

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ac978b262727fa842aa1a71349a23767

Regards
Erwin

Re: case statement within insert

[tango ward]

On Fri, May 25, 2018 at 10:19 PM, Adrian Klaver 
wrote:

> On 05/25/2018 07:05 AM, Adrian Klaver wrote:
>
>> On 05/25/2018 06:52 AM, Adrian Klaver wrote:
>>
>>> On 05/25/2018 02:04 AM, tango ward wrote:
>>>


 I want to insert data from mysql into a table in postgresql. I want to
 check when the subjectcode contains PE or NSTP so I can assign True or
 False to another column in destination DB.


 # Source data:

   # Source data: MySQL
 curr_msql.execute(''' SELECT code, subjectname
FROM test_subj ''')

 # Destination
 for row in curr_msql:
  curr_psql.execute(''' INSERT INTO subs (
  created, modified,
  subjcode,
 subjname,
  is_pe_or_nstp)

VALUES (current_timestamp,
 current_timestamp,
%s, %s,
CASE
  WHEN code like '%%PE%%' or code like '%%NSTP%%'

>>>
>>> Shouldn't the above be?:
>>>
>>> subjcode like '%%PE%%' or subjcode like '%%NSTP%%'
>>>
>>
>> Turns out that won't work as you cannot refer to a column in the CASE:
>>
>> cur.execute("insert into  cell_per(category, cell_per, season,
>> plant_type, short_category)  values('herb test', 1, 'annual', CASE WHEN
>> category like '%%herb%%' THEN 'herb' ELSE 'flower' END, 'ht' )")
>>
>> ProgrammingError: column "category" does not exist
>> LINE 1: ...gory)  values('herb test', 1, 'annual', CASE WHEN category l...
>>   ^
>> HINT:  There is a column named "category" in table "cell_per", but it
>> cannot be referenced from this part of the query.
>>
>>
>> This works:
>>
>> cur.execute("insert into  cell_per(category, cell_per, season,
>> plant_type, short_category)  values(%s, 1, 'annual', CASE WHEN %s like
>> '%%herb%%' THEN 'herb' ELSE 'flower' END, 'ht' )", ('herb test', 'herb
>> test'))
>>
>>
>> So change code to row['code']?
>>
>
> Insufficient caffeine.
>
> ...
> WHEN %s like '%%PE%%' or %s like '%%NSTP%%'
>
> ...
> ,  (row['code'], row['subjectname'], row['code'], row['code'])
>
> FYI this is why I like the named parameters then the above could be
> shortened to:
>
> {'code': row['code'], 'subjectname': row['subjectname']}
>
> and you get clearer query code:
>
> VALUES (current_timestamp, current_timestamp,
>   %(code)s, %(subjectname)s,
>   CASE
> WHEN %(code)s like '%%PE%%' or %(code)s like '%%NSTP%%'
>
> THEN True
> ELSE False
> END)
>
>
>>
>>
>>
>>>  THEN True
  ELSE False
  END) ''', (row['code'], row['subjectname']))

 I am getting TypeError: not all arguments converted during string
 formatting.

 Any advice pls?







>>>
>>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Using %s works. I also learned that if I just use '%PE%' or '%NSTP%', the
LIKE expression will treat them as placeholder. Solution is to double the
percent signs.


Thanks a lot guys! I love you all.

Re: existence of a savepoint?

[David G. Johnston]

On Sunday, May 27, 2018, Stuart McGraw  wrote:

> Is there some way to to test if a savepoint of a given name
> exists?  Or better yet, the number of stacked savepoints of
> that name?
>

A scan of the documentation doesn't show any commands or functions that
would provide this information.

David J.

existence of a savepoint?

[Stuart McGraw]

Is there some way to to test if a savepoint of a given name
exists?  Or better yet, the number of stacked savepoints of
that name?

Re: Syndicating PostgreSQL mailing list to Discourse

[Peter Geoghegan]

On Sun, May 27, 2018 at 6:13 AM, Stephen Frost  wrote:
> I'm curious what would be different here from what our archives provide.
> We could certainly have a single "all lists" archive page but that seems
> more likely to be just completely confusing than actually useful at all.

Any replacement to our own archives will need to provide access to
mail from over 20 years ago to be in any way usable. It's not uncommon
to have to go back that far.

Personally, I don't buy the idea that the need to use a mailing list
rather than a web forum is a notable obstacle for new contributors.
PGLister seems pretty slick to me. It has eliminated all of the
frustrations that I had. Maybe we need to do a better job when it
comes to communicating what the benefits of a mailing list are,
though.

There are real, practical reasons to prefer a mailing list; that
preference isn't just due to ingrained habit. I'm pleased that there
has been a drive to modernize some of the community's infrastructure
in recent years, but only because those changes turned out to be
unalloyed improvements (at least in my view). Besides, while mailing
lists may seem antiquated to a lot of people, aren't web forums almost
as antiquated? Sites like Stack Overflow are very clearly not designed
to work as discussion forums. They do not allow subjective questions,
and it's common for moderators to swiftly delete new threads. Stack
Overflow is popular because it provides the fastest possible access to
a crowdsourced answer, without requiring or even encouraging
participation.

--
Peter Geoghegan

Re: UPDATE from CTE syntax error

[Alexander Farber]

On Sat, May 26, 2018 at 6:37 PM, Adrian Klaver 
wrote:
>
>
> https://www.postgresql.org/docs/10/static/sql-update.html
> "column_name
>
> The name of a column in the table named by table_name. The column name
> can be qualified with a subfield name or array subscript, if needed. Do not
> include the table's name in the specification of a target column — for
> example, UPDATE table_name SET table_name.col = 1 is invalid.
> "
>
> So it should be:
>
> SET letters = el.letters
>
>
Thank you Adrian, this has worked

Re: Syndicating PostgreSQL mailing list to Discourse

[Stephen Frost]

Greetings,

* Erlend Sogge Heggen (e.so...@gmail.com) wrote:
> Since it's read-only it would only be used for things like:
> 
>- Fast search with advanced filters (Discourse puts PostgreSQL full text
>search to good use!)

While it might not be the case for other projects, we actually do use
PostgreSQL for our archives, including having FTS..

>- All active lists aggregated into one feed, but also available as
>individual categories that can be tracked/watched.

I'm curious what would be different here from what our archives provide.
We could certainly have a single "all lists" archive page but that seems
more likely to be just completely confusing than actually useful at all.

>- Single-page topics, easier to read on mobile for the young'uns.

We've been working to make the mobile experience better for our
archives and we'd love to make the experience better for everyone, so
please make suggestions or even send in patches; all of the code running
the archives is open.

> If our mirror archive gets enough traction we'd like to make it possible to
> sign up to the forum and seamlessly interact with the mailing list,
> provided it's something the community actually wants of course. We're doing
> a similar experiment with the ruby-talk mailing list, which you can see
> being tested  at https://rubytalk.org/.

How do you plan to address the issues around DMARC/SPF/DKIM..?  If the
answer is "we don't plan to do anything" or "we are going to send email
from our own domain" then you're certainly not making it "seamless" for
us or for the user.  If you have a different solution, then I think
we're certainly curious to hear it, as it would be nice to change the
-bugs and -docs forms back to using the end user's email address when
sending to the list instead of having to have a 'noreply' address be
used. 

Please do *not* start causing us trouble by sending what looks like
forged email through our mailing lists and causing bounces for us to
deal with.  We have more than enough of that already and if it becomes
an issue then we'll have to block, bounce, and/or unsubscribe whatever
is causing it.

Thanks!

Stephen


signature.asc
Description: PGP signature