Understanding years part of Interval

[Marcos Pegoraro]

I was just playing with some random timestamps for a week, for a month, for
a year ...

select distinct current_date+((random()::numeric)||'month')::interval from
generate_series(1,100) order by 1;
It´s with distinct clause because if you change that 'month' for a 'year'
it´ll return only 12 rows, instead of 100. So, why years part of interval
works differently than any other ?

select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins
48.00 secs
select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins
0.00 secs
select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins 0.00
secs

thanks
Marcos

Re: Understanding years part of Interval

[Erik Wienhold]

> On 06/02/2023 12:20 CET Marcos Pegoraro  wrote:
>
> I was just playing with some random timestamps for a week, for a month,
> for a year ...
>
> select distinct current_date+((random()::numeric)||'month')::interval from 
> generate_series(1,100) order by 1;
> It´s with distinct clause because if you change that 'month' for a 'year'
> it´ll return only 12 rows, instead of 100. So, why years part of interval
> works differently than any other ?
>
> select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins 48.00 
> secs
> select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins 0.00 
> secs
> select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins 0.00 
> secs

Explained in 
https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-INTERVAL-INPUT:

Field values can have fractional parts: for example, '1.5 weeks' or
'01:02:03.45'. However, because interval internally stores only
three integer units (months, days, microseconds), fractional units
must be spilled to smaller units. Fractional parts of units greater
than months are rounded to be an integer number of months, e.g.
'1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and
days are computed to be an integer number of days and microseconds,
assuming 30 days per month and 24 hours per day, e.g., '1.75 months'
becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as
fractional on output.

Internally interval values are stored as months, days, and
microseconds. This is done because the number of days in a month
varies, and a day can have 23 or 25 hours if a daylight savings time
adjustment is involved.

--
Erik

ALTER COLUMN to change GENERATED ALWAYS AS expression?

[Philip Semanchuk]

Hi all,
I have a column defined GENERATED ALWAYS AS {my_expression} STORED. I’d like to 
change the {my_expression} part. After reading the documentation for ALTER 
TABLE (https://www.postgresql.org/docs/current/sql-altertable.html) and trying 
a few things that resulted in syntax errors, there doesn’t seem to be a way to 
alter the column’s GENERATED expression in place. It seems like my only option 
is to drop and re-add the column. Is that correct? 

Thanks
Philip

Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

[Sebastien Flaesch]

Hello!

Assuming that a sequence is used to implement GENERATED { ALWAYS | BY DEFAULT } 
AS IDENTITY

Is there any built-in function that returns the underlying sequence name used 
for such column?

Otherwise, an SQL query to return the sequence name?

I need the sequence name, in order to reset it (setval) or to get the last 
generated value (currval) ...

The query must work with all PostgreSQL versions 10 to 15 ...

Seb

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

[Thomas Kellerer]

Sebastien Flaesch schrieb am 06.02.2023 um 18:17:

Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY DEFAULT 
} AS IDENTITY|

Is there any built-in function that returns the underlying sequence name used 
for such column?

Otherwise, an SQL query to return the sequence name?

I need the sequence name, in order to reset it (setval) or to get the last 
generated value (currval) ...

The query must work with all PostgreSQL versions 10 to 15 ...


Despite its name pg_get_serial_sequence() also works for identity columns

plpgsql: ambiguous column reference in ON CONFLICT clause

[Torsten Förtsch]

Is there a way to prevent this from happening? I know I can use the PK
constraint name or rename the OUT variable i. The question is can this be
resolved while keeping the arbiter inference and the variable name.

CREATE TABLE x.x (
i INT PRIMARY KEY
);

CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT)
LANGUAGE plpgsql AS $$
  BEGIN
INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING;
  END
$$;

postgres=# select * from x.ins(1);
ERROR:  column reference "i" is ambiguous
LINE 3: ON CONFLICT (i) DO NOTHING
^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  INSERT INTO x.x(i)
SELECT p_i
ON CONFLICT (i) DO NOTHING
CONTEXT:  PL/pgSQL function x.ins(integer) line 3 at SQL statement

The conflicting variable is the OUT parameter of the function.

Normally, I'd suggest to fully qualify the name but the following or
similar is a syntax error:

INSERT INTO x.x(i) AS t
SELECT p_i
ON CONFLICT (t.i) DO NOTHING;

According to the documentation in
https://www.postgresql.org/docs/current/plpgsql-implementation.html:

> Query parameters will only be substituted in places where they are
syntactically permissible.

and

> Another way to understand this is that variable substitution can only
insert data values into an SQL command; it cannot dynamically change which
database objects are referenced by the command.

After reading this I am wondering if the current behavior is actually a bug.

Thanks,
Torsten

Re: plpgsql: ambiguous column reference in ON CONFLICT clause

[Adrian Klaver]

On 2/6/23 09:46, Torsten Förtsch wrote:
Is there a way to prevent this from happening? I know I can use the PK 
constraint name or rename the OUT variable i. The question is can this 
be resolved while keeping the arbiter inference and the variable name.


CREATE TABLE x.x (
     i INT PRIMARY KEY
);

CREATE OR REPLACE FUNCTION x.ins(p_i INT, OUT i INT)
LANGUAGE plpgsql AS $$
   BEGIN
     INSERT INTO x.x(i)
     SELECT p_i
         ON CONFLICT (i) DO NOTHING;
   END
$$;

postgres=# select * from x.ins(1);
ERROR:  column reference "i" is ambiguous
LINE 3:         ON CONFLICT (i) DO NOTHING
                             ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  INSERT INTO x.x(i)
     SELECT p_i
         ON CONFLICT (i) DO NOTHING
CONTEXT:  PL/pgSQL function x.ins(integer) line 3 at SQL statement

The conflicting variable is the OUT parameter of the function.

Normally, I'd suggest to fully qualify the name but the following or 
similar is a syntax error:


     INSERT INTO x.x(i) AS t
     SELECT p_i
         ON CONFLICT (t.i) DO NOTHING;

According to the documentation in 
https://www.postgresql.org/docs/current/plpgsql-implementation.html 
:


 > Query parameters will only be substituted in places where they are 
syntactically permissible.



See:

https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-VAR-SUBST

for your choices.



and

 > Another way to understand this is that variable substitution can only 
insert data values into an SQL command; it cannot dynamically change 
which database objects are referenced by the command.


After reading this I am wondering if the current behavior is actually a bug.

Thanks,
Torsten


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: plpgsql: ambiguous column reference in ON CONFLICT clause

[David G. Johnston]

On Monday, February 6, 2023, Torsten Förtsch  wrote:

>
> After reading this I am wondering if the current behavior is actually a
> bug.
>

Arguably it is a bug, and a known one at that if you want some light
reading, but regardless there is presently no proposal to get rid of the
POLA violation and little chance for any solution to be back-patched since
the issue is lack of good existing technical options.

https://www.postgresql.org/message-id/1990141.1638388378%40sss.pgh.pa.us

https://www.postgresql.org/message-id/flat/15533-ac9506ee433a0d18%40postgresql.org

David J.

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

[Sebastien Flaesch]

I am writing generic code and don't know in advance the name of identity column.

Only the table name is known (parsing of INSERT INTO tabname statement is easy)

Column names may use uppercase or lowercase letters.

I guess I could figure out what column is defined as identity, from 
pg_attrribute.attidentity = 'd' / 'a', since I know the table name...

For serial/bigserial/smallserial types, I am using:

select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 
'nextval.''([^'']*)') seqname, c.attname
  from pg_class p
   join pg_attrdef a on (p.oid=a.adrelid)
   join pg_attribute c on (p.oid=c.attrelid and a.adnum=c.attnum)
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and pg_get_expr(a.adbin,0) like 'nextval%'
   and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema());

I hope it's possible to do something similar for identity columns.

Seb


From: Thomas Kellerer 
Sent: Monday, February 6, 2023 6:43 PM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY 
DEFAULT } AS IDENTITY column

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch schrieb am 06.02.2023 um 18:17:
> Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY 
> DEFAULT } AS IDENTITY|
>
> Is there any built-in function that returns the underlying sequence name used 
> for such column?
>
> Otherwise, an SQL query to return the sequence name?
>
> I need the sequence name, in order to reset it (setval) or to get the last 
> generated value (currval) ...
>
> The query must work with all PostgreSQL versions 10 to 15 ...

Despite its name pg_get_serial_sequence() also works for identity columns

Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY column

[Sebastien Flaesch]

... and I would like to automatically return the last generated identity, and 
also reset the sequence, if a value was specified explicitly for the identity 
column and this value is greater than the last generated value.

With serial/bigserial/smallserial I am adding a returning clause to each INSERT:

insert into tab1 (name) VALUES ('aaa')
  returning tab1.pkey,
 (select case when tab1.pkey > (select last_value from 
public.tab1_pkey_seq)
then setval('public.tab1_pkey_seq',tab1.pkey,true) else 0 end)

Seb

From: Sebastien Flaesch 
Sent: Monday, February 6, 2023 7:03 PM
To: Thomas Kellerer ; pgsql-general@lists.postgresql.org 

Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY 
DEFAULT } AS IDENTITY column


EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

I am writing generic code and don't know in advance the name of identity column.

Only the table name is known (parsing of INSERT INTO tabname statement is easy)

Column names may use uppercase or lowercase letters.

I guess I could figure out what column is defined as identity, from 
pg_attrribute.attidentity = 'd' / 'a', since I know the table name...

For serial/bigserial/smallserial types, I am using:

select ns.nspname||'.'||substring(pg_get_expr(a.adbin,0) from 
'nextval.''([^'']*)') seqname, c.attname
  from pg_class p
   join pg_attrdef a on (p.oid=a.adrelid)
   join pg_attribute c on (p.oid=c.attrelid and a.adnum=c.attnum)
   join pg_namespace ns on (p.relnamespace=ns.oid)
 where upper(p.relname)=upper('tab1')
   and pg_get_expr(a.adbin,0) like 'nextval%'
   and (ns.nspname=current_schema() or ns.oid=pg_my_temp_schema());

I hope it's possible to do something similar for identity columns.

Seb


From: Thomas Kellerer 
Sent: Monday, February 6, 2023 6:43 PM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Get the sequence name corresponding to a GENERATED { ALWAYS | BY 
DEFAULT } AS IDENTITY column

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch schrieb am 06.02.2023 um 18:17:
> Assuming that a sequence is used to implement |GENERATED { ALWAYS | BY 
> DEFAULT } AS IDENTITY|
>
> Is there any built-in function that returns the underlying sequence name used 
> for such column?
>
> Otherwise, an SQL query to return the sequence name?
>
> I need the sequence name, in order to reset it (setval) or to get the last 
> generated value (currval) ...
>
> The query must work with all PostgreSQL versions 10 to 15 ...

Despite its name pg_get_serial_sequence() also works for identity columns

Re: plpgsql: ambiguous column reference in ON CONFLICT clause

[Pavel Stehule]

po 6. 2. 2023 v 19:02 odesílatel David G. Johnston <
david.g.johns...@gmail.com> napsal:

> On Monday, February 6, 2023, Torsten Förtsch 
> wrote:
>
>>
>> After reading this I am wondering if the current behavior is actually a
>> bug.
>>
>
> Arguably it is a bug, and a known one at that if you want some light
> reading, but regardless there is presently no proposal to get rid of the
> POLA violation and little chance for any solution to be back-patched since
> the issue is lack of good existing technical options.
>
> https://www.postgresql.org/message-id/1990141.1638388378%40sss.pgh.pa.us
>
>
> https://www.postgresql.org/message-id/flat/15533-ac9506ee433a0d18%40postgresql.org
>

This is an interesting issue. I fully agree, so it is very pretty
unintuitive, but when you start calculating with possibility of expression
indexes, it is probably the best that is possible. I don't think it is a
bug. Unfortunately, at this moment we have no information about single or
double braces usage. So there is not any possibility to say so there should
not be an expression's index. And if there can be a functional index, then
using a possible variable can be really confusing without unique meaning.

This is probably only one case, where you can specify an index in a query,
so there are not any other similar cases, and this special case should be
documented and explained in plpgsql doc.

Regards

Pavel



>
> David J.
>
>

Re: Sequence vs UUID

[veem v]

So, it may be the machine on which the code is getting executed behind the
scene , in the site "https://dbfiddle.uk/"; is playing a key role in the
speed, however, the comparative performance of UUID vs sequence should stay
the same.
 So I think, after this test we can safely conclude that if we compare
the performance of the UUID(both version-4, version 7) VS sequence. The
UUID performs a lot worse as compared to sequence. So unless there exists
some strong reason/justification for UUID, we should default use the
sequence. Correct me if I'm wrong. And also  I understand the cases
of multi master replication/sharding etc, may be a factor but other than
that I can't think of any scenario where sequences can be used.



On Fri, 3 Feb 2023 at 23:07, Dominique Devienne  wrote:

> On Fri, Feb 3, 2023 at 5:48 PM veem v  wrote:
>
>> Actually I did the testing by connecting to "https://dbfiddle.uk/";
>> postgres version -15.
>>
>> PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
>> 20210514 (Red Hat 8.5.0-10), 64-bit
>>
>> Am I doing it wrong, please confirm?
>>
>>
> No clue. But even SQLite can generate 1M uuid (16 bytes random blobs are
> equivalent) is 1/3 of the time, so 30x faster.
> So your timings of generating 100K uuids and counting them seems way too
> slow to me. --DD
>
> sqlite> select count(randomblob(16)) from generate_series(1,1000*1000);
> QUERY PLAN
> `--SCAN generate_series VIRTUAL TABLE INDEX 3:
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 1500   Start at 15
> 1 Null   0 1 10   r[1..1]=NULL
> 2 VOpen  0 0 0 vtab:274D3E0   0
> 3 Integer1 4 00   r[4]=1
> 4 Multiply   6 6 50   r[5]=r[6]*r[6]
> 5 Integer3 2 00   r[2]=3
> 6 Integer2 3 00   r[3]=2
> 7 VFilter0 1120   iplan=r[2]
> zplan=''
> 8   Function   1 8 7 randomblob(1)  0   r[7]=func(r[8])
> 9   AggStep0 7 1 count(1)   1   accum=r[1]
> step(r[7])
> 10VNext  0 8 00
> 11AggFinal   1 1 0 count(1)   0   accum=r[1] N=1
> 12Copy   1 9 00   r[9]=r[1]
> 13ResultRow  9 1 00   output=r[9]
> 14Halt   0 0 00
> 15Transaction0 0 1 0  1   usesStmtJournal=0
> 16Integer1000  6 00   r[6]=1000
> 17Integer168 00   r[8]=16
> 18Goto   0 1 00
> ┌───┐
> │ count(randomblob(16)) │
> ├───┤
> │ 100   │
> └───┘
> Run Time: real 0.278 user 0.25 sys 0.00
>
>
>> On Fri, 3 Feb 2023 at 21:28, Dominique Devienne 
>> wrote:
>>
>>> Something's off regarding Guid generations IMHO...
>>> You generate 100K Guids in ~1s. While we generate (in C++, Windows
>>> Release, using Boost) 16M of them in +/- the same time:
>>>
>>

Re: Question regarding UTF-8 data and "C" collation on definition of field of table

[Peter J. Holzer]

On 2023-02-05 18:57:13 -0600, Ron wrote:
> Why are you specifying the collation to be "C" when the default db encoding
> is UTF8, and UTF-8 has Greek, Chinese and English encodings?

C is equally bad for Greek, Chinese and English ;-)

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Sequence vs UUID

[Julian Backes]

I don't really understand what you mean by 'performance'. To me it is not
surprising that incrementing (I know it is not just incrementing) a
64bit integer is faster than generating 128 bit data with a good amount of
random data even if it seems to be too slow. So in my opinion you need to
separate
1) generating data (which might happen on the client in case of UUID and
not in the db...)
2) inserting data
3) selecting data

in both sequential as well as parallel scenarios.

Am Mo., 6. Feb. 2023 um 19:32 Uhr schrieb veem v :

> So, it may be the machine on which the code is getting executed behind the
> scene , in the site "https://dbfiddle.uk/"; is playing a key role in the
> speed, however, the comparative performance of UUID vs sequence should stay
> the same.
>  So I think, after this test we can safely conclude that if we compare
> the performance of the UUID(both version-4, version 7) VS sequence. The
> UUID performs a lot worse as compared to sequence. So unless there exists
> some strong reason/justification for UUID, we should default use the
> sequence. Correct me if I'm wrong. And also  I understand the cases
> of multi master replication/sharding etc, may be a factor but other than
> that I can't think of any scenario where sequences can be used.
>
>
>
> On Fri, 3 Feb 2023 at 23:07, Dominique Devienne 
> wrote:
>
>> On Fri, Feb 3, 2023 at 5:48 PM veem v  wrote:
>>
>>> Actually I did the testing by connecting to "https://dbfiddle.uk/";
>>> postgres version -15.
>>>
>>> PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0
>>> 20210514 (Red Hat 8.5.0-10), 64-bit
>>>
>>> Am I doing it wrong, please confirm?
>>>
>>>
>> No clue. But even SQLite can generate 1M uuid (16 bytes random blobs are
>> equivalent) is 1/3 of the time, so 30x faster.
>> So your timings of generating 100K uuids and counting them seems way too
>> slow to me. --DD
>>
>> sqlite> select count(randomblob(16)) from generate_series(1,1000*1000);
>> QUERY PLAN
>> `--SCAN generate_series VIRTUAL TABLE INDEX 3:
>> addr  opcode p1p2p3p4 p5  comment
>>   -        -  --  -
>> 0 Init   0 1500   Start at 15
>> 1 Null   0 1 10   r[1..1]=NULL
>> 2 VOpen  0 0 0 vtab:274D3E0   0
>> 3 Integer1 4 00   r[4]=1
>> 4 Multiply   6 6 50   r[5]=r[6]*r[6]
>> 5 Integer3 2 00   r[2]=3
>> 6 Integer2 3 00   r[3]=2
>> 7 VFilter0 1120   iplan=r[2]
>> zplan=''
>> 8   Function   1 8 7 randomblob(1)  0
>> r[7]=func(r[8])
>> 9   AggStep0 7 1 count(1)   1   accum=r[1]
>> step(r[7])
>> 10VNext  0 8 00
>> 11AggFinal   1 1 0 count(1)   0   accum=r[1] N=1
>> 12Copy   1 9 00   r[9]=r[1]
>> 13ResultRow  9 1 00   output=r[9]
>> 14Halt   0 0 00
>> 15Transaction0 0 1 0  1
>> usesStmtJournal=0
>> 16Integer1000  6 00   r[6]=1000
>> 17Integer168 00   r[8]=16
>> 18Goto   0 1 00
>> ┌───┐
>> │ count(randomblob(16)) │
>> ├───┤
>> │ 100   │
>> └───┘
>> Run Time: real 0.278 user 0.25 sys 0.00
>>
>>
>>> On Fri, 3 Feb 2023 at 21:28, Dominique Devienne 
>>> wrote:
>>>
 Something's off regarding Guid generations IMHO...
 You generate 100K Guids in ~1s. While we generate (in C++, Windows
 Release, using Boost) 16M of them in +/- the same time:

>>>

Re: Sequence vs UUID

[Peter J. Holzer]

On 2023-02-06 20:04:39 +0100, Julian Backes wrote:
> I don't really understand what you mean by 'performance'. To me it is not
> surprising that incrementing (I know it is not just incrementing) a
> 64bit integer is faster than generating 128 bit data with a good amount of
> random data even if it seems to be too slow.

That's not really the problem with UUIDs, though. My (not very fast)
laptop can call getrandom() 1 million times per second (in a single
thread). Avoiding the system call[1] could make this fast enough to be
completely negligible compared to the time of writing a row to disk.

But UUIDs are random and that plays havoc with locality. For example
consider one table with invoices and another with invoice items. If you
want to get all the invoices including the items of a single day, the
data is probably nicely clustered together in the tables. But the join
needs to look up random ids in the index, which will be spread all over
the index. In a simple benchmark for this scenario the UUIDs were about
4.5 times slower than sequential ids. (In other benchmarks the
difference was only a few percent)

So depending on the querys the difference may be negligible or huge.

It really depends on your access patterns.

hp


[1] There was even a discussion about making that much faster on the
LKML recently.


-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Understanding years part of Interval

[Erik Wienhold]

> On 06/02/2023 18:33 CET Marcos Pegoraro  wrote:
>
> Em seg., 6 de fev. de 2023 às 10:59, Erik Wienhold  escreveu:
> > > On 06/02/2023 12:20 CET Marcos Pegoraro  wrote:
> >  >
> >  > I was just playing with some random timestamps for a week, for a month,
> >  > for a year ...
> >  >
> >  > select distinct current_date+((random()::numeric)||'month')::interval 
> > from generate_series(1,100) order by 1;
> >  > It´s with distinct clause because if you change that 'month' for a 'year'
> >  > it´ll return only 12 rows, instead of 100. So, why years part of interval
> >  > works differently than any other ?
> >  >
> >  > select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins 
> > 48.00 secs
> >  > select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins 
> > 0.00 secs
> >  > select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins 
> > 0.00 secs
> >
> >  Explained in 
> > https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-INTERVAL-INPUT:
> >
> >  Field values can have fractional parts: for example, '1.5 weeks' or
> >  '01:02:03.45'. However, because interval internally stores only
> >  three integer units (months, days, microseconds), fractional units
> >  must be spilled to smaller units. Fractional parts of units greater
> >  than months are rounded to be an integer number of months, e.g.
> >  '1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and
> >  days are computed to be an integer number of days and microseconds,
> >  assuming 30 days per month and 24 hours per day, e.g., '1.75 months'
> >  becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as
> >  fractional on output.
> >
> >  Internally interval values are stored as months, days, and
> >  microseconds. This is done because the number of days in a month
> >  varies, and a day can have 23 or 25 hours if a daylight savings time
> >  adjustment is involved.
> >
> I´ve sent this message initially to general and Erik told me it's documented,
> so it's better to hackers help me if this has an explaining why it's done 
> that way.
>
> select '1 year'::interval = '1.05 year'::interval -->true ?
> I cannot agree that this select returns true.

The years are converted to months and the fractional month is rounded half up:

1.05 year = 12.6 month
=> 1 year 0.6 month
=> 1 year 1 month(after rounding)

Compare that to 12.5 months to see when the rounding occurs:

12.5 month / 12 month
=> 1.0416... years

Plug 1.0416 and 1.0417 into the interval to observe the rounding:

=# select '1.0416 year'::interval, '1.0417 year'::interval;
 interval |   interval
--+--
 1 year   | 1 year 1 mon

--
Erik

Language options for GIN index support functions

[Phillip Diffley]

Hello,

The support functions and operator methods needed to extend a GIN index are
documented in C syntax
. Do these
functions need to be implemented in C, or is there an interface for other
languages like PL/pgSQL?

Thanks!
Phillip

Re: Language options for GIN index support functions

[Pavel Stehule]

Hi


út 7. 2. 2023 v 3:49 odesílatel Phillip Diffley 
napsal:

> Hello,
>
> The support functions and operator methods needed to extend a GIN index
> are documented in C syntax
> . Do
> these functions need to be implemented in C, or is there an interface for
> other languages like PL/pgSQL?
>

PL/pgSQL can use only function that are callable from SQL - they have to
use Version1 calling convention
https://www.postgresql.org/docs/current/xfunc-c.html#id-1.8.3.13.7

GIN support functions use classic C functions, that means they can be
called only from C and languages that support C calls. PL/pgSQL doesn't do
this.

PL/pgSQL is just glue for SQL - it can be used for implementation of some
customer business tasks, but it cannot be (almost) used for Postgres
extending.

Regards

Pavel


> Thanks!
> Phillip
>