CODEWITHSUNDEEP
mysqlsqldatabasepostgresqldatabase-design
Alfred Zhong
Alfred Zhong

Reputation: 7081

Is it possible to name column by index

I am wondering if it is possible to use SQL to create a table that name columns by index(number). Say, I would like to create a table with 10 million or so columns, I definitely don't want to name every column...

I know that I can write a script to generate a long string as SQL command. However, I would like to know if there is a more elegant way to so

Like something I make up here:

CREATE TABLE table_name
(
number_columns 10000000,
data_type INT
)

I guess saying 10 million columns caused a lot of confusion. Sorry about that. I looked up the manual of several major commercial DBMS and seems it is not possible. Thank you for pointing this out.

But another question, which is most important, does SQL support numerical naming of columns, say all the columns have the same type and there is 50 columns. And when referring it, just like

SELECT COL.INDEX(3), COL.INDEX(2) FROM MYTABLE

Does the language support that?

Upvotes: 0

Views: 135

Answers (5)

Enkode
Enkode

Reputation: 4793

This is an option for finding columns using ordinal values. It might not be the most elegant or efficient but it works. I am using it to create a new table for faster mappings between data that I need to parse through all the columns / rows.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnNames TABLE (colName varchar(64), colIndex int)
DECLARE @TableName varchar(64) = 'YOURTABLE' --Table Name
DECLARE @rowNumber int = 2 -- y axis
DECLARE @colNumber int = 24 -- x axis

DECLARE @myColumnToOrderBy varchar(64) = 'ID' --use primary key

--Store column names in a temp table
INSERT INTO @columnNames (colName, colIndex)
SELECT COL.name AS ColumnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM sys.tables AS TAB
    INNER JOIN sys.columns AS COL ON COL.object_id = TAB.object_id
    WHERE TAB.name = @TableName
    ORDER BY COL.column_id;

DECLARE @colName varchar(64)
SELECT @colName = colName FROM @columnNames WHERE colIndex = @colNumber

--Create Dynamic Query to retrieve the x,y coordinates from table
SET @sqlCommand = 'SELECT ' + @colName + ' FROM (SELECT ' + @colName + ', ROW_NUMBER() OVER (ORDER BY ' + @myColumnToOrderBy+ ') AS RowNum FROM ' + @tableName + ') t2 WHERE RowNum = ' + CAST(@rowNumber AS varchar(5))
EXEC(@sqlCommand)

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657777

You can easily do that in Postgres with dynamic SQL. Consider the demo:

DO LANGUAGE plpgsql
$$
BEGIN
    EXECUTE '
    CREATE TEMP TABLE t ('
    || (
        SELECT string_agg('col' || g || ' int', ', ')
        FROM generate_series(1, 10) g  -- or 1600?
        )
    || ')';
END;
$$;

But why would you even want to give life to such a monstrosity?

As @A.H. commented, there is a hard limit on the number of columns in PostgreSQL:

There is a limit on how many columns a table can contain. Depending on the column types, it is between 250 and 1600. However, defining a table with anywhere near this many columns is highly unusual and often a questionable design.

Emphasis mine. More about table limitations in the Postgres Wiki.

Access columns by index number

As to your additional question: with a schema like the above you can simply write:

SELECT col3, col2 FROM t;

I don't know of a built-in way to reference columns by index. You can use dynamic SQL again. Or, for a table that consists of integer columns exclusively, this will work, too:

SELECT c[3] AS col3, c[2] AS col2
FROM  (
    SELECT translate(t::text, '()', '{}')::int[] AS c -- transform row to ARRAY
    FROM   t
    ) x

Upvotes: 1

somnath
somnath

Reputation: 1335

Note: As mentioned by @GDP you can have only 4096 cols and definitely the idea is not appreciated and as again @GDP said that database design ideas need to be explored to consider if something else could be a better way to handle this requirement.

However, I was just wondering apart from the absurd requirement if ever I need to do this how can I do it? I thought why not create a custom / user defined MySQL function e.g. create_table() tht will receive the parameters you intend to send and which will in turn generate the required CREATE TABLE command.

Upvotes: 0

Matt Dodge
Matt Dodge

Reputation: 11142

Generally when working with databases your schema should be more or less "defined" so dynamic column adding isn't a built in functionality.

You can, however, run a loop and continually ALTER TABLE to add columns like so:

BEGIN
    SET @col_index = 0;
    start_loop: LOOP
        SET @col_index = @col_index + 1;
        IF @col_index <= num_columns THEN
            SET @alter_query = (SELECT CONCAT('ALTER TABLE table_name ADD COLUMN added_column_',@col_index,' VARCHAR(50)'));
            PREPARE stmt FROM @alter_query;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            ITERATE start_loop;
        END IF;
        LEAVE start_loop;
    END LOOP start_loop;
END;

But again, like most of the advice you have been given, if you think you need that many columns, you probably need to take a look at your database design, I have personally never heard of a case that would need that.

Upvotes: 0

GDP
GDP

Reputation: 8178

Couldn't resist looking into this, and found that the MySQL Docs say "no" to this, that

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table

Upvotes: 2

Related Questions