Re: Slow alter sequence with PG10.1

On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger wrote: > Dear community, > > I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after upgrading > it from PG9.6.6. My application heavily uses sequences and requires > different increments of sequence numbers, e.g. a

Re: Information on savepoint requirement within transctions

On Fri, Jan 26, 2018 at 9:47 AM, Melvin Davidson wrote: > > ...the presence of a version is bad, only the badgering of people asking > questions to provide it when it has no bearing on the answer... > > Really? Is it that hard for someone to provide version and O/S? > ​Its

Re: Information on savepoint requirement within transctions

On Fri, Jan 26, 2018 at 8:42 AM, Melvin Davidson wrote: > > On Fri, Jan 26, 2018 at 10:32 AM, Robert Zenz > wrote: > ​The convention for these lists is to inline or bottom-post. Top-posting is discouraged. ​ > Well, no. What I'm looking for

Re: Information on savepoint requirement within transctions

On Fri, Jan 26, 2018 at 8:57 AM, Robert Zenz wrote: > In PostgreSQL the use of savepoints is > required: > > start transaction > insert into A > create savepoint > insert into B but fail > rollback to savepoint > insert into C > commit > >

Re: Information on savepoint requirement within transctions

On Fri, Jan 26, 2018 at 9:27 AM, Melvin Davidson wrote: > > > >> As far as I'm aware neither PostgreSQL nor OS version do matter for this > > > Yes as of this date. However, that is not to say that the SQL standard (or > PostgreSQL) may change > in the _future_, such that

AFTER UPDATE trigger updating other records

On Wednesday, January 24, 2018, Ian Harding wrote: > > -- This is not what I expect to see. I have even tried running the update > -- unrestricted from within the trigger but I get the same result. From > -- outside the trigger I run the update unrestricted... > > UPDATE

Re: AFTER UPDATE trigger updating other records

On Thu, Jan 25, 2018 at 3:06 PM, Ian Harding wrote: > > 4 |3 | Top.Bar.Blah > 5 |4 | Top.Bar.Blah.Scooby > ​​ > > barf$# UPDATE area SET areapath = (select areapath from area a > where areaid = area.parentid) || subpath(areapath,

Re: Information on savepoint requirement within transctions

On Tue, Jan 30, 2018 at 8:25 AM, Rakesh Kumar wrote: > > > > I'm not sure about the terminology here, though, because the Transaction > > Tutorial (https://www.postgresql.org/docs/9.6/static/tutorial- > transactions.html) > > speaks of "aborted" transactions, while you

Re: Alter view with dependence without drop view!

On Tue, Jan 30, 2018 at 8:34 AM, bto...@computer.org wrote: > > When this procedure got old, I started using a script created using > pg_dump and pg_restore, as initially outlined here: > > ​Yeah, the short answer is PostgreSQL doesn't make it possible to edit "middle"

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

On Sunday, January 28, 2018, Rob Sargent wrote: > > The commit in question is the function btw. > That doesn't make sense - functions can't commit. David J.

Re: Information on savepoint requirement within transctions

On Mon, Jan 29, 2018 at 1:37 AM, Robert Zenz wrote: > Documentation, bug report, mailing list discussions, > something like that. In particular I'm interested in the questions: > > * Why are they required in combination with failing statements (when every > other

Re: Information on savepoint requirement within transctions

On Mon, Jan 29, 2018 at 9:00 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "David G. Johnston" <david.g.johns...@gmail.com> writes: > > On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> What we do have though is client-side s

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

On Sunday, January 28, 2018, Ryan Murphy wrote: > Hello, > > I heard at a PostgreSQL talk that you should not liberally create temp > tables in the course of frequently-used functions etc, because (roughly) > you're using up some of the same resources that you for your

Re: A little RULE help?

On Wed, Jan 31, 2018 at 8:39 AM, Steven Winfield < steven.winfi...@cantabcapital.com> wrote: > > Don't use RULEs. > -- > Vik Fearing +33 6 46 75 15 36 <+33%206%2046%2075%2015%2036> > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > > > > > > Repeating this, as if I should

Re: Information on savepoint requirement within transctions

On Tue, Jan 30, 2018 at 1:40 AM, Robert Zenz <robert.z...@sibvisions.com> wrote: > On 30.01.2018 03:07, David G. Johnston wrote: > > ​So, my first pass at this. > > Nice, thank you. > > > + These are of particular use for client software to use when executing >

Re: Rolls

On Thursday, February 1, 2018, Andrew Bartley wrote: > Hi all, > > I am trying to work out a way to create a roll/user that can only execute > one particular function and nothing else. The particular function has been > created with "SECURITY DEFINER". > Never tried it but

Create schema with in a specific database from a script file

On Thursday, February 1, 2018, Abhra Kar wrote: > > In xyz.sh I executed the following script --- > > *su -c "psql -c \"\c ABC \"" postgres* > > *su -c "psql -c \"create schema authorization myschema\"" postgres* > > > > In the terminal got message “connected to ABC

Re: Recreating functions after starting the database server.

On Wed, Jan 31, 2018 at 5:45 AM, Konrad Witaszczyk wrote: > Hi, > > I'm considering changing my database schema upgrade routines to recreate > functions and all objects depending on them (e.g. triggers, views) just > after > the database server is started. It would make the

Re: Regex Replace with 2 conditions

On Mon, Feb 5, 2018 at 6:34 AM, Denisa Cirstescu < denisa.cirste...@tangoe.com> wrote: > Is there a way to specify 2 conditions in regexp_replace? > ​Tom and Francisco ​both give excellent responses. I have written a SQL function that achieves this, but I am not happy with > it because it is

Re: Information on savepoint requirement within transctions

On Mon, Jan 29, 2018 at 6:59 AM, Robert Zenz wrote: > > It may be worth updating the docs here... > > I'd vote for that. I would have expected to see this mentioned in the > documentation a little bit more prominent than just a single sentence at > the end > of the

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 9:09 AM, Steven Hirsch wrote: > I have a body of code using JDBC to work with a PostgreSQL 9.6 database. > All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids. All are > working correctly in terms of using the next value as a default. However,

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 10:58 AM, Steven Hirsch wrote: > On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but > that too returns NULL. So, where is the '0' coming from when I do: > > SELECT currval( pg_get_serial_sequence('udm_as >

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 12:54 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > The only 'currval' procedure is the one defined at installation (in >> public). >> > ​So, the installed version of currval would be defined in "pg_catalog", not "public" ... David J. ​

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 12:51 PM, Steven Hirsch <snhir...@gmail.com> wrote: > On Thu, 8 Feb 2018, David G. Johnston wrote: > > On Thu, Feb 8, 2018 at 10:58 AM, Steven Hirsch <snhir...@gmail.com> wrote: >> On a hunch, I tried 'SELECT currval(NULL)' to see if it re

Re: Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 2:22 PM, Steven Hirsch <snhir...@gmail.com> wrote: > On Thu, 8 Feb 2018, David G. Johnston wrote: > > On Thu, Feb 8, 2018 at 12:54 PM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> The only 'curr

Re: Regex Replace with 2 conditions

On Tue, Feb 6, 2018 at 8:46 AM, George Neuner wrote: > On Mon, 5 Feb 2018 18:22:02 +0100, Francisco Olarte > wrote: > > > >I repeat for the last time. YOU ARE NOT USING ASCII. ASCII IS A SEVEN > >BIT CODE, 0-128. "?" IS NOT IN THE ASCII CHARACTER

Re: execute block like Firebird does

On Mon, Feb 12, 2018 at 6:48 AM, PegoraroF10 wrote: > Another approach to solve my problem would be a function that receives a > dynamic SQL, runs it and returns a XML or JSON and on client side I convert > that XML back to a recordset. Is that possible ? Yes, you can pass

Re: execute block like Firebird does

On Monday, February 12, 2018, PegoraroF10 wrote: > Explaining better my problem. All reports our customer use are > customizable. > So, when a customer runs a report it just runs all SQLs that are inside > that > report, being SQL or Execute Blocks. But because they are

Re: Odd behavior with 'currval'

On Fri, Feb 9, 2018 at 8:27 AM, Francisco Olarte wrote: > On Thu, Feb 8, 2018 at 8:12 PM, Steven Hirsch wrote: > . > > > 2. Why is the currval() function being so blasted dumb? If > > 'pg_get_serial_sequence' cannot resolve the sequence, it

Re: cursors and function question

On Tuesday, February 13, 2018, armand pirvu wrote: > > CREATE OR REPLACE FUNCTION foofunc() >RETURNS text AS $$ > > select foofunc(); > foofunc > --- > ("E1","CAT1 ",0) > > But I am looking to get > >

Re: cursors and function question

On Tue, Feb 13, 2018 at 12:03 PM, armand pirvu wrote: > > ERROR: RETURN cannot have a parameter in function returning set > LINE 10: return var2; > HINT: Use RETURN NEXT or RETURN QUERY. > > > and it just sits there > > Any hints ? > >

Re: cursors and function question

On Tue, Feb 13, 2018 at 3:31 PM, Adrian Klaver wrote: > 2) By global table do you mean a temporary table? If so not sure that is > going to work as I am pretty sure it will disappear after the function is > run. ​Temporary tables can survive until either session or

Re: I do not get the point of the information_schema

On Tue, Feb 13, 2018 at 4:17 PM, Peter J. Holzer wrote: > > It is possible that all the columns that PostgreSQL has are required by > the standard and that MariaDB is non-conforming by omitting them, but at > least some of the names look quite PostgreSQL-specific to me. So my >

Re: I do not get the point of the information_schema

On Tue, Feb 13, 2018 at 3:57 PM, Peter J. Holzer wrote: > (That said, it looks like both PostgreSQL and MariaDB include additional > columns beyond those mandated by the standard - you can't rely on those, > of course. And some databases like Oracle don't even have an

Re: Upgrading from Postgresql 9.1 to 10

On Wednesday, February 14, 2018, pavan95 wrote: > Hi all, > > Is it possible to upgrade an existing postgresql 9.1 production system to > latest Postgres 10.0 version? > > The main requirement is to get rid of downtime. Please help me out! > Zero downtime is only

Re: postgres connection with port option in shell script

On Wed, Feb 14, 2018 at 8:21 AM, Abhra Kar wrote: > Hi, > > I want to get postgres connection in script file. I am executing > below command and successfully getting connected --- > > > psql postgresql://$USER:$PASSWORD@$HOST/$DATABASE < > > If all you are going to do

Re: Regarding pg_multixact/members folder size cleanup in postgres 9.3.6.

On Wed, Feb 14, 2018 at 9:06 AM, Yogesh Sharma wrote: > I am using postgres 9.3.6 version and i am using multiple INSERT/UPDATE > SQL commands with explicit share lock. > ​[...]​ > 4. I Want older postgres behavior in newer versions. So how to set this > behavior through

Re: New Copy Formats - avro/orc/parquet

On Saturday, February 10, 2018, Nicolas Paris wrote: > Hello > > I d'found useful to be able to import/export from postgres to those modern > data > formats: > - avro (c writer=https://avro.apache.org/docs/1.8.2/api/c/index.html) > - parquet (c++

Re: "could not receive data from client" && "incomplete startup packet"

On Wed, Feb 7, 2018 at 7:34 AM, David Gauthier wrote: > I have a v9.3.0 PG DB on a linux box as the DB server. > ​[...]​ > Any help would be appreciated! > > Upgrade to 9.3.20 and ensure you are using a current version of Perl and DB modules. David J.

Re: Any hope for more specific error message for "value too long..."?

On Fri, Feb 16, 2018 at 5:30 PM, Ken Tanzer wrote: > > That doesn't matter much in a simple example like that, but the example > below is currently making me wish PG was just a little bit more specific. > Is there much chance of this changing in future releases? > > ​ I'm

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

On Fri, Feb 16, 2018 at 4:50 PM, Olegs Jeremejevs wrote: > Hi, > > I'm aware that these default privileges are documented: > > https://www.postgresql.org/docs/10/static/ddl-schemas. > html#DDL-SCHEMAS-PRIV > > However, I'm unable to find any reasoning behind their

Re: strange construct with RETURN within plpgsql

On Fri, Feb 16, 2018 at 5:31 AM, mariusz wrote: > so, if there is a reason for such a construct and it does something i > didn't notice, please let me know what is the purpose of keyword RETURN > after a valid statement. > ​

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

On Saturday, February 17, 2018, Olegs Jeremejevs wrote: > Thanks for the reply. > > > I'm not sure whether you are really being limited/forced here or if you > are thinking that having CREATE and USAGE on a schema is more powerful than > it is... > > As far as I know,

Re: Rationale for PUBLIC having CREATE and USAGE privileges on the schema "public" by default

On Saturday, February 17, 2018, Olegs Jeremejevs wrote: > Okay, in other words, there's no way to completely defend oneself from DoS > attacks which require having a session? If so, is there a scenario where > some bad actor can create a new user for themselves (to connect

Any reason not to show "null input" (strict) column in \df+ output?

The STRICT-ness of a function seems like it should be viewable when using \df+; is its absence just an oversight? David J.

Re: Remove default privilege from DB

On Fri, Feb 16, 2018 at 8:59 AM, Stephen Frost <sfr...@snowman.net> wrote: > Tom, > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > Stephen Frost <sfr...@snowman.net> writes: > > > * David G. Johnston (david.g.johns...@gmail.com) wrote: >

Re: Remove default privilege from DB

On Fri, Feb 16, 2018 at 7:56 AM, Durumdara wrote: > I want to know what happened in the background. > I will make "negative" state if I revoke DefACL without prior grant? > ​Not really following the whole thread but figured I'm comment on this point that confused me in the

Re: strange construct with RETURN within plpgsql

On Fri, Feb 16, 2018 at 6:08 AM, mariusz <mar...@mtvk.pl> wrote: > On Fri, 2018-02-16 at 05:40 -0700, David G. Johnston wrote: > > On Fri, Feb 16, 2018 at 5:31 AM, mariusz <mar...@mtvk.pl> wrote: > > > > so, if there is a reason for such a construct an

Re: Postgres hangs for the query "lock table in exclusive mode"

On Thursday, February 22, 2018, Vinodh NV wrote: > > > Postgres hangs for the query "lock table in exclusive mode" > Can you please let me know what could be the reason and how to resolve it? > > Not readily, but that's why the pg_locks view exists.

Re: problems with postgresql 10.1 hba_conf on fedora 27

On Wed, Dec 20, 2017 at 4:05 PM, support-tiger wrote: > can someone please help ? > > postgresql-10 > > fedora 27 > ​Installed via yum? ​ > our hba_conf that worked with pg 9x > ​There are six of them, which one specifically? now gives error on restart and we cannot

Re: postgresql 9.5 has ocuuered OOM

On Wed, Dec 20, 2017 at 9:44 AM, Uwe wrote: > IIRC work_mem is a PER CONNECTION setting, > ​The docs for this setting clearly state that a single connection/session can itself use multiple times this values for a single query.

Re: problems with postgresql 10.1 hba_conf on fedora 27

On Wednesday, December 20, 2017, rob stone wrote: > > > localall all postgrespeer > > > > hostallall 127.0.0.1/32 md5 > > > > hostallall ::1/128 md5 > > > > now gives error on restart and we

Re: psql '\copy to' and unicode escapes

On Mon, Feb 26, 2018 at 9:53 AM, Steven Hirsch wrote: > I fear that I'm missing something very obvious, but I cannot find a syntax > that permits me to use an escaped hexadecimal representation in a CSV file > and have that representation interpreted as the equivalent unicode

Re: Using DSN Connection and knowing windows username

On Wednesday, June 20, 2018, Łukasz Jarych wrote: > > How to know in postgresql which specific windows user is using database? > You cannot. All the server knows is the specific user credentials it is authenticating. That said you can authenticate those credentials in such a way so that

Re: using pg_basebackup for point in time recovery

On Thu, Jun 21, 2018 at 4:26 PM, Vik Fearing wrote: > On 21/06/18 07:27, Michael Paquier wrote: > > Attached is a patch which includes your suggestion. What do you think? > > As that's an improvement, only HEAD would get that clarification. > > Say what? If the clarification applies to

Re: Restore relhaspkey in PostgreSQL Version 11 Beta

On Mon, Jul 30, 2018 at 4:11 PM, Melvin Davidson wrote: > *Random atavistic things? I hardly think relhaspkey is random. It's been > there since version 7.2.* > *Exactly how does keeping it around slow you/us down?* > My recap of the discussion thread: That this has been around for a long time

Re: Question on postgresql.conf

On Monday, July 30, 2018, Alvaro Aguayo Garcia-Rada wrote: > > As far as I know, it's not currently possible. > That would be incorrect, you just need to change server startup commands. https://www.postgresql.org/docs/10/static/runtime-config-file-locations.html David J.

Re: Postgres - search for value throughout many tables?

On Wednesday, August 8, 2018, czezz wrote: > Hi everyone, > I want to aks if anyone knows is there a way to search for specific > "value" throughout list of tables OR all tables in databse? > Can you pg_dump your database to plain text and search that? Nothing built in provides that ability

Re: check_function_bodies not doing much

On Tue, Aug 7, 2018 at 12:31 PM, Tom Lane wrote: > Marcelo Lacerda writes: > > 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

Re: Instead trigger on a view to update base tables ?

On Tue, Aug 7, 2018 at 12:09 PM, Day, David wrote: > EXECUTE format(' UPDATE admin.user SET (%I) = ( SELECT %I FROM $1 ) WHERE > id = $2)', USER_SETTING, USER_SETTING ) > USING NEW, NEW.id; > > When this executes my exception handler generates "err syntax error at or > near

Re: PostgreSQL System Views or Dictionary Tables

On Thu, Aug 16, 2018 at 2:06 PM, Diego Grampin wrote: > Hello, > > I'm new at PostgreSQL, but known Oracle since ten years ago. > > In PostgreSQL, where can i found system views or dictionary tables (v$*, > dba_* at oracle) or structural dba tables ?. And comand prompt parameters > of the

Re: How to revoke privileged from PostgreSQL's superuser

On Monday, August 6, 2018, wrote: > > I have a request for revoking the access to user's data from DBA-user. > I think the request is right because users should be the only ones can > access their data. > User then needs to encrypt data prior to storing it. Superuser can still access the data

Re: Copying data from a CSV file into a table dynamically

On Tuesday, August 14, 2018, Ruiqiang Chen wrote: > CSV file has no limitation of # of rows, excel max row is 2^20. Am I > correct? > It's considered bad form to hijack threads in this manner. Text files have no inherent limits. You can check the Excel documentation for the version you care

Re: What is the use case for UNLOGGED tables

On Tuesday, August 14, 2018, Ravi Krishna wrote: > Then what exactly is the benefit of UNLOGGED tables, unless we have > permanent unlogged tables for disposable > data. > I use them for "ELT" oriented processing where the final results get stored on permanently logged tables but I want to

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

On Tuesday, August 28, 2018, Shaun Savage wrote: > I want to dynamically return a column from a function. > Variable object identifiers requires executing dynamic SQL. See: https://www.postgresql.org/docs/10/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN David J.

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

On Tuesday, August 28, 2018, Pavel Stehule wrote: > 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. > > > Personally, your design is unahappy - against to

Re: Will there ever be support for Row Level Security on Materialized Views?

On Mon, Aug 27, 2018 at 4:06 PM, Ken Tanzer wrote: > People on this list generally seem pretty generous in spirit and sharing > of their knowledge, insights and opinions. It seemed a pretty reasonable > and typical question to ask. I guess if there's no answer to be had, then > so be it! >

Re: Can Pg somehow recognize/honor linux groups to control user access ?

On Wed, Aug 22, 2018 at 9:38 AM, Ravi Krishna wrote: > I am not sure this is the same what I was thinking about. For example, in > db2, connect privilege can be granted to a group and so is grant privilege > as in > GRANT SELECT ON TABLE SCHEMA.TABLE TO GROUP ABC; > GRANT CONNECT ON DATABASE TO

Re: Postgres function with output parameters and resultset

On Friday, July 20, 2018, Arulalan Narayanasamy < arulalan.narayanas...@gmail.com> wrote: > Hi, > I need to create a function which should return resultset and output > parameters. For example, I need to retrieve all the records from EMP table > whose Grade is 'A' as resultset and total number of

Re: User documentation vs Official Docs

On Friday, July 20, 2018, Joshua D. Drake wrote: > > I was hoping to get the -general community to step and build some recipes > and howto articles without at the same time dictating the solution. That's > a good thing because a non-dictated solution is likely to have more > strength. > People

Re: control over database files

On Tuesday, July 17, 2018, Aaron Gray wrote: > > I am wanting to be able to have control over what files that ables are > stored in. Basically I am looking to have literally tens of thousands of > tables all of the same type and to be able to store these as separate files > in a filing system. >

Re: Building a notification system.

On Sunday, July 15, 2018, Anto Aravinth wrote: > > I'm not sure, how to get started with this. Read about NOTIFY: > https://www.postgresql.org/docs/current/static/sql-notify.html > > > Not sure that fits my use case, thanks for your help in this. > It doesn't. You need to record time stamps for

Re: Building a notification system.

On Sunday, July 15, 2018, David G. Johnston wrote: > On Sunday, July 15, 2018, Anto Aravinth > wrote: >> >> I'm not sure, how to get started with this. Read about NOTIFY: >> https://www.postgresql.org/docs/current/static/sql-notify.html >> >> >> Not s

Re: User documentation vs Official Docs

On Mon, Jul 16, 2018 at 1:32 PM, Joshua D. Drake wrote: > -general. > > Over the last year as I have visited many meetups and interacted with > people at conferences etc... There are three prevailing issues that > continue to come up in contributing to the community. This email is about > one of

Re: User documentation vs Official Docs

On Mon, Jul 16, 2018 at 3:19 PM, Joshua D. Drake wrote: > On 07/16/2018 03:14 PM, David G. Johnston wrote: > > > What does the community think about a community run, community organized, >> sub project for USER documentation? This type of documentation would be >>

Re: searching a value in a variable/field in all tables in a schema

On Thu, Aug 30, 2018 at 1:44 PM, Ruiqiang Chen wrote: > Does anyone have experience of searching a value in a variable/field in > all tables in a schema? > SELECT 'Tbl1'::text AS loc, fld FROM schema.tbl1 WHERE fld = 'value' [UNION ALL SELECT 'Tbl2'::text AS loc, fld FROM schema.tbl2 WHERE fld

Re: Issue with psqlrc with command line.

On Wed, Aug 29, 2018 at 8:31 AM, saurabh shelar wrote: > Hi David, > > Thank you for the help. > > I was just got confused with the below line mentioned in the document. > However, it seems it is still the same behaviour *(i.e --no-psqlrc)*. > > *"Before PostgreSQL 9.6, the -c option implied -X

Re: Executing a Function with an INSERT INTO command fails

On Wed, Aug 29, 2018 at 9:27 AM, TalGloz wrote: > This is very strange, even if I comment all the loops in the function and > leave only the INSERT INTO command the insert still doesn't happen. > Add "RAISE ERROR" to the top function's BEGIN block and verify that the next time you run your

Re: Issue with psqlrc with command line.

On Wednesday, August 29, 2018, saurabh shelar wrote: > Hi David, > > Thank you for the swift response. > > However, could you please confirm if the below scenario is expected. > > - included the alias in the file. > - And passed the file with psql and it worked. > > *-bash-4.2$ cat test * >

Re: Issue with psqlrc with command line.

On Wednesday, August 29, 2018, saurabh shelar wrote: > > As per the document from PG-9.6 it is possible. > You cannot use psql variables with -c “ command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single

Re: Executing a Function with an INSERT INTO command fails

On Wed, Aug 29, 2018 at 9:47 AM, TalGloz wrote: > > > Add "RAISE ERROR" to the top function's BEGIN block and verify that the > > next time you run your calling query it indeed fails. I'm suspecting > that > > the code you are iterating over is not the same code that is being > > executed > >

Re: Return select statement with sql case statement

On Wednesday, July 4, 2018, Ron wrote: > > Ah, didn't notice that. Then... dynamic sql constructed by the > programming language executing the query? > That, the UNION idea, or pull the common stuff into the from clause and write two left joins then coalesce whichever one provided the row. In

Re: FK v.s unique indexes

On Thursday, July 5, 2018, Rafal Pietrak wrote: > > W dniu 04.07.2018 o 00:55, David G. Johnston pisze: > > On Tuesday, July 3, 2018, Rafal Pietrak > <mailto:ra...@ztk-rp.eu>> wrote: > > > > > > ERROR: there is no unique constraint matching given

Split daterange into sub periods

On Thursday, July 5, 2018, hmidi slim wrote: > > I got this error: > > > > *ERROR: result of range difference would not be contiguous* > > Is there any operators to make the split of daterang > > To refine what Adrian said, operators cannot return a setof result so this is basically impossible.

Re: As a table owner, can I grant "grant" ?

On Thu, Jul 5, 2018 at 9:58 AM, David Gauthier wrote: > Postgres 9.5.2 on linux > > Given that I am not superuser, but DO have createrole... > Can I grant some other role the ability to grant access to a table I > created ? > > For Example: I create a table called foo. As the creator/owner of

Re: How to watch for schema changes

On Thu, Jul 5, 2018 at 10:07 AM, Igor Korot wrote: > On Thu, Jul 5, 2018 at 11:49 AM, David G. Johnston > wrote: > > > You can, and depending on how often you intend to execute said code, it > is > > probably the better way. It also requires pl/pgsql while CREATE OR &

Re: How to watch for schema changes

On Thu, Jul 5, 2018 at 8:40 AM, Igor Korot wrote: > Hi, David, > > On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston > wrote: > > On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote: > >> > >> > >> I presume threre is a query which check for the fu

Re: FK v.s unique indexes

On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak wrote: > I was thinking, that when "add constraint" cannot choose appropriate > index, may be some explicit help (like ... using ;) would be > due. > ​Basically all the FK trigger does is: SELECT EXISTS(SELECT 1 FROM pk_table WHERE pk_col1 = val1

Re: FK v.s unique indexes

On Thu, Jul 5, 2018 at 2:36 PM, Rafal Pietrak wrote: > > > W dniu 05.07.2018 o 23:04, David G. Johnston pisze: > > On Thu, Jul 5, 2018 at 1:45 PM, Rafal Pietrak > <mailto:ra...@ztk-rp.eu>>wrote: > > > > I was thinking, that when "add constraint&q

Re: How to watch for schema changes

On Mon, Jul 9, 2018 at 1:49 PM, Igor Korot wrote: > Just a thought... > Is it possible to create a trigger for a system table? > Not sure, and doesn't seem documented either way, but seems easy enough to try on a test cluster... ​[...]​ > Successful "CREATE TABLE..." statement creates a row

Re: How to tell which event was fired in Trigger function

On Wed, Jul 11, 2018 at 10:54 AM, Igal @ Lucee.org wrote: > On 7/11/2018 10:38 AM, Adrian Klaver wrote: > >> On 07/11/2018 10:36 AM, Igal @ Lucee.org wrote: >> >>> How can I tell inside the trigger function if the event was DELETE or >>> INSERT/UPDATE? >>> >> >>

Re: Using CTE vs temporary tables

On Wed, Jul 11, 2018 at 9:35 AM, Ravi Krishna wrote: > ​Does temp tables also suffer from optimization fence we see in CTE.​ > >> ​I suppose it depends on how they end up being referenced in the query. It is not possible for the auto-vacuum daemon to vacuum/analyze them so if you aren't doing

Re: Using CTE vs temporary tables

On Wed, Jul 11, 2018 at 9:30 AM, hmidi slim wrote: > Hi, > I have a big query that used about 15 cte and its execution time is > acceptable. I'm trying to optimize my query because it contains about 150 > lines of code and becomes hard to understand it and add new filter or > condition easily. >

Re: timestamp (military) at time zone without the suffix

On Wednesday, July 11, 2018, David Gauthier wrote: > Hi: > > I would like to get the utc timestamp, 24-hr clock (military time), > without the time zone suffix. > > Below commands were run nearly at the same time... > > sqfdev=> select now()::timestamp(0) ; > now >

Re: timestamp (military) at time zone without the suffix

On Wednesday, July 11, 2018, David Gauthier wrote: > OK, the "to_char" gets rid of the timezone extension. But the times still > don't make sense. > > When I go to store this in a DB, I want to store the UTC time. How d I do > that ? > Use the data type that represents exactly that,

Re: sorting/comparing column values in non-alphanumeric sorting ways ?

On Wednesday, July 11, 2018, David Gauthier wrote: > > This won't work... > update tv set greatest = greatest(tcfg1,tcfg2,tcfg3) > ...because it thinks 1.0.9 is greater than 1.0.10 > > Is there a way to get this to work right ? > > Haven't used it personally but this seems promising:

Re: timestamp (military) at time zone without the suffix

On Wednesday, July 11, 2018, David Gauthier wrote: > > I want to store the current UTC date/time in the DB. Does PG > unconditionally store something like UTC, then let the queries figure out > how they want to look at it (with "at time zone" and "to_char()" etc...) ? > Or do I have to

Re: How to watch for schema changes

On Thu, Jul 12, 2018 at 8:50 AM, Igor Korot wrote: > > No, see: > > > > https://www.postgresql.org/docs/10/static/sql-createtrigger.html > > > > AFTER trigger on views are STATEMENT level only. > > But I do have access to the STATEMENT right? > ​Yes, except nothing in the system actually

Re: How to watch for schema changes

On Tue, Jul 3, 2018 at 10:21 AM, Igor Korot wrote: > Hi, ALL, > Is there any trigger or some other means I can do on the server > which will watch for CREATE/ALTER/DROP TABLE command and after successful > execution of those will issue a NOTIFY statement? > ​You just asked this question two

Re: How to watch for schema changes

On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot wrote: > ​​ > > I presume threre is a query which check for the function/trigger > existence? Something like: > > IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION; > ​CREATE OR REPLACE is how you re-create a function that (whose

<    1   2   3   4   5   6   7   8   9   10   >