public inbox for [email protected]
help / color / mirror / Atom feedHow to surround a selected value with double quotes?
6+ messages / 3 participants
[nested] [flat]
* How to surround a selected value with double quotes?
@ 2026-06-22 07:46 Subramanian,Ramachandran <[email protected]>
2026-06-22 08:26 ` Re: How to surround a selected value with double quotes? Laurenz Albe <[email protected]>
2026-06-22 10:09 ` Re: How to surround a selected value with double quotes? hubert depesz lubaczewski <[email protected]>
2026-06-22 10:32 ` Re: How to surround a selected value with double quotes? hubert depesz lubaczewski <[email protected]>
0 siblings, 3 replies; 6+ messages in thread
From: Subramanian,Ramachandran @ 2026-06-22 07:46 UTC (permalink / raw)
To: [email protected] <[email protected]>
Hello,
I wrote a script to analyze and run vacuum on tables that need it.
Sadly at the time I wrote the script, I never expected a schema name to be pure numbers.
So my Script does not work now.
I want to add double quote marks to each schema name and table name select. I tried using the CONCAT function, but it does not work as I expected it to.
I would be grateful if someone can help me understand the mistake I am making with the CONCAT.
Regards
Ram
The table 7464128.locale_7464128 in database data listening on port 5432 with size 303104 must be vacuumed . It has a XID gap of 5616
Use the command psql -p 5432 -d data -c " vacuum freeze 7464128.locale_7464128"
ERROR: trailing junk after numeric literal at or near "7464128.locale_7464128"
LINE 1: vacuum freeze analyze 7464128.locale_7464128
^
data=# \dt *.locale_7464128
List of relations
Schema | Name | Type | Owner
---------+----------------+-------+----------
7464128 | locale_7464128 | table | postgres
(1 row)
data=# vacuum freeze analyze 7464128.locale_7464128 ;
ERROR: trailing junk after numeric literal at or near "7464128.locale_7464128"
LINE 1: vacuum freeze analyze 7464128.locale_7464128 ;
^
data=# vacuum freeze analyze "7464128"."locale_7464128" ;
VACUUM
data=# \q
#Build the SQL needed to get the list of tables that are approaching the SAFE_XID_GAP
LIST_OLDEST_UNFROZEN_XID_SQL=" SELECT \
cast (age(PGCL.relfrozenxid) as integer) as GAP, \
INTB.table_catalog as DB_NAME, \
"$PORT_NO" , \
CONCAT('"',INTB.table_schema,'"') || '.' || \
CONCAT('"',PGCL.relname,'"') as TABLE_NAME, \
pg_table_size(PGCL.oid) as TABLE_SIZE \
FROM \
pg_class PGCL, \
information_schema.tables INTB \
WHERE \
PGCL.relname=INTB.table_name \
AND \
PGCL.relkind='r' \
AND \
age(PGCL.relfrozenxid) > "$SAFE_XID_GAP" \
ORDER BY \
1 DESC ;"
Freundliche Grüße
i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie
Alte Leipziger Lebensversicherung a.G.
Hallesche Krankenversicherung a.G.
Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
Hallesche Krankenversicherung a.G., Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum
______________________
ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: [email protected]
www.alte-leipziger.de
www.hallesche.de
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to surround a selected value with double quotes?
2026-06-22 07:46 How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
@ 2026-06-22 08:26 ` Laurenz Albe <[email protected]>
2026-06-22 08:55 ` AW: How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2026-06-22 08:26 UTC (permalink / raw)
To: Subramanian,Ramachandran <[email protected]>; [email protected] <[email protected]>
On Mon, 2026-06-22 at 07:46 +0000, Subramanian,Ramachandran wrote:
> I wrote a script to analyze and run vacuum on tables that need it.
I assume you are talking about a bash script.
> Sadly at the time I wrote the script, I never expected a schema name to be pure numbers.
>
> So my Script does not work now.
>
> I want to add double quote marks to each schema name and table name select.
> I tried using the CONCAT function, but it does not work as I expected it to.
>
> I would be grateful if someone can help me understand the mistake I am making with the CONCAT.
You are a victim of (self-inflicted) SQL injection.
Just surrounding the schema name with double quotes is not enough.
What if the schema name itself contains a double quote?
You should use PostgreSQL's functions to properly quote an identifier.
With a shell script, I think your only choice is to use SQL:
#!/bin/bash
schema='12345 43'
table='tab"567'
quoted_schema=$(psql -Atq -v var="$schema" <<EOF
SELECT quote_ident(:'var');
EOF
)
quoted_table=$(psql -Atq -v var="$table" <<EOF
SELECT quote_ident(:'var');
EOF
)
sql="SELECT col FROM $quoted_schema.$quoted_table"
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* AW: How to surround a selected value with double quotes?
2026-06-22 07:46 How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
2026-06-22 08:26 ` Re: How to surround a selected value with double quotes? Laurenz Albe <[email protected]>
@ 2026-06-22 08:55 ` Subramanian,Ramachandran <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Subramanian,Ramachandran @ 2026-06-22 08:55 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; [email protected] <[email protected]>
Thank you so much!!! Yes I ment bash script.
QUOTE_IDENT() worked like black magic!!
Postgres puts quotes for the names that need them and no quotes for those that do not need them 😊 😊
LG
Ram
11657 | data | 5432 | "7464128"."rel_7464128_m2m_story_relatedProjects_project" | 8192
11655 | data | 5432 | "7464128"."rel_7464128_m2m_story_requiredStories_story" | 0
11527 | data | 5432 | catemplate_backup_version_250605_1756913256437."rel_1_m2m_companies_commercialContactPersons_persons" | 0
11527 | data | 5432 | catemplate_backup_version_250908_1760965502453."rel_1_m2m_companies_commercialContactPersons_persons" | 0
Freundliche Grüße
i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie
Alte Leipziger Lebensversicherung a.G.
Hallesche Krankenversicherung a.G.
Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
Hallesche Krankenversicherung a.G., Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum
______________________
ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: [email protected]
www.alte-leipziger.de
www.hallesche.de
-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <[email protected]>
Gesendet: Montag, 22. Juni 2026 10:26
An: Subramanian,Ramachandran IT-md-db <[email protected]>; [email protected]
Betreff: Re: How to surround a selected value with double quotes?
On Mon, 2026-06-22 at 07:46 +0000, Subramanian,Ramachandran wrote:
> I wrote a script to analyze and run vacuum on tables that need it.
I assume you are talking about a bash script.
> Sadly at the time I wrote the script, I never expected a schema name
> to be pure numbers.
>
> So my Script does not work now.
>
> I want to add double quote marks to each schema name and table name select.
> I tried using the CONCAT function, but it does not work as I expected it to.
>
> I would be grateful if someone can help me understand the mistake I am making with the CONCAT.
You are a victim of (self-inflicted) SQL injection.
Just surrounding the schema name with double quotes is not enough.
What if the schema name itself contains a double quote?
You should use PostgreSQL's functions to properly quote an identifier.
With a shell script, I think your only choice is to use SQL:
#!/bin/bash
schema='12345 43'
table='tab"567'
quoted_schema=$(psql -Atq -v var="$schema" <<EOF SELECT quote_ident(:'var'); EOF
)
quoted_table=$(psql -Atq -v var="$table" <<EOF SELECT quote_ident(:'var'); EOF
)
sql="SELECT col FROM $quoted_schema.$quoted_table"
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to surround a selected value with double quotes?
2026-06-22 07:46 How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
@ 2026-06-22 10:09 ` hubert depesz lubaczewski <[email protected]>
2026-06-22 10:59 ` AW: How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: hubert depesz lubaczewski @ 2026-06-22 10:09 UTC (permalink / raw)
To: Subramanian,Ramachandran <[email protected]>; +Cc: [email protected] <[email protected]>
On Mon, Jun 22, 2026 at 07:46:09AM +0000, Subramanian,Ramachandran wrote:
> #Build the SQL needed to get the list of tables that are approaching the SAFE_XID_GAP
> LIST_OLDEST_UNFROZEN_XID_SQL=" SELECT \
> cast (age(PGCL.relfrozenxid) as integer) as GAP, \
> INTB.table_catalog as DB_NAME, \
> "$PORT_NO" , \
> CONCAT('"',INTB.table_schema,'"') || '.' || \
> CONCAT('"',PGCL.relname,'"') as TABLE_NAME, \
> pg_table_size(PGCL.oid) as TABLE_SIZE \
*NEVER* concatenate identifiers. It will lead to bugs. Instead use
format() function (I find it MUCH simpler than quote_ident):
In your case, instead of the concat calls (why concat, if you can do ||,
anyway?
select
format('%I.%I', INTB.table_schema, PGCL.relname)
FROM
…
depesz
^ permalink raw reply [nested|flat] 6+ messages in thread
* AW: How to surround a selected value with double quotes?
2026-06-22 07:46 How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
2026-06-22 10:09 ` Re: How to surround a selected value with double quotes? hubert depesz lubaczewski <[email protected]>
@ 2026-06-22 10:59 ` Subramanian,Ramachandran <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Subramanian,Ramachandran @ 2026-06-22 10:59 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: [email protected] <[email protected]>
Hello,
thank you for your reply. I tried FORMAT(%I.%I,schema,tablename) and it works too. I have altered my script accordingly.
Now to the question why CONCAT and || , I am extremely new to Postgres and Linux .I am working on PG/Linux only since Last November. 😊 I come from 30 yrs of working on mainframes ( DB2 / IMS / REXX) and I tend to simply use what works in DB2 and fix only what does not work. The short answer is , lack of knowledge and experience.
Thank you so much for taking the time to reply to my question 😊
LG
Ram
Freundliche Grüße
i. A. Ramachandran Subramanian
Zentralbereich Informationstechnologie
Alte Leipziger Lebensversicherung a.G.
Hallesche Krankenversicherung a.G.
Alte Leipziger Lebensversicherung a.G., Alte Leipziger-Platz 1, 61440 Oberursel
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape, Wiltrud Pekarek, Udo Wilcsek
Sitz Oberursel (Taunus) · Rechtsform VVaG · Amtsgericht Bad Homburg v. d. H. HRB 1583 · USt.-IdNr. DE 114106814
Hallesche Krankenversicherung a.G., Löffelstraße 34-38, 70597 Stuttgart
Vors. des Aufsichtsrats: Dr. Walter Botermann · Vorstand: Christoph Bohn (Vors.), Dr. Jürgen Bierbaum (stv. Vors.), Frank Kettnaker, Dr. Jochen Kriegmeier, Alexander Mayer, Christian Pape,
Wiltrud Pekarek, Udo Wilcsek
Sitz Stuttgart · Rechtsform VVaG · Amtsgericht Stuttgart HRB 2686 · USt.-IdNr. DE 147802285
Beiträge zu privaten Kranken- und Pflegekrankenversicherungen unterliegen nicht der Versicherungsteuer (§ 4 Nr. 5 VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
Die Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG finden Sie hier: https://www.alte-leipziger.de/impressum
______________________
ALH Gruppe
Alte Leipziger-Platz 1, 61440 Oberursel
Tel.: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: [email protected]
www.alte-leipziger.de
www.hallesche.de
-----Ursprüngliche Nachricht-----
Von: [email protected] <[email protected]>
Gesendet: Montag, 22. Juni 2026 12:09
An: Subramanian,Ramachandran IT-md-db <[email protected]>
Cc: [email protected]
Betreff: Re: How to surround a selected value with double quotes?
On Mon, Jun 22, 2026 at 07:46:09AM +0000, Subramanian,Ramachandran wrote:
> #Build the SQL needed to get the list of tables that are approaching the SAFE_XID_GAP
> LIST_OLDEST_UNFROZEN_XID_SQL=" SELECT \
> cast (age(PGCL.relfrozenxid) as integer) as GAP, \
> INTB.table_catalog as DB_NAME, \
> "$PORT_NO" , \
> CONCAT('"',INTB.table_schema,'"') || '.' || \
> CONCAT('"',PGCL.relname,'"') as TABLE_NAME, \
> pg_table_size(PGCL.oid) as TABLE_SIZE \
*NEVER* concatenate identifiers. It will lead to bugs. Instead use
format() function (I find it MUCH simpler than quote_ident):
In your case, instead of the concat calls (why concat, if you can do ||, anyway?
select
format('%I.%I', INTB.table_schema, PGCL.relname) FROM …
depesz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to surround a selected value with double quotes?
2026-06-22 07:46 How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
@ 2026-06-22 10:32 ` hubert depesz lubaczewski <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2026-06-22 10:32 UTC (permalink / raw)
To: Subramanian,Ramachandran <[email protected]>; +Cc: [email protected] <[email protected]>
On Mon, Jun 22, 2026 at 07:46:09AM +0000, Subramanian,Ramachandran wrote:
> #Build the SQL needed to get the list of tables that are approaching the SAFE_XID_GAP
> LIST_OLDEST_UNFROZEN_XID_SQL=" SELECT \
> cast (age(PGCL.relfrozenxid) as integer) as GAP, \
> INTB.table_catalog as DB_NAME, \
> "$PORT_NO" , \
> CONCAT('"',INTB.table_schema,'"') || '.' || \
> CONCAT('"',PGCL.relname,'"') as TABLE_NAME, \
> pg_table_size(PGCL.oid) as TABLE_SIZE \
> FROM \
> pg_class PGCL, \
> information_schema.tables INTB \
> WHERE \
> PGCL.relname=INTB.table_name \
> AND \
> PGCL.relkind='r' \
> AND \
> age(PGCL.relfrozenxid) > "$SAFE_XID_GAP" \
> ORDER BY \
> 1 DESC ;"
Some more comments, this time related to how you write bash/shell scripts.
1. you don't need to end line with \ if the value is in quotes - this is
needed only if you split single command and it's options
2. if you have long string, multiline, it's better to use here-doc.
like:
psql … << _END_OF_SQL_
SELECT
cast (age(PGCL.relfrozenxid) as integer) as GAP,
INTB.table_catalog as DB_NAME,
"$PORT_NO" ,
CONCAT('"',INTB.table_schema,'"') || '.' ||
CONCAT('"',PGCL.relname,'"') as TABLE_NAME,
pg_table_size(PGCL.oid) as TABLE_SIZE
FROM
pg_class PGCL,
information_schema.tables INTB
WHERE
PGCL.relname=INTB.table_name
AND
PGCL.relkind='r'
AND
age(PGCL.relfrozenxid) > "$SAFE_XID_GAP"
ORDER BY
1 DESC
_END_OF_SQL_
3. if you really need to put the multi-line value in a variable, you can
do it using:
read -r -d '' LIST_OLDEST_UNFROZEN_XID_SQL << _END_OF_SQL_
…
_END_OF_SQL_
Best regards,
depesz
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-06-22 10:59 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-22 07:46 How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
2026-06-22 08:26 ` Laurenz Albe <[email protected]>
2026-06-22 08:55 ` AW: How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
2026-06-22 10:09 ` hubert depesz lubaczewski <[email protected]>
2026-06-22 10:59 ` AW: How to surround a selected value with double quotes? Subramanian,Ramachandran <[email protected]>
2026-06-22 10:32 ` hubert depesz lubaczewski <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox