SQL-Ex blog
Запросы SQL для изменения типа данных столбца
Добавил Sergey Moiseenko on Суббота, 23 апреля. 2022
- SQL Server 2019
- MySQL Server
- PostgreSQL
SQL-запрос для изменения типа столбца в базе данных SQL Server
Мы можем использовать оператор ALTER TABLE ALTER COLUMN для изменения типа столбца в таблице. Использует следующий синтаксис:
ALTER TABLE [tbl_name] ALTER COLUMN [col_name] [DATA_TYPE]
- tbl_name: задает имя таблицы.
- col_name: задает имя столбца, тип которого мы хотим изменить. col_name должно быть указано после ключевых слов ALTER COLUMN.
- DATA_TYPE: задает новый тип данных и длину столбца.
CREATE TABLE [dbo].[tblstudent]
(
[id] [INT] IDENTITY(1, 1) NOT NULL,
[student_code] [VARCHAR](20) NOT NULL,
[student_firstname] [VARCHAR](250) NOT NULL,
[student_lastname] [VARCHAR](10) NOT NULL,
[address] [VARCHAR](max) NULL,
[city_code] [VARCHAR](20) NOT NULL,
[school_code] [VARCHAR](20) NULL,
[admissiondate] [DATETIME] NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ( [id] ASC )
)
Предположим, что вы хотите изменить тип данных [address] с varchar(max) на nvarchar(1500). Выполните следующий запрос для изменения типа столбца.
Alter table tblstudent alter column address nvarchar(1500)
Проверим изменения с помощью следующего скрипта.
use StudentDB
go
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS
where table_name='tblStudent'
Видно, что тип данных столбца изменился.
- При уменьшении размера столбца SQL Server проверит данные в таблице и, если данные превышают новую длину, вернет предупреждение и прервет выполнение оператора.
- При изменении типа данных nvarchar на varchar, если столбец содержит строку Юникод, то SQL Server возвращает ошибку и прерывает оператор.
- В отличие от MySQL изменение типа данных нескольких столбцов не допускается.
- Вы не можете добавить
а. ограничение NOT NULL, если столбец содержит NULL-значения;
б. ограничение UNIQUE, если в столбце имеются дубликаты.
Запрос SQL для изменения типа столбца в MySQL
Для изменения типа данных столбца мы можем использовать оператор ALTER TABLE MODIFY COLUMN. Синтаксис изменения типа данных столбца имеет следующий вид:
ALTER TABLE [tbl_name] MODIFY COLUMN [col_name_1] [DATA_TYPE],
MODIFY [col_name_2] [data_type],
MODIFY [col_name_3] [data_type]
- Tbl_name: задает имя таблицы, содержащая столбец, который мы хотим изменить.
- Col_name: задает имя столбца, тип которого мы хотим изменить. Col_name должно быть указано после ключевых слов MODIFY COLUMN. Мы можем изменить тип данных нескольких столбцов. При изменении типа данных нескольких столбцов, столбцы разделяются запятой (,).
- Datatype: задает новый тип данных и длину столбца. Тип данных должен указываться после имени столбца.
create table tblactor
(
actor_id int,
first_name varchar(500),
first_name varchar(500),
address varchar(500),
CityID int,
lastupdate datetime
)
Рассмотрим несколько примеров.
Пример 1: Запрос для изменения типа данных одного столбца
Мы хотим изменить тип столбца address с varchar(500) на тип данных TEXT. Выполните следующий запрос для изменения типа данных.
mysql> ALTER TABLE tblActor MODIFY address TEXT
Для проверки изменений выполните следующий запрос:
mysql> describe tblactor
Как можно увидеть, тип данных столбца address был изменен на TEXT.
Пример 2: SQL-запрос для изменения типа данных нескольких столбцов
Мы можем изменить тип данных нескольких столбцов в таблице. В нашем примере мы хотим изменить тип столбцов first_name и last_name. Новым типом данных столбцов становится TINYTEXT.
mysql> ALTER TABLE tblActor MODIFY first_name TINYTEXT, modify last_name TINYTEXT;
Выполните следующий запрос, чтобы проверить изменения:
mysql> describe tblActor
Как видно, тип данных столбцов first_name и last_name изменился на TINYTEXT.
Пример 3: Переименование столбца в MySQL
Чтобы переименовать столбцы, мы должны использовать оператор ALTER TABLE CHANGE COLUMN. Предположим, что вы хотите переименовать столбец CityID в CityCode; вы должны выполнить следующий запрос.
mysql> ALTER TABLE tblActor CHANGE COLUMN CityID CityCode int
Выполните команду describe, чтобы увидеть изменения структуры таблицы.
Видно, что имя столбца изменилось.
Запрос SQL для изменения типа столбца в базе данных PostgreSQL
Мы можем использовать оператор ALTER TABLE ALTER COLUMN для изменения типа данных столбца. Синтаксис изменения типа данных столбца:
ALTER TABLE [tbl_name] ALTER COLUMN [col_name_1] TYPE [data_type],
ALTER COLUMN [col_name_2] TYPE [data_type],
ALTER COLUMN [col_name_3] TYPE [data_type]
- Tbl_name: задает имя таблицы, содержащая столбец, который вы хотите изменить.
- Col_name: задает имя столбца, тип которого мы хотим изменить. Col_name должно быть указано после ключевых слов ALTER COLUMN. Мы можем изменить тип данных нескольких столбцов.
- Data_type: задает новый тип данных и длину столбца. Тип данных должен быть указан после ключевого слова TYPE.
create table tblmovies
(
movie_id int,
Movie_Title varchar(500),
Movie_director TEXT,
Movie_Producer TEXT,
duraion int,
Certificate varchar(5),
rent numeric(10,2)
)
Теперь рассмотрим несколько примеров.
Пример 1: Запрос SQL для изменения типа данных одного столбца
Мы хотим изменить тип столбца movie_id с типа данных int4 на int8. Для изменения типа данных выполните следующий запрос.
ALTER TABLE tblmovies ALTER COLUMN movie_id TYPE BIGINT
Для проверки изменений выполните следующий запрос:
SELECT
table_catalog,
table_name,
column_name,
udt_name,
character_maximum_length
FROM
information_schema.columns
WHERE
table_name = 'tblmovies';
Как видно, тип данных столбца movie_id стал int8.
Пример 2: Запрос SQL для изменения типа данных нескольких столбцов
Мы можем изменить тип данных сразу нескольких столбцов таблицы. В нашем примере мы хотим изменить тип столбцов movie_title и movie_producer. Новым типом данных для столбца movie_title становится TEXT, а для movie_producer — varchar(2000).
ALTER TABLE tblmovies ALTER COLUMN movie_title TYPE text, ALTER COLUMN movie_producer TYPE varchar(2000);
Выполните следующий запрос для проверки изменений:
SELECT
table_catalog,
table_name,
column_name,
udt_name,
character_maximum_length
FROM
information_schema.columns
WHERE
table_name = 'tblmovies';
Как видно, типом данных столбца movie_title является TEXT, а movie_producer — varchar(2000).
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Как узнать тип данных в столбце sql
Приведу запрос с помощью которого можно имена полей и тип данных в таблице products:
SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = products Получить названия типов данных у таблицы в Postgresql на python
из которой можно получить только OID типа данных колонки. Собственно вопрос: Как получить именно название типа (integer, varchar итд)? Может есть какая-то функция, которая возвращает название по OID? В документации смотрел, но ничего такого не нашел.
Отслеживать
задан 29 мая 2016 в 13:21
Артем Межеловский Артем Межеловский
153 1 1 серебряный знак 5 5 бронзовых знаков
Мне вспоминается только \d [имя_таблицы] . Была ещё какая-то конструкция с SHOW CREATE TABLE но в MySQL (см. serverfault.com/questions/231952/…).
29 мая 2016 в 13:38
@DimXenon это фишка psql, из адаптера для работы с БД это вряд ли сработает.
– user181100
29 мая 2016 в 13:48
По теме: поищите таблицу pg_types . Скорее всего, ваш ответ там.
– user181100
29 мая 2016 в 13:48
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
import psycopg2 if __name__ == '__main__': DSN = 'host=HOST port=PORT dbname=DBNAME user=USER password=PASS' with psycopg2.connect(DSN) as connection: with connection.cursor() as cursor: cursor.execute("SELECT * FROM TABLE_NAME LIMIT 0") for i in cursor.description: cursor.execute("SELECT typname FROM pg_type WHERE oid=".format(oid=i[1])) print("Column name: ", i[0], " Column type: ", cursor.fetchone()[0]) Как было указано в комментариях выше, искать надо в таблице pg_type. pg_type — служебная таблица в схеме pg_catalog. https://www.postgresql.org/docs/current/static/catalogs.html В ней хранятся информация обо всех типах данных. Эта таблица может быть полезна, если нужно создать свой собственный тип данных в psycopg2. Подробности: http://initd.org/psycopg/docs/advanced.html#type-casting-of-sql-types-into-python-objects
Также название типа данных можно получить по-другому (опущу мешанину соединений и курсоров):
SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = TABLE_NAME Читабельное название типа находится в колонке data_type. https://www.postgresql.org/docs/9.1/static/infoschema-columns.html У второго решения есть плюс: тип данных хранится как varchar, тогда как в pg_type тип данных — name. Цитата из документации:
The name type exists only for the storage of identifiers in the internal system catalogs and is not intended for use by the general user.
Как получить список и описание всех колонок в таблице Microsoft SQL Server?
В данной заметке будет рассмотрено несколько способов получения информации о столбцах таблицы в базе данных Microsoft SQL Server, например, мы научимся получать список колонок таблицы, включая их тип данных, с помощью SQL запроса.
Начну с того, что если Вам нужно просто визуально посмотреть, какие колонки или какой тип данных у той или иной колонке в таблице, то Вы для этого можете использовать графический функционал SQL Server Management Studio, а именно «Обозреватель объектов». Например, для того чтобы посмотреть информацию о столбцах таблицы, необходимо плюсиком открыть соответствующий контейнер.
Но если Вам необходимо выгрузить эту информацию или обработать ее в SQL инструкции, то в этом случае необходимо обращаться к системным объектам SQL Server с помощью языка SQL, как и к каким именно объектам обращаться мы сейчас и рассмотрим.
Примечание! Все примеры ниже мы будем рассматривать в Microsoft SQL Server 2016 Express. В базе данных создана тестовая таблица TestTable, она имеет всего три столбца.
Получаем список колонок таблицы с помощью представления информационной схемы
В Microsoft SQL Server существует специальная схема — INFORMATION_SCHEMA, которая содержит метаданные для всех объектов базы данных. В данной схеме есть представление COLUMNS, с помощью которого и можно получить информацию о колонках таблицы. Также в ней есть и другие полезные представления, о которых мы разговаривали в статье — «Представления информационной схемы Microsoft SQL Server».
А теперь допустим, нам нужно получить информацию о столбцах в таблице, например, имя столбца, тип данных и возможность принятия значения NULL, для этого мы напишем следующий запрос, в котором обратимся к представлению COLUMNS информационной схемы.
SELECT TABLE_NAME AS [Имя таблицы], COLUMN_NAME AS [Имя столбца], DATA_TYPE AS [Тип данных столбца], IS_NULLABLE AS [Значения NULL] FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='TestTable'
Получаем список столбцов таблицы с помощью системного представления sys.columns
Также информацию о колонках таблицы можно получить с помощью системного представления sys.columns, но только в этом случае для получения точно такого же результата, как был выше, необходимо будет объединять несколько системных представлений, а именно sys.tables, sys.columns и sys.types, так как в представлении sys.columns есть только идентификаторы нужных нам данных.
SELECT T.name AS [Имя таблицы], C.name AS [Имя столбца], DataType.name AS [Тип данных столбца], CASE WHEN C.is_nullable = 0 THEN 'NO' ELSE 'YES' END AS [Значения NULL] FROM sys.tables T LEFT JOIN sys.columns C ON T.object_id = C.object_id LEFT JOIN sys.types DataType ON C.user_type_id = DataType.user_type_id WHERE T.name='TestTable'
Получаем список колонок таблицы с помощью системной процедуры sp_columns
В SQL Server существует специальная системная процедура sp_columns, которая как раз и предназначена для получения информации о колонках таблицы.
EXEC sp_columns TestTable
Какой из рассмотренных выше способов Вам подойдет и окажется удобней решать Вам, а у меня на этом все, удачи!
Заметка! Новичкам рекомендую посмотреть мой видеокурс по T-SQL для начинающих, с помощью него Вы «с нуля» научитесь работать с SQL и программировать на T-SQL.