Change the output columns for a postgres query
The query is as below.
The attached file shows the existing worksheet with the columns that are output from this query.
The attached file also shows the desired worksheet, with some columns removed and the postcode column included twice, the extra postcode column to be included as the first column.
CREATE OR REPLACE VIEW ADDRESS_VIEW
AS
SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,
AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,
FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,
LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,
AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,
SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,
L.LOCALITY_NAME as LOCALITY_NAME,
ST.STATE_ABBREVIATION as STATE_ABBREVIATION,
AD.POSTCODE as POSTCODE,
ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,
AD.CONFIDENCE as CONFIDENCE,
AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as PRIMARY_SECONDARY,
AD.LEGAL_PARCEL_ID as LEGAL_PARCEL_ID,
AD.DATE_CREATED as DATE_CREATED
FROM
ADDRESS_DETAIL AD
LEFT JOIN FLAT_TYPE_AUT FTA ON AD.FLAT_TYPE_CODE=FTA.CODE
LEFT JOIN LEVEL_TYPE_AUT LTA ON AD.LEVEL_TYPE_CODE=LTA.COD
JOIN STREET_LOCALITY SL ON AD.STREET_LOCALITY_PID=SL.
LEFT JOIN STREET_SUFFIX_AUT SSA ON SL.STREET_SUFFIX_CODE=SSA.
LEFT JOIN STREET_CLASS_AUT SCA ON SL.STREET_CLASS_CODE=SCA.C
LEFT JOIN STREET_TYPE_AUT STA ON SL.STREET_TYPE_CODE=STA.CO
JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
JOIN ADDRESS_DEFAULT_GEOCODE ADG ON AD.ADDRESS_DETAIL_PID=ADG.
LEFT JOIN GEOCODE_TYPE_AUT GTA ON ADG.GEOCODE_TYPE_CODE=GTA.
LEFT JOIN GEOCODED_LEVEL_TYPE_AUT GLTA ON AD.LEVEL_GEOCODED_CODE=GLT
JOIN STATE ST ON L.STATE_PID=ST.STATE_PID
WHERE
AD.CONFIDENCE > -1
Existing-and-desired-worksheets.xlsx
The attached file shows the existing worksheet with the columns that are output from this query.
The attached file also shows the desired worksheet, with some columns removed and the postcode column included twice, the extra postcode column to be included as the first column.
CREATE OR REPLACE VIEW ADDRESS_VIEW
AS
SELECT
AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
AD.LOCALITY_PID as LOCALITY_PID,
AD.BUILDING_NAME as BUILDING_NAME,
AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
AD.LOT_NUMBER as LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,
FTA.NAME as FLAT_TYPE,
AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER as FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,
LTA.NAME as LEVEL_TYPE,
AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
AD.LEVEL_NUMBER as LEVEL_NUMBER,
AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,
AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST as NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
AD.NUMBER_LAST as NUMBER_LAST,
AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,
SL.STREET_NAME as STREET_NAME,
SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
SCA.NAME as STREET_CLASS_TYPE,
SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
SSA.NAME as STREET_SUFFIX_TYPE,
L.LOCALITY_NAME as LOCALITY_NAME,
ST.STATE_ABBREVIATION as STATE_ABBREVIATION,
AD.POSTCODE as POSTCODE,
ADG.LATITUDE as LATITUDE,
ADG.LONGITUDE as LONGITUDE,
GTA.NAME as GEOCODE_TYPE,
AD.CONFIDENCE as CONFIDENCE,
AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
AD.PRIMARY_SECONDARY as PRIMARY_SECONDARY,
AD.LEGAL_PARCEL_ID as LEGAL_PARCEL_ID,
AD.DATE_CREATED as DATE_CREATED
FROM
ADDRESS_DETAIL AD
LEFT JOIN FLAT_TYPE_AUT FTA ON AD.FLAT_TYPE_CODE=FTA.CODE
LEFT JOIN LEVEL_TYPE_AUT LTA ON AD.LEVEL_TYPE_CODE=LTA.COD
JOIN STREET_LOCALITY SL ON AD.STREET_LOCALITY_PID=SL.
LEFT JOIN STREET_SUFFIX_AUT SSA ON SL.STREET_SUFFIX_CODE=SSA.
LEFT JOIN STREET_CLASS_AUT SCA ON SL.STREET_CLASS_CODE=SCA.C
LEFT JOIN STREET_TYPE_AUT STA ON SL.STREET_TYPE_CODE=STA.CO
JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
JOIN ADDRESS_DEFAULT_GEOCODE ADG ON AD.ADDRESS_DETAIL_PID=ADG.
LEFT JOIN GEOCODE_TYPE_AUT GTA ON ADG.GEOCODE_TYPE_CODE=GTA.
LEFT JOIN GEOCODED_LEVEL_TYPE_AUT GLTA ON AD.LEVEL_GEOCODED_CODE=GLT
JOIN STATE ST ON L.STATE_PID=ST.STATE_PID
WHERE
AD.CONFIDENCE > -1
Existing-and-desired-worksheets.xlsx
Though you could change the lower case upper casedness like.
as "POSTCODE"
as "postcode"
as "POSTCODE"
as "postcode"
ASKER
Thanks for you comment.
Perhaps then only one column for the postcode - as long as it is the first column.
This query returns 38 columns.
I have tried to modify it to return only the 17 columns as in the example excel worksheet. (including the two postcode columns)
I would like the query to be modified to return only 16 columns with postcode as the first column.
I can't get my versions to run.
Perhaps then only one column for the postcode - as long as it is the first column.
This query returns 38 columns.
I have tried to modify it to return only the 17 columns as in the example excel worksheet. (including the two postcode columns)
I would like the query to be modified to return only 16 columns with postcode as the first column.
I can't get my versions to run.
can you give more detail of the messages you get when you try your query.
try
CREATE OR REPLACE VIEW ADDRESS_VIEW AS SELECT
AD.POSTCODE as "postcode",
AD.LOT_NUMBER_PREFIX,
AD.LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX,
AD.FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX,
AD.NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX,
SL.STREET_NAME,
SL.STREET_TYPE_CODE,
L.LOCALITY_NAME,
AD.POSTCODE as "PostCode",
ST.STATE_ABBREVIATION,
ADG.LONGITUDE,
AD.LEGAL_PARCEL_ID
FROM
ADDRESS_DETAIL AD
JOIN STREET_LOCALITY SL ON (AD.STREET_LOCALITY_PID = SL.STREET_LOCALITY_PID)
JOIN LOCALITY L ON (AD.LOCALITY_PID = L.LOCALITY_PID)
JOIN ADDRESS_DEFAULT_GEOCODE ADG ON (AD.ADDRESS_DETAIL_PID = ADG.ADDRESS_DETAIL_PID)
JOIN STATE ST ON (L.STATE_PID = ST.STATE_PID)
WHERE
AD.CONFIDENCE > -1;
try
CREATE OR REPLACE VIEW ADDRESS_VIEW AS SELECT
AD.POSTCODE as "postcode",
AD.LOT_NUMBER_PREFIX,
AD.LOT_NUMBER,
AD.LOT_NUMBER_SUFFIX,
AD.FLAT_NUMBER_PREFIX,
AD.FLAT_NUMBER,
AD.FLAT_NUMBER_SUFFIX,
AD.NUMBER_FIRST_PREFIX,
AD.NUMBER_FIRST,
AD.NUMBER_FIRST_SUFFIX,
SL.STREET_NAME,
SL.STREET_TYPE_CODE,
L.LOCALITY_NAME,
AD.POSTCODE as "PostCode",
ST.STATE_ABBREVIATION,
ADG.LONGITUDE,
AD.LEGAL_PARCEL_ID
FROM
ADDRESS_DETAIL AD
JOIN STREET_LOCALITY SL ON (AD.STREET_LOCALITY_PID = SL.STREET_LOCALITY_PID)
JOIN LOCALITY L ON (AD.LOCALITY_PID = L.LOCALITY_PID)
JOIN ADDRESS_DEFAULT_GEOCODE ADG ON (AD.ADDRESS_DETAIL_PID = ADG.ADDRESS_DETAIL_PID)
JOIN STATE ST ON (L.STATE_PID = ST.STATE_PID)
WHERE
AD.CONFIDENCE > -1;
ASKER
Thanks. I've attached a screen grab showing the tables and the error message.
Postgres-query-problem.PNG
Postgres-query-problem.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. Changing the column names is above my knowledge level.
Thanks again.
Thanks again.
See how the view is currently defined.
select definition from pg_views where viewname='address_view';
Use alias to match upper case/lower case letters in the column names. ie
as "PostCode" ....
look for columns that exist in the current definition that are required by other objects in the database.
select definition from pg_views where viewname='address_view';
Use alias to match upper case/lower case letters in the column names. ie
as "PostCode" ....
look for columns that exist in the current definition that are required by other objects in the database.
It is not possible to have two columns from a query to be assigned the same name.