Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ddAQe-0006jJ-1D for pgsql-performance@arkaria.postgresql.org; Thu, 03 Aug 2017 07:20:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ddAQd-0005sv-F4 for pgsql-performance@arkaria.postgresql.org; Thu, 03 Aug 2017 07:20:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ddAOt-0002g6-52 for pgsql-performance@postgresql.org; Thu, 03 Aug 2017 07:18:31 +0000 Received: from mxgate01.infotech-enterprises.com ([110.76.168.168] helo=mxgate01.cyient.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ddAOn-0001d4-Nb for pgsql-performance@postgresql.org; Thu, 03 Aug 2017 07:18:29 +0000 X-IronPort-AV: E=Sophos;i="5.41,314,1498501800"; d="scan'208,217";a="79547953" Received: from unknown (HELO CYMADFPBSMTP) ([172.17.1.108]) by mxgate01.cyient.com with ESMTP; 03 Aug 2017 12:40:54 +0530 Received: from CYMADFPBSMTP.CORP.CYIENT.COM (CYMADFPBSMTP.CORP.CYIENT.COM [127.0.0.1]) by CYMADFPBSMTP.CORP.CYIENT.COM (Service) with ESMTP id 6CC7581B0E04 for ; Thu, 3 Aug 2017 12:43:59 +0530 (IST) Received: from CYINMBX2.CORP.CYIENT.COM (unknown [172.17.4.162]) by CYMADFPBSMTP.CORP.CYIENT.COM (Service) with ESMTP id 5647081B0E00 for ; Thu, 3 Aug 2017 12:43:59 +0530 (IST) Received: from CYINMBX5.CORP.CYIENT.COM (172.17.4.165) by CYINMBX2.CORP.CYIENT.COM (172.17.4.162) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.544.27; Thu, 3 Aug 2017 12:48:20 +0530 Received: from CYINMBX5.CORP.CYIENT.COM ([fe80::a17d:aba8:c7aa:dfba]) by CYINMBX5.CORP.CYIENT.COM ([fe80::a17d:aba8:c7aa:dfba%19]) with mapi id 15.01.0544.027; Thu, 3 Aug 2017 12:48:20 +0530 From: Daulat Ram To: "pgsql-performance@postgresql.org" Subject: Create view Thread-Topic: Create view Thread-Index: AdMMKIYmX8UCfeihTg+JBhHJ4HguZw== Date: Thu, 3 Aug 2017 07:18:20 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [172.17.4.81] Content-Type: multipart/alternative; boundary="_000_f320c963d3c4419d8f8a2749ceb8a07fcyientcom_" MIME-Version: 1.0 List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_f320c963d3c4419d8f8a2749ceb8a07fcyientcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Dear team, Can you please let me know how we can create a view using db link, A base table column having serial datatype. And we want to create a view of= that table on server B. But unable to create and getting the below issue. Error: ERROR: type "serial" does not exist LINE 17: as roaster_test ( roaster_id serial, ^ ********** Error ********** ERROR: type "serial" does not exist SQL state: 42704 Character: 432 Script: create or replace view roaster_test as select * from dblink('port=3D5433 host=3DINN14U-DW1427 dbname=3Dpostgres u= ser=3Dpostgres password=3Dpostgres94', 'select roaster_id, roaster_date, pickdrop, roaster_state, cab_id, shift_key,= roaster_creation_date, status integer, notificationcount, totaltraveldistance, start_trip, end_trip, trip_du= ration from public.roaster') as roaster_test ( roaster_id serial, roaster_date date, pickdrop "char", roaster_state character varying, cab_id character varying, shift_key integer, roaster_creation_date date, status integer, notificationcount integer, totaltraveldistance double precision, start_trip text, end_trip text, trip_duration text) Suggest me if there is any alternate way for the same. Regards, Daulat ________________________________ DISCLAIMER: This email message is for the sole use of the intended recipient(s) and may= contain confidential and privileged information. Any unauthorized review, = use, disclosure or distribution is prohibited. If you are not the intended = recipient, please contact the sender by reply email and destroy all copies = of the original message. Check all attachments for viruses before opening t= hem. All views or opinions presented in this e-mail are those of the author= and may not reflect the opinion of Cyient or those of our affiliates. --_000_f320c963d3c4419d8f8a2749ceb8a07fcyientcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Dear team,

 

Can you please let me know how we can create a view = using db link,

A base table column having serial datatype. And we w= ant to create a view of that table on server B. But unable to create and ge= tting the below issue.

 

Error:

 

ERROR:  type "serial" does not exist<= o:p>

LINE 17: as roaster_test ( roaster_id serial,

        &nbs= p;            &= nbsp;           &nbs= p;    ^

********** Error **********

 

ERROR: type "serial" does not exist

SQL state: 42704

Character: 432

 

Script:

 

create or replace view roaster_test as

select * from  dblink('port=3D5433 host=3DINN14= U-DW1427 dbname=3Dpostgres user=3Dpostgres password=3Dpostgres94',

'select

 roaster_id,  roaster_date,  pickdrop= ,  roaster_state,  cab_id,  shift_key,  roaster_creatio= n_date,

  status integer,

  notificationcount,  totaltraveldistance,=   start_trip,  end_trip,  trip_duration from public.roaster'= )

as roaster_test ( roaster_id serial,

  roaster_date date,

  pickdrop "char",

  roaster_state character varying,

  cab_id character varying,

  shift_key integer,

  roaster_creation_date date,

  status integer,

  notificationcount integer,

  totaltraveldistance double precision,

  start_trip text,

  end_trip text,

  trip_duration text)

 

 

 

Suggest me if there is any alternate way for the sam= e.

 

Regards,

Daulat



DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may= contain confidential and privileged information. Any unauthorized review, = use, disclosure or distribution is prohibited. If you are not the intended = recipient, please contact the sender by reply email and destroy all copies of the original message. Check all a= ttachments for viruses before opening them. All views or opinions presented= in this e-mail are those of the author and may not reflect the opinion of = Cyient or those of our affiliates.
 --_000_f320c963d3c4419d8f8a2749ceb8a07fcyientcom_--