Postgres Pro
Downloads
Home   >   mailing lists

Thread: dblinks

dblinks

From
sumaya@silvermoongroup.com
Date:
Hi,
 
I am accessing data across different databases using dblinks but the dates are getting messed up:
table: mytable
columns: my_id        int
                my_date    date
values:    1
                2008-04-09
 
When retrieving data from the table in the relevant database the date is returned correctly: 2008-04-09
but when retrieving across dblinks using statement below, I get the following error:
statement looks like
 
select * from mytable@mydblink;
 
the error refers to the datestyle setting and shows the date as '2008-09-04'
 
Please advise if there is a way around this.
 
Thanks,
Sumaya
 

Re: dblinks

From
"Jonah H. Harris"
Date:
On Sat, May 17, 2008 at 6:58 PM,  <sumaya@silvermoongroup.com> wrote:
> statement looks like
> select * from mytable@mydblink;

Postgres does not support this style of database link syntax.  Are you
using Oracle or EnterpriseDB?

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

Re: dblinks

From
sumaya@silvermoongroup.com
Date:
Hi,
 
I'm using enterpisedb.
 
The thing is it does return data, it's just that if there is a date column on the table, it gives me the error.  Is there no way around this?  Unfortunately the architecture will be able to be changed.
 
Thanks,
Sumaya
-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Saturday, May 17, 2008 07:31 PM
To: sumaya@silvermoongroup.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] dblinksre

On Sat, May 17, 2008 at 6:58 PM, wrote: > statement looks like > select * from mytable@mydblink; Postgres does not support this style of database link syntax. Are you using Oracle or EnterpriseDB? -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/

Re: dblinks

From
sumaya@silvermoongroup.com
Date:
Hi,
 
I meant to say, the architecture will NOT be able to be changed.
 
Regards,
Sumaya
-----Original Message-----
From: sumaya@silvermoongroup.com [mailto:sumaya@silvermoongroup.com]
Sent: Sunday, May 18, 2008 04:47 AM
To: 'Jonah H. Harris', sumaya@silvermoongroup.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] dblinks

Hi,
 
I'm using enterpisedb.
 
The thing is it does return data, it's just that if there is a date column on the table, it gives me the error.  Is there no way around this?  Unfortunately the architecture will be able to be changed.
 
Thanks,
Sumaya
-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Saturday, May 17, 2008 07:31 PM
To: sumaya@silvermoongroup.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] dblinksre

On Sat, May 17, 2008 at 6:58 PM, wrote: > statement looks like > select * from mytable@mydblink; Postgres does not support this style of database link syntax. Are you using Oracle or EnterpriseDB? -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/

Re: dblinks

From
"Jonah H. Harris"
Date:
On Sun, May 18, 2008 at 4:47 AM,  <sumaya@silvermoongroup.com> wrote:
> I'm using enterpisedb.

OK.  As the Postgres community only supports PostgreSQL, please submit
this and all future EnterpriseDB questions directly to
support@enterprisedb.com or http://forums.enterprisedb.com.

Thanks.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

Re: dblinks

From
"Ramasubramanian G"
Date:

HI Sumaya,

            This is the way you have to use dblink. And one more think. To excute this query you nedd to have dblink functions installed in your database schema.

select * from dblink('YOUR_DB_LINK_NAME','select * from mytable')as tmp(column1 datatype,column2 datatype.....)

Regards,

Ram

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of sumaya@silvermoongroup.com
Sent: Sunday, May 18, 2008 2:18 PM
To: Jonah H. Harris; sumaya@silvermoongroup.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] dblinks

 

Hi,

 

I'm using enterpisedb.

 

The thing is it does return data, it's just that if there is a date column on the table, it gives me the error.  Is there no way around this?  Unfortunately the architecture will be able to be changed.

 

Thanks,

Sumaya

-----Original Message-----
From: Jonah H. Harris [mailto:jonah.harris@gmail.com]
Sent: Saturday, May 17, 2008 07:31 PM
To: sumaya@silvermoongroup.com
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] dblinksre

On Sat, May 17, 2008 at 6:58 PM, wrote: > statement looks like > select * from mytable@mydblink; Postgres does not support this style of database link syntax. Are you using Oracle or EnterpriseDB? -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/

Re: dblinks

From
"Dave Page"
Date:
On Mon, May 19, 2008 at 4:06 AM, Ramasubramanian G
<ramasubramanian.g@renaissance-it.com> wrote:
> HI Sumaya,
>
>             This is the way you have to use dblink. And one more think. To
> excute this query you nedd to have dblink functions installed in your
> database schema.
>
> select * from dblink('YOUR_DB_LINK_NAME','select * from mytable')as
> tmp(column1 datatype,column2 datatype.....)

The OP was using EnterpriseDB's Postgres Plus Advanced Server, which
has Oracle-style dblinks built in. They are not used in the same way
as the PostgreSQL dblink module.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com