PostgreSQL - 如何获取列格式

PostgreSQL - 如何获取列格式？

sql
SELECT attname, atttypid, format_type(atttypid, atttypmod) AS data_type
FROM pg_attribute
WHERE attrelid = 'your_table_name'::regclass
AND attnum > 0

sql
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'your_table_name'
AND a.attnum > 0

sql
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
WHERE c.relname = 'your_table_name'
AND a.attnum > 0

python
import psycopg2
def get_column_formats(table_name):
    conn = psycopg2.connect(database="your_database", user="your_username", password="your_password", host="your_host", port="your_port")
    cur = conn.cursor()
    query = """
    SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
    FROM pg_attribute a
    JOIN pg_class c ON a.attrelid = c.oid
    WHERE c.relname = %s
    AND a.attnum > 0
    """
    cur.execute(query, (table_name,))
    columns = cur.fetchall()
    cur.close()
    conn.close()
    return columns
table_name = "your_table_name"
columns = get_column_formats(table_name)
for column in columns:
    print(f"Column Name: {column[0]}, Data Type: {column[1]}")