SQLite Error: UNIQUE constraint failed

I think it would do you a great deal of good to take an sqlite tutorial. That way you could learn how to answer your question yourself.

This forum supports Node-RED and is not a "Teach me how to use Sqlite or MySQL or how do I code JavaScript. there are plenty of places to learn those basics.

In NR you define the SQLITE database when you configure the sqlite node. You can add as many tables as you want to one database. So, off hand, I can't think of a way to tell how many databases you have created except to look at all the sqlite nodes you have in your flows.

Thank you zenofmud for your help.
You are right , i can ask the question about NR here. I create database and table in NR according to 2 tutorials. one was with random function and i could do it correctly. in my project with csv file i did to create DB as well as those tutorials but i got errors. I can not understand how works my random projekt and DB of it but my new DB plays just with me. i am confused for this reson i wrote my question here.
tnx a lot an have a nice day.

Hi everyone,
last problem is solved and it is worked correct till today. I found today that my data in database are not sequential like my csv file. why is it happend? Data transfer sequential but when i see output von DB with debug node , i am counfused because of that.
Is that so, or i have mistake in my function?

Best Regards

Records in a database are not in any particular order. When you query it you can tell it to give you the data in whatever order you want, so if you have a time column then you can add sort by time to the query to tell it to give you the data in that order. Make sure that you have an index on that column in the db or the sort will be very slow.

1 Like

Thank you for your replay. it is correct and i can do this but i have every 24 hours temperature from a device.This data save in csv file for example since 03-11-2020 01:00:00 till next day 04-11-2020 00:45:00 , therfore i cannot select time as query and temperature too. How can i play with date and time and select them as increse oder decrese of order?

What are the columns in the database and what types are they?

error TEXT NOT NULL (it is '0' in every rows)
UTCOffsetMin TEXT NOT NULL(it is '60'in every rows)
Temperature FLOAT NOT NULL

That looks ok

I don't understand what you mean. Why can't you select the columns and sort by time?
What query are you using to get the data?

because of DateTime column. my time increse till 11:45:00 then decrese on 00:00:00 and 00:15:00, 00:30:00 and 00:45:00.

Sorry, I have no idea what you mean.

You should be storing date/time values as integers which are UNIX timestamps. Then you can easily work with them in Node-RED (a JavaScript timestamp is 1000x a UNIX timestamp). SQLite doesn't have a proper date/time field type but all of its date/time functions will work with 3 different formats of which integer is best for working with Node-RED.

Datatypes In SQLite Version 3

This page of the manual shows you how to work with date/time values in SQLite

Date And Time Functions (sqlite.org)

1 Like

Thank you for your response. I try it .

Best regards

I have another question. I recieve csv. file from some devices every day. I want to save data automatically to database but i have not idea how i can do it. i thank you for your advice.

Bst Regards

SQLite - How to import a CSV file into SQLite table? | TablePlus


This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.