Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ddAW9-00078O-Cq for pgsql-performance@arkaria.postgresql.org; Thu, 03 Aug 2017 07:26:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ddAW8-0006Up-MK for pgsql-performance@arkaria.postgresql.org; Thu, 03 Aug 2017 07:26:00 +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 1ddAW7-0006UX-W8 for pgsql-performance@postgresql.org; Thu, 03 Aug 2017 07:26:00 +0000 Received: from mail-wr0-x22c.google.com ([2a00:1450:400c:c0c::22c]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1ddAW4-0001p6-GP for pgsql-performance@postgresql.org; Thu, 03 Aug 2017 07:25:59 +0000 Received: by mail-wr0-x22c.google.com with SMTP id 33so2128070wrz.4 for ; Thu, 03 Aug 2017 00:25:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=tocQ0RKLV1gVMxBvdDcV88S7ag5RQTU/lewmcmYCuOI=; b=DS39zugj7ZcbbwAbiue6F81mfJtl5PXS5+lCDe1uW83QH1ozwGZGKUW0uY4xGqZYi1 fnp5XWnzfw8d3WxeVg0RI7w7aYORrzrNrM9NKGFkLJxoGjFODzi4agfbk8z/RrjUKrBu xBlej9aHpZTjWWTVtRQ9/EFv3PfM5lsBJd8DiwYrWFG6D74+VNhglG4KLL7lXbF7aPUk wbvVURgxKsGr6HIMHkEWsCu9++NuR+pbMJODjX95LKXfsTshU9GfgDXu0I3Pl4i2s53Q r6wXHdc5Sx06L+hiDCu4HYAnm3rqZ7kd2tUGtDt64Ffa//4m9fKvBOYewNiUbsJVX5w9 gd+w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=tocQ0RKLV1gVMxBvdDcV88S7ag5RQTU/lewmcmYCuOI=; b=mtmG57n/diQMa0xPWVDsCnZM+wbiJ5ZfpBOdcJ3Z0Ld5BusTPKxOHoOhHqdySOu3mV QSS8yA4JO4cguOGNI9SednWybwvd9oqZa+C3ZJqK3aS2qS+H7BoY1+UFA5GOGz7BGBnC bEoycrjTMyptvJKClkr0EObjOXyPb8XZVFrtoGp84xE7/I4/KboreroJe/OnFj25VNzE nmaWNbhe02j/20c0tvIW3ucf2tk3/U75tCVj8HPz7Ixl84UYtEG8ADV3MteVnN0URCFC aQ8yL+ItRKWTsT6h9Q5ub/abfrtq6Rsbr2bgsmUi4LSXHBGy79V6nH1S4IGtHmFiLvFH l7cw== X-Gm-Message-State: AIVw112GsfbdLdn+XfG4UEYxjHHKcJEU6a4zu03Sa9VN/agtPOz3cdA+ h07OvKyfUpTO4f9QrbK234k3yl/0Wg== X-Received: by 10.223.162.148 with SMTP id s20mr511937wra.2.1501745155737; Thu, 03 Aug 2017 00:25:55 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.152.66 with HTTP; Thu, 3 Aug 2017 00:25:15 -0700 (PDT) In-Reply-To: References: From: Pavel Stehule Date: Thu, 3 Aug 2017 09:25:15 +0200 Message-ID: Subject: Re: Create view To: Daulat Ram Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403045ec49c8d0e7c0555d44a6c" 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 --f403045ec49c8d0e7c0555d44a6c Content-Type: text/plain; charset="UTF-8" Hi This is wrong mailing list for this question - please, use pgsql-general for similar questions. I don't see any relation to performance. 2017-08-03 9:18 GMT+02:00 Daulat Ram : > 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=5433 host=INN14U-DW1427 dbname=postgres > user=postgres password=postgres94', > > 'select > > roaster_id, roaster_date, pickdrop, roaster_state, cab_id, > shift_key, roaster_creation_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 same. > Serial is "pseudotype" and can be used only for CREATE TABLE command. This pseudotype is translated to "int DEFAULT nextval(automatic_sequence)" Use int instead in your case. Regards Pavel Stehule > > > 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 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. > --f403045ec49c8d0e7c0555d44a6c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

This is wrong mailing list for this q= uestion - please, use pgsql-general for similar questions. I don't see = any relation to performance.

2017-08-03 9:18 GMT+02:00 Daulat Ram = <Daulat.Ram@c= yient.com>:

Dear team,

=C2=A0

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.

=C2=A0

Error:

=C2=A0

ERROR:=C2=A0 type "serial" does not exist<= u>

LINE 17: as roaster_test ( roaster_id serial,=

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 ^

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

=C2=A0

ERROR: type "serial" does not exist=

SQL state: 42704

Character: 432

=C2=A0

Script:

=C2=A0

create or replace view roaster_test as

select * from=C2=A0 dblink('port=3D5433 host=3DI= NN14U-DW1427 dbname=3Dpostgres user=3Dpostgres password=3Dpostgres94',

'select

=C2=A0roaster_id,=C2=A0 roaster_date,=C2=A0 pickdrop= ,=C2=A0 roaster_state,=C2=A0 cab_id,=C2=A0 shift_key,=C2=A0 roaster_creatio= n_date,

=C2=A0 status integer,

=C2=A0 notificationcount,=C2=A0 totaltraveldistance,= =C2=A0 start_trip,=C2=A0 end_trip,=C2=A0 trip_duration from public.roaster&= #39;)

as roaster_test ( roaster_id serial,

=C2=A0 roaster_date date,

=C2=A0 pickdrop "char",

=C2=A0 roaster_state character varying,

=C2=A0 cab_id character varying,

=C2=A0 shift_key integer,

=C2=A0 roaster_creation_date date,

=C2=A0 status integer,

=C2=A0 notificationcount integer,

=C2=A0 totaltraveldistance double precision,<= u>

=C2=A0 start_trip text,

=C2=A0 end_trip text,

=C2=A0 trip_duration text)

=C2=A0

=C2=A0

=C2=A0

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


Serial is "pseudoty= pe" and can be used only for CREATE TABLE command. This pseudotype is = translated to "int DEFAULT nextval(automatic_sequence)"

Use int instead in your case.

Regards

Pavel Stehule

=C2=A0

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.

--f403045ec49c8d0e7c0555d44a6c--