Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [string-representation]

A string representation is a textual form of a non-textual value (e.g. a number).

A string representation is a textual form of a non-textual value (e.g. a number).

For example, in PostgreSQL, a timestamp field can be represented as text using the to_char() function:

to_char(<timestamp field name>, 'YYYY-MM-DD HH24:00');
9 questions
34
votes
4 answers

Unquoting JSON strings; print JSON strings without quotes

SELECT json_array_elements('["one", "two"]'::json) gives result | json_array_elements | | :------------------ | | "one" | | "two" | I would like to have the same but without the quotes: one two Looks like I can't use…
Maxim Yefremov
  • 465
  • 1
  • 4
  • 7
29
votes
1 answer

What date/time literal formats are LANGUAGE and DATEFORMAT safe?

It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language: yyyyMMdd -- unseparated, date…
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
14
votes
1 answer

Why is to_char left padding with spaces?

When ever I use 099 indicating 3 digits 0-padded, I'm getting spaces on the left side. SELECT '>' || to_char(1, '099') || '<'; ?column? ---------- > 001< (1 row) Why is to_char left padding here? Why are there leading spaces?
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
10
votes
2 answers

Why isn't to_char IMMUTABLE, and how can I work around it?

How can I index a to_char() of a column? I have tried: adam_db=> CREATE INDEX updates_hourly_idx ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00')); But got the error: ERROR: functions in index expression must be marked…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
4
votes
1 answer

How is encoding done in SQL database tables for pattern matching?

Are strings in table columns represented as bit patterns or Unicode? Does the database engine bring them into memory in a different representation character set from the disk-persisted representations for optimal performance? I want to know for…
Yousef
  • 41
  • 2
2
votes
1 answer

Which databases provide string pooling?

Suppose I have many data objects that include very many share very many identical strings (e.g. tags, URLs, "DEBUG"/"TRACE"/"INFO", etc...). Which databases (if any) pool identical strings internally to prevent them from being copied many times?
user48956
  • 171
  • 1
  • 6
1
vote
1 answer

Missing "public" schema when converting a "regclass" value to text

I am writing a script in which I need to parse the name of a table (in regclass). The parsing (with parse_ident()) works so far. However, the script fails when the table is in the public schema because PostgreSQL (10.3) automatically removes the…
tinlyx
  • 3,810
  • 14
  • 50
  • 79
1
vote
1 answer

PostgreSQL function's input/output as JavaScript nested objects

I am looking for a simple/clean way that can be used consistently to automatically input JavaScript nested objects to user-defined PostgreSQL functions and also automatically output to JavaScript nested objects. Shall I used nested composite types?…
geeko
  • 41
  • 1
  • 13
0
votes
1 answer

How to get one complete column in mysql?

I have a script that will fetch Last_Error in mysql slave in a particular interval of time and write that to a file. To fetch Last_Error, currently I am using below command in the script. mysql -u root -pxxxxxxxxx -e "show slave status\G;" 2>&1 |…
Panda
  • 101