SQLite Forum

Getting 'default' values: for a view?
Login

Getting 'default' values: for a view?

(1) By midijohnny on 2021-11-03 22:33:00 [link] [source]

Is there a way of evaluating the 'default' expression for a table in a context other than INSERTing a new row?

Example:

create table people(name text default 'fred');
insert into people default values; -- will insert 'fred'
insert into people values(null);

I am wondering if there is some construction that would allow me to generate the row - without actually necessarily persisting it - in the same way?

select ifnull(name,'I would like the default here') from people;

I can see how to see the expression; so for static strings , you can do this:

SELECT TRIM(dflt_value,"'") -- bad assumption
FROM pragma_table_info('people')
WHERE NAME='name'

The TRIM here is to remove the single quotes from the default; but that only works of course if the default happens to be a static string, rather than a calculated one.

(2) By anonymous on 2021-11-03 23:33:53 in reply to 1 [link] [source]

One way is:

begin; insert into people default values returning *; rollback;

(3) By midijohnny on 2021-11-04 17:47:43 in reply to 2 [source]

Nice: I didn't know about the returning clause - could be useful for other things as well.

It won't quite work for what I need at the moment (but I see there are some features being looked at for future releases) - but good to know of this. Thanks!

(4) By Simon Slavin (slavin) on 2021-11-04 22:15:40 in reply to 2 [link] [source]

Very clever. I'm impressed.