How to insert a string array into a column of sqlite3 table

Question

How do I create a table which can hold string arrays as column values. e.g.:

CREATE TABLE bug
(
    id BIGINT NOT NULL,
    title VARCHAR,
    description VARCHAR,
    timestamp TIMESTAMP,
    duedate DATETIME,
    assignedto VARCHAR,
    userid BIGINT NOT NULL,
    viewedby VARCHAR[],
    watchers VARCHAR[],
    priority VARCHAR,
    tags VARCHAR[],
    comments VARCHAR[],
    PRIMARY KEY(id)
    FOREIGN KEY(userid) REFERENCES user(id)
);

And how do we insert values into this field while inserting data.

MikeT · Accepted Answer · 2018-07-17 02:39:01Z

1

Typically you wouldn't as you then reduce the usability (i.e. there is no way of discerning say the nth element easily).

Rather you could create a table that would have a column for each element and a link/reference/mapping to the respective bug.

e.g. you could have something like the following :-

DROP TABLE If EXISTS bug;
DROP TABLE IF EXISTS bugarrayelements;
CREATE TABLE IF NOT EXISTS bug
(
    id BIGINT NOT NULL,
    title VARCHAR,
    description VARCHAR,
    timestamp TIMESTAMP,
    duedate DATETIME,
    assignedto VARCHAR,
    userid BIGINT NOT NULL,
    viewedby VARCHAR[],
    watchers VARCHAR[],
    priority VARCHAR,
    tags VARCHAR[],
    comments VARCHAR[],
    PRIMARY KEY(id)
    --FOREIGN KEY(userid) REFERENCES user(id) -- commented out for convenience
);
CREATE TABLE IF NOT EXISTS bugarrayelements
(bug_reference INTEGER, element_order INTEGER, element_data TEXT
);

-- First bug with representation of a 5 element string array
INSERT INTO bug (id,title,userid) VALUES(1,'bug001',100);
INSERT INTO bugarrayelements VALUES 
    (1,1,'This failed.'),
    (1,2,'It failed with this error.'),
    (1,3,'It failed on line 2450.'),
    (1,4,'Out of a total of 9567 lines of code.'),
    (1,5,'blah blah blah.')
;

-- Second bug
INSERT INTO bug (id,title,userid) VALUES(2,'bug500',321);
INSERT INTO bugarrayelements VALUES
    (2,1,'This failed.'),
    (2,2,'It failed with this error.'),
    (2,3,'It failed on line 2450'),
    (2,4,'Out of a total of 9567 lines of code.'),
    (2,5,'blah blah blah.'),
    (2,1,'This failed.'),
    (2,2,'It failed with this error.'),
    (2,3,'It failed on line 2450'),
    (2,4,'Out of a total of 9567 lines of code.'),
    (2,5,'blah blah blah.')
;
SELECT * FROM bug JOIN bugarrayelements ON bug_reference = bug.id ORDER BY bug.id,element_order;
SELECT bug.title,bug.userid, group_concat(element_data,' ~~~ ') FROM bug JOIN bugarrayelements ON bug_reference = bug.id GROUP BY bug.id ORDER BY bug.id, element_order;

using the above the first result would be :-

the second result would be :-

The above shows multiple insertions (i.e. all array elements), they could also be added individually (probably within a transaction)

answered Jul 17, 2018 at 2:39

MikeT

57.9k16 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Narasimha Over a year ago

Thanks Mike. I realised that but I guess this can be done in mongoDB by creating a complex document. So would mongoDB be a better choice in such a scenario ?

MikeT Over a year ago

I have no idea, as I've never used mongoDB. Perhaps you should change the tag or ask another question with the mongoDB tag rather than SQLite3.

Collectives™ on Stack Overflow

How to insert a string array into a column of sqlite3 table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related