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

Jeanna Geier 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?

SELECT INTO is used in procedural language functions to place column
values into program variables. To copy the contents of a view into a
table, look at instead CREATE TABLE AS or INSERT with SELECT.

--
Guy Rouillier

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

"Jeanna Geier" <jgeier@apt-cafm.com> writes:

If I cast the entire operation to an INT: ...
I'm getting an 'ERROR: integer out of range' error returned when I run my
SELECT statement:

Perhaps use floor() instead?

regards, tom lane

On Tue, Jan 09, 2007 at 10:10:46 -0600,
Jeanna Geier <jgeier@apt-cafm.com> wrote:

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:

This suggests that the values are too large to be represented in int4. Can
you switch to using int8?