Re: Creating Table Copy

On Tue, Jun 17, 2014 at 5:51 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)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