Re: Return Multiple Rows from Store Function

Hi 2018-07-27 11:24 GMT+02:00 Brahmam Eswar : > Hi , > > Returning multiple rows from store functions using "RETURNS TABLE" and > RETURN QUERY. The results set may have more than 50k records. Does it give > any performance issues related to memory? if yes how to avoid it > > This command uses

Re: User documentation vs Official Docs

Hi 2018-08-10 21:00 GMT+02:00 Bruce Momjian : > On Fri, Jul 20, 2018 at 05:31:40PM -0700, Adrian Klaver wrote: > > JD sit down, I am going to agree with you:) The documentation as it > stands > > is very good, though it requires some fore knowledge to successfully > > navigate. On pages with a

Re: check_function_bodies not doing much

2018-08-08 0:02 GMT+02:00 Marcelo Lacerda : > That's a whole different nightmare that I'm expecting. > > > "Yep I double-checked all my functions to see if any would break if I > change this field mytable.a into 2 fields mytable.a1 and mytable.a2 and > everything is ok." > > *1 month later* >

Re: check_function_bodies not doing much

Hi 2018-08-07 21:17 GMT+02:00 Marcelo Lacerda : > I was trying to get postgres to warn me that I'm referencing a table that > it doesn't exists inside a function so I was told on the IRC to check the > setting "check_function_bodies", however when I use it in a plpgsql > function it doesn't

Re: using a plpgsql function argument as a table column.

Hi 2018-08-29 7:09 GMT+02:00 Shaun Savage : > I have a table with many years as columns. y1976, y2077, .. , y2019,y2020 > I want to dynamically return a column from a function. > no - it is not possible - the functions should to return exact same set of columns. Teoretically you can use SETOF

Re: Returning Vector of Pairs with a PostgreSQL C Extension Function

2018-08-27 14:40 GMT+02:00 TalGloz : > It looks like changing the > > elements[0] = CStringGetDatum(localT1.c_str()); > elements[1] = CStringGetDatum(localT2.c_str()); > > to: > > elements[0] = PointerGetDatum(cstring_to_text(localT1.c_str())); > elements[1] =

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

2018-07-16 13:52 GMT+02:00 Dmitry Igrishin : > > > пн, 16 июл. 2018 г. в 14:26, : > >> We – and the majority of our customers - are mainly focused on Windows. >> We use pgadmin iii and our own assistants. pgadmin iv ist still too slow on >> Windows compared to pgadmin iii. That is one reason why

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

2018-07-16 14:28 GMT+02:00 Dmitry Igrishin : > > > пн, 16 июл. 2018 г. в 15:01, Pavel Stehule : > >> >> >> 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin : >> >>> >>> >>> пн, 16 июл. 2018 г. в 14:26, : >>> >>>>

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

2018-07-16 15:22 GMT+02:00 Dmitry Igrishin : > > > пн, 16 июл. 2018 г. в 16:00, Pavel Stehule : > >> >> >> 2018-07-16 14:28 GMT+02:00 Dmitry Igrishin : >> >>> >>> >>> пн, 16 июл. 2018 г. в 15:01, Pavel Stehule

Re: How to remove elements from array .

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

Re: How to remove elements from array .

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

Re: How to set array element to null value

2018-07-09 11:58 GMT+02:00 Brahmam Eswar : > I'm trying to reset array element to null. but 3rd line of below snippet > is giving the compilation error. > > > FOR indx_1 IN array_lower(X, 1)..array_upper(X, 1) LOOP > IF X[indx_1].REFERENCE_VALUE = 'ABC' THEN > X[indx_1].REFERENCE_VALUE:=''; > END

Re: Re: PostgreSQL needs percentage function

2017-12-18 17:13 GMT+01:00 Nick Dro : > > Hi, > I know how to implement this. It's not the issue. > It's very easy to implement absolute value as well yet still PostgreSQL > gives abs(x) function which is build in function. > My claim is that if there is a build in

Re: Re: PostgreSQL needs percentage function

2017-12-19 10:13 GMT+01:00 Nick Dro : > This is exactly why I think there should be some build-in function for > that... > Percentage calculation exists in almost any databse and information system > - it requires from use to implement many functions on thier own for >

Re: How to find the hits on the databases and tables in Postgres

2018-05-05 13:03 GMT+02:00 PT : > On Fri, 4 May 2018 17:14:39 +0530 > nikhil raj wrote: > > > Hi, > > Any one can please help me out > > > > How to monitor the Hits on database and how many hits on each user tables > > Through query. > > Is

Re: Enhancement to psql command, feedback.

2018-05-09 9:59 GMT+02:00 John McKown : > I just wanted to throw this out to the users before I made a complete fool > of myself by formally requesting it. But I would like what I hope would be > a minor change (enhancement) to the psql command. If you look on this

Re: Query function arg data types ONLY (no arg names)

Hi 2018-04-28 18:52 GMT+02:00 Jeremy Finzel : > It appears that neither pg_get_function_arguments > nor pg_get_function_identity_arguments could be used for this. I want to > get function argument data types from the catalog by ordinal position, > without the argument name. >

Re: How do I get the SQL statement in a trigger?

documented at > https://www.postgresql.org/docs/current/static/functions-info.html sure - it is better - forgot it Regards Pavel > > > > > > > > > > On 10/05/18 11:44, Pavel Stehule wrote: > >> >> >> 2018-05-10 12:42 GMT+02:00 a <372660...@qq.com

Re: How do I get the SQL statement in a trigger?

Hi 2018-05-10 12:23 GMT+02:00 a <372660...@qq.com>: > Hi I would like to write a trigger that recorded every sql statement under > the effected entry. So if it is possible to retrieve the sql statement > within a trigger?? > You can read a tom command from pg_stat_activity table postgres=#

Re: How do I get the SQL statement in a trigger?

Regards Pavel > > > -- Original message ------ > *From:* "Pavel Stehule"; > *Sendtime:* Thursday, May 10, 2018 6:38 PM > *To:* "a"<372660...@qq.com>; > *Cc:* "pgsql-general"; > *Subject:* Re: How do I get the SQL statement in a tri

Re: array_agg to array

Hi 2018-05-16 8:14 GMT+02:00 Philipp Kraus : > Hello, > > I have got a function with a reg expr to split chemical formulas e.g. H2O > -> H2 O. > > CREATE OR REPLACE FUNCTION daimon.text2sumformula(text) RETURNS text[] AS > $$ > select array_agg(i::text) as e

Re: Function to set up variable inside it

Hi 2018-05-16 6:52 GMT+02:00 Łukasz Jarych : > Hi Guys, > > thank you for your help ! > > Hmm yes something like this. > > I was wondering if is possible to pass variable into function: > > CREATE FUNCTION change_trigger() RETURNS trigger AS $$ > > BEGIN > >

Re: Importing data from CSV into a table with array and composite types

select array_agg(a) from f, unnest(pv) where f.c = 1000; ┌───┐ │ array_agg │ ╞═══╡ │ {10,30} │ └───┘ (1 row) > Thank you! > > Shore > > > -- Original message -- > *From:* "Pavel Stehule"; > *Sendtime:* Friday, Ma

Re: Function to set up variable inside it

2018-05-15 14:28 GMT+02:00 Łukasz Jarych : > Hi Guys, > > I am using postgres 10.3 (or 4?). > IT is possible to set up variable inside function? > I don't understand to the question. What do you think? Regards Pavel > > Best, > Jacek >

Re: json_populate_recordset

2018-06-07 18:51 GMT+02:00 Adrian Klaver : > From here: > > https://www.postgresql.org/docs/10/static/functions-json.html > > select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", > "a b"], "c": {"d": 4, "e": "a b c"}}') > > What is the null::myrowtype doing? > this function

Re: Microsoft buys GitHub, is this a threat to open-source

2018-06-04 20:34 GMT+02:00 Michael Nolan : > > > On Mon, Jun 4, 2018 at 12:15 PM, Tom Lane wrote: > >> Michael Nolan writes: >> > Microsoft has bought GitHub for $7.5 billion, is this a threat to the >> open >> > source community? >> >> A fair question, but one that seems entirely off-topic for

Re: limit and query planner

2018-06-05 20:24 GMT+02:00 armand pirvu : > All > > Please see below > > explain analyze select * from sp_i2birst_reg_staging_test where evt_id = > 'ACSF17' > and status=0 limit 10; > QUERY PLAN > >

Re: Whither 1:1?

2018-06-01 18:52 GMT+02:00 Guyren Howe : > It’s come to my attention that what seems an obvious and useful database > design pattern — 1:1 relations between tables by having a shared primary > key — is hardly discussed or used. > > It would seem to be a very simple pattern, and useful to avoid

Re: Database name with semicolon

Hi 2018-06-27 15:22 GMT+02:00 Joby John : > Hi, > > > > We have a windows application which communicate to Postgres database via > PostgreSQL ODBC drivers for windows. > > One of our customers has a database with semicolon in its name (e.g.: “db; > name”) and our application is failing to

Re: EXTERNAL: Re: Database name with semicolon

= '"; sss"' > > Regards, > > Joby John > > > > *From:* Pavel Stehule [mailto:pavel.steh...@gmail.com] > *Sent:* 27 June 2018 15:26 > *To:* Joby John > *Cc:* pgsql-gene...@postgresql.org > *Subject:* EXTERNAL: Re: Database name with semicolon >

Re: Migrating to postgresql from oracle

Hi 2017-12-23 19:53 GMT+01:00 Timo Myyrä : > Hi, > > I'm preparing migration of our asset management system database from > Oracle 12c to > PostgreSQL 10. I'm using ora2pg and a bit of sed to mangle the SQL ready > for > import to pg but I've hit first problem: >

Re: OPtimize the performance of a query

Hi 2018-01-16 17:44 GMT+01:00 hmidi slim : > Sorry I forget the lower command when I wrote the code, it is like this: > lower(g.country_code) like lower('US') > (lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like > lower('L')) > please, don't do top

Re: OPtimize the performance of a query

ith indexes? > https://en.wikipedia.org/wiki/Posting_style#Top-posting .. please, don't do it. IS DISTINCT FROM has sense if your data - or your queries has NULL. If not, and it is probably your case, then <> should be preferred. Regards Pavel > 2018-01-16 17:49 GMT+01:00 Pavel Stehule &

Re: Dynamic PL/pgSQL select query: value association propblem

Hi 2018-02-16 13:20 GMT+01:00 Thiemo Kellner : > Hi all > > I would like to have a generic trigger function that compares on insert if > there is already a record in the table with the very same values. Using > PL/pgSQL ( I am not bound to that) I know the insert

Re: strange construct with RETURN within plpgsql

2018-02-16 13:31 GMT+01:00 mariusz : > > hello all, > > i just noticed some strange thing in plpgsql, that is keyword RETURN is > allowed as noop after a valid statement. > shame on me, after so many years of using plpgsql i happened to write a > bug omitting semicolon after

Re: oracle to postgresql conversion tool

Hi 2018-02-22 5:59 GMT+01:00 Marcin Giedz : > Hi, there are at least 5 tools I found on the PG list but could you > recommend well tested, free one ? we need to migrate production 30GB oracle > 11 db to postgres 9 and are looking for best approach. Of course if there > is

Re: strange construct with RETURN within plpgsql

2018-02-16 14:20 GMT+01:00 mariusz <mar...@mtvk.pl>: > On Fri, 2018-02-16 at 13:51 +0100, Pavel Stehule wrote: > > > > It is not a bug, it is feature. Sometimes not nice. RETURN is keyword > > in procedural part, but it is nothing in sql part. > > > thanks, i h

Re: shared_buffers 8GB maximum

2018-02-18 14:41 GMT+01:00 Vitaliy Garnashevich : > > I certainly wouldn't recommend using 1/2 of RAM right away. There's a >> good chance it would be a waste of memory - for example due to double >> buffering, which effectively reduces "total" cache hit ratio. >> > >

Re: Does PostgreSQL check database integrity at startup?

2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter < rich...@simkorp.com.br>: > Em 26/12/2017 12:25, Pavel Stehule escreveu: > > > > 2017-12-26 14:44 GMT+01:00 Martin Marques <martin.marq...@2ndquadrant.com> > : > >> El 26/12/17 a las 09:52,

Re: Does PostgreSQL check database integrity at startup?

2017-12-26 14:44 GMT+01:00 Martin Marques : > El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió: > > Recently I had a problem with a base file with size 0 in a standby > server. > > > > This raised one question: does PostgreSQL (9.6.6) check base

Re: Does PostgreSQL check database integrity at startup?

2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter < rich...@simkorp.com.br>: > Em 26/12/2017 13:40, Pavel Stehule escreveu: > > > > 2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter < > rich...@simkorp.com.br>: > >> Em 26/12/2017 12:25, Pave

Re: plpgsql function-parsing

Hi 2018-03-06 16:51 GMT+01:00 chris : > Hi, > > I am trying to create a function that gets passed a statement as a string > and then I need to change the table_name within the string by adding a > "_cdc" to it, then execute the statement > > ex: > > string passed could be

Re: Troubleshooting a segfault and instance crash

Hi 2018-03-08 18:40 GMT+01:00 Blair Boadway : > Hello, > > > > We’re seeing an occasional segfault on a particular database > > > > Mar 7 14:46:35 pgprod2 kernel:postgres[29351]: segfault at 0 ip > 00302f32868a sp 7ffcf1547498 error 4 in libc-2.12.so[302f20+ >

Re: Troubleshooting a segfault and instance crash

'auditor' > > pgaudit.log_parameter = off > > pgaudit.log_catalog = off > > pgaudit.log_statement_once = on > > pgaudit.log_level = log > > > > > > select * from information_schema.role_table_grants where grantee = > 'auditor'; > > (0 rows) > > > > > > th

Re: psql variables in the DO command

2018-03-06 10:17 GMT+01:00 Pavel Luzanov <p.luza...@postgrespro.ru>: > On 05.03.2018 18:35, Pavel Stehule wrote: > > I am slowly working on prototype. The work is simple, when variables are > just scalars. But it is much harder, when we allow composite variables. > When prot

Re: psql variable to plpgsql?

2018-04-11 11:36 GMT+02:00 Thiemo Kellner, NHC Barhufpflege < thiemo.kell...@gelassene-pferde.biz>: > Zitat von Pavel Stehule <pavel.steh...@gmail.com>: > > No, there is not possible to read/write client side variables from server >> side. >> > > I did

Re: Troubleshooting a segfault and instance crash

2018-03-25 0:41 GMT+01:00 Blair Boadway : > Thanks for the tip. We are using RHEL 6.9 and definitely up to date on > glibc (2.12-1.209.el6_9.2). We also have the same versions on a very > similar system with no segfault. > > > > My colleague got a better backtrace that

Re: Prompt for parameter value in psql

Hi 2018-03-16 16:12 GMT+01:00 Tiffany Thang : > Hi, > Would it be possible to prompt for a user input in psql like in Oracle > sqlplus? > > In oracle, we use the & sign, for example, > select * from emp where empid= > >

Re: ora2pg and invalid command \N

2018-03-16 18:12 GMT+01:00 Charlin Barak : > Hi, > I'm using ora2pg to migrate our Oracle database to Postgres. I was able to > generate the data file using TYPE=COPY but when I attempted to load the > file via psql, I got lots of "invalid command \N" errors. The

Re: Is there a way to get the name of the calling function in pgplsql?

Hi 2018-03-21 8:24 GMT+01:00 Thiemo Kellner : > Hi all > > In a function I would like to log the caller. Is there a way to get its > name in pgplsql? > you can read it from stack

Re: Is there a way to get the name of the calling function in pgplsql?

Pavel > > Zitat von Pavel Stehule <pavel.steh...@gmail.com>: > > https://www.postgresql.org/docs/current/static/plpgsql-contr >> ol-structures.html#PLPGSQL-CALL-STACK >> > > -- > Öffentlicher PGP-Schlüssel: h

Re: psql variables in the DO command

2018-03-05 15:02 GMT+01:00 Pavel Luzanov <p.luza...@postgrespro.ru>: > On 05.03.2018 16:56, Pavel Stehule wrote: > > >> I can't use psql variable in the DO command. Is it intentional behavior? >>> >> >> yes. psql variables living on client side, and a

Re: psql variables in the DO command

Hi 2018-03-05 14:13 GMT+01:00 Pavel Luzanov : > Hello, > > I can't use psql variable in the DO command. Is it intentional behavior? > yes. psql variables living on client side, and are not accessible from server side . DO command is executed on server side. you can

Re: psql variables in the DO command

2018-03-05 16:19 GMT+01:00 Pavel Luzanov <p.luza...@postgrespro.ru>: > On 05.03.2018 18:01, Pavel Stehule wrote: > > > It is most correct when you thinking about it. > > 1. :xx is out of SQL syntax, so can by safely used. There is not risk of > unwanted usage. > &g

Re: psql variables in the DO command

2018-03-05 14:52 GMT+01:00 Pavel Luzanov <p.luza...@postgrespro.ru>: > On 05.03.2018 16:42, Pavel Stehule wrote: > > > I can't use psql variable in the DO command. Is it intentional behavior? >> > > yes. psql variables living on client side, and are not accessible f

Re: Database name with semicolon

2018-06-28 12:10 GMT+02:00 joby.john@nccgroup.trust < Joby.John@nccgroup.trust>: > > See if something like below works: > > {db; name} > > > > Hi, > > Thanks for the responses. > > I tried putting curly brackets around the value of database like you > mentioned. > {db; name} > But the server was

Re: How to improve sql query to achieve the better plan

orkitem_id" btree (parent_workitem_id) > "index_workitems_on_project_id" btree (project_id) > "index_workitems_on_standard_workitem_id" btree (standard_workitem_id) > "index_workitems_on_workitem_category_id" btree (workitem_category_id) >

Re: How to improve sql query to achieve the better plan

Hi ne 30. 9. 2018 v 18:23 odesílatel Arup Rakshit napsal: > I have the below query which is taking 1873 ms. How can I improve this? > > explain analyze select > sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 - > coalesce(workitems.discount, 0)/ 100)) as total_budget_cents, >

Re: Optimizing Postgresql ILIKE while query

Hi po 22. 10. 2018 v 7:57 odesílatel aman gupta napsal: > Hi Team, > > Greetings for the day!! > > Platform: > > PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 > 20150623 (Red Hat 4.8.5-11), 64-bit > > > Issue: > > > We have the base table which contains 22M records and we

Re: Largest & Smallest Functions

st 7. 11. 2018 v 22:38 odesílatel Ken Tanzer napsal: > Hi. Building on the [type]_larger and _smaller functions (and lifting > from the documentation), I put together a couple of functions that will > take any number of arguments: > > CREATE FUNCTION largest(VARIADIC anyarray) RETURNS

Re: Largest & Smallest Functions

čt 8. 11. 2018 v 7:34 odesílatel Andrew Gierth napsal: > >>>>> "Pavel" == Pavel Stehule writes: > > Pavel> The variadic parameters should not be a arrays - can be of "any" > Pavel> type. But this functionality is available only for C lang

Re: Recommendation for upgrading from PostgreSQL 9.3

Hi po 12. 11. 2018 v 10:18 odesílatel Karl Martin Skoldebrand < ks0c77...@techmahindra.com> napsal: > Are there any recommendations regarding upgrading from PG 9.3 to 9.6 or > 10.x? > > I found a few changes at > https://severalnines.com/blog/upgrading-your-database-to-postgresql-version-10 >

Re: Recommendation for upgrading from PostgreSQL 9.3

po 12. 11. 2018 v 11:45 odesílatel Karl Martin Skoldebrand < ks0c77...@techmahindra.com> napsal: > I found a note on logical replication in PostgreSQL 10.x with the caveat > “There are also a number of caveats regarding what objects are actually > replicated—for example, only tables are

Re: Plpgsql search_path issue going from 9.3 to 9.6

Hi út 13. 11. 2018 v 14:18 odesílatel George Woodring < george.woodr...@iglass.net> napsal: > We are having an issue with one of our plpgsql functions after migrating > from 9.3 to 9.6. The function works fine until you change the search path. > > psql (9.6.10) > Type "help" for help. > >

Re: LOG: incomplete startup packet

t means nothing, but better to work live without this garbage. Pavel > Regards, > Paul > > On Thu, Nov 8, 2018 at 1:06 PM Pavel Stehule > wrote: > >> Hi >> >> čt 8. 11. 2018 v 10:19 odesílatel Pavel Demidov >> napsal: >> >>> Hello, >&g

Re: How can I get and handle the status of sql statements that run in plpgsql ?

Hi st 3. 10. 2018 v 18:26 odesílatel David Gauthier napsal: > I found "no_data" here... > https://www.postgresql.org/docs/10/static/errcodes-appendix.html > > update blah, blah... > if(no_data) then > raise exception "update failed to update anything"; > end if > UPDATE IF NOT FOUND

Re: array must have even number of elements

Hi čt 20. 9. 2018 v 19:55 odesílatel Susan Hurst < susan.hu...@brookhurstdata.com> napsal: > > Why must an array have an even number of elements? I need to use a > trigger function on any table, some of which may have an odd number of > columns that I want to cleanse before inserting/updating.

Re: array must have even number of elements

gt; > --- > > Susan E Hurst > Principal Consultant > Brookhurst Data LLC > Email: susan.hu...@brookhurstdata.com > Mobile: 314-486-3261 > > On 2018-09-20 13:04, Pavel Stehule wrote: > > Hi > > čt 20. 9. 2018 v 19:55 o

Re: [unixODBC]Unrecognized key passed to SQLGetInfo. {S1C00,NativeErr = 209}

Hi čt 3. 1. 2019 v 12:00 odesílatel Mihalidesová Jana < jana.mihalides...@cetin.cz> napsal: > Hi, > > > > I try to connect from oracle 11.2.0.4 to postgres but I still got. Do you > have any idea what configuration should I check. What is wrong? > > > > ERROR at line 1: > > ORA-28500: connection

Re: [unixODBC]Unrecognized key passed to SQLGetInfo. {S1C00,NativeErr = 209}

+ > > SQL> > > > > Thx a lot! > There is a problem with SQLGetInfo function - so there can be two issues - 1. driver, 2. odbc environment > > J > > > > *From:* Pavel Stehule > *Sent:* Thursday, January 3, 2019 12:26 PM

Re: Weird behaviour of ROLLUP/GROUPING

st 16. 1. 2019 v 13:51 odesílatel Guillaume Lelarge napsal: > Hi, > > One of my customers found something quite weird on his 9.6 cluster. Here > is a quick demo showing the issue: > > -- quick demo table > CREATE TABLE t1 (a integer, b timestamp, c integer); > > -- a working query > SELECT >

Re: Oracke BLOB to Postgres BYTEA using ora2pg

6d1278707400054d414a4f52', > 'hex') > > (6 rows) > > > > So, how I convert bytea to text? > I use a function - when encoded data are in server encoding. looks like a issue, because Postgres uses every where utf8, and this doesn't look like it - maybe utf16 So

Re: Oracke BLOB to Postgres BYTEA using ora2pg

út 15. 1. 2019 v 11:37 odesílatel Pavel Stehule napsal: > > > út 15. 1. 2019 v 11:13 odesílatel Mihalidesová Jana < > jana.mihalides...@cetin.cz> napsal: > >> Hi, >> >> >> >> These are original data in blob on oracle >> >> &

Re: Oracke BLOB to Postgres BYTEA using ora2pg

út 15. 1. 2019 v 12:56 odesílatel Daniel Verite napsal: > Mihalidesová Jana wrote: > > > nipjd=> select distinct encode(serializable_value, 'escape') from > > alf_node_properties_zaloha where serializable_value is not null; > > >encode > > >

Re: Oracke BLOB to Postgres BYTEA using ora2pg

Hi út 15. 1. 2019 v 9:40 odesílatel Mihalidesová Jana < jana.mihalides...@cetin.cz> napsal: > Hi, > > > > We try to migrate from oracle to postgres using ora2pg but we hit some > weird behavior of bytea. Or it’s just our ignorance. > > Table migration were ok, but we are not able to read bytea

Re: IF NOT EXIST

Hi út 18. 12. 2018 v 7:11 odesílatel Igor Korot napsal: > Hi, ALL, > I have a following statement: > > IF NOT EXIST( SELECT 1 SELECT 1 FROM pg_proc AS proc, pg_namespace AS > ns ) CREATE FUNCTION(); > > Unfortunately trying to execute it thru the ODBC interface with: > > ret = SQLExecDirect(

Re: Format an Update with calculation

út 18. 12. 2018 v 8:15 odesílatel Bret Stern < bret_st...@machinemanagement.com> napsal: > My statement below updates the pricing no problem, but I want it to be > formatted with 2 dec points eg (43.23). > > Started playing with to_numeric but can't figure it out. Lots of examples > with to_char

Re: new stored procedure with OUT parameters

ables (now). So you cannot to use any OUT parameter from this environment - and then has not sense to use signature with OUT parameters. But it is possible from PL/pgSQL - and it is not consistent. > Thanks, > Anton > > On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule > wrote: >

Re: conditionally terminate psql script

Hi po 17. 12. 2018 v 13:14 odesílatel napsal: > > > Hi, > > is there a way to stop execution of a psql script if a select returns some > rows (or no rows) > The idea is to add a safety check on data, specifically to select all new > rows that would conflict > on a bulk insert, show them and

Re: new stored procedure with OUT parameters

Hi út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175geo...@gmail.com> napsal: > Hi all, > > I was playing around with the stored procedure support in v11 and found > that pure OUT parameters are not supported. Is there any reason we only > support INOUT but not OUT parameters? > The procedure

Re: Regarding Tds_fdw

Hi st 28. 11. 2018 v 11:28 odesílatel Durgamahesh Manne < maheshpostgr...@gmail.com> napsal: > Hi > > Respected community members > > I have configured tds_fdw on postgres server.. I have created multiple > foreign tables related to sql server as of now i could run select queries > with out any

Re: PL/pgSQL HTTP Request

Hi pá 7. 12. 2018 v 14:48 odesílatel Sathish Kumar napsal: > Hi Team, > > Do PL/pgSQL support to create a function to make HTTP request. We have a > requirement to send data to external server from Postgres DB using > HTTP/HTTPS Post Method. > Surely It doesn't support it. You can use some

Re: PL/pgSQL HTTP Request

pá 7. 12. 2018 v 14:58 odesílatel Sathish Kumar napsal: > Hi Pavel, > > We would like to use with Google Cloud Sql where third party extensions > are not supported. > Then it easy - you cannot to do from Postgres. Pavel > > On Fri, Dec 7, 2018, 9:55 PM Pavel Stehule

Re: DROP CASCADE transitive dependencies

po 3. 12. 2018 v 20:07 odesílatel C GG napsal: > > > On Mon, Dec 3, 2018 at 1:26 PM Tom Lane wrote: > >> C GG writes: >> > ...PostgreSQL 9.5... >> > `DROP SCHEMA blah;` reports all the dependent objects and advises to >> `DROP >> > SCHEMA blah CASCADE;` ... >> >> > Will DROP ... CASCADE

Re: debugging intermittent slow updates under higher load

Hi čt 6. 12. 2018 v 12:18 odesílatel Chris Withers napsal: > On 06/12/2018 11:00, Alexey Bashtanov wrote: > > > >> I'm loath to start hacking something up when I'd hope others have done > >> a better job already... > > If you log all queries that take more than a second to complete, is your > >

Re: amazon aroura config - seriously overcommited defaults? (May be Off Topic)

so 8. 12. 2018 v 20:04 odesílatel Square Bob napsal: > All; > > > My apologies if this is off topic. > > > Our company is moving to Aurora, In the past I would take care not to > allow postgresql to over-commit memory beyond the actual memory on the > server, which meant I would add the buffer

Re: Package-support plans?

Hi út 20. 11. 2018 v 8:40 odesílatel Nicklas Karlsson napsal: > Yes, that it surely one option but is there any grant that can be made on > a function so that it can only be called from within the same schema? Even > then it is a bit cumbersome > I have a patch, that can do it - but, probably

Re: Potentially undocumented behaviour change in Postgres 11 concerning OLD record in an after insert trigger

pá 4. 1. 2019 v 17:44 odesílatel Tom Lane napsal: > Kristjan Tammekivi writes: > > I've noticed a change in the behaviour in triggers / hstores in Postgres > > 11.1 when compared to Postgres 10.5. > > [ reference to OLD in an insert trigger doesn't throw error anymore ] > > Hmm. This seems to

Re: Showing table comments with psql

Hi pá 4. 1. 2019 v 17:57 odesílatel Mark Jeffcoat napsal: > I'm creating table and view comments with "COMMENT ON", and I can find > the comment in pg_description, but I can't find a way to show the > table comments using psql. > > $ psql --version > psql (PostgreSQL) 11.1 (Debian 11.1-1+b2) >

Re: (Again) Column Store on PostGreSQL

Hi pá 4. 1. 2019 v 17:12 odesílatel Simon AUBERT napsal: > Hello, > > We can find this very informative blog post : > https://blog.2ndquadrant.com/column-store-plans/ > > And this wiki page : > https://wiki.postgresql.org/wiki/ColumnOrientedSTorage > I must say the approach with the

Re: Bitmap Heap Scan and Bitmap Index Scan

Hi so 15. 9. 2018 v 9:39 odesílatel Arup Rakshit napsal: > Here is a explain plan of a very simple query: > > aruprakshit=# explain analyze select first_name, last_name from users > where lower(state) = 'colorado'; > QUERY PLAN > >

Re: Does idle sessions will consume more cpu and ram? If yes,how to control them

čt 20. 12. 2018 v 2:41 odesílatel Ron napsal: > On 12/19/18 7:27 PM, Michael Paquier wrote: > [snip] > > Each backend stores its own copy of the relation cache, so if you have > > idle connections which have been used for other work in the past then > > the memory of those caches is still

Re: assembling PGresults from multiple simultaneous queries (libpq, singlerowmode)

Hi ne 7. 4. 2019 v 20:47 odesílatel Konstantin Izmailov napsal: > Hi, > I'm experimenting with Postgres 10 and protocol v3. I noticed that the > Postgres allows executing multiple queries simultaneously (I basically > commented out a check that prevents sending another query in libpq while >

Re: Unable to Vacuum Large Defragmented Table

po 8. 4. 2019 v 7:57 odesílatel Igal Sapir napsal: > David, > > On Sun, Apr 7, 2019 at 8:11 PM David Rowley > wrote: > >> On Mon, 8 Apr 2019 at 14:57, Igal Sapir wrote: >> > However, I have now deleted about 50,000 rows more and the table has >> only 119,688 rows. The pg_relation_size() still

Re: Unable to Vacuum Large Defragmented Table

po 8. 4. 2019 v 17:22 odesílatel Igal Sapir napsal: > Pavel, > > On Sun, Apr 7, 2019 at 11:22 PM Pavel Stehule > wrote: > >> >> po 8. 4. 2019 v 7:57 odesílatel Igal Sapir napsal: >> >>> David, >>> >>> On Sun, Apr 7, 2019 at 8:

Re: Getting error while running the pg_basebackup through PGBOUNCER

po 8. 4. 2019 v 15:42 odesílatel Raghavendra Rao J S V < raghavendra...@gmail.com> napsal: > Hi All, > > We are using PGBOUNCER(connection pool mechanisam). PGBOUNCER uses port > 5433. > > Postgres database port number is 6433. By using port 5433 PGBOUNCER is > connecting to postgres port 6433

Re: Where **not** to use PostgreSQL?

Hi čt 28. 2. 2019 v 12:47 odesílatel Thomas Güttler < guettl...@thomas-guettler.de> napsal: > Hi experts, > > where would you suggest someone to **not** use PostgreSQL? > > Why would you do this? > > What alternative would you suggest instead? > Don't use Postgres like cache, don't use Postgres

Re: query has no destination for result data

I believe language plpgsql is not considered part of the function body > so it is not included in the line count: > > https://www.postgresql.org/docs/10/plpgsql-structure.html > > When tracking a line number down I usually do: > > \ef some_function line_number > > which counts the line in the

Re: How to parse XML in Postgres newer versions also

ne 17. 3. 2019 v 14:49 odesílatel Pavel Stehule napsal: > Hi > > ne 17. 3. 2019 v 12:11 odesílatel Andrus napsal: > >> Hi! >> >> In Postgres 9.1.2 script below produces proper results: >> >> 1.34 >> 5.56 >> >> In Postgres 11 it

Re: How to parse XML in Postgres newer versions also

ne 17. 3. 2019 v 15:19 odesílatel Andrus napsal: > Hi! > > >You can use XMLTABLE function > >select xmltable.* > > from t, > > lateral > > xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' > as > > ns), > > > > '/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/.'

  1   2   3   4   5   >