0

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

Xie Steven
  • 8,544
  • 1
  • 9
  • 23
  • 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 Dec 17 '21 at 03:23

1 Answers1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I checked `\dx+ orafce` result. It seemed to be from 'orafce' extension, but why would it be added to pg_catalog? – Xie Steven Dec 17 '21 at 07:22
  • It cannot be in `pg_catalog`. Most likely it is in `public`. `\dx` will tell you the schema of the extension. – Laurenz Albe Dec 17 '21 at 07:52
  • 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. – Xie Steven Dec 21 '21 at 02:56
  • See my updated answer. – Laurenz Albe Dec 21 '21 at 08:08
  • 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 – Xie Steven Dec 21 '21 at 08:24
  • The remaining alternative is that somebody messed up the catalogs. What do you get for `\dx orafce`? – Laurenz Albe Dec 21 '21 at 08:30
  • Yes. I see the `trunc(timestamp with time zone,text)` function by `\dx+ orafce`. – Xie Steven Dec 21 '21 at 08:46
  • That's not what I was asking. – Laurenz Albe Dec 21 '21 at 09:12
  • I got `List of installed extensions Name | Version | Schema |Description orafce | 3.15 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS (1 row)` by `\dx orafce` – Xie Steven Dec 21 '21 at 09:26
  • Thanks. So the extension is installed in `public`. Do you see that function in `\df public.trunc`? Anyway, if there is `pg_catalog.trunc` function that references orafce, then somebody messed up your PostgreSQL catalogs. – Laurenz Albe Dec 21 '21 at 09:32
  • `Do you see that function in \df public.trunc? ` No. Anyway, thank you for your patient answer. – Xie Steven Dec 22 '21 at 01:50