Xata•2mo ago

is there currently a way, while creating

is there currently a way, while creating records i would like to modify xata's internal columns, for example give a old date for a record created right now

10 Replies

kostas•2mo ago

Hi, the internal datetime columns are created automatically by a trigger and cannot be overwritten from the REST API*. You would need to use an additional column for the custom value. *It is possible to overwrite or modify these values with SQL for postgres-enabled databases (https://xata.io/docs/postgres), i.e. insert into mytable(xata_createdat) VALUES('2024-07-29 06:34:42.475522+00');

Oreki•2mo ago

Oh alright, thank you!

kostas•2mo ago

I just tried it out and turns out updating xata internal fields via SQL also works on the "traditional" databases as well, using SQL over HTTP: https://xata.io/docs/sdk/sql/overview

SQL over HTTP

How to access Xata using SQL directly over HTTP

Oreki•2mo ago

So i cannot edit them during creation but once i have created, i can edit using raw sql?

kostas•2mo ago

You can insert custom values for those datetime columns if you insert them using SQL. Otherwise, yes, the REST API will automatically set the values and then you can update them with SQL

Oreki•2mo ago

Alright thank you, i'll just update them after inserting with client what type is xata.createdAt?

`UPDATE posts SET "xata.createdAt" = '${new Date(post.isoDate || post.pubDate || new Date()).toISOString()}' WHERE id = '${inserted.id}'`,

`UPDATE posts SET "xata.createdAt" = '${new Date(post.isoDate || post.pubDate || new Date()).toISOString()}' WHERE id = '${inserted.id}'`,

this seems to be throwing an error

kostas•2mo ago

Looking into this a bit more, turns out the values are immutable. You can specify them during insert time but you cannot update them once they've been written. Something along the lines:

const mydate = new Date("2022-10-25 01:00:00");

const res =
  await xata.sql`insert into posts("xata.createdAt") VALUES(${mydate})`;

const mydate = new Date("2022-10-25 01:00:00");

const res =
  await xata.sql`insert into posts("xata.createdAt") VALUES(${mydate})`;

Oreki•2mo ago

Ahh so only way to pass in custom creation date is when you create using raw SQL query

kostas•2mo ago

yes It's best to use an extra column to hold "your" creation date when that is necessary. And if such a value exists for some record, use that in your application, instead of the xata.createdAt one

Oreki•2mo ago

Yeah that'd be the best, I was thinking if I could reduce the amount of columns somehow but that's not possible

Gaming

Programming

is there currently a way, while creating