asked on

How do I create a view in PostgreSQL

I have a view in SQLserver which works perfectly and now
need to have the same view in PostgreSQL

The view in SQLServer has UNIONS and involves about 5 tables (Attached in the notepad file)

I test my Postgre installation with a simple view and it worked but on trying my actual
view with the script from SQLServer I got and error

I treied to narrow it down to just a simple select from 1 table and it did not work
below is the error and my table and the fields i was trying to create a view from

What could i be doing wrong

Thanks

Olukay
completeview.txt

chaau

Postgres converts everythin into lower case when the names are used without quotes. If your tables and columns created with upper case characters you need to quote all identifiers.

More info here

So, modify the syntax to this:

CREATE VIEW a_view
AS
SELECT "ARSALEDate" FROM "ARSales";

Open in new window

Olukayode Oluwole

ASKER

I have changed the table names to lower case and the view gets created
But if i have 2 views the results of which need to be concatenated
how does PostgreSQL handle this

In SqlServer UNION ALL as below does it

Whats the equivalent of UNION ALL in PostgreSQL

Thanks

SELECT arsales.ARSALEDate AS Date01, arsales.ARSALEDocumentType AS Type, - (1 * arsalesdetail.ARSALEDQty) AS Qty1,
arsalesdetail.ARSALEDItemCost AS Cost01, arsales.ARSALEExtDocumentNo AS DocNo, arsales.ARSALECheckNumber AS Memo,
arsalesdetail.ARSALEDItemId AS ID, loctab.loc_desc AS location, arsales.ARSALEBillTo AS Customer
FROM arsales,arsalesdetail,loctab
WHERE arsales.ARSALEPostedYN = '1'
AND arsales.ARSALEDocumentType = 'Invoice'
AND arsales.ARSALEDocumentNo = arsalesdetail.ARSALEDDocumentNo
AND arsales.in_user = loctab.location

UNION ALL

SELECT arsales.ARSALEDate AS Date01, arsales.ARSALEDocumentType AS Type, arsalesdetail.ARSALEDQty AS Qty1,
arsalesdetail.ARSALEDItemCost AS Cost01, arsales.ARSALEExtDocumentNo AS DocNo, arsales.ARSALECheckNumber AS Memo,
arsalesdetail.ARSALEDItemId AS ID, loctab.loc_desc AS location, arsales.ARSALEBillTo AS Customer
FROM arsales,arsalesdetail,loctab
WHERE arsales.ARSALEPostedYN = '1'
AND arsales.ARSALEDocumentType = 'Return'
AND arsales.in_user = loctab.location
AND arsales.ARSALEDocumentNo = arsalesdetail.ARSALEDDocumentNo

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Olukayode Oluwole

ASKER

I am ok now good.

Olukayode Oluwole

ASKER

Great support. Thanks