Hello QDB team, Is there a way/workaround to prevent ingesting rows in a table with the same values in "symbol" and "timestamp" columns (those are de-facto a composite PrimaryKey and regular SQL DB would throw "duplicate PK" error). We're using the Sender interface from the Java ILP client to ingest new rows. Accidentally, our software may attempt to persist new rows with the same symbol and timestamp values.

hi @Hristo Katsarski, unfortunately there is no way to set UNIQUE constraint in QuestDB or do deduplication of records automatically. you can try to deal with it in SQL but that is likely to come with a performance penalty. • you can use

first()

,

last()

select symbol, first(col1), first(col2), timestamp from table;
select symbol, last(col1), last(col2), timestamp from table;

(you can add GROUP BY clause but QuestDB can figure it out from the list of columns too as above)

• you can also try to use

LATEST ON

instead of `last()`:

select symbol, col1, col2, timestamp from table latest on timestamp partition by symbol;

Hi @Hristo Katsarski , as a separate item, @Maciej Bodek is going to follow up here to take you through how to ingest data frequently, into the same tables, via the brand new CSV import UI that we are releasing in the coming few hours

Hi @Imre, thank you for the detailed reply, I have to see if I can apply it for our use cases, at present I'm filtering duplicate timestamps from returned ResultSet-s.

Hi @Hristo Katsarski, Before I come back with some detailed instructions (we haven’t yet released the new version as well), could you give me a rough estimate on how big your file would be - how many rows are we talking about on average? 🙂

Hi @Maciej Bodek , we'd like to import periodically new data from CSV file, data size can vary but won't be bigger than 10-20 K rows.

Sure, that won’t be an issue. I’ll come back with detailed instructions once the release is done. 🙂

thank you 👍

Hey @Hristo Katsarski, 7.1.2 is already available, feel free to download and check out the new Import UI. Writing to an existing table can be done by dropping/selecting/pasting a file with the data, then making sure Write mode is set to Append, and just hit Upload. A few notes: • The currently shipped version does not allow you to change schema, designated timestamp and it’s pattern for an already existing table. You can all do it by selecting a different target table name though, it will automatically unlock the entire form. • Check out

Settings

panel for any tweaks you might need. If you have any problems, perhaps import to a new table first, and then you can click

Details

button next to the upload status, to see detailed upload result. Let me know if you have any issues, happy to explain further 👋