In POSTGRES - I am trying to create a view from 2 tables. When the value of '0' is coded for insertion as a value for the EAST_LONGITUDE_NMBR column of datatype NUMERIC[24,20] in the lower portion of a UNION select statement, an ERROR Message is generated.
The view EXTENTS' column EAST_LONGITUDE_NMBR comes from the table and column, CELL_EXTENT.EAST_LONGITUDE_NMBR with a datatype of NUMERIC[24,20]
The following is the code.
CREATE VIEW EXTENTS
(
ID,
EXTENT_TYPE,
NAME,
EAST_LONGITUDE_NMBR
)
AS
SELECT
"CELL_EXTENT"."CELL_ID_NMBR",
'CELL',
UPPER ("CELL_EXTENT"."CELL_NAME"),
"CELL_EXTENT"."EAST_LONGITUDE_NMBR"
FROM "EARTH"."CELL_EXTENT"
UNION
(SELECT
"AREA_INTEREST"."AREA_ID_NMBR",
'GEOPOLITICAL',
UPPER ("AREA_INTEREST"."AREA_NAME"),
0
FROM "EARTH"."AREA_INTEREST");
The inserted value '0' in the lower UNION select causes the following error in the creation of view EXTENTS.
ERROR: UNION types numeric[] and integer cannot be matched
I have tried the following and received the errors shown:
0 ERROR: UNION types numeric[] and integer cannot be matched
0.0 ERROR: UNION types numeric[] and numeric cannot be matched
0.0::NUMERIC[] ERROR: cannot cast type numeric to numeric[]
0::NUMERIC[] ERROR: cannot cast type integer to numeric[]
I have checked numerous websites with discussions about the Postgres datatypes, particularly NUMERIC, NUMERIC[], INTEGER, DECIMAL
Difference between DECIMAL and NUMERIC datatype in PSQL
https://github.com/npgsql/npgsql/issues/655
https://www.cybertec-postgresql.com/en/mapping-oracle-datatypes-to-postgresql/
http://www.postgresqltutorial.com/postgresql-cast/
http://www.postgresqltutorial.com/postgresql-to_number/
I could go on, but you get the picture. There is a lot about datatypes but there are no examples for '0' as an actual value in Postgres code for a column of datatype NUMERIC[] in a UNION statement.
I feel this is a simple fix, a couple of keystrokes here or there to set the value proper, but it eludes me. I am using pgAdmin4.
Can you help? Thanks, Margaret
