Re: [GENERAL] column names and dollar sign

Thanks Tom Armand On May 17, 2017, at 4:14 PM, Tom Lane wrote: > "Armand Pirvu (home)" writes: >> Ran into the following statement > >> CREATE TABLE test( >> Date$ date, >> Month_Number$ int, >> Month$ varchar(10), >>

Re: [GENERAL] column names and dollar sign

Thank you Steve — Armand On May 17, 2017, at 4:10 PM, Steve Atkins wrote: > >> On May 17, 2017, at 2:02 PM, Armand Pirvu (home) >> wrote: >> >> Hi >> >> Ran into the following statement >> >> CREATE TABLE test( >> Date$ d

[GENERAL] column names and dollar sign

Hi Ran into the following statement CREATE TABLE test( Date$ date, Month_Number$ int, Month$ varchar(10), Year$ int ); While it does execute, I wonder if the $ has any special meaning ? Can anyone shed some light please ? Thanks Armand -- Sent via pgsql-

[GENERAL] pglogical and slony

Thanks to Adrian I got pointed in the direction of enabling triggers to use with pglogical In other words, whatever comes down the pglogical in terms of inserts/updates/deletes, I can track those in some audit table or do something else That got me thinking why not putting at the end of pglogi

Re: [GENERAL] views and fdw usage and performance

On May 9, 2017, at 7:11 PM, Adrian Klaver wrote: > On 05/09/2017 05:02 PM, armand pirvu wrote: >> Well >> Jt1 is prod and jt2 is dev > > You are talking schemas, not databases, correct? > > Correct >> Before someone pushes to prod it does work in dev. The jdbc connection > > That would co

[GENERAL] views and fdw usage and performance

Hi I have two schemas jt1, and jt2 in the same db In both I have the same table tbl3 The idea is to keep in sync jt1.tbl3 from jt2.tbl3 each time I have an insert/update/delete on jt2.tbl3 So I was thinking about the following cases to avoid replication 1) in jt2 rather than have the tbl3 tab

Re: [GENERAL] data transformation and replication

9.5 both But the enable always trigger I missed that Once that set it runs Thank you for your help Armand On May 9, 2017, at 8:26 AM, Adrian Klaver wrote: > On 05/08/2017 08:31 PM, Armand Pirvu (home) wrote: >> My bad >> db1 I have two tables t1 and t2 (or more) >>

Re: [GENERAL] data transformation and replication

. Any pointers ? Thanks Armand On May 8, 2017, at 4:49 PM, Adrian Klaver wrote: > On 05/08/2017 12:46 PM, Armand Pirvu (home) wrote: >> Hi >> >> Here it is a scenario which I am faced with and I am hoping to find a >> pointer/tip/help >> >> db1 is th

[GENERAL] data transformation and replication

Hi Here it is a scenario which I am faced with and I am hoping to find a pointer/tip/help db1 is the OLTP system db2 is the Reporting system The data from db1 needs to get to db2, but the database on those two have tables with different layout/structure and hence data will need to suffer som

Re: [GENERAL] getting column names

Thanks David Worked like a charm and results are correct Armand On Apr 4, 2017, at 5:00 PM, David G. Johnston wrote: > On Tue, Apr 4, 2017 at 2:51 PM, Armand Pirvu (home) > wrote: > > But if I join back to foo1 like below I get > > select a.audit_id, a.table_name

[GENERAL] getting column names

Hi Can somebody please tell me if the below is possible ? I may not see the forest from the trees Thanks Armand levregdb=# select * from foo1; audit_id | table_name --+ 6012 | foo2 6013 | foo2 6014 | foo2 select * from foo2; levregdb=# select * from

Re: [GENERAL] store key name pattern search

Thank you Jeff Nice . I also looked at akeys/skeys My goal is to get the key by pattern matching and then passing it back like in col1->key Armand On Apr 4, 2017, at 12:42 PM, Jeff Janes wrote: > On Tue, Apr 4, 2017 at 8:41 AM, Armand Pirvu (home) > wrote: > Hi >

Re: [GENERAL] store key name pattern search

Thank you — Armand On Apr 4, 2017, at 10:50 AM, Oleg Bartunov wrote: > > On Tue, Apr 4, 2017 at 11:41 AM, Armand Pirvu (home) > wrote: > Hi > > I have the following case > > > select * from foo; >

[GENERAL] store key name pattern search

Hi I have the following case select * from foo; col1 - "show_id"=>"1", "group_id"=>"32", "group_name"=>"slb", "group_add_by"=>"557651" "

Re: [GENERAL] audit function and old.column

by%’ it would be great In this case I could go around like it I will post another question related to hstore search since although it has a common ground it is different regarding the goal Thanks Armand On Mar 23, 2017, at 6:38 PM, Adrian Klaver wrote: > On 03/23/2017 02:00 PM, Armand

[GENERAL] audit function and old.column

Hello I am using in a project the audit trigger from the wiki One particular aspect is that not all tables that I am after have the same columns . And it is one in particular I am stumbling onto , and it ends i n _add_by. It can be group_add_by, car_add_by and so on. The old value of this colu

Re: [GENERAL] variable value in array_to_string

(and never will) from my own mom let alone somebody else My apologies for asking help. Will try not to do it again Thanks and laters On Nov 21, 2016, at 7:12 PM, David G. Johnston wrote: > Please don't top-post - it makes following the thread a lot harder. > > On Mon, Nov 21

Re: [GENERAL] variable value in array_to_string

Played with unnest but not much luck NOTICE: {item_id,show_id} NOTICE: item_id It takes only the first array element in consideration Ug On Nov 21, 2016, at 5:02 PM, Armand Pirvu (home) wrote: > My bad on the back tick. No idea why it turned that way > > OK got t

Re: [GENERAL] variable value in array_to_string

here on I was looking to get say a.item_id , a.show_id and all sort of variations Thanks for help On Nov 21, 2016, at 4:26 PM, David G. Johnston wrote: > On Mon, Nov 21, 2016 at 3:09 PM, Armand Pirvu (home) > wrote: > Hi > > Is there anyway I can pass a variable in the

[GENERAL] variable value in array_to_string

Hi Is there anyway I can pass a variable in the array_to_string function ? CREATE OR REPLACE FUNCTION test1 () RETURNS void AS $$ DECLARE foo text; foo1 text; begin execute 'select ARRAY( SELECT d.COLUMN_NAME::text from information_schema.constraint_table_usage c, information_schema.key_co

Re: [GENERAL] functions - triggers cross schemas

rvu (home) > wrote: > 2 schemas , schema1 and schema2, with same tables , a base table and a > tracking one. > > > Search path is set to schema1 by default. > insert into schema2.test1 (col2 , col3) values ('foo1','foo2') > I get an entry in s

[GENERAL] functions - triggers cross schemas

2 schemas , schema1 and schema2, with same tables , a base table and a tracking one. Search path is set to schema1 by default. insert into schema2.test1 (col2 , col3) values ('foo1','foo2') I get an entry in schema1.test1_hist and not in schema2.test1_hist I understand that the trigger inhe

Re: [GENERAL] executing os commands from a function

, David G. Johnston wrote: > On Thu, Sep 29, 2016 at 2:41 PM, Armand Pirvu (home) > wrote: > I used plsh extension but I had to use two functions and a trigger, see code > below > > ​I don't see any way to not use a trigger given your requirement. And as > written

[GENERAL] executing os commands from a function

functions and a trigger, see code below CREATE or REPLACE FUNCTION func2 (var1 text) RETURNS text AS ' #!/bin/bash touch /home/postgres/$1; ' LANGUAGE plsh; commit; CREATE FUNCTION func1() RETURNS trigger AS ' BEGIN perform func2(NEW.col1); RETURN NEW; END; ' LANGUAGE plpgsql; C

[GENERAL] jsonb search

installed the extension 1 - Exact matching without knowing the hierarchy, just the key and element, I built a set like col1 | col2 --+-- 1 | {"Home Email": {"EmailAddress&qu

Re: [GENERAL] stored proc

On 29/09/2011 02:34, J.V. wrote: Is it possible to group a bunch of methods and functions into a single file (stored procedures & functions) and have a main method that can be called to orchestrate the methods, pass in params, get back results, log to a file? I know this can be done with Oracle

Re: [GENERAL] Oracle to Postgres migration open source tool

On 07/07/2011 15:21, salah jubeh wrote: > > Hello, > > I do no have any experience with oracle, try to dump the oracle database in plain format and then try to execute the DDL and DML statements. It might be cumbersome to fix all the errors you might get. > But as an initial solution give it a s

Re: [GENERAL] Understanding Schema's

On 15/12/2010 00:20, Carlos Mennens wrote: Why would anyone in a random scenario want to have independent schema's to cross query? I'm just trying to see how this would be useful in any scenario. One very real example :-) When migrating from say mysql to PgSQL it is possible to populate a "myg

Re: [GENERAL] Re: Modfying source code to read tuples before and after UPDATE...how to?

rmd22 wrote: Do you think it's possible? replied offlist suggesting he have a chat with his prof and find the reason behind this task... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] create table as select VS create table; insert as select

I have inherited an application that populates a number of temp.y tables using create table ... as select ... This is taking roughly five to ten minutes to run As this process hammers the database, I can only run benchmarks at night so am asking here if anyone know if create table ...; then ins

[GENERAL] SPI high level abstraction?

Is there any C libs out there that will abstract the SPI interface? I have a number of functions that do input process output and they are all almost identical (tablename fields, change) I could abstract the code but would prefer to re-use an exissting abstraction layer as the less code I write

Re: [GENERAL] pgtune

Greg Smith wrote: Set "-c 300" when you run pgtune and it will do the right thing here. regarding tuning a database we had a rather neat tool for oracle some years ago that would connect to a live database and monitor the QEP (query execution plan) cache for badly indexed queries etc. It would

Re: [GENERAL] MySQL versus Postgres

Randal L. Schwartz wrote: Do not overemphasize the example mode at the cost of presenting concepts or structure. You need all three. Hmm I recently had to find out the various options to get values from a tuple via SPI - referred by docs to the contrib examples :-( Docs did not help and the ex

Re: [GENERAL] How Big is Too Big for Tables?

P Kishor wrote: On Wed, Jul 28, 2010 at 1:38 PM, Stephen Frost wrote: * P Kishor (punk.k...@gmail.com) wrote: Three. At least, in my case, the overhead is too much. My data are single bytes, but the smallest data type in Pg is smallint (2 bytes). That, plus the per row overhead adds to a fair

Re: [GENERAL] Migrating from MySQL

John R Pierce wrote: On 06/26/10 11:22 PM, RP Khare wrote: I downloaded PostgreSQL plus advanced server from EnterpriseDB website. Should I go with the original community PGSQL edition or EnterpriseDB edition? thats entirely up to your preferences and needs.Personally, I think that the s

[GENERAL] subscribe

subscribe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] permissions on user-defined functions

I am new to PostgreSQL... I've created a table (table1) and database user (dbuser). dbuser has only SELECT permission on table1. Next I created a function insert_table1 that inserts a new row into table1. I want to give dbuser the ability to invoke the insert_table1 function without granting dbu