DBLINK Between Postgres and EnterpriseDB

650 Views Asked by At

I have 2 databases, one of them (DB_1) is POSTGRESQL 9.3 on port 5432 and other one (DB_2) is Postgres EnterpriseDB on port 5444. I'm trying to create a view in DB_1 by creating a DBLINK between the 2 databases by running the following statement:

    CREATE EXTENSION IF NOT EXISTS dblink;
    SELECT customer.cust_key FROM dblink
    ('dbname=**** port=5444 user=**** password=****',
     'SELECT cust_key FROM data.customer    WHERE customer.tab_no=0') 
      AS customer (cust_key char(12));

where customer is the table in DB_2, cust_key is the field within customer table and data is the schema on DB_2

I'm getting the following error:

ERROR:  invalid value for parameter "DateStyle": "Redwood, SHOW_TIME"
DETAIL:  Unrecognized key word: "redwood".

Upon further investigation, I found that postgressql.conf for DB_2 (EDB) contains the following:

# - Locale and Formatting -
#datestyle = 'iso, dmy'         # PostgreSQL default for your locale
datestyle = 'redwood,show_time'
edb_redwood_date = on           # translate DATE to TIMESTAMP(0)

where as DB_1 (Postgres) has the following:

datestyle = 'iso, mdy'

I have tried changing the datestyle property in EDB (DB_2) to match DB_1, but am still getting the same error. Strangely, their is no field of Date type in customer table, so why this property is causing an issue, is beyond me!

Any suggestions on how to get this to work please??

Thanks

0

There are 0 best solutions below