Convert a MySQL Query to PostgreSQL

Question

I have a MySQL query that needs to be converted over to PostgreSQL. As I understand, PostgreSQL has no UTC_TIMESTAMP() or DATE_ADD() functions available. After reading the docs, I'm not entirely certain which date functions solve my needs in this context.

Here's the query:

INSERT INTO snippets (title, content, created, expires)
VALUES(?, ?, UTC_TIMESTAMP(), DATE_ADD(UTC_TIMESTAMP(), INTERVAL ? DAY))

For reference, here is my snippets model:

CREATE TABLE snippets (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    created TIMESTAMP(0) NOT NULL,
    expires TIMESTAMP(0) NOT NULL
);

The use of the non-standard serial types is discouraged. It's better to use the standard compliant identity columns with modern Postgres versions — user330315
– user330315, Commented Nov 23, 2022 at 8:06

Paul T. · Accepted Answer · 2022-11-23 13:19:00Z

1

I've not done much with postgresql either, but maybe something like:

INSERT INTO snippets (title, content, created, expires)
VALUES('test', 'stuff', (now() at time zone 'utc'), (now() at time zone 'utc' + interval '1 day' * 3))

Which gives:

id  title   content created             expires
1   test    stuff   2022-11-23 13:02:13 2022-11-26 13:02:13

Then to restore the parameterized version of the query:

INSERT INTO snippets (title, content, created, expires)
VALUES(?, ?, (now() at time zone 'utc'), (now() at time zone 'utc' + interval '1 day' * ?))

I was not sure how to test the parameterized equivalent, but here's a dbfiddle example with the output from the first query.

edited Nov 23, 2022 at 13:19

answered Nov 23, 2022 at 2:46

Paul T.

5,02712 gold badges48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Either interval '1 day' * ? or make_interval(days => ?)

Paul T. Over a year ago

@a_horse_with_no_name ... thanks I've updated the answer (and the fiddle) with the first part of your suggestion.

Collectives™ on Stack Overflow

Convert a MySQL Query to PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related