Thread: Creating Table Copy

   I have a table with some NULL values in a date column. I need to make a
copy of that table containing only those rows where the date column is not
null. Reading the CREATE TABLE man page I've tried to create a copy of the
original table from which I could drop rows. While the table creating is
successful, it contains no rows. I do not see an option to include the data
as well as the schema.

   The syntax I thought would work is,

# create table benthos (like benthosraw including all);

but it creates an empty table.

   Please pass me a clue on copying the data as well as the schema.

   While I suspect there's a way to write a SELECT statement for those rows
that are not null and save the results to a different table name, I've not
found the syntax in my postgres and SQL references.

TIA,

Rich

On Mon, 16 Jun 2014, Rich Shepard wrote:

>  While I suspect there's a way to write a SELECT statement for those rows
> that are not null and save the results to a different table name, I've not
> found the syntax in my postgres and SQL references.

   Got it:

#  create table benthos as select * from benthosraw where sampdate is not
null;

Rich

On Tue, Jun 17, 2014 at 5:51 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>   I have a table with some NULL values in a date column. I need to make a
> copy of that table containing only those rows where the date column is not
> null. Reading the CREATE TABLE man page I've tried to create a copy of the
> original table from which I could drop rows. While the table creating is
> successful, it contains no rows. I do not see an option to include the data
> as well as the schema.
>
>   The syntax I thought would work is,
>
> # create table benthos (like benthosraw including all);
>
> but it creates an empty table.
>
>   Please pass me a clue on copying the data as well as the schema.
>
>   While I suspect there's a way to write a SELECT statement for those rows
> that are not null and save the results to a different table name, I've not
> found the syntax in my postgres and SQL references.
CREATE TABLE AS:
http://www.postgresql.org/docs/9.3/static/sql-createtableas.html

=# create table aa (a int, b int);
CREATE TABLE
=# insert into aa values (generate_series(1,10), NULL);
INSERT 0 10
=# insert into aa values (generate_series(11,20), generate_series(11,20));
INSERT 0 10
=# create table aa_copy as select * from aa where b is not null;
SELECT 10
=# select * from aa_copy ;
 a  | b
----+----
 11 | 11
 12 | 12
 13 | 13
 14 | 14
 15 | 15
 16 | 16
 17 | 17
 18 | 18
 19 | 19
 20 | 20
(10 rows)
--
Michael