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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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@apt-cafm.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