felipenazario
commented
3 weeks ago The code below is via python using psycopg2-binary (version 2.9.10) and the names are all right.
import psycopg2
from psycopg2 import sql
# Define the database connection parameters
# Change for the IP of the container
db_config = {
'dbname': 'form',
'user': 'postgres',
'password': '123',
'host': '172.17.0.2',
'port': '5432'
}
# Function to execute a query and fetch results
def query_database(query):
try:
# Establish a connection to the database
with psycopg2.connect(**db_config) as conn:
# Create a cursor object
with conn.cursor() as cursor:
# Execute the query
cursor.execute(sql.SQL(query))
# Fetch all results
results = cursor.fetchall()
# Return the results
return results
except psycopg2.Error as e:
print(f"An error occurred: {e}")
finally:
# The connection will be automatically closed after exiting the 'with' block
pass
# Sample usage
query = """
SELECT
CONCAT(
current_database(),
'.',
table_schema,
'.',
table_name
) as query
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY
1;
"""
results = query_database(query)
# Display results
for row in results:
print(row)The output.
('form.public.lime_old_survey_358156_20230628123112',)
('form.public.lime_old_survey_911213_20220916112346',)
('form.public.lime_survey_258123',)
('form.public.lime_survey_xpto',)
Andersson007
betanummeric
SUMMARY
I am querying all the table name of a database and I notice that some names are being displayed truncated, but when I query using the command psql or via python using psycopg2-binary (version 2.9.10) the names are all right.
ISSUE TYPE
COMPONENT NAME
community.postgresql.postgresql_query
ANSIBLE VERSION
COLLECTION VERSION
CONFIGURATION
OS / ENVIRONMENT
uname -a
STEPS TO REPRODUCE
Create the PostgreSQL using Docker for a quick and isolated test.
Connect to the container and then to the PostgreSQL.
Create the database and exit.
Connect to the form database.
Create the tables.
Query the form database.
EXPECTED RESULTS
The correct output will be the complete name of the tables.
ACTUAL RESULTS
Query the same SQL using Ansible.
The output of the table names will be truncated, when it should not be.