Обсуждение: insert retrieved data into a new table

Readers,

Is it possible to use the result of a SELECT function to populate a
new table? The objective is to write the new table for use by gnuplot.

The SELECT function features a mathematical operator such as SUM which
obtains a value. Is it possible to use the value from this function to
be entered into the new table. Please advise the relevant parts of the
documentation to read.

Thanks.

e-letter wrote on 24.04.2011 22:52:
> Readers,
>
> Is it possible to use the result of a SELECT function to populate a
> new table? The objective is to write the new table for use by gnuplot.
>
> The SELECT function features a mathematical operator such as SUM which
> obtains a value. Is it possible to use the value from this function to
> be entered into the new table. Please advise the relevant parts of the
> documentation to read.
>

CREATE TABLE new_table
AS
SELECT col1, col2, col3
FROM some_table
WHERE ...

This is the syntax I always use, but I have just realized that it not documented in the manual...


Or using SELECT INTO:
http://www.postgresql.org/docs/current/static/sql-selectinto.html

SELECTcol1, col2, col3
   INTO new_table
FROM some_table
WHERE ...

Thomas

e-letter <inpost@gmail.com> writes:
> Is it possible to use the result of a SELECT function to populate a
> new table? The objective is to write the new table for use by gnuplot.

Sure, see INSERT ... SELECT.

            regards, tom lane

Gurjeet Singh wrote on 24.04.2011 23:22:

>     CREATE TABLE new_table
>     AS
>     SELECT col1, col2, col3
>     FROM some_table
>     WHERE ...
>
>     This is the syntax I always use, but I have just realized that it not documented in the manual...
>
>
> It sure is:
>
> http://www.postgresql.org/docs/9.0/static/sql-createtableas.html
>
> It should be merged into the CREATE TABLE doc, IMHO.
>

Ah! Thanks, I was only looking at the CREATE TABLE syntax :)