Search code examples
postgresqlpostgresql-13

How to use trunc() function in postgresql

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.

enter image description here

enter image description here

Solution

  • 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.