Skip to content
TheChatArchiveThe Chat Archive
Qwenqwen-max-2025-01-25· Alibaba (Qwen)Hugging Face LMArena Human Preference 140K

write a SQL query to list all columns in table public.users

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with MiniMaxminimax-m1’s answer.
  1. User

    write a SQL query to list all columns in table public.users

  2. Qwen Assistant

    To list all columns in the public.users table, you can query the system catalog tables provided by your database. Below is an example for PostgreSQL, which uses the information_schema.columns view or the pg_attribute system catalog.

    Using information_schema.columns

    The information_schema.columns view provides metadata about all columns in the database in a standardized way. Here's the query:

    SELECT column_name, data_type, character_maximum_length, is_nullable
    FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'users'
    ORDER BY ordinal_position;
    

    Explanation:

    • column_name: The name of the column.
    • data_type: The data type of the column (e.g., integer, varchar, etc.).
    • character_maximum_length: The maximum length for string types (e.g., varchar(255)).
    • is_nullable: Indicates whether the column allows NULL values (YES or NO).
    • table_schema: Filters the schema to public.
    • table_name: Filters the table to users.
    • ordinal_position: Ensures the columns are listed in the order they appear in the table.

    Using pg_attribute (PostgreSQL-specific)

    If you prefer to use PostgreSQL's internal system catalogs, you can query the pg_attribute table along with pg_class and pg_namespace:

    SELECT a.attname AS column_name,
           pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
           CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END AS is_nullable
    FROM pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class c ON c.oid = a.attrelid
    JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public' AND c.relname = 'users' AND a.attnum > 0 AND NOT a.attisdropped
    ORDER BY a.attnum;
    

    Explanation:

    • pg_attribute: Contains information about table columns.
    • pg_class: Represents tables and other relations.
    • pg_namespace: Represents schemas.
    • a.attname: The name of the column.
    • format_type: Formats the column's data type (including dimensions like varchar(255)).
    • a.attnotnull: Indicates whether the column is NOT NULL.
    • n.nspname: Filters the schema to public.
    • c.relname: Filters the table to users.
    • a.attnum > 0: Excludes system columns.
    • NOT a.attisdropped: Excludes dropped columns.

    Output Example

    For a table public.users with the following structure:

    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    

    The output might look like this:

    column_namedata_typecharacter_maximum_lengthis_nullable
    idintegerNO
    usernamecharacter varying50NO
    emailcharacter varying100YES
    created_attimestampYES

    Notes:

    1. If you're using a different database (e.g., MySQL, SQL Server, Oracle), the system catalog queries will differ. Let me know if you need a solution for another database.
    2. Ensure you have sufficient privileges to query system catalogs or views.

    Let me know if you need further clarification!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.