Как удалить все таблицы в postgresql

9

alt=»Блог Михаила Григорьева» />

19 Сен 2017 16:09:40 | 0 comments

Как удалить все таблицы из БД PostgreSQL и MySQL ?

Многие конечно не будут заморачиваться удалением таблиц и удалят и создадут заново базу, это удобно если у Вас только один пользователь имеет доступ к БД с простыми набором правами, но пользователей много, то удаление может быть не самой хорошей идеей.

Итак как удалить все таблицы из БД PostgreSQL и MySQL читаем ниже…

Удаление всех таблиц в БД для PostgreSQL:

1. Сохраняем список таблиц в файл:

2. Удаляем таблицы:

,где
PGUSER — имя пользователя
PGDBNAME — имя БД в которой нужно удалить все таблицы

Удаление всех таблиц в БД для MySQL:

,где в переменных
DB — база в которой нужно удалить все таблицы
USER и PASSWD — логин и пароль пользователя у которого есть полные права на базу из переменной DB в которой нужно удалить все таблицы.

Так же получить список таблиц в БД можно через INFORMATION_SCHEMA и тогда запрос удаления всех таблиц будет такой:

Если при удалении таблиц у нас появляется ошибка:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
то перед удалением нужно отключить проверку на Foreign Key командой: SET FOREIGN_KEY_CHECKS=0;
а потом включить: SET FOREIGN_KEY_CHECKS=1;
Таким образом полная команда удаления всех таблиц из БД будет такого вида:

На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.

Как удалить все таблицы в базе данных PostgreSQL?

Как я могу удалить все таблицы в PostgreSQL, работая из командной строки?

I не хотите удалить саму базу данных, только все таблицы и все данные в них.

задан 24 июля ’10, 20:07

О какой командной строке вы говорите? Насколько нам известно, вы ищете реализацию Windows PowerShell. — Greg Smith

Простите. Работая в Unix, после ввода «psql» в командной строке — так что сама среда командной строки psql. — AP257

DROP SCHEMA public CASCADE; — содрогаться — wildplasser

@ 0fnt вам нужно будет выполнить «CREATE SCHEMA public;» чтобы снова добавить новые таблицы (выяснил на собственном опыте) — nym

Кстати, когда вы падаете public , вы потеряете все установленные расширения. — sudo

27 ответы

Если все ваши таблицы находятся в одной схеме, этот подход может работать (приведенный ниже код предполагает, что имя вашей схемы public )

Если вы используете PostgreSQL 9.3 или выше, вам также может потребоваться восстановить гранты по умолчанию.

ответ дан 19 окт ’16, 10:10

Обратите внимание, что это также удалит все функции, представления и т. Д., Определенные в общедоступной схеме. — Брэд Кох

Обратите внимание, что это не приведет к удалению системных таблиц (например, тех, которые начинаются с pg_ ), поскольку они находятся в другой схеме, pg_catalog . — конгус

Это создаст схему с OWNER, установленную для пользователя, под именем которого вы вошли в psql. Это приведет к конфликту с приложениями, которые входят в систему как другой пользователь. В этом случае вам также необходимо запустить «ALTER SCHEMA public OWNER to postgres;» (или любому пользователю, которого ваше приложение использует для создания таблиц) — Mgojohn

Исходя из другого ответа, вы, вероятно, захотите GRANT ALL ON SCHEMA public TO public; после создания. — Федерико

@ Федерико Зачем тебе GRANT ALL после создания? — 425нес

Вы можете написать запрос для генерации сценария SQL следующим образом:

В случае, если некоторые таблицы автоматически удаляются из-за опции каскадирования в предыдущем предложении.

Кроме того, как указано в комментариях, вы можете отфильтровать таблицы, которые хотите удалить, по имени схемы:

А затем запустите его.

Великолепная КОПИЯ + ПАСТА тоже подойдет.

ответ дан 29 окт ’12, 17:10

Я думаю, вы имели в виду: вы можете написать такой запрос . . А затем запустить вывод запроса — Винко Врсалович

выберите ‘удалить таблицу, если существует «‘ || tablename || ‘» cascade;’ из pg_tables; обеспечит правильное удаление таблиц с прописными буквами. — Иво ван дер Вейк

Предложение «where schemaname = ‘public’», которое ЛенВ добавил в свой ответ, может быть очень полезным для сокращения объема удаления только до управляемой вами базы данных, а не до системной — Гийом Жандре

@jwg: также, потому что иногда у вас нет разрешения на drop schema public cascade; , но у вас почти всегда есть разрешения на удаление таблиц. — Berkes

Версия для закрытых схем: выберите ‘удалить таблицу, если существует «‘ || имя схемы || ‘». «‘ || имя таблицы || ‘» cascade;’ из pg_tables, где schemaname = ‘user_data’; — Ludwig

Наиболее распространенный ответ на момент написания этой статьи (январь 2014 г.):

Это работает, однако, если вы намереваетесь восстановить общедоступную схему в исходное состояние, это не решит задачу полностью. В pgAdmin III для PostgreSQL 9.3.1, если вы щелкните по «общедоступной» схеме, созданной таким образом, и посмотрите на «панель SQL», вы увидите следующее:

Однако, напротив, новая база данных будет иметь следующее:

Для меня использование веб-фреймворка python, который создает таблицы базы данных (web2py), использование первого вызывало проблемы:

Итак, на мой взгляд, полностью правильный ответ:

Также обратите внимание, что для выполнения этих команд в pgAdmin III я использовал инструмент запросов (значок увеличительного стекла «Выполнение произвольных SQL-запросов») или вы могли использовать плагины-> Консоль PSQL.

Внимание

Если у вас установлены какие-либо расширения, они будут удалены при удалении схемы, поэтому вы должны отметить, что вам нужно установить, а затем при необходимости выполнить инструкции. Например

CREATE EXTENSION postgis;

ответ дан 18 апр.

Подтвержденный. Двухстрочное решение ( drop становятся create ) раньше работал в PostgreSQL 9.1. После обновления до 9.3 два дополнительных grant является необходимым. — Цзинхао Ши

Еще одно подтверждение: используя Django, я получил ту же ошибку; Мне нужно было запустить эти гранты, прежде чем django сможет взаимодействовать с базой данных. — RJH

Это сработало отлично, за исключением того, что мне также пришлось переустановить некоторые расширения: CREATE EXTENSION IF NOT EXISTS hstore; СОЗДАТЬ РАСШИРЕНИЕ, ЕСЛИ НЕ СУЩЕСТВУЕТ pgcrypto; — шейкер

Вы можете удалить все таблицы с помощью

ИМО, это лучше, чем drop schema public , потому что вам не нужно воссоздавать schema и восстановить все гранты.

Дополнительный бонус в том, что для этого не требуется внешний язык сценариев или копирование сгенерированного SQL обратно в интерпретатор.

ответ дан 09 авг.

Спасибо, что разместили это! Я не мог использовать drop schema уловка, поскольку пользователь не был владельцем схемы, только таблиц. Но этот сработал 🙂 — вдбор

Очень чистое и конкретное . отличное решение, которое тоже должно быть принято — вы даже можете добавить в предложение where, чтобы ограничить таблицы, которые вы хотите сохранить, как в тех, которые необходимы для таких расширений, как PostGIS . — DPSпространственный

Я бы предложил изменить эту строку EXECUTE ‘DROP TABLE IF EXISTS ‘ || quote_ident(r.tablename) || ‘ CASCADE’; с этим: EXECUTE format(‘DROP TABLE IF EXISTS %I CASCADE’, quote_ident(r.tablename)); — Тайгер

@tyger Почему? Мне это кажется ненужным усложнением. Is есть ли возможность инъекции (и действительно ли это исправляет ее, если она есть)? [Я не знаю, достаточно ли глуп Постгрес, чтобы позволить имена таблиц сделать это возможным] Если есть, вам действительно следует изменить свой комментарий на редактирование в ответе (объясняя, почему в комментариях редактирования). — Прорицание

@Auspex Хех, когда я это делал, с первым вариантом были проблемы. Не могу вспомнить прямо сейчас . — Тайгер

Если все, что вы хотите бросить, это принадлежащих тем же пользователем, то вы можете использовать:

Это упадет многое что принадлежит пользователю.

Это включает в себя материализованные представления, представления, последовательности, триггеры, схемы, функции, типы, агрегаты, операторы, домены и так далее (так что на самом деле: многое) тот the_user владеет (= создано).

Вы должны заменить the_user с фактическим именем пользователя, в настоящее время нет возможности отбросить все для «текущего пользователя». В следующей версии 9.5 будет возможность drop owned by current_user .

Это отбросило все схемы, принадлежащие пользователю (чего я не хотел делать). — Питер Л

@PeterL: это четко задокументировано в руководстве, но я отредактировал свой пост, чтобы прояснить, что «все» на самом деле означает многое — a_horse_with_no_name

Я бы использовал drop, принадлежащий current_user; Таким образом, вам даже не нужно беспокоиться о том, чтобы ввести правильное имя пользователя. — JavaGeek

На самом деле очень хорошее решение для меня. Моя база данных и public схема принадлежит postgres , но все остальное принадлежит определенному пользователю, поэтому удаление всего, что принадлежит этому пользователю, очищает базу данных кроме для схемы. — Прорицание

лучший ответ, если вы создали конкретного пользователя для определенного приложения (набора вещей) в базе данных и хотите удалить именно это: +1: — Прокурор

Согласно приведенному выше Пабло, просто отказаться от конкретной схемы в отношении case:

Я использовал это, и у меня это сработало. Я предполагаю where schemaname=’public’ часть значима? — ибик

@ibic Если вы не укажете, что вы потенциально можете попробовать удалить все внутренние таблицы postgres, что, скорее всего, не то, что вам нужно. — водоворот

Более безопасным вариантом будет: select ‘drop table «‘ || tablename || ‘» cascade;’ from pg_tables where tableowner = ‘some_user’; — рукботто

должен сделать трюк.

ответ дан 28 окт ’12, 01:10

Обратите внимание, что это также удалит все функции, представления и т. Д., Определенные в общедоступной схеме. — Джо Ван Дайк

также вам придется снова воссоздать позже, чтобы снова добавить таблицы с помощью CREATE SCHEMA public; . Также см stackoverflow.com/a/14286370 для дополнительной информации — Mikermcneil

Это действительно интересный вопрос, и вы сможете решить его несколькими способами:

1. Удалив и воссоздав текущую схему

Здесь, в общем, имеем public схема по умолчанию. Итак, я использую это как экземпляр.

Если вы используете PostgreSQL 9.3 или выше, вам также может потребоваться восстановить гранты по умолчанию.

Плюсы:

Это очистит всю схему и воссоздает ее как новую.

Минусы:

Вы потеряете и другие сущности, такие как Functions , Views , Materialized views , И т.д.

2. Используя выборку всех имен таблиц из pg_tables таблице.

PostgreSQL хранит все таблицы в своей таблице записей с именем pg_table .

Как видите, с помощью подзапроса мы можем удалить все таблицы из схемы.

Плюсы:

Когда другие объекты данных важны, и вы просто хотите удалить из схемы только таблицы, этот подход будет вам действительно полезен.

3. Терминал

• Войдите в систему, используя пользователя postgres в вашей оболочке

• Подключите вашу базу данных

Вставьте эти команды:

Примечание. Этот набор команд аналогичен первому пункту, поэтому плюсы и минусы останутся прежними.

№2 хорошо сработал для меня. Мне пришлось удалить все «но это нормально — Андреа Жирарди

Вслед за Пабло и ЛенВ, вот однострочная программа, которая выполняет все операции и готовит, и выполняет:

psql -U $PGUSER $PGDB -t -c «select ‘drop table \»‘ || tablename || ‘\» cascade;’ from pg_tables where schemaname = ‘public'» | psql -U $PGUSER $PGDB

NB: либо установить, либо заменить $PGUSER и $PGDB с желаемыми ценностями

ответ дан 23 окт ’12, 16:10

Следующие шаги могут быть полезны (для пользователей Linux):

Сначала войдите в postgres командная строка с помощью следующей команды:

Войдите в базу данных с помощью этой команды (мое имя базы данных: maoss ):

Теперь введите команду для удаления всех таблиц:

ответ дан 24 мая ’20, 21:05

следовал инструкциям на моем ubuntu 19.04, он работал безупречно! — Алекс ММ

Если у вас есть процедурный язык PL / PGSQL установлен вы можете использовать следующее, чтобы удалить все без внешнего сценария оболочки / Perl.

Вместо того, чтобы вводить это в приглашении «psql», я бы посоветовал вам скопировать его в файл, а затем передать файл в качестве входных данных в psql, используя параметры «—file» или «-f»:

Кредит, за который следует отдать должное: я написал функцию, но думаю, что запросы (или, по крайней мере, первый) пришли от кого-то из одного из списков рассылки pgsql много лет назад. Не помню точно, когда и какой.

Создан 16 июля ’12, 04:07

Если вы все равно хотите уничтожить все таблицы, вы можете обойтись без таких тонкостей, как CASCADE, поместив все таблицы в один оператор. Это также ускоряет выполнение.

Выполнение его напрямую:

Замените TRUNCATE с DROP применимо.

когда не работает на public schema, не забудьте включить имя схемы в выражение: string_agg(quote_ident(schemaname) || ‘.’ || quote_ident(tablename), ‘, ‘) вместо того, чтобы просто передавать имена таблиц. — B12Тостер

Я немного изменил ответ Пабло, чтобы сгенерированные команды SQL возвращались в виде одной строки:

Создан 08 июля ’17, 00:07

Используйте этот скрипт в pgAdmin:

ответ дан 02 мар ’18, в 17:03

Этот sql не удался для меня. Я использовал SELECT concat (‘drop table’, tablename, ‘cascade;’) AS drop_table_sql FROM pg_tables WHERE schemaname = ‘public’ — Кейт Джон Хатчисон

Должно быть, я что-то сделал не так, Лука. Я просто попробовал еще раз, и это сработало. — Кейт Джон Хатчисон

На всякий случай . Простой скрипт Python, очищающий базу данных Postgresql

Убедитесь, что после копирования отступ правильный, поскольку Python полагается на него.

ответ дан 25 окт ’12, 10:10

работает очаровать. Я выбрал это, потому что мне понравилось жестко закодировать информацию о соединении с БД — последнее, что я хочу сделать, это попасть не в тот БД! и, кроме того, мой список таблиц — это движущаяся цель. — Дж. Л. Пейре

Вы можете использовать функцию string_agg для создания списка, разделенного запятыми, который идеально подходит для DROP TABLE. Из сценария bash:

ответ дан 18 дек ’12, 21:12

должно быть #! / bin / sh — Хороший человек

Если вы хотите удалить данные (не удалить таблицу):

Или, если вам нужна таблица drop, вы можете использовать этот sql:

Примечание: мой ответ касается реального удаления таблиц и других объектов базы данных; для удаление всех данных in таблицы, т.е. усечение всех таблиц, Месяц спустя Endre Both представил аналогично хорошо выполненный (прямое исполнение) оператор.

В тех случаях, когда вы не можете просто DROP SCHEMA public CASCADE; , DROP OWNED BY current_user; или что-то в этом роде, вот автономный сценарий SQL, который я написал, который безопасен для транзакций (т.е. вы можете поместить его между BEGIN; и либо ROLLBACK; просто проверить это или COMMIT; чтобы на самом деле сделать дело) и очищает «все» объекты базы данных . ну, все те, которые используются в базе данных, которую использует наше приложение, или я мог бы разумно добавить, а именно:

• триггеры на столах
• ограничения на таблицы (FK, PK, CHECK , UNIQUE )
• индикаторы
• VIEW s (нормальный или материализованный)
• таблицы
• последовательности
• подпрограммы (агрегатные функции, функции, процедуры)
• все nōn-default (т.е. не public или внутренняя база данных) схемы, принадлежащие «нам»: сценарий полезен, если он выполняется «не суперпользователем базы данных»; суперпользователь может сбросить все схемы (хотя действительно важные из них по-прежнему явно исключены)
• расширения (добавленные пользователем, но я обычно намеренно оставляю их внутри)

Не отбрасываются (некоторые намеренно; некоторые только потому, что у меня не было примера в нашей БД):

• что собой представляет public схема (например, для материалов, предоставляемых расширением)
• сопоставления и другие локали
• триггеры событий
• материал для текстового поиска,… (см. здесь для других вещей, которые я мог пропустить)
• роли или другие параметры безопасности
• составные типы
• тосты
• FDW и зарубежные столы

Это на самом деле полезно в тех случаях, когда дамп, который вы хотите восстановить, имеет другую версию схемы базы данных (например, с Debian dbconfig-common , Flyway или Liquibase / DB-Manul), чем база данных, в которую вы хотите его восстановить.

У меня также есть версия, которая удаляет «все, кроме двух таблиц и того, что им принадлежит» (последовательность, проверенная вручную, извините, я знаю, скучная) на случай, если кому-то это интересно; разница мала. Свяжитесь со мной или проверьте это репо если интересует.

Протестировано, кроме более поздних дополнений ( extensions предоставлено Клеман Прево), в PostgreSQL 9.6 ( jessie-backports ). Удаление агрегатов проверено на 9.6 и 12.2, процедура удаления проверена на 12.2. Исправления и дальнейшие улучшения приветствуются!

Как удалить все таблицы в postgresql

DROP TABLE removes tables from the database. Only the table owner, the schema owner, and superuser can drop a table. To empty a table of rows without destroying the table, use DELETE or TRUNCATE .

DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. ( CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)

Parameters

Do not throw an error if the table does not exist. A notice is issued in this case.

The name (optionally schema-qualified) of the table to drop.

Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects (see Section 5.14).

Refuse to drop the table if any objects depend on it. This is the default.

Examples

To destroy two tables, films and distributors :

Compatibility

This command conforms to the SQL standard, except that the standard only allows one table to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension.

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

How to create and delete databases and tables in PostgreSQL

PostgreSQL and other relational database management systems use databases and tables to structure and organize their data. We can review the definition of those two terms quickly:

• databases: separate different sets of structures and data from one another
• tables: define the data structure and store the actual data values within databases

In PostgreSQL, there is also an intermediary object between databases and tables called schema:

• schema: a namespace within a database that contains tables, indexes, views, and other items.

This guide won't deal directly with PostgreSQL's concept of a schema, but it's good to know it's there.

Instead, we'll be focusing on how to create and destroy PostgreSQL databases and tables. The examples will primarily use SQL, but towards the end, we'll show you how to do a few of these tasks using the command line. These alternatives use tools included in the standard PostgreSQL installation that are available if you have administrative access to the PostgreSQL host.

Some of the statements covered in this guide, particularly the PostgreSQL CREATE TABLE statement, have many additional options that were outside of the scope of this article. If you'd like additional information, find out more by checking out the official PostgreSQL documentation.

To follow along with this guide, you will need to log in to a PostgreSQL instance with a user with administrative privileges using the psql command line client. Your PostgreSQL instance can be installed locally, remotely, or provisioned by a provider.

Specifically, your PostgreSQL user will need the CREATE DB privilege or be a Superuser , which you can check with the \du meta-command in psql :

The postgres superuser, which is created automatically upon installation, has the required privileges, but you can use any user with the Create DB privilege.

Create a new database

Once you are connected to your PostgreSQL instance using psql or any other SQL client, you can create a database using SQL.

The basic syntax for creating a database is:

This will create a database called db_name on the current server with the current user set as the new database's owner using the default database settings. You can view the properties of the default template1 template using the following psql meta-command:

You can add additional parameters to alter the way your database is created. These are some common options:

• ENCODING: sets the character encoding for the database.
• LC_COLLATE: sets the collation, or sort, order for the database. This is a localization option that determines how items are organized when they are ordered.
• LC_CTYPE: sets the character classification for the new database. This is a localization option that affects what characters are considered uppercase, lowercase, and digits.

These can help ensure that the database can store data in the formats you plan to support and with your project's localization preferences.

For example, to ensure that your database is created with Unicode support and to override the server's own locale to use American English localization (these all happen to match the values in the template1 shown above, so no change will actually occur), you could type:

To follow along with the examples in this guide, create a database called school using your instance's default locale settings and the UTF8 character encoding:

This will create your new database using the specifications you provided.

List existing databases

To determine what databases are currently available on your server or cluster, you can use the following SQL statement:

This will list each of the databases currently defined within the environment:

As mentioned before, if you are connected using the psql client, you can also get this information \l meta-command:

This will show the available database names along with their owners, encoding, locale settings, and privileges:

The school database that we created is displayed among the other databases on the system. This is a good way to get an overview of the databases within your server or cluster.

Create tables within databases

After creating one or more databases, you can begin to define tables to store your data. Tables consist of a name and a defined schema which determines the fields and data types that each record must contain.

PostgreSQL CREATE TABLE syntax

You can create tables using the CREATE TABLE statement. A simplified basic syntax for the command looks like the following:

The components of the above syntax include the following:

• CREATE TABLE table_name : The basic creation statement that signals that you wish to define a table. The table_name placeholder should be replaced with the name of the table you wish to use.
• column_name TYPE : Defines a basic column within the table. The column_name placeholder should be replaced with the name you wish to use for your column. The TYPE specifies the PostgreSQL data type for the column. Data stored within the table must conform to the column structure and column data types to be accepted.
• column_constraint : Column constraints are optional restraints to add further restrictions on the data that can be stored in the column. For example, you can require that entries be not null, unique, or positive integers.
• table_constraints : Table constraints are similar to column constraints but involve the interaction of multiple columns. For instance, you could have a table constraint that checks that a DATE_OF_BIRTH is before DATE_OF_DEATH in a table.

Create tables conditionally with the IF NOT EXISTS clause

By default, if you attempt to create a table in PostgreSQL that already exists within the database, an error will occur. To work around this problem in cases where you want to create a table if it isn't present, but just continue on if it already exists, you can use the IF NOT EXISTS clause. The IF NOT EXISTS optional qualifier that tells PostgreSQL to ignore the statement if the database already exists.

To use the IF NOT EXISTS clause, insert it into the command after the CREATE TABLE syntax and before the table name:

This variant will attempt to create the table. If a table with that name already exists within the specified database, PostgreSQL will throw a warning indicating that the table name was already taken instead of failing with an error.

How to create tables in PostgreSQL

The above syntax is enough to create basic tables. As an example, we'll create two tables within our school database. One table will be called supplies and the other will be called teachers :

In the supplies table, we want to have the following fields:

• ID: A unique ID for each type of school supply.
• Name: The name of a specific school item.
• Description: A short description of the item.
• Manufacturer: The name of the item manufacturer.
• Color: The color of the item.
• Inventory: The number of items we have for a certain type of school supply. This should never be less than 0.

We can create the supplies table with the above qualities using the following SQL.

First, change to the school database you created with psql by typing:

This will change the database that our future commands will target. Your prompt should change to reflect the database.

Next, create the supplies table with the following statement:

This will create the supplies table within the school database. The PRIMARY KEY column constraint is a special constraint used to indicate columns that can uniquely identify records within the table. As such, the constraint specifies that the column cannot be null and must be unique. PostgreSQL creates indexes for primary key columns to increase querying speed.

Verify that the new table is present by typing:

Verify that the schema reflects the intended design by typing:

We can see each of the columns and data types that we specified. The column constraint that we defined for the inventory column is listed towards the end.

Next, we will create a teachers table. In this table, the following columns should be present:

• Employee ID: A unique employee identification number.
• First name: The teacher's first name.
• Last name: The teacher's last name.
• Subject: The subject that the teacher is hired to teach.
• Grade level: The grade level of students that the teach is hired to teach.

Create the teachers table with the above schema with the following SQL:

How to create tables with primary keys and foreign keys

You can find information about creating tables with primary and foreign keys in some of our other PostgreSQL guides. Primary keys and foreign keys are both types of database constraint within PostgreSQL.

A primary key is a special column or column that is guaranteed to be unique across rows within the same table. All primary keys can be used to uniquely identify a specific row. Primary keys not only ensure that each row has a unique value for the primary key columns, they also ensure that no rows contain NULL values for that column. Often, the primary key in PostgreSQL uses the following format to specify an automatically assigned incrementing primary key: id SERIAL PRIMARY KEY .

Foreign keys are a way to ensure that a column or columns in one table match the values contained within another table. This helps ensure referential integrity between tables.

How to view tables in PostgreSQL

In PostgreSQL you can list tables in a few different ways depending on what information you are looking for.

If you'd like to see what tables are available within your database, you can use the \dt meta-command included with the psql client to list all tables, as we demonstrated above:

You can also check that the schema for the table matches your specifications:

The teachers table seems to match our definition.

If you need to change the schema of an existing table in PostgreSQL, you can use the ALTER TABLE command. The ALTER TABLE command is very similar to the CREATE TABLE command, but operates on an existing table.

Alter table syntax

The basic syntax for modifying tables in PostgreSQL looks like this:

The <change_command> indicates the exact type of change you would like to make, whether it involves setting different options on the table, adding or removing columns, or changing types or constraints. The <change_parameters> part of the command contains any additional information that PostgreSQL needs to complete the change.

Adding columns to tables

You can add a column to a PostgreSQL table with the ADD COLUMN change command. The change parameters will include the column name, type, and options, just as you would specify them in the CREATE TABLE command.

For example, to add a column called missing_column of the text type to a table called some_table , you would type:

Removing columns from tables

If, instead, you'd like to remove an existing column, you can use the DROP COLUMN command instead. You need to specify the name of the column you wish to drop as a change parameter:

Changing the data type of a column

To change the data type that PostgreSQL uses for a specific column, you can use ALTER COLUMN change command with the SET DATA TYPE column command. The parameters include the column name, its new type, and an optional USING clause to specify how the old type should be converted to the new type.

For example, to set the value of a id column in the resident table to a int using an explicit cast, we can type the following:

Other table changes

Many other types of changes can be achieved with the ALTER TABLE command. For more information about the options available, check out the official PostgreSQL documentation for ALTER TABLE .

If you wish to delete a table, you can use the DROP TABLE SQL statement. This will delete the table as well as any data stored within it.

The basic syntax looks like this:

This will delete the table if it exists and throw an error if the table name does not exist.

If you wish to delete the table if it exists and do nothing if it does not exist, you can include the IF EXISTS qualifier within the statement:

Tables that have dependencies on other tables or objects cannot be deleted by default while those dependencies exist. To avoid the error, you can optionally include the CASCADE parameter, which automatically drops any dependencies along with the table:

If any tables have a foreign key constraint, which references the table that you are deleting, that constraint will automatically be deleted.

Delete the supplies table we created earlier by typing:

We will keep the teachers database to demonstrate that the statement to delete databases also removes all child objects like tables.

The DROP DATABASE statement tells PostgreSQL to delete the specified database. The basic syntax looks like this:

Replace the database_name placeholder with the name of the database you wish to remove. This will delete the database if it is found. If the database cannot be found, an error will occur:

If you wish to delete the database if it exists and otherwise do nothing, include the optional IF EXISTS option:

This will remove the database or do nothing if it cannot be found.

To remove the school database that we used in this guide, list the existing databases on your system:

Open a new connection to one of the databases you do not wish to delete:

Once the new connection is open, delete the school database with the following command:

This will remove the school database along with the teachers table defined within.

If you have been following along using SQL, you can end here or skip to the conclusion. If you'd like to learn about how to create and delete databases from the command line, continue on to the next section.

Using administrative command line tools to create and delete databases

If you have shell access to the server or cluster where PostgreSQL is installed, you may have access to some additional command line tools that can help create and delete databases. The createdb and dropdb commands are bundled with PostgreSQL when it is installed.

Create a new database from the command line

The basic syntax for the createdb command (which should be run by a system user with admin access to PostgreSQL) is:

This will create a database called db_name within PostgreSQL using the default settings.

The command also accepts options to alter its behavior, much like the SQL variant you saw earlier. You can find out more about these options with man createdb . Some of the most important options are:

• —encoding= : sets the character encoding for the database.
• —locale= : sets the locale for the database.

These can help ensure that the database can store data in the formats you plan to support and with your project's localization preferences.

For example, to ensure that your database is created with Unicode support and to override the server's own locale to use American English localization, you could type:

Assuming you have the correct permissions, the database will be created according to your specifications.

To follow along with the examples in this guide, you could create a database called school using the default locale and the UTF8 character encoding by typing:

You could then connect to the database using psql to set up your tables as usual.

Drop databases from the command line

The dropdb command mirrors the DROP DATABASE SQL statement. It has the following basic syntax:

Change the database_name placeholder to reference the database you wish to delete.

By default, this command will result in an error if the database specified cannot be found. To avoid this, you can include the optional —if-exists flag:

This will delete the specified database if it exists. Otherwise, it will do nothing.

To delete the school database we created earlier, type:

This will remove the database and any child elements, like tables, within.

This article covered the basics of how to create and delete databases and tables within PostgreSQL. These are some of the most basic commands required to set up a database system and being defining the structure of your data.

As mentioned earlier, the SQL statements covered in this PostgreSQL tutorial, particularly the CREATE TABLE statement, have many additional parameters can be used to change PostgreSQL's behavior. You can find out more about these by checking out the official PostgreSQL documentation.

When using Prisma to develop with PostgreSQL, you will usually create databases and tables with Prisma Migrate. You can learn how use it in our guide on developing with Prisma Migrate.

Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.

Does PostgreSQL support the `IF NOT EXISTS` clause when using the `CREATE DATABASE` command?

Yes, PostgreSQL supports the use of IF NOT EXISTS when creating both databases and tables. The below demonstrates using the clause for table creation.

How do you create a database from a dump in PostgreSQL?

To create a database from a dump (pg_dump), PostgreSQL provides the utility program pg_restore .

This program recreates the database in the same state as it was at the time of the dump. Example syntax would look like the following:

Which command line creates a database in PostgreSQL?

To create a database in PostgreSQL, use the createdb command. The syntax is as follows:

How do you drop a database in PostgreSQL?

The DROP DATABASE statement tells PostgreSQL to delete the specified database. The basic syntax looks like this:

How do you change a column's data type in PosgreSQL?

To change the data type for a specific column, use the ALTER COLUMN change command with the SET DATA TYPE column command.

The basic syntax includes column name, the new type, and an optional USING clause to specify the old type's conversion.