How to use trunc() function in postgresql

Question

I'm a little confused about using trunc() function in postgresql.

As far as I know, if I want to trunc date, I need to use the date_trunc() function in posgresql.

It looks like this: select date_trunc('month',now()).

But I found that there's a trunc() function in pg_catalog. If I use it like ths: select trunc(now(),'MM'). I will get the same result. This usage is similar to oracle.

I'm not sure if this is a built-in functions in postgre 13.3.

I've done some researchs on the internet, but I cannot see any description about these 2900+ functions in pg_catalog.

My v13 has 5 trunc functions in pg_catalog, but none of them take a timestamptz as their first argument. Maybe you installed some compatibility extension that added it. (But I wouldn't think it would add it to pg_catalog) — jjanes
– jjanes, Commented Dec 17, 2021 at 3:23

Laurenz Albe · Accepted Answer · 2021-12-21 09:35:59Z

1

No, that is no built-in PostgreSQL function. Also, it references the library from the orafce extension that defines a function like that.

To verify that, connect to PostgreSQL with psql and run \dx to list the extensions. orafce should be among them. To see the objects provided by the extension, run \dx+ orafce. To see the schema where orafce is installed, use \dx orafce.

The image you added to the question suggests that the function is in pg_catalog, but the extension is installed in public. Since it is forbidden to create functions in pg_catalog, somebody must have modified the PostgreSQL catalogs to make that happen. I'd say that your database is messed up. You can try to DROP EXTENSION orafce, followed by CREATE EXTENSION orafce SCHEMA public, perhaps that can repair the damage.

edited Dec 21, 2021 at 9:35

answered Dec 17, 2021 at 3:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

11 Comments

Xie Steven Over a year ago

I checked \dx+ orafce result. It seemed to be from 'orafce' extension, but why would it be added to pg_catalog?

Laurenz Albe Over a year ago

It cannot be in pg_catalog. Most likely it is in public. \dx will tell you the schema of the extension.

Xie Steven Over a year ago

I check the result by \dx. It seems to belong to orafce. But why is it in pg_catalog? You can see the screenshot in my updated question.

Laurenz Albe Over a year ago

See my updated answer.

Xie Steven Over a year ago

This the version infomation that I got by using select version(); PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

|

Collectives™ on Stack Overflow

How to use trunc() function in postgresql

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related