SQL error

Anyone see an error here?? I am going crazy... thx

38 Replies

i'm pretty sure you can't do line 9 for create_data +

PerfectOP•3y ago

ok so some logs point me to the last line, I guess it does not like using create_date in calculation yep any ideas how to do that?

mrnicericee•3y ago

you could do a trigger to update expire_date after create

PerfectOP•3y ago

oh jeez never even heard of that before

mrnicericee•3y ago

is this postgres?

PerfectOP•3y ago

yea

mrnicericee•3y ago

the super ugly but super informative postgres docs https://www.postgresql.org/docs/current/sql-createtrigger.html the more friendly one https://www.tutorialspoint.com/postgresql/postgresql_triggers.htm

PostgreSQL Documentation

CREATE TRIGGER

CREATE TRIGGER CREATE TRIGGER — define a new trigger Synopsis CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name …

PostgreSQL - TRIGGERS

PostgreSQL - TRIGGERS, PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.

PerfectOP•3y ago

cool thx, I could also probably do this right?

mrnicericee•3y ago

yeah that would work

PerfectOP•3y ago

timestamp would def be same since its based on transaction start afaik I'll stick with that for now and check these triggers out later thx for the help!

mrnicericee•3y ago

super useful for certain stuff, but it's hard to test because you don't know when it will trigger its usually fast

PerfectOP•3y ago

interesting We have "rooms" that expire after some time and this was best way I could think of doing the calc for it not in TS trigger sounds really useful for this sort of thing

mrnicericee•3y ago

yeah no worries! glad to put it on your plate, it's good to know 🙂

PerfectOP•3y ago

@MrNiceRicee oh also, why is it ok for me to use duration column but not create_date unless its not and I am about to find that out lol

mrnicericee•3y ago

postgres does not have access to create_date, when it's creating the row

PerfectOP•3y ago

oh but it does have duration since its coming from insert query?

mrnicericee•3y ago

hold on, lemme look back at it lol oh interesting, i think it should also fail too with that

PerfectOP•3y ago

I am about to see haha I assume it will

mrnicericee•3y ago

yeah probably a trigger will solve it

PerfectOP•3y ago

looks like I might need to do triggers

PerfectOP•3y ago

PerfectOP•3y ago

date_trunc I guess?

mrnicericee•3y ago

DEFAULT missing

PerfectOP•3y ago

oh wait yeah let me retry

PerfectOP•3y ago

cannot use column ref in default wait I think it was working

mrnicericee•3y ago

yeah it's telling you that you can't use the duration column crys

PerfectOP•3y ago

yep still broken without default alright triggers it is @MrNiceRicee last thing, what should I put as value of something I will update with trigger? like just a data type of date?

mrnicericee•3y ago

sorry, when i mentioned trigger meant it in two parts trigger -> the thing that listens on the database function -> thing that runs

PerfectOP•3y ago

oh I see now so here is my issue I guess I wait on server for respone from db the data I get back from db would need to have that expire_date to send out to event bus

mrnicericee•3y ago

--- function
CREATE OR REPLACE FUNCTION add_room_expiration_func() RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
[this where you write that update]

END;
$$
-------- trigger
CREATE TRIGGER add_room_expiration_trigger AFTER INSERT ON "rooms" FOR EACH ROW EXECUTE PROCEDURE add_room_expiration_func();

--- function
CREATE OR REPLACE FUNCTION add_room_expiration_func() RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
[this where you write that update]

END;
$$
-------- trigger
CREATE TRIGGER add_room_expiration_trigger AFTER INSERT ON "rooms" FOR EACH ROW EXECUTE PROCEDURE add_room_expiration_func();

something like that

PerfectOP•3y ago

since this trigger happens after that insert, how do I ensure that date happens to come back oh so maybe I could do a before insert? i'll have to look deeper into this or maybe its just worth doing this calculation on server

mrnicericee•3y ago

easiest would be doing the calculation on server, be easier to test and ensure you have the data there triggers are great if you wanna do an audit_table

PerfectOP•3y ago

yeah sounds like I would never know what expire_date would be unless I wait a bit then requery the calculation should come out the same anyways js Dates are just weird sometimes haha

mrnicericee•3y ago

rephrase that sentence, dates are weird in every language in general

PerfectOP•3y ago

also since you seem to know a lot about DBS i had ONE other question lol will postgres always use UTC unless I tell it otherwise? That is what I seem to have assumed after what I could find also true

mrnicericee•3y ago

i'm fairly certain it's typically UTC and also depends if you want to add timezones then it's that timezone where the db lives -- i think, gotta double check

PerfectOP•3y ago

I think ur right and u can set that but I THINK timezone defaults to UTC, just wasnt sure if it auto changes ever, I think in docker it doesn't but that could be wrong and I could not find any solid answer tbh

Gaming

Programming

SQL error