Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

need to return query with alias *example:* create table emp (id integer, ename text); insert into emp values(1, 'aaa'); create or replace function f_sample1() returns table (id integer, ename text) as $$ declare begin return query select id, ename from emp; end$$ language plpgsql; select

Re: [GENERAL] Sub-query having NULL row returning FALSE result

NOT IN (SELECT DocumentTypeID FROM relDocumentTypeMetaDataName WHERE DocumentTypeID IS NOT NULL); Thanks Sridhar OpenText On Wed, Jun 29, 2016 at 6:04 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jun 29, 2016 at 2:07 AM, Sridhar N Bamandlapally < > sridhar@g

Re: [GENERAL] table name size

M, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 13, 2016 at 7:21 AM, Sridhar N Bamandlapally < > sridhar@gmail.com> wrote: > >> Hi >> >> how to create table name with size, the limitation we are facing is 63 >> length &g

Re: [GENERAL] Sub-query having NULL row returning FALSE result

wrote: > Sridhar N Bamandlapally <sridhar@gmail.com> writes: > > postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR); > > postgres=# INSERT INTO emp VALUES (null, 'aaa'); > > ... > > postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id

[GENERAL] Sub-query having NULL row returning FALSE result

Hi Please go through below case postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR); CREATE TABLE postgres=# INSERT INTO emp VALUES (null, 'aaa'); INSERT 0 1 postgres=# INSERT INTO emp VALUES (null, 'bbb'); INSERT 0 1 postgres=# INSERT INTO emp VALUES (3, 'ccc'); INSERT 0 1 postgres=#

[GENERAL] table name size

Hi how to create table name with size, the limitation we are facing is 63 length these are dynamic tables created from application issue is: we cannot suggest/tell client to change NAMEDATALEN constant in src/include/pg_config_manual.h do we have any other option, please Thanks Sridhar

Re: [GENERAL] [HACKERS] Online DW

, every online archive db is use case for this. Thanks Sridhar Opentext On 10 Jun 2016 22:36, "David G. Johnston" <david.g.johns...@gmail.com> wrote: > On Fri, Jun 10, 2016 at 4:11 AM, Sridhar N Bamandlapally < > sridhar@gmail.com> wrote: > >> Hi >>

Re: [GENERAL] [HACKERS] Online DW

One thing we can restrict to "begin noarchive" transaction block are DELETE and SELECT only Sridhar Opentext On 10 Jun 2016 21:57, "Sridhar N Bamandlapally" <sridhar@gmail.com> wrote: > This is what I feel will give me solution to maintain production > (cu

Re: [GENERAL] [HACKERS] Online DW

10 June 2016 at 18:56, John R Pierce <pie...@hogranch.com> wrote: > >> On 6/10/2016 2:18 AM, Sridhar N Bamandlapally wrote: >> >>> This/These will be performed in Production to clean-up archive which >>> will not be sync with Archive/DW DB only >&

Re: [GENERAL] [HACKERS] Online DW

is Archive/DW DB may need to build WITHOUT CONSTRAINTS May need to introduce ARCHIVE system/tag in pg_hba.conf Thanks Sridhar OpenText On Fri, Jun 10, 2016 at 2:22 PM, Craig Ringer <cr...@2ndquadrant.com> wrote: > On 10 June 2016 at 16:11, Sridhar N Bamandlapally

[GENERAL] Online DW

Hi Is there any feature in PostgreSQL where online DW (Dataware housing) is possible ? am looking for scenario like 1. Production DB will have CURRENT + LAST 7 DAYS data only 2. Archive/DW DB will have CURRENT + COMPLETE HISTORY expecting something like streaming, but not ETL Thanks Sridhar

Re: [GENERAL] [HACKERS] OUT parameter and RETURN table/setof

g Thanks Sridhar OpenText On Mon, Jun 6, 2016 at 5:57 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 6, 2016 at 7:17 AM, Sridhar N Bamandlapally < > sridhar@gmail.com> wrote: > >> Hi >> >> Is there any option in PGPLS

[GENERAL] OUT parameter and RETURN table/setof

Hi Is there any option in PGPLSQL which can RETURNS table or SETOF rows along with an OUT parameter? please Thanks Sridhar OpenText

Re: [GENERAL] UUID datatype

This I got, need some implicit way, like maybe in RULE on SELECT can we write this ? Thanks Sridhar OpenText On Mon, May 30, 2016 at 1:05 PM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Mon, May 30, 2016 at 4:25 PM, Sridhar N Bamandlapally > <sridhar@gmail.com

[GENERAL] UUID datatype

Hi Is there a way to implicit SELECT on UUID datatype in uppercase ? Please Thanks Sridhar

Re: [GENERAL] Connections - Postgres 9.2

Hi I control this way if "state_change" is from longtime and "state" is idle then I use function: *pg_terminate_backend ( integer ) * ==> return TRUE if killed-successful else FALSE example: # select pg_terminate_backend ( pid ) from pg_stat_activity where state='idle' and state_change <

Re: [GENERAL] NULL concatenation

o Sridhar, > > Have you tried the 'coalesce' function to handle the nulls? > > > Kind Regards, > > Adam Pearson > -- > *From:* pgsql-general-ow...@postgresql.org < > pgsql-general-ow...@postgresql.org> on behalf of Sridhar N Bamandla

Re: [GENERAL] [HACKERS] NULL concatenation

Thanks Pavel Great !! I was thinking both || and CANCAT does same Thanks again - Sridhar OpenText On Thu, May 12, 2016 at 2:22 PM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > Hi > > 2016-05-12 10:47 GMT+02:00 Sridhar N Bamandlapally <sridhar@gmai

[GENERAL] NULL concatenation

Hi In migration, am facing issue with NULL concatenation in plpgsql, by concatenating NULL between any where/position to Text / Varchar, the total string result is setting value to NULL *In Oracle:* declare txt1 VARCHAR2(100) := 'ABCD'; txt2 VARCHAR2(100) := NULL; txt3

Re: [GENERAL] pg_largeobject

abled. Segment size cannot be larger than 1GB" ) Thanks Sridhar On Tue, Mar 29, 2016 at 9:01 PM, Daniel Verite <dan...@manitou-mail.org> wrote: > Sridhar N Bamandlapally wrote: > > > due to size limitation BYTEA was not considered > > You could adopt for a custo

Re: [GENERAL] pg_largeobject

954183248 > Website: www.ocs.pe > > Sent from my Sony Xperia™ smartphone > > > Sridhar N Bamandlapally wrote > > > all media files are stored in database with size varies from 1MB - 5GB > > based on media file types and user-group we storing in differe

Re: [GENERAL] pg_largeobject

was not considered Thanks Sridhar On Tue, Mar 29, 2016 at 3:05 PM, John R Pierce <pie...@hogranch.com> wrote: > On 3/29/2016 2:13 AM, Sridhar N Bamandlapally wrote: > >> Hi >> >> pg_largeobject is creating performance issues as it grow due to single &

[GENERAL] pg_largeobject

Hi pg_largeobject is creating performance issues as it grow due to single point storage(for all tables) is there any alternate apart from bytea ? like configuration large-object-table at table-column level and oid PK(primary key) stored at pg_largeobject Thanks Sridhar

[GENERAL] Nested funtion

Hi Is there any way to create nested function? oracle to postgres migration required super function variable reference into nested function without nested function parameter Oracle sample: --- create or replace function f1(n number) return number is vs number:=1;

[GENERAL] Rules on View

Hi Is there a way to avoid creating rule under creation of view ? please let me know Thanks Sridhar

Re: [GENERAL] [JDBC] JDBC behaviour

Ok, let me put this way in JDBC we have *setAutoCommit( false ) *, and all dmls are independent transactions and when any transaction fails then the session not allowing next transactions in Java when we do setAutoCommit( false ) its behaving like all transactions in BEGIN-END block, this is

Re: [GENERAL] [JDBC] JDBC behaviour

If we want transactions in "begin-end" then its fine, but in this case all these transactions are independent with autocommit off, user choice to continue with commit or rollback Thanks Sridhar On Thu, Feb 18, 2016 at 1:43 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > >Is it

Re: [GENERAL] JDBC behaviour

Hi We are facing issue with PostgreSQL JDBC behaviour in Java, under autocommit false mode, 1. In between if any transaction then for next transaction, throws exception saying "current transaction is aborted, commands ignored until end of transaction block" 2. Even if exception is suppressed

[GENERAL] JDBC behaviour

Hi We are facing issue with PostgreSQL JDBC behaviour in Java, under autocommit false mode, 1.in between if any transaction

[GENERAL] How to drop stats on table

Hi is there any feature available in postgres to drop stats on table? thanks Sridhar

Re: [GENERAL] [ADMIN] How to drop stats on table

<laurenz.a...@wien.gv.at> wrote: > Sridhar N Bamandlapally wrote: > > is there any feature available in postgres to drop stats on table? > > What about > > DELETE FROM pg_catalog.pg_statistic WHERE starelid = > > Yours, > Laurenz Albe >

Re: [GENERAL] avoid lock conflict between SELECT and TRUNCATE

as there is no option for incremental update/insert on user and renaming will have app query errors I guess 1) creating temporary table (say temp_users) on table users with required data/columns-list and index on column user_id, ...this will be faster as there will be no joins with other

[GENERAL] PostgreSQL customer list

Hi I need some top 10 customers list in financial or healthcare domain ( irrespective of open-source or proprietary tool ) We need to showcase to our customer for building analytical database Please do share, it will be really helpful Thanks Sridhar BN