On 2017/06/29 8:15 AM, Robert M. Münch wrote:
On 27 Jun 2017, at 22:11, David Raymond wrote:
Single quotes should be used for strings, so DEFAULT '-'
I thought it doesn't matter if I use " or ' for strings. What's the difference?
I had this misconception at some point too. Double quotes are for
specifying Identifiers (The names of stuff) in SQLite so as to not
confuse a possible column name (aka identifier) with say an internal
keyword or a string value.
SQLite isn't helping the confusion in this case, because it allows
double-quotes to be regarded as string values IF an identifier with that
name doesn't exist. This is of course all good and well until you
misspell a column name...
To demonstrate the difference and possible pitfalls:
-- SQLite version 3.17.0 [ Release: 2017-02-13 ] on SQLitespeed
version 2.0.2.4.
--
================================================================================================
CREATE TABLE QTest(
"ID" INT,
Value1 TEXT,
Value2 TEXT
);
INSERT INTO QTest(ID, Value1) VALUES
(1, 'Gorilla Conflict')
,(2, 'Moroccan Coffee Beans')
,(3, "Monaco Raceway")
;
-- All these were regarded as strings.
UPDATE QTest SET Value2 = 'Value1' || ' in the Jungle.';
SELECT ID, Value2 FROM QTest;
-- ID | Value2
-- ------------ | ---------------------
-- 1 | Value1 in the Jungle.
-- 2 | Value1 in the Jungle.
-- 3 | Value1 in the Jungle.
-- Here we expected that outcome because using single quotes means
-- that can be nothing other than the string 'Value1'
UPDATE QTest SET Value2 = "Value1" || " in the Jungle.";
SELECT ID, Value2 FROM QTest;
-- ID | Value2
-- ------------ | ------------------------------------
-- 1 | Gorilla Conflict in the Jungle.
-- 2 | Moroccan Coffee Beans in the Jungle.
-- 3 | Monaco Raceway in the Jungle.
-- Here "value1" is correctly regarded as an identifier but
-- " in the Jungle" is regarded as a string, even though it's
-- in double-quotes. This time the weirdness helped us....
UPDATE QTest SET Value2 = "Valeu1" || " in the Jungle.";
SELECT ID, Value2 FROM QTest;
-- ID | Value2
-- ------------ | ---------------------
-- 1 | Valeu1 in the Jungle.
-- 2 | Valeu1 in the Jungle.
-- 3 | Valeu1 in the Jungle.
-- Here our troubles start. It's exactly the same format as before,
-- but because of the spelling mistake, our needed-to-be-an-identifier
-- value1 simply got transformed to a string, no error.
-- (In fact, this entire script runs without errors, you can copy-paste
-- it into your own DB script mechanism)
UPDATE QTest SET Value2 = Value1 || ' in the Jungle.';
SELECT ID, Value2 FROM QTest;
-- ID | Value2
-- ------------ | ------------------------------------
-- 1 | Gorilla Conflict in the Jungle.
-- 2 | Moroccan Coffee Beans in the Jungle.
-- 3 | Monaco Raceway in the Jungle.
-- This is the most correct way to do it. The same spelling mistake
-- here would error out.
-- The only time it is needed (or really a good idea) to use double
-- quotes, is when an identifier name either contains weird
-- characters or the identifier is the same as an internal Keyword.
-- These are not valid identifiers: *1 A~; , JOIN
-- But these definitely are valid: "*1 A~;" , "JOIN"
-- In SQLite, even this is valid: CREATE TABLE " "(" " INT);
-- Yes - that is a table with the name SPACE and a column named SPACE.
DROP TABLE QTest;-- Cleanup
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.030s
-- Total Script Query Time: 0d 00h 00m and
00.001s
-- Total Database Rows Changed: 15
-- Total Virtual-Machine Steps: 289
-- Last executed Item Index: 11
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
