SQLite prevents duplicate column names in my VIEW

I am creating a SQLite VIEW that is the result of multiple joined tables. All my tables have an _id column as required by Android. The result has multiple columns with the same _id name, but SQLite adds ":1" and ":2" to the duplicate names so they are no longer duplicates.

If you run the below SQL you can see the resulting view has interesting column names:

CREATE TABLE things ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "name" TEXT NOT NULL);

CREATE TABLE thing_colors ("_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "thing_id" INTEGER NOT NULL , "color" TEXT NOT NULL);

INSERT INTO things VALUES ("1","car");
INSERT INTO things VALUES ("2","horse");
INSERT INTO things VALUES ("3","lamp");

INSERT INTO thing_colors VALUES ("1","1","blue");
INSERT INTO thing_colors VALUES ("2","1","red");
INSERT INTO thing_colors VALUES ("3","2","brown");
INSERT INTO thing_colors VALUES ("4","3","silver");
INSERT INTO thing_colors VALUES ("5","3","gold");

CREATE VIEW things_and_colors AS SELECT * FROM things JOIN thing_colors ON things._id=thing_colors.thing_id;

SELECT * FROM things_and_colors;

I find these renamed column names useful but is this normal SQL behavior and is it fine for me to rely on it?

But of course this is just an example, in real life I am joining three tables and the result has about开发者_StackOverflow 70 columns in it, of which 3 are named _id.

Don't select star, select the columns individually and assign an alias as needed.

No, you can't depend on the view renaming your columns to avoid conflicts. I don't have a copy of the standard handy so I can't quote chapter and verse but I know that PostgreSQL will say this:

ERROR:  column "_id" specified more than once

and MySQL will say this:

ERROR 1060 (42S21): Duplicate column name '_id'

Those are the only databases I have handy at the moment.