[GENERAL] how to find the number of rows inserted into the master table?

Hi, I have a master table 'Master' with 3 partition tables 'child1', 'child2',' child3' which inherits the master table 'Master'. I have check constraints in the child tables to insert the appropriate values and also there are functions and triggers defined to do this. My question is, if I inser

Re: [GENERAL] foreign key violation error with partitioned table

The postgresql partitions is done using inheritance . So basically your master table is empty and the child tables(partitions) contains all the records...right. You can check if your master table contains any records by using this query SELECT * FROM ONLY This will return zero if your master t

[GENERAL] how to find the number of rows inserted into the master table?

Hi, I have a master table 'Master' with 3 partition tables 'child1', 'child2',' child3' which inherits the master table 'Master'. I have check constraints in the child tables to insert the appropriate values and also there are functions and triggers defined to do this. My question is, if I inser

[GENERAL] CLOB support in postgresql

What is the best way to store clob data in postgresql? Currently I store my clob data as a text. The data consists of large xml files. When I access the text field using getString method in jdbc, the program is not able to hold a huge string in memory .If I can stream this data to and from the

Re: [GENERAL] oracle rank() over partition by queries

Thanks Markus Markus Schiltknecht <[EMAIL PROTECTED]> wrote: Hello Sharmi Joe, sharmi Joe wrote: > Is there a way to get the oracle's rank() over partition by queries in > postgresql? These are known as window functions. AFAIK Gavin Sherry is working on an implementation for Postgres. Regards

[GENERAL] creation of tables with warnings

Hi Sometimes when I create a table with the CREATE TABLE sql command, it creates a table but with some warnings For example, I create this table in Aqua studio as create table foo(col1 varchar2); gives Warnings: ---> W (1): <--- 0 record(s) affected The table is created. So

Re: [GENERAL] creation of tables with warnings

sorry about that. I copied it.This is proper one create table foo(col1 varchar); i use aqua studio Thanks Scott Marlowe <[EMAIL PROTECTED]> wrote: On 9/17/07, SHARMILA JOTHIRAJAH wrote: > Hi > Sometimes when I create a table with the > CREATE TABLE sql command, > it create

[GENERAL] Postgresql simple query performance question

Hi We are in the process of testing for migration of our database from Oracle to Postgresql. I hava a simple query Select count(*) from foo This table has 29384048 rows and is indexed on foo_id The tables are vacuumed and the explain plan for postgresql is QUERY PLAN

Re: [GENERAL] Postgresql simple query performance question

foo (cost=0.00..1120560.48 rows=29384048 width=0) > (actual time=0.232..60657.948 rows=29384048 loops=1) > Total runtime: 68797.358 ms Thanks again sharmila - Original Message From: Pavel Stehule <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc:

Re: [GENERAL] Postgresql simple query performance question

<[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: Pavel Stehule <[EMAIL PROTECTED]>; pgsql-general@postgresql.org Sent: Tuesday, November 6, 2007 8:03:48 PM Subject: Re: [GENERAL] Postgresql simple query performance question "SHARMILA JOTHIRAJAH" <

Re: [GENERAL] Postgresql simple query performance question

From: Simon Riggs <[EMAIL PROTECTED]> To: Bill Moran <[EMAIL PROTECTED]> Cc: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]>; pgsql-general@postgresql.org Sent: Wednesday, November 7, 2007 6:34:26 AM Subject: Re: [GENERAL] Postgresql simple query performance question On Tue, 2007-11

[GENERAL] Postgres table size

Hi I have a table with 29384048 records in oracle and postgresql. The table has 47 columns (16 numeric and 27 varchar and the rest timestamp). The tablesize in postgresql is twice as much than the tablesize in oracle (for the same number of rows and columns). There are no updates or deletes in

[GENERAL] pg_dump problem

Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found What causes this problem? Thanks sharmila _

Re: [GENERAL] pg_dump problem

nesday, November 14, 2007 1:41:56 PM Subject: Re: [GENERAL] pg_dump problem On Wed, 2007-11-14 at 10:32 -0800, SHARMILA JOTHIRAJAH wrote: > Hi > I try to use pg_dump to dump my database. > pg_dump smrs > and it gives me an error > pg_dump: failed sanity check, parent table OID

Re: [GENERAL] pg_dump problem

670739 0 n so what else can cause tis problem? sharmila - Original Message From: Tom Lane <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 2:21:03 PM Subject:

Re: [GENERAL] pg_dump problem

- Original Message From: Tom Lane <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 3:33:20 PM Subject: Re: [GENERAL] pg_dump problem SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> writes: > lo

Re: [GENERAL] pg_dump problem

-0800, SHARMILA JOTHIRAJAH wrote: > Hi > I try to use pg_dump to dump my database. > pg_dump smrs > and it gives me an error > pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found Is there any possibility you have Slony installed? Is this a rep

Re: [GENERAL] pg_dump problem

Hi The dump works now after deleting those rows from the pg_rewrite table Thanks for your help sharmila - Original Message From: Tom Lane <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 3:33:2

Re: [GENERAL] Postgres table size

o: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Sent: Friday, November 16, 2007 2:12:46 PM Subject: Re: [GENERAL] Postgres table size On Fri, 2007-11-16 at 07:36 -0800, SHARMILA JOTHIRAJAH wrote: > Hi, > > You were right. I installed beta2 and the table size now is > 4682817536. Thanks

Re: [GENERAL] Postgres table size

didnot delete/insert/update any rows after the first insertion. Thanks sharmila - Original Message From: Erik Jones <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org Sent: Wednesday, November 21, 2007 11:38:44 AM Subject:

Re: [GENERAL] Postgres table size

>>> Calculation >> >varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes >> >*for 5 varchar cols = >> >5*7 = 35 bytes >> > >> >numeric (according to manual--- The actual storage requirement is >> >two bytes for each group of four deci

Re: [GENERAL] Postgres table size

Thanks Tom and Erik and all the others who helped. You guys really rock!!! Sharmila - Original Message From: Tom Lane <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: Erik Jones <[EMAIL PROTECTED]>; pgsql-general@postgresql.org Sent: Wednesday, Nove

[GENERAL] postgres 8.3 beta 2 storage question

Hi, 1.How does postgres version 8.3 betat 2 handle varchar and numeric data types in terms of storage I understand for varchar it has 1byte overhead (instead of 4) if length<128 How does it handle for numeric? The manual says "The actual storage requirement is two bytes for each group of fo

Re: [GENERAL] postgres 8.3 beta 2 storage question

> 1.How does postgres version 8.3 betat 2 handle varchar and numeric data > types in terms of storage > I understand for varchar it has 1byte overhead (instead of 4) if length<128 > How does it handle for numeric? The manual says > "The actual storage requirement is two bytes for each group of

[GENERAL] WAL shipping question

Hi, Im trying to play a bit with log shipping between 2 servers primary and standby. These servers are running versions 8.3betat3. I had set up continuous archiving at the primary server. The manuals' example of archive_command is archive_command = 'cp -i %p /mnt/server/archivedir/%f http://tools

Re: [GENERAL] WAL shipping question

> Im trying to play a bit with log shipping between 2 servers primary and standby. These servers are running versions 8.3betat3. I had set up continuous archiving at the primary server. The manuals' example of archive_command is > archive_command = 'cp -i %p /mnt/server/archivedir/%f > This b

Re: [GENERAL] WAL shipping question

> This basically archives the data in the primary server itself...right!!! > But how can I set up continuous archiving from primary to a directory > (WAL archive directory) on the stand-by server ? The closest thing to a worked out example of how to do this I'm aware of is at http://archive

Re: [GENERAL] WAL shipping question

> This basically archives the data in the primary server itself...right!!! > But how can I set up continuous archiving from primary to a directory > (WAL archive directory) on the stand-by server ? >>The closest thing to a worked out example of how to do this I'm aware of >>is at http://ar

Re: [GENERAL] WAL shipping question

>The main thing that's improved in 8.3 is the integration of pg_standby as >a more rugged restore_command than most people were coding on their own: >http://www.postgresql.org/docs/8.3/static/pgstandby.html >You should use it instead of the example restore.sh included in the >message I refe

[GENERAL] record-based log shipping

Hi, Have anyone implemented or tried record-based log shipping? If so is there any other materials in the web other than the documentation (it has very few details about this) Thanks sharmila Be a bett

Re: [GENERAL] record-based log shipping

Thanks > Have anyone implemented or tried record-based log shipping? > If so is there any other materials in the web other than the > documentation (it has very few details about this) > >>I don't know exactly what you mean by "record-based log shipping", but >>perhaps you're looking for somethin

[GENERAL] Version Upgrade using WAL files ?

Hi, Is it possible to implement postgres version upgrade using WAL files? Currently the WAL file replication donot work between two different postgres version. Is there a posiibility to implement this using WAL files so that when I want to upgrade to the latest version I can use these WAL? Thank

[GENERAL] slony1 replication question

Hi, I need some help with slony... I read the slony manual and tried to replicate the database as given in this... http://www.slony.info/documentation/firstdb.html These are the steps that I did. 1. set the shell variables like clustername,masterdbname etc thro export command 2. created a user

[GENERAL] slony error --need help

Hi I had posted this in the slony mailing list but no luck in getting any answers...Pls help me as I'm stuck with this error for the last 4 days Im trying to replicate between postgres version 7.4.18 and version 8.1.10. I configured postgres-7.4 with enable-thread-safety option I configured

[GENERAL] postgres 8.3 betat 1 version download

Hi, I want to download postgresql 8.3 beta 1 version for testing a patch. Im not able to download this from this website http://www.postgresql.org/ftp/source/v8.3beta1/ and its giving an FTP error "550 Failed to change directory" Is there any other way I can get this betat1 version? Thanks sharmi

[GENERAL] Postgres auditing features

Hi Does postgresql have any build-in auditing features like in Oracle's total-recall or fine grained auditing   http://www.oracle.com/technology/products/database/oracle11g/pdf/total-recall-datasheet.pdf http://www.oracle.com/technology/deploy/security/database-security/fine-grained-audit

[GENERAL] Oracle Help in PG?

Hi, Do we have anything like "Oracle Help for Java" in Postgresql? Thanks

[GENERAL] Heikkki's Visibility Map patch for postgres 8.4 ?

Hi, Is Heikki's Visibility Map patch included for the Postgresql 8.4 version http://archives.postgresql.org/pgsql-hackers/2007-11/msg00142.php If not whats the status of that patch? Im especially interested in the index-only scan mentioned there!!! Thanks Sharmila

[GENERAL] Manipulating Large Object datatype in Postgresql

Hi, We have tables with data type CLOb and BLOBs (in oracle). This needs o be migrated to Postgresql. What data types can be used for this. I've done some resaerch/search in this and found that (correct me if Im wrong) 1. For CLObs the equivalent are TEXT and OID(lob). But streaming(thro' jdbc) i

[GENERAL] dbi_link help

Hi, Im new to dbi_link. I had installed dbi_link and run the dbi_link.sql script . This is the script that I ran after that and it didn't have any errors.  Now the schemas dbi_link and EMPLOYEE are created in my postgres database. The user is "postgres" in both the databases with the same passwo

[GENERAL] ora2pg or dbi_link ?

Hi, Ive been struggling to query some of my oracle tables from  postgres using the dbi_link and its not working. Have anyone tried ora2pg for querying the oracle database from postgres? If so whats your feedback on that? Thanks Sharmila

[GENERAL] ora2pg or dbi_link ?

Hi, Ive been struggling to query some of my oracle tables from  postgres using the dbi_link and its not working. Have anyone tried ora2pg for querying the oracle database from postgres? If so whats your feedback on that? Thanks Sharmila

Re: [GENERAL] dbi_link help

Thanks Richard --- On Tue, 2/10/09, Richard Huxton wrote: From: Richard Huxton Subject: Re: [GENERAL] dbi_link help To: sharmi...@yahoo.com Cc: "General postgres mailing list" Date: Tuesday, February 10, 2009, 2:51 AM SHARMILA JOTHIRAJAH wrote: > When I try to insert into or se

Re: [GENERAL] ora2pg or dbi_link ?

Thanks Peter... --- On Tue, 2/10/09, Peter Eisentraut wrote: From: Peter Eisentraut Subject: Re: [GENERAL] ora2pg or dbi_link ? To: sharmi...@yahoo.com Cc: "General postgres mailing list" Date: Tuesday, February 10, 2009, 1:46 AM SHARMILA JOTHIRAJAH wrote: > Ive been struggling

[GENERAL] Good Delimiter for copy command

Hi, What is a good delimiter to use for COPY command. Im trying to use COPY command to copy data from one table to another in 2 different databases. Can you suggest a unique delimiter that I can use for this COPY command Thanks Sharmial

Re: [GENERAL] dbi_link help

;  data_source_id | env_name | env_value | env_action +--+---+---- (0 rows) it returns no rows... Why is this table NULL ? If anyone is using dbi-link and has some solution pls reply Thanks Sharmila --- On Mon, 2/9/09, SHARMILA JOTHIRAJAH wrote: From: S

[GENERAL] COPy command question

Hi, A question about the Postgresql's COPY command. This is the syntax of this command from the manual COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] . I want to migrate my tables from Oracle to Postgres. The COPY FROM command can take input fro

Re: [GENERAL] COPy command question

ches ? Also does COPY treat timestamp & LOBs data different? Thanks Sharmila --- On Thu, 2/12/09, Scott Marlowe wrote: From: Scott Marlowe Subject: Re: [GENERAL] COPy command question To: sharmi...@yahoo.com Cc: "General postgres mailing list" Date: Thursday, February 12, 2009, 1:3

Re: [GENERAL] Good Delimiter for copy command

ql-general@postgresql.org Date: Tuesday, February 10, 2009, 2:41 PM On Tue, Feb 10, 2009 at 07:07:42AM -0800, SHARMILA JOTHIRAJAH wrote: > What is a good delimiter to use for COPY command. Im trying to use > COPY command to copy data from one table to another in 2 different > databases. > Ca

Re: [GENERAL] Good Delimiter for copy command

--- On Thu, 2/12/09, Andrew Gould wrote: > From: Andrew Gould > Subject: Re: [GENERAL] Good Delimiter for copy command > To: sharmi...@yahoo.com > Cc: pgsql-general@postgresql.org, "Sam Mason" > Date: Thursday, February 12, 2009, 4:15 PM > On Thu, Feb 1

[GENERAL] How to pipe the psql copy command to Unix 'Date' command

Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying that to postgres using copy. So I need something like this Start_Time|java testCode ...|psql -c "copy dummy from stdin with delimiter ',' null 'NULL'" test| End_time so that the outp

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

--- On Thu, 2/19/09, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' > command > To: sharmi...@yahoo.com > Cc: "General postgres mailing list" > Date: Thursday, February 19, 2009, 12:31 PM

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

t; > Date: Thursday, February 19, 2009, 1:31 PM > On Feb 19, 2009, at 9:40 AM, SHARMILA JOTHIRAJAH wrote: > > > --- On Thu, 2/19/09, Tom Lane > wrote: > > > >> From: Tom Lane > >> Subject: Re: [GENERAL] How to pipe the psql copy > command to Unix 

[GENERAL] NOVALIDATE in postgresql?

Hi In Oracle I can use the NOVALIDATE for constraints... like this ALTER TABLE employee ADD CONSTRAINT emp_ck CHECK (married IN ('Y','N')) NO VALIDATE; When the table is already populated this will be faster. Can you do the same in Postgresql? Thanks Sharmila -- Sent via pgsql-general

[GENERAL] Function that returns Boolean

Hi, This is a simple function that returns a boolean .. create or replace function check_value( newValue IN VARCHAR, oldValue IN VARCHAR ) RETURN BOOLEAN as ' BEGIN IF ( newValue != oldValue) then return true; else return false; END IF; END; ' LANGU

Re: [GENERAL] Function that returns Boolean

Its a typo..it should be "RETURNS BOOLEAN" and not "RETURN BOOLEAN" -Sharmila --- On Mon, 2/23/09, SHARMILA JOTHIRAJAH wrote: > From: SHARMILA JOTHIRAJAH > Subject: Function that returns Boolean > To: "General postgres mailing list" > Date: Monday, F

Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command

hursday, February 19, 2009, 11:08 PM > On Feb 19, 2009, at 6:30 PM, R Smith wrote: > > > > > On Feb 19, 2009, at 11:07 AM, SHARMILA JOTHIRAJAH > wrote: > > > >> Thanks all > >> -Sharmila > >> > >> > >> --- On Thu, 2/19/0

[GENERAL] Sync 2 tables in 2 databases

Hi, I have 2 postgres databases with similar structure. I want to keep some tables in sync in these 2 databases(They can be synced just once a day). Is there a way to archive this using function ? Something like Select syncTable('foo') where syncTable is a function that compares table 'f

Re: [GENERAL] Sync 2 tables in 2 databases

--- On Wed, 3/11/09, Scott Marlowe wrote: > > > > Hi, > > I have 2 postgres databases with similar structure. I > want to keep some tables in sync in these 2 databases(They > can be synced just once a day). Is there a way to archive > this using function ? > > Something like > > > > Selec

Re: [GENERAL] Sync 2 tables in 2 databases

> > Hi, > > I have 2 postgres databases with similar structure. I > want to keep some tables in sync in these 2 databases(They > can be synced just once a day). Is there a way to archive > this using function ? > > Something like > > > > Select syncTable('foo') > > > > where syncTable is a f

[GENERAL] Read a CLOB data from an Oracle table and INSERT it into a BYTEA column in Postgres using jdbc?

Hi, Im reading from an Oracle table and inserting the values to a postgres table thro' jdbc. Everything works fine except for the BLOB data in Oracle. My code snippet ... while (rs1.next()) { for (int m=1;m<=colCount;m++) { pstmt.set

[GENERAL] Space for pg_dump

Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Space for pg_dump

--- On Tue, 3/31/09, Scott Marlowe wrote: > From: Scott Marlowe > Subject: Re: [GENERAL] Space for pg_dump > To: "SHARMILA JOTHIRAJAH" > Cc: "General postgres mailing list" > Date: Tuesday, March 31, 2009, 11:49 AM > On Tue, Mar 31, 2009 at 7:5

Re: [GENERAL] Space for pg_dump

--- On Tue, 3/31/09, Scott Marlowe wrote: > From: Scott Marlowe > Subject: Re: [GENERAL] Space for pg_dump > To: "SHARMILA JOTHIRAJAH" > Cc: "General postgres mailing list" > Date: Tuesday, March 31, 2009, 12:07 PM > On Tue, Mar 31, 2009 at 9:5

[GENERAL] How to find the query completeion time?

Hi, Is there a way in Postgres to find when a particular query will finish? For example, for a query like this SELECT * FROM TABLE1 Can we find out from any of the catalog tables(or any other way) when this query is likely to complete? Thanks -- Sent via pgsql-general mailing list

Re: [GENERAL] How to find the query completeion time?

--- On Thu, 4/2/09, Leif B. Kristensen wrote: > From: Leif B. Kristensen > Subject: Re: [GENERAL] How to find the query completeion time? > To: pgsql-general@postgresql.org > Date: Thursday, April 2, 2009, 10:53 AM > On Thursday 2. April 2009, SHARMILA > JOTHIRAJAH wrote:

[GENERAL] Convert Oracle function to PostgreSQL

Hi, I use this Oracle function(from AskTom - http://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:210612357425) SQL> create or replace type myTableType as table of varchar2 (255); 2 / Type created. ops$tk...@dev8i> create or replace function in_list( p_str

[GENERAL] Good PG Books ?

Hi, I'm trying to get a good postgresql book for reference. I love the Postgresql manual but I would also like to keep a good PG book handy. Any suggestions? Thanks