Re: Create view

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 <Daulat(dot)Ram(at)cyient(dot)com>:

> 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.
>