public inbox for [email protected]
help / color / mirror / Atom feedCreate view
2+ messages / 2 participants
[nested] [flat]
* Create view
@ 2017-08-03 07:18 Daulat Ram <[email protected]>
2017-08-03 07:25 ` Re: Create view Pavel Stehule <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Daulat Ram @ 2017-08-03 07:18 UTC (permalink / raw)
To: pgsql-performance
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.
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Create view
2017-08-03 07:18 Create view Daulat Ram <[email protected]>
@ 2017-08-03 07:25 ` Pavel Stehule <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Stehule @ 2017-08-03 07:25 UTC (permalink / raw)
To: Daulat Ram <[email protected]>; +Cc: pgsql-performance
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 <[email protected]>:
> 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.
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-08-03 07:25 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-03 07:18 Create view Daulat Ram <[email protected]>
2017-08-03 07:25 ` Pavel Stehule <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox