How to surround a selected value with double quotes?
How to surround a selected value with double quotes?
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.
______________________ ALH Gruppe Alte Leipziger Lebensversicherung a. G., Alte Leipziger-Platz 1, 61440 Oberursel 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 (1) Nr. 5 b VersStG) · Versicherungsleistungen sowie Umsätze aus Versicherungsvertreter-/Maklertätigkeiten sind umsatzsteuerfrei
Alte Leipziger-Platz 1, 61440 Oberursel
Tel: +49 (6171) 66-4882
Fax: +49 (6171) 66-800-4882
E-Mail: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de
Pflichtangaben der ALH Gruppe gemäß § 35a GmbHG bzw. § 80 AktG
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" <
AW: How to surround a selected value with double quotes?
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: ramachandran.subramanian@alte-leipziger.de www.alte-leipziger.de www.hallesche.de -----Ursprüngliche Nachricht----- Von: Laurenz Albe Gesendet: Montag, 22. Juni 2026 10:26 An: Subramanian,Ramachandran IT-md-db ; pgsql-novice@lists.postgresql.org 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" <
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
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 \
> 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
AW: How to surround a selected value with double quotes?
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: ramachandran.subramanian@alte-leipziger.de
www.alte-leipziger.de
www.hallesche.de
-----Ursprüngliche Nachricht-----
Von: depesz@depesz.com
Gesendet: Montag, 22. Juni 2026 12:09
An: Subramanian,Ramachandran IT-md-db
Cc: pgsql-novice@lists.postgresql.org
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