Skip site navigation (1) Skip section navigation (2)

Re: SQL View to PostgreSQL View

From: "Igor Neyman" <ineyman(at)perceptron(dot)com>
To: "Rehan Saleem" <pk_rehan(at)yahoo(dot)com>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: SQL View to PostgreSQL View
Date: 2012-02-28 14:27:19
Message-ID: F4C27E77F7A33E4CA98C19A9DC6722A208D18F78@EXCHANGE.corp.perceptron.com (view raw or flat)
Thread:
Lists: pgsql-sql

From: Rehan Saleem [mailto:pk_rehan(at)yahoo(dot)com] 
Sent: Sunday, February 26, 2012 1:50 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: SQL View to PostgreSQL View

Hi ,
I am trying to convert sql view to postgresql view but i am getting the following error i dont know how to handle dbo. in postgresql and when i remove dbo. from table name then view got created but it does not show any data, while this is working perfectly fine in sql, here is my code and error details 

CREATE OR REPLACE VIEW vwkbcomparesites as
select a.kbid kb_a, b.kbid kb_b, a.chr chr_a, a.start start_a, a."end" end_a, (a."end" - a.start)+1 tagsize_a, 
	b.chr chr_b, b.start start_b, b."end" end_b, (b."end" - b.start)+1 tagsize_b,
    abs((a."end" + a.start)/2 - (b."end" + b.start)/2) centredistance,
		case 
			when a."end" <= b."end" and a.start >= b.start  
				then (a."end" - a.start) 
			when b."end" <= a."end" and b.start >= a.start   
				then (b."end" - b.start) 
			when a."end" <= b."end" and a.start <= b.start    
				then (a."end" - b.start) 
			when a."end" >= b."end" and a.start >= b.start
				then (b."end" - a.start)  
			end bpoverlap
	from  dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr
	inner join dbo.kbdetails kbd on a.kbid=kbd.kbid
		where kbd.active='1' ;

i am getting this error , how can i fix this.

ERROR:  schema "dbo" does not exist
LINE 15:  from  dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr
                ^

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

ERROR: schema "dbo" does not exist
SQL state: 3F000
Character: 761

>>

First, I assume you are converting your view from SQL Server, not from SQL.
SQL Server is RDBMS, while SQL is a language being used by multiple RDBMSs including PostgreSQL.

Second, there is no "standard" dbo ("database owner") role in Postgres.
Before converting from one RDBMS to another you need to do some basic (at least) documentation reading on "target" RDBMS (in this case - PostgreSQL).
Otherwise, you will stumble on every step.

Regards,
Igor Neyman



In response to

pgsql-sql by date

Next:From: Marcin KrawczykDate: 2012-02-29 09:29:17
Subject: custom_variable_classes in 9.1
Previous:From: Richard KlinglerDate: 2012-02-28 12:38:35
Subject: Re: Natural sort order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group