Re: SELECT INTO using Views?

Thanks for the reply! That worked, but I'm running into one other issue that
I'm having some trouble resolving...

Problem: We want all values in the measurement view (and table once I copy
it into there) to be shown in ints vs. floats/decimals.

In my View (called 'measurement'), there is a calculated column, area_sq,
that is defined as type float8.
This column is calculated in the following manner: a.area *
su.units_per_sqfoot::integer AS area_sq, where a.area is a float8 and
su.units_per_sqfoot is a float8 that I'm casting to an INT.
When I execute this, it is returning a float.

If I cast the entire operation to an INT:
(a.area * su.units_per_sqfoot::integer)::integer AS area_sq
or by
(a.area * su.units_per_sqfoot)::integer AS area_sq,
I'm getting an 'ERROR: integer out of range' error returned when I run my
SELECT statement:

SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq,
(a.area * su.units_per_sqfoot)::integer AS area_sq, e.slope AS slope_inches,
sa.slopearea * cu.units_per_cufoot::integer AS
slopearea_sq, da.linear_unit AS linear_unit_lin, (p.perimeter::integer
* lu.units_per_foot::double precision)::integer AS perimeter_lin,
da.cubic_unit AS cubic_unit_cu, e.height * lu.units_per_foot::integer AS
height_lin, e.height::double precision * a.area *
cu.units_per_cufoot::integer AS volume_cu, da.drawingid
FROM
((((((((((SELECT perimeter.elementid, perimeter.perimeter
FROM elementdata.perimeter
UNION
SELECT length.elementid, length.length AS perimeter
FROM elementdata.length)
UNION
SELECT circumference.elementid, circumference.circumference AS perimeter
FROM elementdata.circumference) p
LEFT JOIN elementdata.area a USING (elementid))
LEFT JOIN element e USING (elementid))
LEFT JOIN elementdata.slopearea sa USING (elementid))
JOIN layer la USING (layerid))
JOIN drawing da USING (drawingid))
JOIN globaldata.linear_units lu USING (linear_unit))
JOIN globaldata.square_units su USING (square_unit))
JOIN globaldata.cubic_units cu USING (cubic_unit));

All of the casts in the SELECT statement appear to be working except for the
one for the area_sq and slopearea_sq and both of these columns are defined
as 'float8', whereas the other ones are either defined as numeric or int4.

Thoughts and/or ideas without having to redo other tables in the database?

Thanks for your replies and assistance, it is all greatly appreciated!
-Jeanna

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Merlin Moncure
Sent: Monday, January 08, 2007 6:28 PM
To: Jeanna Geier
Cc: pgsql-general
Subject: Re: [GENERAL] SELECT INTO using Views?

On 1/9/07, Jeanna Geier <jgeier(at)apt-cafm(dot)com> wrote:
> Hello List!
>
> I have a question regarding SELECT INTO...
>
> Can it be used with Views? I have a View that is populated (~35,000 rows)
> that I want to create a Table from the data in it....
>
> So, would I be able to copy the data from the View to the Table using the
> SELECT INTO command?

Administrator=# create temp view v as select 'postgresql r0x0r'::text;
CREATE VIEW
Administrator=# create temp table t as select * from v;
SELECT

merlin

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match