How do I use the Postgres INTERVAL types correctly?

[vinaysshenoy]

I am trying to insert ISO8601 Duration timestamps as Postgres Interval values.

I am having a bit of trouble inserting them into the database using the string template method. Take the following query as an example:

INSERT INTO "PROJECTS" (
                "id",
                "name",
                "description",
                "thumbnail_url",
                "suggested_grades_min",
                "suggested_grades_max",
                "prep_time",
                "duration",
                "created_at",
                "updated_at"
) VALUES (
                $(id),
                $(name),
                $(description),
                $(thumbnailUrl),
                $(suggestedGrades.minimum),
                $(suggestedGrades.maximum),
                $(prepTime),
                CAST($(duration) AS interval),
                $(timestamps.createdAt),
                $(timestamps.updatedAt)
)

This fails with the following error:

invalid input syntax for type interval: \"\"PT1H\"\"
.
.
.
CAST('\"PT1H\"' AS interval)

It seems to me that the library is introducing quotes around the value (I use Luxon Duration objects in my models. I am not able to figure out how to tell it to remove the quotes and just generate CAST('PT1H') AS INTERVAL instead.

I can manage to do this with the bulk insert APIs like so (when defining the column):

{
    name: 'duration',
    init: (cb) => {
        return `'${cb.value}'`
    },
    mod: ':raw',
    cast: 'interval'
}

I could use the bulk insert APIs to insert a single row, but I would like to see if there is an elegant way to do this using the standard APIs. Or even if this is the right way to solve it for the bulk insert APIs as well.

Can someone guide me on this, please?

[vinaysshenoy]

For now, I was able to do a workaround by constructing a record manually for insertion:

const insertSql = `
            INSERT INTO "PROJECTS" (
                "id",
                "name",
                "description",
                "thumbnail_url",
                "suggested_grades_min",
                "suggested_grades_max",
                "prep_time",
                "duration",
                "status",
                "created_at",
                "updated_at"
            ) VALUES (
                $(id),
                $(name),
                $(description),
                $(thumbnailUrl),
                $(suggestedGrades.minimum),
                $(suggestedGrades.maximum),
                $(prepTime),
                CAST($(duration:raw) AS interval),
                $(status),
                $(timestamps.createdAt),
                $(timestamps.updatedAt)
            )
`

await conn.none(insertSql, {
    id: record.id,
    name: record.name,
    description: record.description,
    thumbnailUrl: record.thumbnailUrl,
    suggestedGrades: {
        minimum: record.suggestedGrades.minimum,
        maximum: record.suggestedGrades.maximum
    },
    prepTime: record.prepTime,
    duration: `'${record.duration.toISO()}'`,
    status: record.status,
    timestamps: {
        createdAt: record.timestamps.createdAt,
        updatedAt: record.timestamps.updatedAt
    }
})

Seems a bit awkward to be constructing an object that is identical just to transform one property before saving. If there's a better approach, would love to know what to do here.

[vitaly-t]

According to the error, you have extraneous double quotes surrounding the interval text. Something doesn't look right. You shouldn't need to include any quotes by hand, something gets mixed up there.

I cannot surmise it any better, without a simple reproducible sample. Create one, if you want further help.

[vinaysshenoy]

@vitaly-t I have a barebones reproducible example repo here, with a docker setup for postgres included for convenience (README has instructions): https://github.com/vinaysshenoy/pg-interval

I have a general idea of what is happening here, which you can see in the example repository I linked. Luxon's Duration type automatically uses .toISO() internally when printing it as a string with the following error:

error: invalid input syntax for type interval: ""PT87.763S""
INSERT INTO EXAMPLES (duration) VALUES (CAST('"PT87.763S"' AS INTERVAL))

When I pass the duration object to pg-promise, it seems to surround it in double quotes before substituting it in the SQL query.

However, if I manually call the .toISO() method on the Duration object as pass it as a string to pg-promise, it works without issues.

[vitaly-t]

In your example, you are passing duration that's an object of type Duration. Library pg-promise sees it as a random object, and applies default :json formatting to it, hence the "" annotation. You need to pass in an actual interval, as a string, then you can recast it as INTERVAL.

[vinaysshenoy]

Ah, alright! I was not aware it uses the :json formatting for objects. Will update my codebase to convert it to a string before saving.

Out of curiosity, is there a way for me to register a "serializer" of some sort for specific data types? I know it's possible when reading data from the database, but not sure if there's a way to do it for writing.

Thanks!

[vitaly-t]

is there a way for me to register a "serializer" of some sort for specific data types?

That's what Custom Type Formatting is there for.

[vinaysshenoy]

Excellent, thank you very much.