Fwd: [Help] create a new db user but need to add prefix 'sys.' in order to execute timestamp_to_str() function
Hi, I have a new db user created by following scripts: CREATE USER "imdb" WITH PASSWORD 'imdb' NAME 'IMDB Explorer' SCHEMA "sys"; CREATE SCHEMA "imdb" AUTHORIZATION "imdb"; ALTER USER "imdb" SET SCHEMA "imdb";
When I tried to execute the function "timestamp_to_str", I have to add "sys." prefix to make it effective. "date_trunc" also needed to do so. The default user "monetdb" can run the function directly without the prefix. What should I do to perform the same way as user "monetdb" ?
sql>select timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); SELECT: no such binary operator 'timestamp_to_str(timestamp,char)' sql>select sys.timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); +---------------------------+ | L2 | +===========================+ | 2020-04-06 20:37:39 +0800 | +---------------------------+ 1 tuple
sql>select date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); SELECT: no such binary operator 'date_trunc(char,timestamp)' sql>select sys.date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); +----------------------------+ | L2 | +============================+ | 2119-02-17 02:08:00.000000 | +----------------------------+ 1 tuple
Hi Kevin,
The timestamp_to_str and date_trunc functions asre defined on the sys schema. You have to use the sys prefix to access them when the user's session is at another schema other than sys.
Best regards,
Pedro
On 4/6/20 3:53 PM, Kevin Chen wrote:
Hi, I have a new db user created by following scripts: CREATE USER "imdb" WITH PASSWORD 'imdb' NAME 'IMDB Explorer' SCHEMA "sys"; CREATE SCHEMA "imdb" AUTHORIZATION "imdb"; ALTER USER "imdb" SET SCHEMA "imdb";
When I tried to execute the function "timestamp_to_str", I have to add "sys." prefix to make it effective. "date_trunc" also needed to do so. The default user "monetdb" can run the function directly without the prefix. What should I do to perform the same way as user "monetdb" ?
sql>select timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); SELECT: no such binary operator 'timestamp_to_str(timestamp,char)' sql>select sys.timestamp_to_str(localtimestamp, '%Y-%m-%d %H:%M:%S %z'); +---------------------------+ | L2 | +===========================+ | 2020-04-06 20:37:39 +0800 | +---------------------------+ 1 tuple
sql>select date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); SELECT: no such binary operator 'date_trunc(char,timestamp)' sql>select sys.date_trunc('minute', timestamp '2119-02-17 02:08:12.345678'); +----------------------------+ | L2 | +============================+ | 2119-02-17 02:08:00.000000 | +----------------------------+ 1 tuple
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
ferreira -
Kevin Chen