Creating Table Copy

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general