问题

I would like to set the precision in a timestamptz(p) where p is a variable instead of a constant.

Like this:

DO $$
DECLARE precision int = 3;
BEGIN SELECT now()::timestamptz(precision);
END; $$;
ERROR:  invalid input syntax for type integer: "precision"
LINE 1: SELECT now()::timestamptz(precision)
                      ^

The same error occurs when precision has a type of int2, int4, and int8 (and any other number type).

This error seams to be more helpful:

# SELECT now()::timestamptz(3::int);
ERROR:  type modifiers must be simple constants or identifiers
LINE 1: SELECT now()::timestamptz(3::int);
                      ^

Do you know of anyway to do this with a variable just short of conditionally executing the more hard-coded precision constant version?

I have several places I need to set the precision to 3 if the date is coming from an API (node's ISO date has less precision) and leave it at 6 when the dates are all from the database.

Just to show how it typically works:

postgres=# SELECT now()::timestamptz(6);
              now              
-------------------------------
 2023-05-20 18:29:23.915378+00
(1 row)

postgres=# SELECT now()::timestamptz(3);
            now             
----------------------------
 2023-05-20 18:29:27.263+00
(1 row)

DO $$
DECLARE precision int = 3;
BEGIN SELECT now()::timestamptz(precision);
END; $$;
ERROR:  invalid input syntax for type integer: "precision"
LINE 1: SELECT now()::timestamptz(precision)
                      ^

# SELECT now()::timestamptz(3::int);
ERROR:  type modifiers must be simple constants or identifiers
LINE 1: SELECT now()::timestamptz(3::int);
                      ^

postgres=# SELECT now()::timestamptz(6);
              now              
-------------------------------
 2023-05-20 18:29:23.915378+00
(1 row)

postgres=# SELECT now()::timestamptz(3);
            now             
----------------------------
 2023-05-20 18:29:27.263+00
(1 row)

答案1

得分: 2

根据您提供的内容，以下是翻译好的部分：

根据[动态SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)执行动态操作：

```sql
DO $$
DECLARE
    precision int = 3;
    time_val timestamptz;
BEGIN
    EXECUTE 'SELECT now()::timestamptz(' || precision || ')' into time_val;
    RAISE NOTICE '时间是：%', time_val;
END; $$;

NOTICE:  时间是：05/20/2023 15:25:59.548 PDT
DO

请注意，date 没有精度值，所以我假设您实际上在谈论日期时间戳：

select '2023-05-20 15:26:32.123456'::timestamptz(6);
          timestamptz           
--------------------------------
 05/20/2023 15:26:32.123456 PDT

select '2023-05-20 15:26:32.123'::timestamptz(6);
         timestamptz         
-----------------------------
 05/20/2023 15:26:32.123 PDT

select '2023-05-20 15:26:32.123'::timestamptz(3);
         timestamptz         
-----------------------------
 05/20/2023 15:26:32.123 PDT

我没有看到更改精度会改变任何内容的情况。

<details>
<summary>英文:</summary>

Go dynamic per [Dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN):

DO $$
DECLARE
precision int = 3;
time_val timestamptz;
BEGIN
EXECUTE 'SELECT now()::timestamptz(' || precision || ')' into time_val;
RAISE NOTICE 'Time is: %', time_val;
END; $$;

NOTICE: Time is: 05/20/2023 15:25:59.548 PDT
DO

FYI, a `date` does not have precision value, so assuming you are actually talking about a datetime/timestamp:

select '2023-05-20 15:26:32.123456'::timestamptz(6);
timestamptz

05/20/2023 15:26:32.123456 PDT

select '2023-05-20 15:26:32.123'::timestamptz(6);
timestamptz

05/20/2023 15:26:32.123 PDT

select '2023-05-20 15:26:32.123'::timestamptz(3);
timestamptz

05/20/2023 15:26:32.123 PDT

I am not seeing that changing the precision changes anything.

</details>