Re: [GENERAL] Taking lot time
When this thread started you said you where selecting 20 rows.
Yes.
Which is correct 20 rows or columns and if columns how many rows?
Yes,I did selected 20 columns from two tables.
Where are you measuring this time?
Here is the query plan,
http://explain.depesz.com/s/EeYT
Any Help appreactiated !
On Thu, Nov 26, 2015 at 12:04 AM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 11/25/2015 07:46 AM, Ramesh T wrote:
>
> First, please take a look at this:
>
> https://en.wikipedia.org/wiki/Posting_style
>
>
>> Hi All,
>> 9.3 version using pgadmin3
>>
>> Query like below,20 columns fetching
>> SELECT col1,col2,col3,...col.20
>>
>
> When this thread started you said you where selecting 20 rows.
> Which is correct 20 rows or columns and if columns how many rows?
>
> FROM detail i,
>> adjdetail ia,
>>WHERE i.trans_id = ia.detail_id AND
>> (i.event = ANY (ARRAY[21, 22, 3, 5]))
>> created indexes on where clause declared columns.
>>
>> Taking a lot of time above ..?any help apprectiated !.
>>
>
> A lot of time being?
>
> Where are you measuring this time?
> Remember populating a GUI with lots of data can take time.
>
>
>> I have total 4 gb ram,i changed below in postgres.conf
>>
>> shared_buffers--1024mb
>> temp_bufffers=8mb
>> work_mem=200mb
>> maintanace_work_mem=500mb
>> seq_page_cost = 1.0
>> random_page_cost = 5.0
>> effective_cache_size = 1024MB
>>
>>
>>
>> On Wed, Nov 18, 2015 at 11:12 PM, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 11/17/2015 04:18 AM, Ramesh T wrote:
>>
>> the query is big it's selecting 20 rows from two table like i
>> mentioned
>> above exaplain analyze
>>
>> what should i do..?any help
>>
>>
>> Please do not top post.
>>
>> I must be missing a post, as I see no explanation of what the query
>> is doing.
>>
>>
>> On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver
>> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>> wrote:
>>
>> On 11/03/2015 06:42 AM, Ramesh T wrote:
>>
>> I have a Query it taking a lot of time to fetch results
>> so,explain query gave
>>
>> "Hash Join (cost=55078.00..202405.95 rows=728275
>> width=418)"
>> " Hash Cond: (itd.tran_id = iad._adj__id)"
>> " -> Seq Scan on inv_detail itd (cost=0.00..40784.18
>> rows=731029
>> width=95)"
>> "Filter: (event_type = ANY
>> ('{21,22,3,5}'::integer[]))"
>> " -> Hash (cost=20590.78..20590.78 rows=610978
>> width=331)"
>> "-> Seq Scan on inv_adj iad
>> (cost=0.00..20590.78
>> rows=610978
>> width=331)"
>>
>> Can you Please let me know wt happen in query..?wt
>> should i do..
>>
>>
>> And the query is?
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>> <mailto:adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
Re: [GENERAL] Taking lot time
Hi All,
9.3 version using pgadmin3
Query like below,20 columns fetching
SELECT col1,col2,col3,...col.20
FROM detail i,
adjdetail ia,
WHERE i.trans_id = ia.detail_id AND
(i.event = ANY (ARRAY[21, 22, 3, 5]))
created indexes on where clause declared columns.
Taking a lot of time above ..?any help apprectiated !.
I have total 4 gb ram,i changed below in postgres.conf
shared_buffers--1024mb
temp_bufffers=8mb
work_mem=200mb
maintanace_work_mem=500mb
seq_page_cost = 1.0
random_page_cost = 5.0
effective_cache_size = 1024MB
On Wed, Nov 18, 2015 at 11:12 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 11/17/2015 04:18 AM, Ramesh T wrote:
>
>> the query is big it's selecting 20 rows from two table like i mentioned
>> above exaplain analyze
>>
>> what should i do..?any help
>>
>
> Please do not top post.
>
> I must be missing a post, as I see no explanation of what the query is
> doing.
>
>>
>> On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver <adrian.kla...@aklaver.com
>> <mailto:adrian.kla...@aklaver.com>> wrote:
>>
>> On 11/03/2015 06:42 AM, Ramesh T wrote:
>>
>> I have a Query it taking a lot of time to fetch results
>> so,explain query gave
>>
>> "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)"
>> " Hash Cond: (itd.tran_id = iad._adj__id)"
>> " -> Seq Scan on inv_detail itd (cost=0.00..40784.18
>> rows=731029
>> width=95)"
>> "Filter: (event_type = ANY ('{21,22,3,5}'::integer[]))"
>> " -> Hash (cost=20590.78..20590.78 rows=610978 width=331)"
>> "-> Seq Scan on inv_adj iad (cost=0.00..20590.78
>> rows=610978
>> width=331)"
>>
>> Can you Please let me know wt happen in query..?wt should i do..
>>
>>
>> And the query is?
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
Re: [GENERAL] Taking lot time
the query is big it's selecting 20 rows from two table like i mentioned
above exaplain analyze
what should i do..?any help
On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 11/03/2015 06:42 AM, Ramesh T wrote:
>
>> I have a Query it taking a lot of time to fetch results
>> so,explain query gave
>>
>> "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)"
>> " Hash Cond: (itd.tran_id = iad._adj__id)"
>> " -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029
>> width=95)"
>> "Filter: (event_type = ANY ('{21,22,3,5}'::integer[]))"
>> " -> Hash (cost=20590.78..20590.78 rows=610978 width=331)"
>> "-> Seq Scan on inv_adj iad (cost=0.00..20590.78 rows=610978
>> width=331)"
>>
>> Can you Please let me know wt happen in query..?wt should i do..
>>
>
> And the query is?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
[GENERAL] Taking lot time
I have a Query it taking a lot of time to fetch results
so,explain query gave
"Hash Join (cost=55078.00..202405.95 rows=728275 width=418)"
" Hash Cond: (itd.tran_id = iad._adj__id)"
" -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029
width=95)"
"Filter: (event_type = ANY ('{21,22,3,5}'::integer[]))"
" -> Hash (cost=20590.78..20590.78 rows=610978 width=331)"
"-> Seq Scan on inv_adj iad (cost=0.00..20590.78 rows=610978
width=331)"
Can you Please let me know wt happen in query..?wt should i do..
Re: [GENERAL] postgres function
'123-987-123' it is not fixed some times it may be '1233-9873-123-098'
as you said it's fixed,
changes the values in middle of the -
sometimes times i need 1233 and 098 or 9873,first position i'll find
direct for second variable we don't know where it's end with -
i.e ,
i need to find second postition of the variable between the '-'
On Thu, Oct 15, 2015 at 6:32 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Oct 15, 2015 at 8:32 AM, Ramesh T <rameshparnandit...@gmail.com>
> wrote:
>
>> select position('-' in '123-987-123')
>> position
>> ---
>> 4
>> But I want second occurrence,
>> position
>> -
>> 8
>>
>> plz any help..?
>>
>>
>
> SELECT length((regexp_matches('123-987-123', '(\d{3}-\d{3}-)\d{3}'))[1])
>
>
> David J.
>
>
Re: [GENERAL] postgres function
select position('-' in '123-987-123')
position
---
4
But I want second occurrence,
position
-
8
plz any help..?
On Thu, Oct 15, 2015 at 12:54 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Oct 14, 2015 at 9:38 AM, Ramesh T <rameshparnandit...@gmail.com>
> wrote:
>
>> Hi All,
>> Do we have function like regexp_substr in postgres..?
>>
>> in oracle this function seach the - from 1 to 2 and return result,
>> regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2)
>>
>
> Maybe one of the functions on this page will get you what you need.
>
> http://www.postgresql.org/docs/devel/static/functions-string.html
>
> David J.
>
>
>
>
Re: [GENERAL] postgres function
yes David gave correct solution
but , the value I'm using and it's column in the table sometimes value
may be '123-987-123' or '123-987-123-13-87'
if pass like below must return else condiion 0,
select case when select split_part('123-987-123','-',4) >0
then 1 else 0 end
it's return error like integer need...
On Thu, Oct 15, 2015 at 8:50 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Oct 15, 2015 at 10:05 AM, Ramesh T <rameshparnandit...@gmail.com>
> wrote:
>
>> '123-987-123' it is not fixed some times it may be '1233-9873-123-098'
>> as you said it's fixed,
>>
>> changes the values in middle of the -
>>
>> sometimes times i need 1233 and 098 or 9873,first position i'll find
>> direct for second variable we don't know where it's end with -
>>
>> i.e ,
>> i need to find second postition of the variable between the '-'
>>
>>
>
> While I and others are likely inclined to provide you a working solution
> to do so you need to state your data and requirement more clearly. Given
> the apparent language dynamic I'd suggest supplying 5-10 example data
> values along with their expected result.
>
> Otherwise, regular expressions almost certainly will let you solve your
> problem (though, like Joe Conway indicated, split_part may be possible)
> once you learn how to construct them. regexp_matches(...) is the access
> point to using them.
>
> David J.
>
>
[GENERAL] postgres function
Hi All, Do we have function like regexp_substr in postgres..? in oracle this function seach the - from 1 to 2 and return result, regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2)
Re: [GENERAL] Format
On Tue, Sep 29, 2015 at 6:43 PM, Ramesh T <rameshparnandit...@gmail.com> wrote: > in my mind to be beautiful ,avoid confusion to understand sql query code > > like > > select abcd,cde,rfg,count(*),bcd,cde,rfg,count(*),bcd,cde,rfg,count(*) ; > > if some one written, then newer or code reader understand easily > select abcd, > cde, > rfg, > count(*), > ..., > .., > ..., > like line by line > > in postgres direct option format is not available.i want see code intended > proper. > in context editor i didn't find format option only like lower to upper.. > > > > On Mon, Sep 28, 2015 at 11:20 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> CREATE FUNCTION >> CREATE VIEW >> >> As yes, I am being mostly serious - though I have been pondering seeing >> what tools can do as opposed to the manual formatting I've been >> performing. Even a basic linter would be helpful... >> >> The big problem is you need to define what it means to be beautiful. >> Understandable is a totally different area and that is more where views and >> functions come in so you can provide names to the various constructs you >> are using. CTE/WITH facilitate this as well. >> >> David J. >> >> >> On Mon, Sep 28, 2015 at 8:45 AM, Ramesh T <rameshparnandit...@gmail.com> >> wrote: >> >>> Hi All, >>> How to change sql format to look beautiful and understandable >>> using pgadmin3 or else ther tools for postgres. >>> >>> any help appreciated.. >>> >> >> >
Re: [GENERAL] Format
Nice, thank you. On Tue, Sep 29, 2015 at 11:16 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 09/29/2015 06:13 AM, Ramesh T wrote: > >> >> >> On Tue, Sep 29, 2015 at 6:43 PM, Ramesh T <rameshparnandit...@gmail.com >> <mailto:rameshparnandit...@gmail.com>> wrote: >> >> in my mind to be beautiful ,avoid confusion to understand sql query >> code >> >> like >> >> select >> abcd,cde,rfg,count(*),bcd,cde,rfg,count(*),bcd,cde,rfg,count(*) ; >> >> if some one written, then newer or code reader understand easily >> select abcd, >>cde, >>rfg, >>count(*), >> ..., >> .., >> ..., >> like line by line >> >> in postgres direct option format is not available.i want see code >> intended proper. >> in context editor i didn't find format option only like lower to >> upper.. >> >> > > http://sqlformat.darold.net/ > > To install locally: > https://github.com/darold/pgFormatter > > http://format-sql.de/ > > To install locally: > https://github.com/paetzke/format-sql > > >> >> On Mon, Sep 28, 2015 at 11:20 PM, David G. Johnston >> <david.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com>> >> wrote: >> >> CREATE FUNCTION >> CREATE VIEW >> >> As yes, I am being mostly serious - though I have been pondering >> seeing what tools can do as opposed to the manual formatting >> I've been performing. Even a basic linter would be helpful... >> >> The big problem is you need to define what it means to be >> beautiful. Understandable is a totally different area and that >> is more where views and functions come in so you can provide >> names to the various constructs you are using. CTE/WITH >> facilitate this as well. >> >> David J. >> >> >> On Mon, Sep 28, 2015 at 8:45 AM, Ramesh T >> <rameshparnandit...@gmail.com >> <mailto:rameshparnandit...@gmail.com>> wrote: >> >> Hi All, >>How to change sql format to look beautiful and >> understandable using pgadmin3 or else ther tools for postgres. >> >> any help appreciated.. >> >> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
[GENERAL] Format
Hi All, How to change sql format to look beautiful and understandable using pgadmin3 or else ther tools for postgres. any help appreciated..
[GENERAL] to pg
CREATE UNIQUE INDEX idx_load_pick ON pick (case picked when picked='y' then load_id else null end ); how can i convert case expressed to postgres..above it is oracle. any help appreciated...
[GENERAL] pgcrypto
Hi, i created extension pgcrypto on public with postgres user.But while trying to use from my own schma suppose qa. when i run digest in function in my qa CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ SELECT encode(digest($1, 'sha1'), 'hex') $$ LANGUAGE SQL STRICT IMMUTABLE; it return error; when i install pgcrypto using postgres user on qa,installed all pgcrypto function placed in qa function it looks confused. what should i do how to access the pgcrypto of public and we need to create extension on each schema..??
[GENERAL] postgres 9.3
I have one database and two schemas in that public,preview and role preview automatically i want to connect preview schema with preview role. set search_path to preview. show search_path; .. preview. when disconnect and connect database it's showing $user$:public schema not showing preview. what is the problem..?how to resolve the issue...? when session closed showing postgres user..even set preview.
Re: [GENERAL] Import Problem
Actually ,oracle have the *qa *schema i have to import to this schema to postgres database.But in postgres database already have the *qa *schema.My problem is that i want to change the name of the oracle schema while import to the postgres database using or2pg.because same schema name not accept right.manullay is not possible change name of schema in qa is have 2 gb data when i try to change name of the data scipt is hanged. On Thu, Sep 17, 2015 at 11:54 AM, Venkata Balaji N <nag1...@gmail.com> wrote: > > On Thu, Sep 17, 2015 at 1:27 AM, Ramesh T <rameshparnandit...@gmail.com> > wrote: > >> Hi All, >>I'm using or2pg tool ,I exported data but I have to change the >> schema import to postgres database.exported data more than gb. >> Can you please let me know, how to do the change the name in data >> script..? >> > > Do you mean that, you want import the data into a different schema in > postgres ? Where do you want to change the name ? > > If i understand your question correctly, there is an schema option in > ora2pg.conf which might help you. > > Regards, > Venkata B N > > Fujitsu Australia >
Re: [GENERAL] Import Problem
with out hanging how to open data script of the oracle schema to change the name. On Thu, Sep 17, 2015 at 6:31 PM, Ramesh T <rameshparnandit...@gmail.com> wrote: > Actually ,oracle have the *qa *schema i have to import to this schema to > postgres database.But in postgres database already have the *qa *schema.My > problem is that i want to change the name of the oracle schema while import > to the postgres database using or2pg.because same schema name not accept > right.manullay is not possible change name of schema in qa is have 2 gb > data when i try to change name of the data scipt is hanged. > > On Thu, Sep 17, 2015 at 11:54 AM, Venkata Balaji N <nag1...@gmail.com> > wrote: > >> >> On Thu, Sep 17, 2015 at 1:27 AM, Ramesh T <rameshparnandit...@gmail.com> >> wrote: >> >>> Hi All, >>>I'm using or2pg tool ,I exported data but I have to change >>> the schema import to postgres database.exported data more than gb. >>> Can you please let me know, how to do the change the name in data >>> script..? >>> >> >> Do you mean that, you want import the data into a different schema in >> postgres ? Where do you want to change the name ? >> >> If i understand your question correctly, there is an schema option in >> ora2pg.conf which might help you. >> >> Regards, >> Venkata B N >> >> Fujitsu Australia >> > >
[GENERAL] Import Problem
Hi All, I'm using or2pg tool ,I exported data but I have to change the schema import to postgres database.exported data more than gb. Can you please let me know, how to do the change the name in data script..?
[GENERAL] view
HI , I have view .when i try to select view ,it had return select * from art; ERROR: permission denied for relation sub_item SQL state: 42501
Re: [GENERAL] postgres connection
FATAL: syntax error in file recovery.conf line 2, near token config 2015-08-07 05:21:13.086 EDT LOG: startup process (PID 6129) exited with exit code 1 2015-08-07 05:21:13.086 EDT LOG: aborting startup due to startup process failure in pg_log I changed in Recovery.conf restore_command = 'cp /test/pgsql/pg_log_archive/%f %p' bash-4.1$ /etc/init.d/postgresql-9.3 start Starting postgresql-9.3 service: [FAILED] bash-4.1$pg_ctl start Starting postgresql-9.3 service: [FAILED] bash-4.1$service postgres-9.3 start Starting postgresql-9.3 service: [FAILED] when i try to conect server from pgadmin3 return error like could not connect to the server: connection refused on host 2.3.421.1 and accepting tcp/ip connection on port 5432. any help to start server.. On Fri, Aug 7, 2015 at 2:26 PM, Ramesh T rameshparnandit...@gmail.com wrote: any help On Thu, Aug 6, 2015 at 6:51 PM, Ramesh T rameshparnandit...@gmail.com wrote: Hi all, I got a problem when i start postgres server. like' could not connect to the server: connection refused on host 2.3.421.1 and accepting tcp/ip connection on port 5432. any help appreciated..
Re: [GENERAL] postgres connection
any help On Thu, Aug 6, 2015 at 6:51 PM, Ramesh T rameshparnandit...@gmail.com wrote: Hi all, I got a problem when i start postgres server. like' could not connect to the server: connection refused on host 2.3.421.1 and accepting tcp/ip connection on port 5432. any help appreciated..
[GENERAL] postgres connection
Hi all, I got a problem when i start postgres server. like' could not connect to the server: connection refused on host 2.3.421.1 and accepting tcp/ip connection on port 5432. any help appreciated..
[GENERAL] pghba.conf
Hi All, I changed in pg_hba.conf like,in postgres 9.3 under linux host replication base_backup_user 127.0.0.1/32 trust after above changes ,restarted it . when i try pgbasebackp getting error like, pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host 192.168.02.64, user base_backup_user, SSL off any help to resolve this issue..
[GENERAL] postgres 9.3
I changed archive_command in postgresql.conf and restarted it.postgres 3 installed on linux.connected from putty. after restarted it getting message like -bash-4.1$ service postgres-9.3 start postgres-9.3: unrecognized service but files available at server side.. any help..?
Re: [GENERAL] instr detail
select position('.' in '.T.homas')
result
1
it returns first postion.but I need last occurence of ' . ' .
actual result
--
3
any help..?appreciated.
On Thu, Jul 30, 2015 at 5:00 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Melvin Davidson melvin6...@gmail.com writes:
Based om the definition of Oracle instr(), the equivalent PostgreSQL
function would be
position(substring in string).
See http://www.postgresql.org/docs/9.4/static/plpgsql-porting.html
particularly the appendix at the bottom. I'm not sure that code
is still the best way to do it (it's very old), but it's there.
regards, tom lane
Re: [GENERAL] instr detail
here is the example tks help..
http://stackoverflow.com/questions/2965655/how-to-find-the-first-and-last-occurrences-of-a-specific-character-inside-a-stri
On Thu, Jul 30, 2015 at 7:38 PM, Ramesh T rameshparnandit...@gmail.com
wrote:
select position('.' in '.T.homas')
result
1
it returns first postion.but I need last occurence of ' . ' .
actual result
--
3
any help..?appreciated.
On Thu, Jul 30, 2015 at 5:00 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Melvin Davidson melvin6...@gmail.com writes:
Based om the definition of Oracle instr(), the equivalent PostgreSQL
function would be
position(substring in string).
See http://www.postgresql.org/docs/9.4/static/plpgsql-porting.html
particularly the appendix at the bottom. I'm not sure that code
is still the best way to do it (it's very old), but it's there.
regards, tom lane
[GENERAL] instr detail
Hi All,
is instr available in postgres 9.3..?
in oracle instr('12.32.42','.',-1) ,any help appreciated
Re: [GENERAL] Postgres Recovery
archive_command = 'test ! -f /mnt/server/archivedir/%f cp %p /mnt/server/archivedir/%f' # Unix restore_command = 'cp /mnt/server/archivedir/%f %p' above two commands and checkpoint settings .as i read doc we need to change postgres.conf.But other way i want try it from server commmand mean from putty is it possible..? i want commands to execute the set to the server without open the postgres.conf...? On Mon, Jul 20, 2015 at 6:23 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Mon, Jul 20, 2015 at 7:00 PM, Ramesh T rameshparnandit...@gmail.com wrote: Hi All, What i need to know for postgres recovery..?let me know in detail. Documentation is always a good start: http://www.postgresql.org/docs/devel/static/backup.html -- Michael
[GENERAL] Postgres Recovery
Hi All, What i need to know for postgres recovery..?let me know in detail.
Re: [GENERAL] timestamp check
Yes,But i need to display last digits also
[image: Inline image 1]
like 1500 08-09-10.738901
On Mon, Jul 13, 2015 at 8:18 PM, Francisco Olarte fola...@peoplecall.com
wrote:
Hi Ramesh:
On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T rameshparnandit...@gmail.com
wrote:
postgres query
select current_timestamp-
TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||'
'||(SELECT utc_offset FROM pg_catalog.pg_timezone_names
WHERE name=DATETIMEZOZE1) , ''-MM-DD HH24'||' '||'MI'||'
'||'SS')::timestamptz
getting result..
[image: Inline image 1]
But in oracle using systimestamp,to_timestamptz and SS TZH is not
supporting to_timestamp in postgres.
I do not know about Oracle, but in postgres you are substracting to
timestamps ( current_timestamp - to_timestamp(whatever) ). This gives you
an interval.
result..
[image: Inline image 2]
diffrence is days displaying in postgres query..i thnk something wrong.
is it..?
Days is displaying in postgres query because it is the default format to
display intervals ( it's a little more complicated, but related ).
$ select '1500 days 8 hours 9 minutes 10 seconds'::interval;
interval
1500 days 08:09:10
(1 row)
If you want a particular format you should use the appropiate formatting
functions, like to_char
$ select to_char('1500 days 8 hours 9 minutes 10 seconds'::interval,'DDD
HH-MI-SS');
to_char
---
1500 08-09-10
(1 row)
Or, you could try to change the default formatting, but this is generally
incorrect.
Regards.
Francisco Olarte.
Re: [GENERAL] timestamp check
i added .MS getting values,But Problem query keep on running but not
displaying results,when i add like limit 5.it is return values..
what is the problem with query..?
changed date and changed_dttimezone are are parameters..
select to_char((current_timestamp -
TO_TIMESTAMP(to_char(chaged_date,'-MM-DD HH24'|| ' '||'MI'||'
'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names
WHERE name=changed_dttimezone), '-MM-DD HH24'||' '||'MI'||'
'||'SS')::timestamptz),'DDD HH:MI:SS.MS')
On Tue, Jul 14, 2015 at 4:23 PM, Ramesh T rameshparnandit...@gmail.com
wrote:
Yes,But i need to display last digits also
[image: Inline image 1]
like 1500 08-09-10.738901
On Mon, Jul 13, 2015 at 8:18 PM, Francisco Olarte fola...@peoplecall.com
wrote:
Hi Ramesh:
On Sun, Jul 12, 2015 at 8:21 AM, Ramesh T rameshparnandit...@gmail.com
wrote:
postgres query
select current_timestamp-
TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||'
'||(SELECT utc_offset FROM pg_catalog.pg_timezone_names
WHERE name=DATETIMEZOZE1) , ''-MM-DD HH24'||' '||'MI'||'
'||'SS')::timestamptz
getting result..
[image: Inline image 1]
But in oracle using systimestamp,to_timestamptz and SS TZH is not
supporting to_timestamp in postgres.
I do not know about Oracle, but in postgres you are substracting to
timestamps ( current_timestamp - to_timestamp(whatever) ). This gives you
an interval.
result..
[image: Inline image 2]
diffrence is days displaying in postgres query..i thnk something wrong.
is it..?
Days is displaying in postgres query because it is the default format to
display intervals ( it's a little more complicated, but related ).
$ select '1500 days 8 hours 9 minutes 10 seconds'::interval;
interval
1500 days 08:09:10
(1 row)
If you want a particular format you should use the appropiate formatting
functions, like to_char
$ select to_char('1500 days 8 hours 9 minutes 10 seconds'::interval,'DDD
HH-MI-SS');
to_char
---
1500 08-09-10
(1 row)
Or, you could try to change the default formatting, but this is generally
incorrect.
Regards.
Francisco Olarte.
Re: [GENERAL] timestamp check
okay,i'm executing a query from pgadmin3. i want display time with timezone.But above query displaying date and time not timezone... On Sat, Jul 11, 2015 at 9:34 PM, David G. Johnston david.g.johns...@gmail.com wrote: On Fri, Jul 10, 2015 at 8:54 AM, Ramesh T rameshparnandit...@gmail.com wrote: select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'),'-MM-DD HH24'||':'||'MI'||':'||'SS')::timestamptz; it's not displaying timezone..any help..? You haven't told us how you are executing the above query. It also seems like an awfully convoluted answer to whatever query you are asking. David J.
Re: [GENERAL] timestamp check
postgres query select current_timestamp- TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name=DATETIMEZOZE1) , ''-MM-DD HH24'||' '||'MI'||' '||'SS')::timestamptz getting result.. [image: Inline image 1] But in oracle using systimestamp,to_timestamptz and SS TZH is not supporting to_timestamp in postgres. result.. [image: Inline image 2] diffrence is days displaying in postgres query..i thnk something wrong. is it..? any help apprictiated. On Sat, Jul 11, 2015 at 11:12 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/10/2015 05:54 AM, Ramesh T wrote: select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'),'-MM-DD HH24'||':'||'MI'||':'||'SS')::timestamptz; it's not displaying timezone..any help..? Forget my previous post, Rays post made me realize the error of my ways, namely thinking current_timestamp-to_timestamp was a function. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] xmltable in postgres like in oracle..?
WITH idtable AS ( SELECT '2342,8766' id ) select * from inv where id in( SELECT id FROM idtable) But in query need seperate row by row like 2342 8766 using these idtable id i'm checking in inv table id, if same id then return result from inv table how to seperate row by row from temp table? On Sun, Jul 12, 2015 at 5:38 PM, Ramesh T rameshparnandit...@gmail.com wrote: xml table is the oracle function ..thank you sir On Sat, Jul 11, 2015 at 11:04 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/11/2015 08:32 AM, Ramesh T wrote: Hi, is xmltable available in postgres..?,if not please give me a advice to replace the xmtable in postgres..? What is xmltable? Go to: http://www.postgresql.org/docs/9.4/interactive/index.html and in the Search field type xml -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] xmltable in postgres like in oracle..?
i got function
select regexp_split_to_table('2,1', E',')::bigint
thanks
On Mon, Jul 13, 2015 at 4:13 PM, Ramesh T rameshparnandit...@gmail.com
wrote:
WITH idtable AS (
SELECT '2342,8766' id
)
select * from inv where id in(
SELECT id
FROM idtable)
But in query need seperate row by row
like 2342
8766
using these idtable id i'm checking in inv table id, if same id then
return result from inv table
how to seperate row by row from temp table?
On Sun, Jul 12, 2015 at 5:38 PM, Ramesh T rameshparnandit...@gmail.com
wrote:
xml table is the oracle function ..thank you sir
On Sat, Jul 11, 2015 at 11:04 PM, Adrian Klaver
adrian.kla...@aklaver.com wrote:
On 07/11/2015 08:32 AM, Ramesh T wrote:
Hi,
is xmltable available in postgres..?,if not please give me a
advice to replace the xmtable in postgres..?
What is xmltable?
Go to:
http://www.postgresql.org/docs/9.4/interactive/index.html
and in the Search field type xml
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: [GENERAL] timestamp check
any help..? On Sun, Jul 12, 2015 at 11:51 AM, Ramesh T rameshparnandit...@gmail.com wrote: postgres query select current_timestamp- TO_TIMESTAMP(to_char(DATE1, '-MM-DD HH24'|| ' '||'MI'||' '||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name=DATETIMEZOZE1) , ''-MM-DD HH24'||' '||'MI'||' '||'SS')::timestamptz getting result.. [image: Inline image 1] But in oracle using systimestamp,to_timestamptz and SS TZH is not supporting to_timestamp in postgres. result.. [image: Inline image 2] diffrence is days displaying in postgres query..i thnk something wrong. is it..? any help apprictiated. On Sat, Jul 11, 2015 at 11:12 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/10/2015 05:54 AM, Ramesh T wrote: select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'),'-MM-DD HH24'||':'||'MI'||':'||'SS')::timestamptz; it's not displaying timezone..any help..? Forget my previous post, Rays post made me realize the error of my ways, namely thinking current_timestamp-to_timestamp was a function. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] xmltable in postgres like in oracle..?
xml table is the oracle function ..thank you sir On Sat, Jul 11, 2015 at 11:04 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/11/2015 08:32 AM, Ramesh T wrote: Hi, is xmltable available in postgres..?,if not please give me a advice to replace the xmtable in postgres..? What is xmltable? Go to: http://www.postgresql.org/docs/9.4/interactive/index.html and in the Search field type xml -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] xmltable in postgres like in oracle..?
Hi, is xmltable available in postgres..?,if not please give me a advice to replace the xmtable in postgres..?
[GENERAL] timestamp check
select current_timestamp-to_timestamp(to_char(current_date,'-MM-DD HH24'||':'||'MI'||':'||'SS')||' '||(SELECT utc_offset FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'),'-MM-DD HH24'||':'||'MI'||':'||'SS')::timestamptz; it's not displaying timezone..any help..?
Re: [GENERAL] regexp_matches for digit
nice i'm looking for this,i thought digit don't work in postgres..
thanks
On Thu, Jul 9, 2015 at 10:21 PM, Chris Mair ch...@1006.org wrote:
Hi,
in oracle regexp_like(entered
date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i')
for postgres i have regexp_matches ,But i need how to match [:digit:] in
postgres when we pass date..?
any help
[:digit:] is Posix syntax, supported by Postgres.
Looks good to me:
graal=# select regexp_matches('2015-07-09',
'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i');
regexp_matches
{2015-07-09}
(1 row)
graal=# select regexp_matches('2015-x7-09',
'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i');
regexp_matches
(0 rows)
What do you need, exactly?
Bye,
Chris.
[GENERAL] regexp_matches for digit
Hi,
in oracle regexp_like(entered
date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i')
for postgres i have regexp_matches ,But i need how to match [:digit:] in
postgres when we pass date..?
any help
[GENERAL] Functions
Hi All, I want move functions from onedatabase to other database in same host on windows 7 and installed postgres version is 9.4.I'm using pgadmin3 tool. any help..?
Re: [GENERAL] Functions
thank you it's helpfull On Fri, May 8, 2015 at 7:43 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 05/08/2015 01:56 AM, Ramesh T wrote: Hi All, I want move functions from onedatabase to other database in same host on windows 7 and installed postgres version is 9.4.I'm using pgadmin3 tool. any help..? Two options with pgAdmin: 1) See here http://www.pgadmin.org/docs/1.20/backup.html http://www.pgadmin.org/docs/1.20/restore.html See the Objects pane for selective restore 2) Go through the tree of objects in the object browser and cut and paste the function scripts. Best option is to create object(tables, functions, etc) definition scripts outside your database in text files so you can point them at any database you want. Ideally they will be in a version control system. -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL]
Hi all, as i mentioned above i have function called delete parts can i mention select deleteparts(); or c:\programfiles\9.4\pgagent\check.sql in this path check.sql contain to delete parts.i placed query select deleteparts(); when click run now job,it returns statistics failed and etc/pg_log not contian any error please suggest How to solve this..
Re: [GENERAL] Pgagent
Hi, in stepsdefinition- do $body$ begin perform delete_empty_parts(); end; $body$ delete_empty_parts is the function i do selected SQL option in step process.Right clicked on job chosen run now it getting failed(On error i selected failed,if select success it getting success) I'm running function on particular database and selected today dates.. but function wasn't executed. let me know how to set it function in pgagent.. On Mon, Apr 13, 2015 at 7:26 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 04/13/2015 06:53 AM, Ramesh T wrote: Hi all, i have a function to delete data from table.Where i need to place function in pgagent.in http://pgagent.in definition section can i select SQL or BATCH ..? or else any other method.? See here: http://www.pgadmin.org/docs/dev/pgagent.html Any Help.. -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] Pgagent
Hi all, i have a function to delete data from table.Where i need to place function in pgagent.in definition section can i select SQL or BATCH ..? or else any other method.? Any Help..
Re: [GENERAL] Pgagent
no error messages.. i checked at PostgreSQL\9.4\data\pg_log Didn't deleted data from table.after schedule time.. one thing i don't understand ,after set timings and in definition section also the function works fine individually. any help.. On Mon, Apr 13, 2015 at 8:47 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 04/13/2015 07:22 AM, Ramesh T wrote: Hi, in stepsdefinition- do $body$ begin perform delete_empty_parts(); end; $body$ delete_empty_parts is the function i do selected SQL option in step process.Right clicked on job chosen run now it getting failed(On error i selected failed,if select success it getting success) Is there an error message in the Postgres logs? I'm running function on particular database and selected today dates.. but function wasn't executed. How do you know? let me know how to set it function in pgagent.. On Mon, Apr 13, 2015 at 7:26 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 04/13/2015 06:53 AM, Ramesh T wrote: Hi all, i have a function to delete data from table.Where i need to place function in pgagent.in http://pgagent.in http://pgagent.in definition section can i select SQL or BATCH ..? or else any other method.? See here: http://www.pgadmin.org/docs/__dev/pgagent.html http://www.pgadmin.org/docs/dev/pgagent.html Any Help.. -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] Stalled post to pgsql-general
Yes. Its working fine. What i mean i created function to delete parts from table.Then i need to place it on pgagent. i placed function in pagent like this.. do $$ declare job_id int; begin /* add a job and get its id: */ insert into pgagent.pga_job (jobjclid, jobname) values (1 /*1=Routine Maintenance*/, 'part delete') returning jobid into job_id; /* add a step to the job: */ insert into pgagent.pga_jobstep (jstjobid, jstname, jstkind, jstcode, jstdbname) values ( job_id, 'my step name', 's',/* sql step */ 'do $BODY$ BEGIN perform delete_empty_parts(); end; $BODY$', /* the sql to run */ 'sakila' /* the name of the database to run the step against */ ); /* add a schedule to the job. This one runs every minute: */ insert into pgagent.pga_schedule (jscjobid, jscname) values (job_id, 'my schedule name'); end $$; it sprogramatic way.. excuted fine.where i need to find this job.Jobs section..?,if yes unable to see the job after excuted..please let me know where i need to excute and where it is place.. 2.is their any chance place *function *in pgagent directly in diagramatic way ..? like right click on job create job.. i think step--defintion.. how to place it and can i select sql or batch..? On Fri, Apr 3, 2015 at 3:50 PM, pgsql-general-ow...@postgresql.org wrote: Your message to pgsql-general has been delayed, and requires the approval of the moderators, for the following reason(s): The author (Ramesh T rameshparnandit...@gmail.com) is not a member of any of the restrict_post groups. If you do not wish the message to be posted, or have other concerns, please send a message to the list owners at the following address: pgsql-general-ow...@postgresql.org -- Forwarded message -- From: Ramesh T rameshparnandit...@gmail.com To: Pavel Stehule pavel.steh...@gmail.com, pgsql-general@postgresql.org pgsql-general@postgresql.org Cc: Date: Fri, 3 Apr 2015 15:50:43 +0530 Subject: Re: The link is good. But What I am expecting the following link..Created Using pgAgent. http://www.postgresonline.com/journal/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html in above link process they placed location of the script file at STEP DEFINTION TAB creation process.. same way is there a chance to place creation of table or delete statements in pgAgent process..? my aim to create job is delete some null data from table daily On Fri, Apr 3, 2015 at 3:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi http://stackoverflow.com/questions/4477301/creating-jobs-and-schedules-programatically-with-pgagent regards Pavel Stehule 2015-04-03 11:27 GMT+02:00 Ramesh T rameshparnandit...@gmail.com: Hi , How to create job in pgAgent.Where I need to place script in pgAgent..any help Advanced thanks...
Re: [GENERAL] stack builder
Hi, I had installed pgadmin3 but not selected stackbuilder ,let me know how to add stackbuilder to pgadmin3 for additional addons. On Wed, Apr 1, 2015 at 7:15 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 04/01/2015 06:22 AM, Ramesh T wrote: Hi all, I didn't selected stack builder at the time of installation of postgres.Any help how to add*application stack builder* to existed postgres. Assuming you are talking about an install done using the EDB installer: http://www.enterprisedb.com/docs/en/9.3/pginstguide/Table% 20of%20Contents.htm 4 Using Stack Builder -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] stack builder
Hi all, I didn't selected stack builder at the time of installation of postgres.Any help how to add* application stack builder* to existed postgres.
Re: [GENERAL] stack builder
I had installed on Linux.re-install postgres for stack builder ..? cron is their ,but I am new to this cron and is this method for job schedulers in postgres. On Wed, Apr 1, 2015 at 8:39 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 04/01/2015 07:59 AM, Ramesh T wrote: Hi, I had installed pgadmin3 but not selected stackbuilder ,let me know how to add stackbuilder to pgadmin3 for additional addons. You cannot, StackBuilder is not part of pgAdmin3, it is another application entirely. How did you install Postgres and what OS? On Wed, Apr 1, 2015 at 7:15 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 04/01/2015 06:22 AM, Ramesh T wrote: Hi all, I didn't selected stack builder at the time of installation of postgres.Any help how to add*application stack builder* to existed postgres. Assuming you are talking about an install done using the EDB installer: http://www.enterprisedb.com/__docs/en/9.3/pginstguide/Table% __20of%20Contents.htm http://www.enterprisedb.com/docs/en/9.3/pginstguide/Table% 20of%20Contents.htm 4 Using Stack Builder -- Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL]
Hi , How to create job in pgAgent.Where I need to place script in pgAgent..any help Advanced thanks...
Re: [GENERAL]
The link is good. But What I am expecting the following link..Created Using pgAgent. http://www.postgresonline.com/journal/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html in above link process they placed location of the script file at STEP DEFINTION TAB creation process.. same way is there a chance to place creation of table or delete statements in pgAgent process..? my aim to create job is delete some null data from table daily On Fri, Apr 3, 2015 at 3:30 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi http://stackoverflow.com/questions/4477301/creating-jobs-and-schedules-programatically-with-pgagent regards Pavel Stehule 2015-04-03 11:27 GMT+02:00 Ramesh T rameshparnandit...@gmail.com: Hi , How to create job in pgAgent.Where I need to place script in pgAgent..any help Advanced thanks...
[GENERAL] xml
Hi all, SELECT xmlagg(xmlelement( name actor, xmlattributes(first_name) )ORDER BY actor_id,',')from actor; the above code return following result, [image: Inline image 1] Question : i want retrieve result from above XML result like penelope,nick,jennifer,jhony, xpath is their but unable to fix this query. any help how to get only names from query not xml.
[GENERAL]
hi all, I am using postgres 9.4 on windows 7. i want import that backup sql file into postgres schema. i googled i got copy, but when i ran in windows COPY actor FROM 'c:\users\venu\downloads\sakila-data.sql'; ERROR: invalid input syntax for integer: CONTEXT: COPY actor, line 1, column actor_id: ** Error ** the backupdata format COPY actor (actor_id, first_name, last_name, last_update) FROM stdin; 1 PENELOPE GUINESS 2006-02-15 09:34:33 2 NICK WAHLBERG 2006-02-15 09:34:33 3 ED CHASE 2006-02-15 09:34:33 4 JENNIFER DAVIS 2006-02-15 09:34:33 how to import to table from file in postgres any help..?
[GENERAL] rules
it is in postgres i need to convert into oracle CREATE RULE payment_insert_p2007_04 AS ON INSERT TO payment WHERE (new.payment_date = '2007-04-01'::timestamp without time zone) DO INSTEAD INSERT INTO payment_p2007_04 (payment_id)VALUES (1); in oracle format i used google i got BEGIN DBMS_MACADM.CREATE_RULE( rule_name = 'Restrict Access to Maintenance Period', rule_expr = 'TO_CHAR(SYSDATE,''HH24'') BETWEEN ''14'' AND ''15'''); END; / it is not works for me ,any one let me know is it possible create rule in oracle on particular table as i mentioned create rule as postgres format.
[GENERAL]
CREATE AGGREGATE group_concat(text) ( SFUNC = _group_concat, STYPE = text ); is it availabe in oracle..?
Re: [GENERAL] dbmsscheduler
dbms_scheduler.create_job( job_name = 'DELETE_EMPTY_PART_NUMS' ,job_type = 'PLSQL_BLOCK') without pgagent or cron is not possible..? On Mon, Feb 9, 2015 at 11:35 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi PostgreSQL doesn't have a dbms_scheduler. If you need it, you can use a EnterpriseDB - commercial fork with Oracle migration tools. There it is. You can use a scheduler pgAgent - https://github.com/postgres/pgagent http://www.pgadmin.org/docs/dev/pgagent.html Regards Pavel 2015-02-03 14:16 GMT+01:00 Ramesh T rameshparnandit...@gmail.com: hi, How to run dbms_scheduler.create_job in postgres and is it available postgres..? any help ,how to use in postgres..? FYI, i am using postgres 9.3 version thanks in advance,
Re: [GENERAL] postgres cust types
exactly what I am trying convert oracle to postgres , following 1)first i am creating type in oracle CREATE TYPE suborder_list AS (suborder_id int); 2)second creating table type in oracle create or replace type suborder_list_table as table of suborder_list; 3)i am using above 1 and 2 created types oracle function create or replace FUNCTION check(id int) RETURNS suborder_list_table is BEGIN v_ret :=suborder_list_table(); FOR VAR_CUR1 IN cur1 LOOP invcount:=0; SELECT COUNT(id) INTO invcount FROM detail iv WHERE iv.id = id AND cd IN (SELECT cd FROM detail) IF (invcount0) THEN v_ret.extend; v_ret(v_ret.count) := suborder_list(VAR_CUR1.id); END IF; END LOOP; RETURN v_ret; here cur1 is cursor above 1 and 2 used in 3'rd step of oracle function,now i need to convert oracle function into postgres format any help..? thanks in advance, On Mon, Feb 9, 2015 at 11:39 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2015-02-03 13:49 GMT+01:00 Ramesh T rameshparnandit...@gmail.com: Hi , i created type on postgres CREATE TYPE order_list AS (order_id bigint); it works fine. then, i try to create a other table type using above created type. like, --create or replace type suborder_list_table as table of suborder_list; this on *oracle *formate This syntax is not supported in Pg - resp. a collections are not supported by PostgreSQL. use a arrays instead DECLARE array_var order_list[]; http://www.postgresql.org/docs/9.4/static/arrays.html Regards Pavel Stehule i need to convert *postgres *and how to create a table type in postgres is it possible or else any other method. FYI,i am using these types in a function. thanks in advance,
Re: [GENERAL] Collection
i solved my problem using string_agg in tab_to_string
ex:-
select string_agg(s.Rnumber ::text,',' )AS number
On Fri, Feb 13, 2015 at 10:40 PM, Raymond O'Donnell r...@iol.ie wrote:
On 13/02/2015 13:13, Ramesh T wrote:
cast(COLLECT (r_id) as num) in oracle..
is their *collect *function in postgres plpgsql?or any alternate
for this..?
I don't use Oracle, but I think array_agg() is the closest - it
aggregates the column into an array.
postgres=# create table test(a integer, b text);
CREATE TABLE
postgres=# insert into test values (1, 'abc');
INSERT 0 1
postgres=# insert into test values (2, 'def');
INSERT 0 1
postgres=# insert into test values (2, 'ghi');
INSERT 0 1
postgres=# select a, array_agg(b) from test group by a;
a | array_agg
---+---
1 | {abc}
2 | {def,ghi}
(2 rows)
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
[GENERAL] Collection
cast(COLLECT (r_id) as num) in oracle.. is their *collect *function in postgres plpgsql?or any alternate for this..? thanks in advance,
[GENERAL] dbmsscheduler
hi, How to run dbms_scheduler.create_job in postgres and is it available postgres..? any help ,how to use in postgres..? FYI, i am using postgres 9.3 version thanks in advance,
[GENERAL] postgres cust types
Hi , i created type on postgres CREATE TYPE order_list AS (order_id bigint); it works fine. then, i try to create a other table type using above created type. like, --create or replace type suborder_list_table as table of suborder_list; this on *oracle *formate i need to convert *postgres *and how to create a table type in postgres is it possible or else any other method. FYI,i am using these types in a function. thanks in advance,
Re: [GENERAL] pg_dump
is their any other method to clone database,other then pgadmin and pgdump.. if is their option pls let me know.. On Wed, Dec 17, 2014 at 4:35 PM, Ramesh T rameshparnandit...@gmail.com wrote: problem solved, C:\Program Files\pgadmin\binpg_dump -U postgres host -p port -C -f c:\bb\db_1 db_2 then to import i used psql On Wed, Dec 17, 2014 at 10:44 AM, Ramesh T rameshparnandit...@gmail.com wrote: Please let me know what happend inside ..? On Wednesday, December 17, 2014, Ramesh T rameshparnandit...@gmail.com wrote: I'm installed pgadmin 3 on windows I'm trying to connect server.the server is on Linux.now I'm trying pgdump from windows from using putty connected to Linux Postgres server..also last mail I sent trying from windows local c:\... :) On Wednesday, December 17, 2014, Adrian Klaver adrian.kla...@aklaver.com wrote: On 12/16/2014 08:17 AM, Ramesh T wrote: C:\Program Files\pgAdmin III\1.14pg_dump -U postgres -p 5432 -C -f c:\backup\db_2.dump db_1; pg_dump: [archiver (db)] connection to database db_1; failed: coul d not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (127.0.0.1) and accepting TCP/IP connections on port 5432? is it right excution process ,if it is yes 5432..? Previously you where using PuTTY to log in to the machine with the Postgres server, so I assume it is not running on the Windows machine. If that is the case doing the above is bound to fail. What the error is telling you is that pg_dump cannot find a Postgres instance listening where you told it to go. Bottom line is you need to be more accurate in what you are doing. So: 1) Back to my first post; That would depend on where your Postgres server is relative to the c\ drive. What machine is the Postgres server located on? What machine are you trying to connect from? Everything you posted relates to the above. These questions need to be answered before we can go any further. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] pg_dump
Please let me know what happend inside ..? On Wednesday, December 17, 2014, Ramesh T rameshparnandit...@gmail.com wrote: I'm installed pgadmin 3 on windows I'm trying to connect server.the server is on Linux.now I'm trying pgdump from windows from using putty connected to Linux Postgres server..also last mail I sent trying from windows local c:\... :) On Wednesday, December 17, 2014, Adrian Klaver adrian.kla...@aklaver.com javascript:_e(%7B%7D,'cvml','adrian.kla...@aklaver.com'); wrote: On 12/16/2014 08:17 AM, Ramesh T wrote: C:\Program Files\pgAdmin III\1.14pg_dump -U postgres -p 5432 -C -f c:\backup\db_2.dump db_1; pg_dump: [archiver (db)] connection to database db_1; failed: coul d not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (127.0.0.1) and accepting TCP/IP connections on port 5432? is it right excution process ,if it is yes 5432..? Previously you where using PuTTY to log in to the machine with the Postgres server, so I assume it is not running on the Windows machine. If that is the case doing the above is bound to fail. What the error is telling you is that pg_dump cannot find a Postgres instance listening where you told it to go. Bottom line is you need to be more accurate in what you are doing. So: 1) Back to my first post; That would depend on where your Postgres server is relative to the c\ drive. What machine is the Postgres server located on? What machine are you trying to connect from? Everything you posted relates to the above. These questions need to be answered before we can go any further. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] pg_dump
problem solved, C:\Program Files\pgadmin\binpg_dump -U postgres host -p port -C -f c:\bb\db_1 db_2 then to import i used psql On Wed, Dec 17, 2014 at 10:44 AM, Ramesh T rameshparnandit...@gmail.com wrote: Please let me know what happend inside ..? On Wednesday, December 17, 2014, Ramesh T rameshparnandit...@gmail.com wrote: I'm installed pgadmin 3 on windows I'm trying to connect server.the server is on Linux.now I'm trying pgdump from windows from using putty connected to Linux Postgres server..also last mail I sent trying from windows local c:\... :) On Wednesday, December 17, 2014, Adrian Klaver adrian.kla...@aklaver.com wrote: On 12/16/2014 08:17 AM, Ramesh T wrote: C:\Program Files\pgAdmin III\1.14pg_dump -U postgres -p 5432 -C -f c:\backup\db_2.dump db_1; pg_dump: [archiver (db)] connection to database db_1; failed: coul d not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (127.0.0.1) and accepting TCP/IP connections on port 5432? is it right excution process ,if it is yes 5432..? Previously you where using PuTTY to log in to the machine with the Postgres server, so I assume it is not running on the Windows machine. If that is the case doing the above is bound to fail. What the error is telling you is that pg_dump cannot find a Postgres instance listening where you told it to go. Bottom line is you need to be more accurate in what you are doing. So: 1) Back to my first post; That would depend on where your Postgres server is relative to the c\ drive. What machine is the Postgres server located on? What machine are you trying to connect from? Everything you posted relates to the above. These questions need to be answered before we can go any further. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] pg_dump
I'm installed pgadmin 3 on windows I'm trying to connect server.the server is on Linux.now I'm trying pgdump from windows from using putty connected to Linux Postgres server..also last mail I sent trying from windows local c:\... :) On Wednesday, December 17, 2014, Adrian Klaver adrian.kla...@aklaver.com wrote: On 12/16/2014 08:17 AM, Ramesh T wrote: C:\Program Files\pgAdmin III\1.14pg_dump -U postgres -p 5432 -C -f c:\backup\db_2.dump db_1; pg_dump: [archiver (db)] connection to database db_1; failed: coul d not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (127.0.0.1) and accepting TCP/IP connections on port 5432? is it right excution process ,if it is yes 5432..? Previously you where using PuTTY to log in to the machine with the Postgres server, so I assume it is not running on the Windows machine. If that is the case doing the above is bound to fail. What the error is telling you is that pg_dump cannot find a Postgres instance listening where you told it to go. Bottom line is you need to be more accurate in what you are doing. So: 1) Back to my first post; That would depend on where your Postgres server is relative to the c\ drive. What machine is the Postgres server located on? What machine are you trying to connect from? Everything you posted relates to the above. These questions need to be answered before we can go any further. -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] pg_dump
hi, i need to export a file the database postgres pg_dump -U postgres -C -Fp -f c:/backup/db2.dump db_1; where i can run pg_dump please send me details i want to export db_1 to db2 i got an error below postgres=# pg_dump -U postgres -C -Fp -f c:/backup/db_2.dump db_1; ERROR: syntax error at or near pg_dump LINE 1: pg_dump -U postgres -C -Fp -f c:/backup/db_2du... i'm using putty to connect host how to bash to c:\..?
Re: [GENERAL] pg_dump
C:\Program Files\pgAdmin III\1.14pg_dump -U postgres -p 5432 -C -f c:\backup\db_2.dump db_1; pg_dump: [archiver (db)] connection to database db_1; failed: coul d not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (::1) and accepting TCP/IP connections on port 5432? could not connect to server: Connection refused (0x274D/10061) Is the server running on host localhost (127.0.0.1) and accepting TCP/IP connections on port 5432? is it right excution process ,if it is yes 5432..? On Tue, Dec 16, 2014 at 9:25 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 12/16/2014 07:52 AM, Ramesh T wrote: CCing list. 1. -bash-4.1$ pg_dump -C -Fp -f C:\backup\db_1.dump db_2 how to export and where i can save export file Per my previous post: That would depend on where your Postgres server is relative to the c:\ drive. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] phppgadmin
tell me good guidelines for phppgadmin..? On Thu, Nov 6, 2014 at 8:54 PM, Ramesh T rameshparnandit...@gmail.com wrote: hello, any guidelines is their how to use these tool..? not home site.. http://phppgadmin.kattare.com/phppgadmin/...:)
[GENERAL] phppgadmin
hello, any guidelines is their how to use these tool..? not home site.. http://phppgadmin.kattare.com/phppgadmin/...:)
Re: [GENERAL] stackbuilder
any help.. On Tue, Sep 9, 2014 at 4:06 PM, Ramesh T rameshparnandit...@gmail.com wrote: Hi, I had installed pgadmin3 but not selected stackbuilder ,let me know how to add stackbuilder to pgadmin3 for additional addons.. thanks, ram
[GENERAL] stackbuilder
Hi, I had installed pgadmin3 but not selected stackbuilder ,let me know how to add stackbuilder to pgadmin3 for additional addons.. thanks, ram
Re: [GENERAL] Deletion
OK.. i created a function for delete customer from different tables in single database. i want rollback.. my question: where i need to place rollback ,with in a function along with deletion statements..? or after run the function ..? i do not need commit.. please let me know.. On Thu, Aug 28, 2014 at 1:20 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/27/2014 11:59 AM, Ramesh T wrote: In oracle I ran the deletion script to clean up the particular database using custid.custid is the parameter .1 is used in the oracle Inthe same way tried but $1 not possible ?but using function is possible is their any problem with fun to Clean the database I have 100 statements in script ... First as has been pointed out before Oracle != Postgres. Also as been pointed out before you have two options: 1) Pay for the EDB Postgres Advanced Sever + and get the Oracle compatibility built ins. 2) Spend the time to convert your Oracle code. As always it comes down to that magic combination of time and/or money. Second I do not understand what you are trying to say above. Maybe if you showed some actual code it would help. -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] Deletion
Hi, I have deletion script to delete particular cust from database.I was saved a file name with custde.sql.i need to run from command line like putty tool..But have a problem example : delete from part where part.custid=$1; when i ran custde.sql from putty tool db=#\i custde.sql it's return error psql:custde.sql:9: ERROR: there is no parameter $1 LINE 1: ..._ID from part where part.CUST_ID = $1); please let me know how to run script ..and how to give parameter..
Re: [GENERAL] Deletion
In oracle I ran the deletion script to clean up the particular database using custid.custid is the parameter .1 is used in the oracle Inthe same way tried but $1 not possible ?but using function is possible is their any problem with fun to Clean the database I have 100 statements in script ... On Wednesday, August 27, 2014, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/27/2014 08:24 AM, Ramesh T wrote: any help... Honestly, this is basic programming/scripting. I would suggest picking up an introductory programming book to get the basics down. In the meantime, you have used a parameter variable ($1) with out actually supplying a parameter. So Postgres has no idea what $1 represents. Either hard code the part.custid value i.e part.custid = 1 or find a way to supply the value. How you do that is going to depend on how you are going to use the script and where you are expecting to pull the value from. thanks, ram On Wed, Aug 27, 2014 at 3:22 PM, Ramesh T rameshparnandit...@gmail.com mailto:rameshparnandit...@gmail.com wrote: Hi, I have deletion script to delete particular cust from database.I was saved a file name with custde.sql.i need to run from command line like putty tool..But have a problem example : delete from part where part.custid=$1; when i ran custde.sql from putty tool db=#\i custde.sql it's return error psql:custde.sql:9: ERROR: there is no parameter $1 LINE 1: ..._ID from part where part.CUST_ID = $1); please let me know how to run script ..and how to give parameter.. -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] POWA tool
i downloaded But where i need to unzip powa-REL_1_1.zip i'm using putty tool remote server.. -bash-4.1$ unzip powa-REL_1_1.zip -bash: unzip: command not found On Tue, Aug 26, 2014 at 12:55 AM, Raghu Ram raghuchenn...@gmail.com wrote: On Fri, Aug 22, 2014 at 4:40 PM, Ramesh T rameshparnandit...@gmail.com wrote: How to include pg_stat_statements in postgres.conf.powa is need it. any help.. Below are the steps to Install POWA Tool: *Step 1:* Download POWA tool from below Website https://github.com/dalibo/powa/archive/REL_1_1.zip *Step 2:* Unpack the Downloaded file [root@localhost tmp]# cd /tmp/ [root@localhost powa-REL_1_1]# unzip powa-REL_1_1.zip *Step 3:* Install POWA Software [root@localhost powa-REL_1_1]# export PATH=/opt/PostgreSQL/9.3/bin:$PATH [root@localhost powa-REL_1_1]# export LD_LIBRARY_PATH=/opt/PostgreSQL/9.3/lib:$LD_LIBRARY_PATH [root@localhost powa-REL_1_1]# make install gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I/opt/PostgreSQL/9.3/include/postgresql/server -I/opt/PostgreSQL/9.3/include/postgresql/internal -D_GNU_SOURCE -I/opt/local/20140108/fc7f8f12-7861-11e3-aaff-000c29d23b02/include/libxml2 -I/usr/local/include/libxml2 -I/usr/local/include -c -o powa.o powa.c gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -L/opt/PostgreSQL/9.3/lib -L/opt/local/20140108/fc7f8f12-7861-11e3-aaff-000c29d23b02/lib -L/usr/local/lib -Wl,--as-needed -Wl,-rpath,'/opt/PostgreSQL/9.3/lib',--enable-new-dtags -shared -o powa.so powa.o /bin/mkdir -p '/opt/PostgreSQL/9.3/share/postgresql/extension' /bin/mkdir -p '/opt/PostgreSQL/9.3/share/postgresql/extension' /bin/mkdir -p '/opt/PostgreSQL/9.3/lib/postgresql' /bin/mkdir -p '/opt/PostgreSQL/9.3/doc/postgresql/extension' /usr/bin/install -c -m 644 ./powa.control '/opt/PostgreSQL/9.3/share/postgresql/extension/' /usr/bin/install -c -m 644 ./powa--1.0.sql ./powa--1.1.sql '/opt/PostgreSQL/9.3/share/postgresql/extension/' /usr/bin/install -c -m 755 powa.so '/opt/PostgreSQL/9.3/lib/postgresql/' /usr/bin/install -c -m 644 ./README.md '/opt/PostgreSQL/9.3/doc/postgresql/extension/' *Step 4:* Create a POWA database Create requires extensions -bash-4.1$ /opt/PostgreSQL/9.3/bin/psql -p 5435 Password: psql.bin (9.3.5) Type help for help. postgres=# create database powa; CREATE DATABASE postgres=# \c powa You are now connected to database powa as user postgres. powa=# create extension pg_stat_statements ; CREATE EXTENSION powa=# create extension btree_gist ; CREATE EXTENSION powa=# create extension powa; CREATE EXTENSION powa=# \dt List of relations Schema | Name | Type | Owner +-+---+-- public | powa_functions | table | postgres public | powa_last_aggregation | table | postgres public | powa_last_purge | table | postgres public | powa_statements | table | postgres public | powa_statements_history | table | postgres public | powa_statements_history_current | table | postgres (6 rows) *Step 5:* add power pg_stat_statements in the shared_preload_libraries in postgresql.conf file -bash-4.1$ more /opt/PostgreSQL/9.3/data/postgresql.conf |grep shared_preload # Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory shared_preload_libraries = 'powa,pg_stat_statements' # (change requires restart) -bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/ start server starting -bash-4.1$ 2014-07-25 03:48:20 IST LOG: registering background worker powa 2014-07-25 03:48:20 IST LOG: loaded library powa 2014-07-25 03:48:20 IST LOG: loaded library pg_stat_statements 2014-07-25 03:48:20 IST LOG: redirecting log output to logging collector process 2014-07-25 03:48:20 IST HINT: Future log output will appear in directory pg_log. *Step 6:* Install Mojolicious Software [root@localhost ui]# pwd /tmp/powa-REL_1_1/ui [root@localhost ui]# ls -l total 36 drwxr-xr-x 4 root root 4096 Aug 19 2014 lib -rw-r--r-- 1 root root 393 Jul 25 04:05 powa.conf -rw-r--r-- 1 root root 393 Aug 19 2014 powa.conf-dist drwxr-xr-x 6 root root 4096 Aug 19 2014 public -rw-r--r-- 1 root root 2121 Aug 19 2014 README.md drwxr-xr-x 2 root root 4096 Aug 19 2014 script drwxr-xr-x 2 root root 4096 Aug 19 2014 t drwxr-xr-x 6 root root 4096 Aug 19 2014 templates -rw-r--r-- 1 root root4 Aug 19 2014 VERSION [root@localhost ui]# cp powa.conf-dist powa.conf [root@localhost ~]# /usr/bin/curl get.mojolicio.us | sh % Total% Received % Xferd Average Speed TimeTime Time Current
Re: [GENERAL] POWA tool
How to include pg_stat_statements in postgres.conf.powa is need it. any help.. thanks, On Wed, Aug 20, 2014 at 11:51 PM, Ramesh T rameshparnandit...@gmail.com wrote: yes, in my postgres.conf pg_stat_statements is not their needs powa is released 19 aug. On Wed, Aug 20, 2014 at 10:17 PM, Raghu Ram raghuchenn...@gmail.com wrote: On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell r...@iol.ie wrote: On 20/08/2014 16:41, Ramesh T wrote: Hello, when i ran following query, postgres=# SELECT * FROM pg_stat_statements; ERROR: relation pg_stat_statements does not exist LINE 1: SELECT * FROM pg_stat_statements; i need to install POWA..i got powa.zip please let me know how to install POWA.ZIP for my postgres using putty tool .. are you referring below Tool ? PoWA is PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graph to help monitor and tune your PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW. http://www.postgresql.org/about/news/1537/ Thanks Regards Raghu Ram
[GENERAL] POWA tool
Hello, when i ran following query, postgres=# SELECT * FROM pg_stat_statements; ERROR: relation pg_stat_statements does not exist LINE 1: SELECT * FROM pg_stat_statements; i need to install POWA..i got powa.zip please let me know how to install POWA.ZIP for my postgres using putty tool .. thanks,
Re: [GENERAL] POWA tool
yes, in my postgres.conf pg_stat_statements is not their needs powa is released 19 aug. On Wed, Aug 20, 2014 at 10:17 PM, Raghu Ram raghuchenn...@gmail.com wrote: On Wed, Aug 20, 2014 at 10:08 PM, Raymond O'Donnell r...@iol.ie wrote: On 20/08/2014 16:41, Ramesh T wrote: Hello, when i ran following query, postgres=# SELECT * FROM pg_stat_statements; ERROR: relation pg_stat_statements does not exist LINE 1: SELECT * FROM pg_stat_statements; i need to install POWA..i got powa.zip please let me know how to install POWA.ZIP for my postgres using putty tool .. are you referring below Tool ? PoWA is PostgreSQL Workload Analyzer that gathers performance stats and provides real-time charts and graph to help monitor and tune your PostgreSQL servers. It is similar to Oracle AWR or SQL Server MDW. http://www.postgresql.org/about/news/1537/ Thanks Regards Raghu Ram
[GENERAL] pgbadger download
can any one send me link pgbadger 6 for me .. advance thanks,
Re: [GENERAL] pgcluu
Hi, I don't have the root permission for yum. secondly,When i try to install it's return following same as previous error/msg tar xvzf Test-Harness-3.32.tar.gz cd Test-Harness-3.32 -bash-4.1$ *perl Makefile.PL* Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 8. BEGIN failed--compilation aborted at Makefile.PL line 8. -bash-4.1$ make make: *** No targets specified and no makefile found. Stop. -bash-4.1$ make test make: *** No rule to make target `test'. Stop. -bash-4.1$ let me know any help..? On Mon, Aug 11, 2014 at 9:07 PM, David Carpio dav...@consistentstate.com wrote: Hello You must install the harness.pm module You may do it via yum yum install perl-Test-Harness or downloaded the tar package and install it http://search.cpan.org/~leont/Test-Harness-3.32/lib/Test/Harness.pm I hope this help you. David On 08/11/2014 08:52 AM, Ramesh T wrote: Hello , I specified correct method i got error/message at perl Makefile.PL tar xzf pgcluu-2.0.tar.gz -bash-4.1$ cd pgcluu-2.0 *-bash-4.1$ perl Makefile.PL* Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1. BEGIN failed--compilation aborted at Makefile.PL line 1. -bash-4.1$ then i trying to install ExtUtils/MakeMaker same place perl Makefile.PL error/notifying tar xzf ExtUtils-MakeMaker-6.99_07.tar.gz -bash-4.1$ cd ExtUtils-MakeMaker-6.99_07 -bash-4.1$ perl Makefile.PL Using included version of CPAN::Meta (2.120351) because it is not already instal led. Using included version of ExtUtils::Install (1.54) because it is not already ins talled. Using included version of CPAN::Meta::YAML (0.008) because it is not already ins talled. Using included version of CPAN::Meta::Requirements (2.120351) because it is not already installed. Using included version of File::Copy::Recursive (0.38) because it is not already installed. Using included version of Parse::CPAN::Meta (1.4405) because it is not already i nstalled. Using included version of JSON::PP (2.27203) because it is not already installed . Using included version of JSON::PP::Compat5006 (1.09) because it is not already installed. Using included version of ExtUtils::Manifest (1.60) because it is not already in stalled. Generating a Unix-style Makefile Writing Makefile for ExtUtils::MakeMaker Writing MYMETA.yml and MYMETA.json Can't locate Test/Harness.pm in @INC (@INC contains: bundled/CPAN-Meta bundled/E xtUtils-Install bundled/CPAN-Meta-YAML bundled/CPAN-Meta-Requirements bundled/Fi le-Copy-Recursive bundled/Parse-CPAN-Meta bundled/JSON-PP bundled/version bundle d/Scalar-List-Utils bundled/JSON-PP-Compat5006 bundled/ExtUtils-Command bundled/ ExtUtils-Manifest bundled/File-Temp lib . /usr/local/lib64/perl5 /usr/local/shar e/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/per l5 /usr/share/perl5) at Makefile.PL line 142. wheni trying test Harness it is also not installing.. let me know how to fix issue..? thanks, rao On Sat, Aug 9, 2014 at 9:43 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/08/2014 06:40 AM, Ramesh T wrote: Hi, i want install pgcluu on postgres 9.3 and i'm putty tool to connect pg database when i ran . tar xzf pgcluu-2.0.tar.gz cd pgcluu-2.0.tar/ perl Makefile.PL make sudo make install it's return like.. bash-4.1$ tar xzf pgcluu-2.0.tar.gz tar (child): pgcluu-2.0.tar.gz: Cannot open: No such file or directory tar (child): Error is not recoverable: exiting now tar: Child returned status 2 tar: Error is not recoverable: exiting now Well at this point you are dead in the water, all the other steps are bound to fail. Are you sure pgcluu-2.0.tar.gz is there? If it is, then it may be corrupted, so try downloading it again. -bash-4.1$ cd pgcluu-2.0.tar/ -bash: cd: pgcluu-2.0.tar/: No such file or directory -bash-4.1$perl Makefile.PL Can't open perl script Makefile.PL: No such file or directory -bash-4.1$ make sudo make install where do i run the tar file let me know.. thanks in advance.. R -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] pgcluu
where need to install Test-Harness-3.32. or ExtUtis/MakeMaker.pm in pg cluu -2.0 folder or /usr/local/lib64/perl.. please let me know advance thanks.. On Tue, Aug 12, 2014 at 3:20 PM, Ramesh T rameshparnandit...@gmail.com wrote: Hi, I don't have the root permission for yum. secondly,When i try to install it's return following same as previous error/msg tar xvzf Test-Harness-3.32.tar.gz cd Test-Harness-3.32 -bash-4.1$ *perl Makefile.PL* Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 8. BEGIN failed--compilation aborted at Makefile.PL line 8. -bash-4.1$ make make: *** No targets specified and no makefile found. Stop. -bash-4.1$ make test make: *** No rule to make target `test'. Stop. -bash-4.1$ let me know any help..? On Mon, Aug 11, 2014 at 9:07 PM, David Carpio dav...@consistentstate.com wrote: Hello You must install the harness.pm module You may do it via yum yum install perl-Test-Harness or downloaded the tar package and install it http://search.cpan.org/~leont/Test-Harness-3.32/lib/Test/Harness.pm I hope this help you. David On 08/11/2014 08:52 AM, Ramesh T wrote: Hello , I specified correct method i got error/message at perl Makefile.PL tar xzf pgcluu-2.0.tar.gz -bash-4.1$ cd pgcluu-2.0 *-bash-4.1$ perl Makefile.PL* Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1. BEGIN failed--compilation aborted at Makefile.PL line 1. -bash-4.1$ then i trying to install ExtUtils/MakeMaker same place perl Makefile.PL error/notifying tar xzf ExtUtils-MakeMaker-6.99_07.tar.gz -bash-4.1$ cd ExtUtils-MakeMaker-6.99_07 -bash-4.1$ perl Makefile.PL Using included version of CPAN::Meta (2.120351) because it is not already instal led. Using included version of ExtUtils::Install (1.54) because it is not already ins talled. Using included version of CPAN::Meta::YAML (0.008) because it is not already ins talled. Using included version of CPAN::Meta::Requirements (2.120351) because it is not already installed. Using included version of File::Copy::Recursive (0.38) because it is not already installed. Using included version of Parse::CPAN::Meta (1.4405) because it is not already i nstalled. Using included version of JSON::PP (2.27203) because it is not already installed . Using included version of JSON::PP::Compat5006 (1.09) because it is not already installed. Using included version of ExtUtils::Manifest (1.60) because it is not already in stalled. Generating a Unix-style Makefile Writing Makefile for ExtUtils::MakeMaker Writing MYMETA.yml and MYMETA.json Can't locate Test/Harness.pm in @INC (@INC contains: bundled/CPAN-Meta bundled/E xtUtils-Install bundled/CPAN-Meta-YAML bundled/CPAN-Meta-Requirements bundled/Fi le-Copy-Recursive bundled/Parse-CPAN-Meta bundled/JSON-PP bundled/version bundle d/Scalar-List-Utils bundled/JSON-PP-Compat5006 bundled/ExtUtils-Command bundled/ ExtUtils-Manifest bundled/File-Temp lib . /usr/local/lib64/perl5 /usr/local/shar e/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/per l5 /usr/share/perl5) at Makefile.PL line 142. wheni trying test Harness it is also not installing.. let me know how to fix issue..? thanks, rao On Sat, Aug 9, 2014 at 9:43 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/08/2014 06:40 AM, Ramesh T wrote: Hi, i want install pgcluu on postgres 9.3 and i'm putty tool to connect pg database when i ran . tar xzf pgcluu-2.0.tar.gz cd pgcluu-2.0.tar/ perl Makefile.PL make sudo make install it's return like.. bash-4.1$ tar xzf pgcluu-2.0.tar.gz tar (child): pgcluu-2.0.tar.gz: Cannot open: No such file or directory tar (child): Error is not recoverable: exiting now tar: Child returned status 2 tar: Error is not recoverable: exiting now Well at this point you are dead in the water, all the other steps are bound to fail. Are you sure pgcluu-2.0.tar.gz is there? If it is, then it may be corrupted, so try downloading it again. -bash-4.1$ cd pgcluu-2.0.tar/ -bash: cd: pgcluu-2.0.tar/: No such file or directory -bash-4.1$perl Makefile.PL Can't open perl script Makefile.PL: No such file or directory -bash
Re: [GENERAL] pgcluu
Hello , I specified correct method i got error/message at perl Makefile.PL tar xzf pgcluu-2.0.tar.gz -bash-4.1$ cd pgcluu-2.0 *-bash-4.1$ perl Makefile.PL* Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 1. BEGIN failed--compilation aborted at Makefile.PL line 1. -bash-4.1$ then i trying to install ExtUtils/MakeMaker same place perl Makefile.PL error/notifying tar xzf ExtUtils-MakeMaker-6.99_07.tar.gz -bash-4.1$ cd ExtUtils-MakeMaker-6.99_07 -bash-4.1$ perl Makefile.PL Using included version of CPAN::Meta (2.120351) because it is not already instal led. Using included version of ExtUtils::Install (1.54) because it is not already ins talled. Using included version of CPAN::Meta::YAML (0.008) because it is not already ins talled. Using included version of CPAN::Meta::Requirements (2.120351) because it is not already installed. Using included version of File::Copy::Recursive (0.38) because it is not already installed. Using included version of Parse::CPAN::Meta (1.4405) because it is not already i nstalled. Using included version of JSON::PP (2.27203) because it is not already installed . Using included version of JSON::PP::Compat5006 (1.09) because it is not already installed. Using included version of ExtUtils::Manifest (1.60) because it is not already in stalled. Generating a Unix-style Makefile Writing Makefile for ExtUtils::MakeMaker Writing MYMETA.yml and MYMETA.json Can't locate Test/Harness.pm in @INC (@INC contains: bundled/CPAN-Meta bundled/E xtUtils-Install bundled/CPAN-Meta-YAML bundled/CPAN-Meta-Requirements bundled/Fi le-Copy-Recursive bundled/Parse-CPAN-Meta bundled/JSON-PP bundled/version bundle d/Scalar-List-Utils bundled/JSON-PP-Compat5006 bundled/ExtUtils-Command bundled/ ExtUtils-Manifest bundled/File-Temp lib . /usr/local/lib64/perl5 /usr/local/shar e/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/per l5 /usr/share/perl5) at Makefile.PL line 142. wheni trying test Harness it is also not installing.. let me know how to fix issue..? thanks, rao On Sat, Aug 9, 2014 at 9:43 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 08/08/2014 06:40 AM, Ramesh T wrote: Hi, i want install pgcluu on postgres 9.3 and i'm putty tool to connect pg database when i ran . tar xzf pgcluu-2.0.tar.gz cd pgcluu-2.0.tar/ perl Makefile.PL make sudo make install it's return like.. bash-4.1$ tar xzf pgcluu-2.0.tar.gz tar (child): pgcluu-2.0.tar.gz: Cannot open: No such file or directory tar (child): Error is not recoverable: exiting now tar: Child returned status 2 tar: Error is not recoverable: exiting now Well at this point you are dead in the water, all the other steps are bound to fail. Are you sure pgcluu-2.0.tar.gz is there? If it is, then it may be corrupted, so try downloading it again. -bash-4.1$ cd pgcluu-2.0.tar/ -bash: cd: pgcluu-2.0.tar/: No such file or directory -bash-4.1$perl Makefile.PL Can't open perl script Makefile.PL: No such file or directory -bash-4.1$ make sudo make install where do i run the tar file let me know.. thanks in advance.. R -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] pgcluu
Hi, i want install pgcluu on postgres 9.3 and i'm putty tool to connect pg database when i ran . tar xzf pgcluu-2.0.tar.gz cd pgcluu-2.0.tar/ perl Makefile.PL make sudo make install it's return like.. bash-4.1$ tar xzf pgcluu-2.0.tar.gz tar (child): pgcluu-2.0.tar.gz: Cannot open: No such file or directory tar (child): Error is not recoverable: exiting now tar: Child returned status 2 tar: Error is not recoverable: exiting now -bash-4.1$ cd pgcluu-2.0.tar/ -bash: cd: pgcluu-2.0.tar/: No such file or directory -bash-4.1$perl Makefile.PL Can't open perl script Makefile.PL: No such file or directory -bash-4.1$ make sudo make install where do i run the tar file let me know.. thanks in advance.. R
[GENERAL] {xml}
Hello,
when i ran following query on postgres 9.3,
SELECT xmlagg(xmlelement(name e,part_id||',')) from part;
result
..
{xml}
how to get part_id's..? please let me know ..
advance thanks,
R..
[GENERAL] Postgres Performence
Hello, How to improve performence of postgres 9.3 database.And also in oracle web based Enterprise manger is available,in postgres any tool their to monitor and change. please let me know performence related info to my postgres 9.3 database.and any tools which are free and pay. thanks, ram
[GENERAL] TZ_OFFSET
Hello,
select TZ_OFFSET ('US/Eastern') from dual ;
it's returning in oracle
--
-04:00
but in postgres
select TZ_OFFSET ('US/Eastern');
its'returning like function tz_offset('us/Eastern') does not exist;
let me know how to solve issue
Re: [GENERAL] tab_to_sting
Hi, when i ran below statement its working fine.. select string_agg(part_id::text,':') from part; But, SELECT tab_to_largeStringcheck(cast(string_agg(part_id::text,':')as t_varchar2_tab)) FROM part [image: Inline image 1] when i ran like SELECT qa.tab_to_largeStringcheck(string_agg(part_id::text,':')) FROM qa.part its returnfunction( text)does'nt exist let me know how solve issue.. thanks, On Thu, Jul 24, 2014 at 10:42 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/24/2014 08:03 AM, Ramesh T wrote: I have try select string_agg(partname,':') from part_tab;its return same, ERROR: function string_agg(bigint, unknown) does not exist LINE 1: select string_agg(part_id,':') from part; Try: select string_agg(part_id::text,':') from part; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. i thought string_agg and array_agg same, is it right..? No: http://www.postgresql.org/docs/9.3/static/functions-aggregate.html array_agg(expression) any array of the argument type input values, including nulls, concatenated into an array string_agg(expression, delimiter) (text, text) or (bytea, bytea) same as argument types input values concatenated into a string, separated by delimiter -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] tab_to_sting
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab, p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS l_string VARCHAR2(32767); BEGIN FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP IF i != p_varchar2_tab.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_varchar2_tab(i); END LOOP; RETURN l_string; END tab_to_string; / The query below shows the COLLECT function in action. COLUMN employees FORMAT A50 SELECT deptno, tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES -- -- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES this function i need to run run in postgres. i think t_varchar2_tab is the create type in oracle ,we need to replace t_varchar2_tab with other type ..? i need this one please let me know.. thanks in advance , ramesh On Thu, Jul 24, 2014 at 2:11 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/23/2014 09:12 AM, Ramesh T wrote: Hi, SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS t_varchar2_tab)) AS employees FROM hr.customer when i run function for table column values to single row function name is hr.tab_to_largestring this code from oracle it return like function collect(character varying) does not exit please let me know in postgres collect () key is thier..? and how to run this function..? To help with getting answers, it would be helpful if you told the list what the Oracle function does or point to the documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/ functions031.htm#SQLRF51285 Another option would be to investigate EnterpriseDB as they have an Oracle compatibility layer available: http://www.enterprisedb.com/ -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] tab_to_sting
SELECT qa.tab_to_largestringcheck(cast(array_agg(part_id)as t_varchar2_tab),':')FROM qa.part when i replace string_agg it's return does not exit, need to enable string_agg ..?i think is predefined right On Thu, Jul 24, 2014 at 7:47 PM, Ramesh T rameshparnandit...@gmail.com wrote: postgres 9.3 On Thu, Jul 24, 2014 at 7:46 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/24/2014 07:11 AM, Ramesh T wrote: hi , i looked into that link ,when i run string_agg does not exist returns ,But i'm using function here not paasing table to the function only i'm passing column name and delimiter to the function from select statement please look into the my first post.. What version of Postgres are you using? The query below should work: SELECT deptno, string_agg(employee, ',') FROM emp GROUP BY deptno; thanks, ram -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] tab_to_sting
SELECT tab_to_largestring(cast(array_agg(dhar_id)as t_varchar2_tab),':')FROM qa.dhar when i run above statement it's return can't convert bigint to the t_varchar2_tab for the above function,i think problem at the t_varchar2_tab .. please any help..?runs on postgres 9.3 thanks in advance, ramesh On Thu, Jul 24, 2014 at 6:24 PM, Ramesh T rameshparnandit...@gmail.com wrote: CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab, p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS l_string VARCHAR2(32767); BEGIN FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP IF i != p_varchar2_tab.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_varchar2_tab(i); END LOOP; RETURN l_string; END tab_to_string; / The query below shows the COLLECT function in action. COLUMN employees FORMAT A50 SELECT deptno, tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES -- -- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES this function i need to run run in postgres. i think t_varchar2_tab is the create type in oracle ,we need to replace t_varchar2_tab with other type ..? i need this one please let me know.. thanks in advance , ramesh On Thu, Jul 24, 2014 at 2:11 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/23/2014 09:12 AM, Ramesh T wrote: Hi, SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS t_varchar2_tab)) AS employees FROM hr.customer when i run function for table column values to single row function name is hr.tab_to_largestring this code from oracle it return like function collect(character varying) does not exit please let me know in postgres collect () key is thier..? and how to run this function..? To help with getting answers, it would be helpful if you told the list what the Oracle function does or point to the documentation: http://docs.oracle.com/cd/E11882_01/server.112/e26088/ functions031.htm#SQLRF51285 Another option would be to investigate EnterpriseDB as they have an Oracle compatibility layer available: http://www.enterprisedb.com/ -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] tab_to_sting
HI, when i use string_agg function it returns string_agg(bigint) does'nt exist. when array_string function it's return can't convert bigint to the t_varchar2_tab. i have questiont_varcha2_tab type is available on postgres 9.3..? i need it please let me know thanks in advance, On Thu, Jul 24, 2014 at 7:26 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/24/2014 05:54 AM, Ramesh T wrote: CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab, p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS l_string VARCHAR2(32767); BEGIN FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP IF i != p_varchar2_tab.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_varchar2_tab(i); END LOOP; RETURN l_string; END tab_to_string; / The query below shows the COLLECT function in action. COLUMN employees FORMAT A50 SELECT deptno, tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES -- -- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES this function i need to run run in postgres. i think t_varchar2_tab is the create type in oracle ,we need to replace t_varchar2_tab with other type ..? i need this one please let me know.. Well following Hubert's suggestion, here is a SO answer using the string_agg function that seems to apply. thanks in advance , ramesh -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] tab_to_sting
i ran the \df string_agg is their but retuns like ERROR: function string_agg(character varying) does not exist LINE 2: qa.tab_to_largestringcheck(cast(string_agg(part_num)as t_var... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. On Thu, Jul 24, 2014 at 8:00 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/24/2014 07:22 AM, Ramesh T wrote: SELECT qa.tab_to_largestringcheck(cast(array_agg(part_id)as t_varchar2_tab),':')FROM qa.part when i replace string_agg it's return does not exit, My guess is if you look at the error message it is complaining about the type of argument passed in. Please show us the actual error message if that is not the case. need to enable string_agg ..?i think is predefined right From psql: production=# SELECT version(); version - PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit (1 row) production=# \df string_agg List of functions Schema |Name| Result data type | Argument data types | Type ++--+-+-- pg_catalog | string_agg | bytea| bytea, bytea| agg pg_catalog | string_agg | text | text, text | agg (2 rows) -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] tab_to_sting
I have try select string_agg(partname,':') from part_tab;its return same, ERROR: function string_agg(bigint, unknown) does not exist LINE 1: select string_agg(part_id,':') from part; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. i thought string_agg and array_agg same, is it right..? On Thu, Jul 24, 2014 at 8:19 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/24/2014 07:46 AM, Ramesh T wrote: i ran the \df string_agg is their but retuns like ERROR: function string_agg(character varying) does not exist LINE 2: qa.tab_to_largestringcheck(cast(string_agg(part_num)as t_var... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Please, do not top post. Also try the query I sent you off-list: SELECT deptno, string_agg(employee, ',') FROM emp GROUP BY deptno; Forget about the t_varcha2_tab type. -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] tab_to_sting
Hi, SELECT hr.tab_to_largestring(CAST(COLLECT(cust_name) AS t_varchar2_tab)) AS employees FROM hr.customer when i run function for table column values to single row function name is hr.tab_to_largestring this code from oracle it return like function collect(character varying) does not exit please let me know in postgres collect () key is thier..? and how to run this function..?
Re: [GENERAL] Need r_constraint_name
thank u ,
SELECT constraint_name
FROM information_schema.table_constraints AS tc
WHERE tc.table_name = p_table_name
AND constraint_name IN (SELECT constraint_name
FROM
information_schema.table_constraints AS tc
WHERE tc.table_name =
p_ref_table_name
AND tc.constraint_type =
'PRIMARY KEY');
is this correct process same as above ..
but i want check r_constraint_name instead of constraint_name in outer
statement in above code..
please let me know..
thanks in advance,
ramesh
On Tue, Jul 22, 2014 at 7:52 AM, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 07/19/2014 12:26 PM, Ramesh T wrote:
Hi,
In oracle got constraint details using user_constraint,
But in postgres how to get the r_constraint_name,constraint_name of the
particular table...?
mainly i need r_constraint_name on table.. how to get it?please let me
know
From psql:
test= CREATE TABLE parent_tbl(id serial primary key, fld_1 text);
NOTICE: CREATE TABLE will create implicit sequence parent_tbl_id_seq
for serial column parent_tbl.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
parent_tbl_pkey for table parent_tbl
CREATE TABLE
test= CREATE TABLE child_tbl (id serial primary key, fk_fld integer
references parent_tbl, fld_2 text);
NOTICE: CREATE TABLE will create implicit sequence child_tbl_id_seq for
serial column child_tbl.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
child_tbl_pkey for table child_tbl
CREATE TABLE
test= \d parent_tbl
Table public.parent_tbl
Column | Type |Modifiers
+-+-
id | integer | not null default nextval('parent_tbl_id_seq'::
regclass)
fld_1 | text|
Indexes:
parent_tbl_pkey PRIMARY KEY, btree (id)
Referenced by:
TABLE child_tbl CONSTRAINT child_tbl_fk_fld_fkey FOREIGN KEY
(fk_fld) REFERENCES parent_tbl(id)
test= \d child_tbl
Table public.child_tbl
Column | Type | Modifiers
+-+-
---
id | integer | not null default nextval('child_tbl_id_seq'::regclass)
fk_fld | integer |
fld_2 | text|
Indexes:
child_tbl_pkey PRIMARY KEY, btree (id)
Foreign-key constraints:
child_tbl_fk_fld_fkey FOREIGN KEY (fk_fld) REFERENCES parent_tbl(id)
If you want to know what query psql uses to get this information start
psql with -E, this will tell you that the queries are:
To get the child key that references the parent from the parent:
test= SELECT conname, conrelid::pg_catalog.regclass,
pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = 'parent_tbl'::regclass AND c.contype = 'f' ORDER BY 1
;
conname| conrelid | condef
---+---+
child_tbl_fk_fld_fkey | child_tbl | FOREIGN KEY (fk_fld) REFERENCES
parent_tbl(id)
To get the information from the child table:
test= SELECT conname,
pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = 'child_tbl'::regclass AND r.contype = 'f' ORDER BY 1
;
conname| condef
---+
child_tbl_fk_fld_fkey | FOREIGN KEY (fk_fld) REFERENCES parent_tbl(id)
I used the regclass cast to convert the table names to the appropriate ids
the query expects. In the psql output you will see the numbers.
--
Adrian Klaver
adrian.kla...@aklaver.com
Fwd: [GENERAL] Need r_constraint_name
-- Forwarded message -- From: Ramesh T rameshparnandit...@gmail.com Date: Tue, Jul 22, 2014 at 7:50 PM Subject: Re: [GENERAL] Need r_constraint_name To: Adrian Klaver adrian.kla...@aklaver.com Just i'm retriving the constraint_name when i enter child_table_name for inner query and that constraint name is checking parent_table on outer statement that constraint_name is equal then display the constraint name ..?but outer select is r_constraint_name i think in postgres r_constraint_name is also include in the pg_constraints details not a seperate column in postgres for that ,if parent table have consraint_name same as the child table return from inner query that constraint_name displayed out.. my assumption..is it corect? from last query.. thanks in advance.. ramesh On Tue, Jul 22, 2014 at 7:18 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/22/2014 03:12 AM, Ramesh T wrote: thank u , SELECT constraint_name FROM information_schema.table_constraints AS tc WHERE tc.table_name = p_table_name AND constraint_name IN (SELECT constraint_name FROM information_schema.table_constraints AS tc WHERE tc.table_name = p_ref_table_name AND tc.constraint_type = 'PRIMARY KEY'); is this correct process same as above .. but i want check r_constraint_name instead of constraint_name in outer statement in above code.. I am not sure you are going to find that column. I am not an Oracle user but I did find this: http://docs.oracle.com/html/B13531_01/ap_d.htm R_CONSTRAINT_NAME is the name of the unique constraint definition for the referenced table. So it would seem r_constraint_name is an column name in an Oracle system view. I know of no such name in the Postgres system catalog. I am sure the same information is available, you are just going to have to be specific about what you are looking for. From the above that would seem to be the name of the unique key that a foreign key references. Is that correct? If so the query you show above will not work as a UNIQUE key does not necessarily have to be the PRIMARY KEY. please let me know.. thanks in advance, ramesh -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] cursor return null
Hello, in postgres function (id bigint ),the following code not return any value with artNums ,But when i do select statement ony it's output the values with out include cursor i.e,cursor problem ..? please let me know what should i do to getvalues from cursor, but i dont where i missing function (id bigint ) DECLARE PartNums varchar (1); artNums CURSOR for SELECT p.PART_NUM part_num FROM lineitem sol, part p WHERE sol.ORDER_ID = id AND p.PART_ID = sol.PART_ID; BEGIN FOR cPart IN artNums LOOP BEGIN PartNums := PartNums || cPart.part_num || ', '; END;
[GENERAL] Need r_constraint_name
Hi, In oracle got constraint details using user_constraint, But in postgres how to get the r_constraint_name,constraint_name of the particular table...? mainly i need r_constraint_name on table.. how to get it?please let me know
[GENERAL] performance monitoring/tuning
Hi, How performance monitoring/tuning on postgres..? please let me know some links to learn, ex-query performence, i need it .. thanks in advance, ramesh
