On Tue, Jun 17, 2014 at 5:51 AM, Rich Shepard <rshepard@xxxxxxxxxxxxxxx> 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
