Thank you Igor and Simon, I think I understand this now. On reading the
docs more closely, it looks like the scond test case (NOT NULL with a
DEFAULT) could be solved by using INSERT OR REPLACE. I'm wondering if
there might be a way to solve the first test case by using a trigger?
Pete
On Thu, Apr 5, 2012 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:
> Message: 10
> Date: Thu, 5 Apr 2012 01:38:57 +0100
> From: Simon Slavin <slav...@bigfraud.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] INSERT INTO with SELECT
> Message-ID: <bb6e260a-182b-4fac-a95d-fb9b115cd...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Apr 2012, at 1:36am, Pete <p...@mollysrevenge.com> wrote:
>
> > Here's my test:
> >
> > CREATE TABLE t2 (Col1 text,Col2 text);
> > insert into t2 (Col1) values('xxx');
> > select * from t2
> > xxx|
> >
> > CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> > insert into t3 SELECT * FROM t2;
> > select * from t3;
> > xxx|
> >
> > Why does t3.Col2 not have it's default value of 'abc'?
>
> Because you fed it a value for the second column: NULL. If you want the
> second column to have a default value you might find that
>
> insert into t3 SELECT Col1 FROM t2;
>
> works.
>
> Simon.
>
> ------------------------------
>
> Message: 11
> Date: Wed, 4 Apr 2012 17:48:05 -0700
> From: Pete <p...@mollysrevenge.com>
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Variation on INSERT with SELECT issue
> Message-ID:
> <CABx6j9=88w76nafvhl6f+hbp2efnsjqha6gqpftdu+exb2m...@mail.gmail.com
> >
> Content-Type: text/plain; charset=ISO-8859-1
>
> The test this time was:
>
> sqlite> create table t1 (Col1,Col2);
> sqlite> insert into t1 (Col1) values ('xxx');
> sqlite> select * from t1;
> xxx|
>
> sqlite> create table t2 (Col1, col2 not null default 'abc');
> sqlite> insert into t2 SELECT * from t1;
> SQL error: t2.col2 may not be NULL
>
> --
> Pete
>
>
> ------------------------------
>
> Message: 12
> Date: Wed, 04 Apr 2012 20:52:58 -0400
> From: Igor Tandetnik <itandet...@mvps.org>
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Variation on INSERT with SELECT issue
> Message-ID: <jliqe2$q3k$1...@dough.gmane.org>
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> On 4/4/2012 8:48 PM, Pete wrote:
> > The test this time was:
> >
> > sqlite> create table t1 (Col1,Col2);
> > sqlite> insert into t1 (Col1) values ('xxx');
> > sqlite> select * from t1;
> > xxx|
> >
> > sqlite> create table t2 (Col1, col2 not null default 'abc');
> > sqlite> insert into t2 SELECT * from t1;
> > SQL error: t2.col2 may not be NULL
>
> Default clause applies when you omit a column from the list in INSERT
> (like you did when inserting into t1). It doesn't apply when you attempt
> to insert NULL explicitly.
> --
> Igor Tandetnik
>
--
Pete
Molly's Revenge <http://www.mollysrevenge.com>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
