{"id":816,"date":"2024-12-11T09:56:37","date_gmt":"2024-12-11T02:56:37","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=816"},"modified":"2025-01-03T15:01:08","modified_gmt":"2025-01-03T08:01:08","slug":"postgresql-alter-table-add-column","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/","title":{"rendered":"PostgreSQL ALTER TABLE ADD COLUMN Statement"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you&#8217;ll learn how to use the PostgreSQL<code>ALTER TABLE ADD COLUMN<\/code> statement to add a new column to a table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='getting-started-with-the-postgresql-alter-table-add-column-statement'>Getting Started with the PostgreSQL ALTER TABLE ADD COLUMN statement <a href=\"#getting-started-with-the-postgresql-alter-table-add-column-statement\" class=\"anchor\" id=\"getting-started-with-the-postgresql-alter-table-add-column-statement\" title=\"Anchor for Getting Started with the PostgreSQL ALTER TABLE ADD COLUMN statement\">#<\/a><\/h2>\n\n\n\n<p>Due to new requirements, you may need to add one or more columns to a table. In PostgreSQL, you can use the <code>ALTER TABLE ... ADD COLUMN<\/code> statement to do that.<\/p>\n\n\n\n<p>Here&#8217;s the basic syntax of the statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> <span class=\"hljs-built_in\">table_name<\/span>\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> new_column data_type <span class=\"hljs-keyword\">constraint<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, provide the name of the table you want to add a column in the <code>ALTER TABLE<\/code> clause.<\/li>\n\n\n\n<li>Second, specify the new column name, data type, and constraint in the <code>ADD COLUMN<\/code> clause.<\/li>\n<\/ul>\n\n\n\n<p>The <code>ALTER TABLE ... ADD COLUMN<\/code> appends the new column at the end of the column list of the table.<\/p>\n\n\n\n<p class=\"note\">PostgreSQL does not allow you to insert a new column at a specified position in the column list like <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-add-column\/\" target=\"_blank\" rel=\"noreferrer noopener\">MySQL<\/a>. But there is a workaround that I will cover shortly.<\/p>\n\n\n\n<p>If you want to add multiple columns at the same time, you can use multiple <code>ADD COLUMN<\/code> clauses:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> <span class=\"hljs-built_in\">table_name<\/span>\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> new_column1 data_type <span class=\"hljs-keyword\">constraint<\/span>,\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> new_column2 data_type <span class=\"hljs-keyword\">constraint<\/span>,\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> new_column3 data_type <span class=\"hljs-keyword\">constraint<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='creating-a-sample-table'>Creating a sample table <a href=\"#creating-a-sample-table\" class=\"anchor\" id=\"creating-a-sample-table\" title=\"Anchor for Creating a sample table\">#<\/a><\/h2>\n\n\n\n<p>First, open your terminal and connect to the PostgreSQL server using <code>psql<\/code> tool:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">psql -U postgres -d inventory<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, create a new table called <code>vendors<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors (\n  id <span class=\"hljs-type\">INT<\/span> <span class=\"hljs-keyword\">GENERATED<\/span> <span class=\"hljs-keyword\">ALWAYS<\/span> <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-keyword\">IDENTITY<\/span> <span class=\"hljs-keyword\">PRIMARY KEY<\/span>,\n  <span class=\"hljs-type\">name<\/span> <span class=\"hljs-type\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, show the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">\\d vendors<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">                                Table <span class=\"hljs-string\">\"public.vendors\"<\/span>\n Column |          Type          | Collation | Nullable |           <span class=\"hljs-keyword\">Default<\/span>\n--------+------------------------+-----------+----------+------------------------------\n id     | integer                |           | not <span class=\"hljs-keyword\">null<\/span> | generated always <span class=\"hljs-keyword\">as<\/span> identity\n name   | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\nIndexes:\n    <span class=\"hljs-string\">\"vendors_pkey1\"<\/span> PRIMARY KEY, btree (id)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='adding-one-column-to-a-table'>Adding one column to a table <a href=\"#adding-one-column-to-a-table\" class=\"anchor\" id=\"adding-one-column-to-a-table\" title=\"Anchor for Adding one column to a table\">#<\/a><\/h2>\n\n\n\n<p>First, add a new column called <code>address<\/code> to the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> address <span class=\"hljs-type\">TEXT<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=QUxURVIgVEFCTEUgdmVuZG9ycyBBREQgQ09MVU1OIGFkZHJlc3MgVEVYVDs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Second, show the table structure using the <code>\\d<\/code> command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">\\d vendors<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">                                 Table <span class=\"hljs-string\">\"public.vendors\"<\/span>\n Column  |          Type          | Collation | Nullable |           <span class=\"hljs-keyword\">Default<\/span>\n---------+------------------------+-----------+----------+------------------------------\n id      | integer                |           | not <span class=\"hljs-keyword\">null<\/span> | generated always <span class=\"hljs-keyword\">as<\/span> identity\n name    | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\n address | text                   |           |          |\nIndexes:\n    <span class=\"hljs-string\">\"vendors_pkey1\"<\/span> PRIMARY KEY, btree (id)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output shows the <code>address<\/code> column at the end of the column list of the <code>vendors<\/code> table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='adding-multiple-columns-to-a-table'>Adding multiple columns to a table <a href=\"#adding-multiple-columns-to-a-table\" class=\"anchor\" id=\"adding-multiple-columns-to-a-table\" title=\"Anchor for Adding multiple columns to a table\">#<\/a><\/h2>\n\n\n\n<p>First, add two columns <code>email<\/code> and <code>phone<\/code> to the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> email <span class=\"hljs-type\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>,\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> phone <span class=\"hljs-type\">VARCHAR<\/span>(<span class=\"hljs-number\">25<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=QUxURVIgVEFCTEUgdmVuZG9ycyBBREQgQ09MVU1OIGVtYWlsIFZBUkNIQVIoMjU1KSBOT1QgTlVMTCwgQUREIENPTFVNTiBwaG9uZSBWQVJDSEFSKDI1KSBOT1QgTlVMTDs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Second, display the structure of the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">\\d vendors<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">                                 Table <span class=\"hljs-string\">\"public.vendors\"<\/span>\n Column  |          Type          | Collation | Nullable |           <span class=\"hljs-keyword\">Default<\/span>\n---------+------------------------+-----------+----------+------------------------------\n id      | integer                |           | not <span class=\"hljs-keyword\">null<\/span> | generated always <span class=\"hljs-keyword\">as<\/span> identity\n name    | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\n address | text                   |           |          |\n email   | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\n phone   | character varying(<span class=\"hljs-number\">25<\/span>)  |           | not <span class=\"hljs-keyword\">null<\/span> |\nIndexes:\n    <span class=\"hljs-string\">\"vendors_pkey1\"<\/span> PRIMARY KEY, btree (id)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output shows the new <code>email<\/code> and <code>phone<\/code> columns at the end of the column list.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='adding-a-new-column-to-a-table-with-data'>Adding a new column to a table with data <a href=\"#adding-a-new-column-to-a-table-with-data\" class=\"anchor\" id=\"adding-a-new-column-to-a-table-with-data\" title=\"Anchor for Adding a new column to a table with data\">#<\/a><\/h2>\n\n\n\n<p>First, insert three rows into the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span>\n  vendors (<span class=\"hljs-type\">name<\/span>, address, email, phone)\n<span class=\"hljs-keyword\">VALUES<\/span>\n  (\n    <span class=\"hljs-string\">'Samsung'<\/span>,\n    <span class=\"hljs-string\">'129 Samsung-ro, Yeongtong-gu, Suwon-si, Gyeonggi-do, South Korea'<\/span>,\n    <span class=\"hljs-string\">'contact@samsung.com'<\/span>,\n    <span class=\"hljs-string\">'+82-2-2255-0114'<\/span>\n  ),\n  (\n    <span class=\"hljs-string\">'Apple'<\/span>,\n    <span class=\"hljs-string\">'One Apple Park Way, Cupertino, CA 95014, USA'<\/span>,\n    <span class=\"hljs-string\">'contact@apple.com'<\/span>,\n    <span class=\"hljs-string\">'+1-408-996-1010'<\/span>\n  ),\n  (\n    <span class=\"hljs-string\">'Google'<\/span>,\n    <span class=\"hljs-string\">'1600 Amphitheatre Parkway, Mountain View, CA 94043, USA'<\/span>,\n    <span class=\"hljs-string\">'contact@google.com'<\/span>,\n    <span class=\"hljs-string\">'+1-650-253-0000'<\/span>\n  );<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=SU5TRVJUIElOVE8gdmVuZG9ycyAobmFtZSwgYWRkcmVzcywgZW1haWwsIHBob25lKSBWQUxVRVMgKCAnU2Ftc3VuZycsICcxMjkgU2Ftc3VuZy1ybywgWWVvbmd0b25nLWd1LCBTdXdvbi1zaSwgR3llb25nZ2ktZG8sIFNvdXRoIEtvcmVhJywgJ2NvbnRhY3RAc2Ftc3VuZy5jb20nLCAnKzgyLTItMjI1NS0wMTE0JyApLCAoICdBcHBsZScsICdPbmUgQXBwbGUgUGFyayBXYXksIEN1cGVydGlubywgQ0EgOTUwMTQsIFVTQScsICdjb250YWN0QGFwcGxlLmNvbScsICcrMS00MDgtOTk2LTEwMTAnICksICggJ0dvb2dsZScsICcxNjAwIEFtcGhpdGhlYXRyZSBQYXJrd2F5LCBNb3VudGFpbiBWaWV3LCBDQSA5NDA0MywgVVNBJywgJ2NvbnRhY3RAZ29vZ2xlLmNvbScsICcrMS02NTAtMjUzLTAwMDAnICk7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Second, add a <code>website<\/code>  column to the <code>vendors<\/code> table with a <code><a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-not-null\/\">NOT NULL<\/a><\/code> constraint:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> website <span class=\"hljs-type\">VARCHAR<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=QUxURVIgVEFCTEUgdmVuZG9ycyBBREQgQ09MVU1OIHdlYnNpdGUgVkFSQ0hBUiBOT1QgTlVMTDs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>PostgreSQL issues the following error:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">ERROR: <span class=\"hljs-keyword\">column<\/span> \"website\" <span class=\"hljs-keyword\">of<\/span> relation \"vendors\" contains <span class=\"hljs-keyword\">null<\/span> <span class=\"hljs-keyword\">values<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>When you add the <code>website<\/code> column, the default values of the column are <code>NULL<\/code>, which violates the <code>NOT NULL<\/code> constraint.<\/p>\n\n\n\n<p>To make it work, you need to follow these steps:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Step 1.<\/strong> Add the <code>website<\/code> column without the <code>NOT NULL<\/code> constraint.<\/li>\n\n\n\n<li><strong>Step 2.<\/strong> Update values for the existing rows to ensure that the <code>website<\/code> column does not contain <code>NULL<\/code>.<\/li>\n\n\n\n<li><strong>Step 3<\/strong>. Add the <code>NOT NULL<\/code> constraint to the <code>website<\/code> column.<\/li>\n<\/ul>\n\n\n\n<p>Third, add the <code>website<\/code> column without the <code>NOT NULL<\/code> constraint:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors\n<span class=\"hljs-keyword\">ADD<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> website <span class=\"hljs-type\">VARCHAR<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=QUxURVIgVEFCTEUgdmVuZG9ycyBBREQgQ09MVU1OIHdlYnNpdGUgVkFSQ0hBUjs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Fourth, verify the change:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">\\d vendors<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">                                 Table <span class=\"hljs-string\">\"public.vendors\"<\/span>\n Column  |          Type          | Collation | Nullable |           <span class=\"hljs-keyword\">Default<\/span>\n---------+------------------------+-----------+----------+------------------------------\n id      | integer                |           | not <span class=\"hljs-keyword\">null<\/span> | generated always <span class=\"hljs-keyword\">as<\/span> identity\n name    | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\n address | text                   |           |          |\n email   | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\n phone   | character varying(<span class=\"hljs-number\">25<\/span>)  |           | not <span class=\"hljs-keyword\">null<\/span> |\n website | character varying      |           |          |\nIndexes:\n    <span class=\"hljs-string\">\"vendors_pkey1\"<\/span> PRIMARY KEY, btree (id)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Fifth, update the values in the <code>website<\/code> column for all rows:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">UPDATE<\/span> vendors\n<span class=\"hljs-keyword\">SET<\/span>\n  website = <span class=\"hljs-string\">'https:\/\/www.samsung.com'<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n  <span class=\"hljs-type\">name<\/span> = <span class=\"hljs-string\">'Samsung'<\/span>;\n\n<span class=\"hljs-keyword\">UPDATE<\/span> vendors\n<span class=\"hljs-keyword\">SET<\/span>\n  website = <span class=\"hljs-string\">'https:\/\/www.apple.com'<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n  <span class=\"hljs-type\">name<\/span> = <span class=\"hljs-string\">'Apple'<\/span>;\n\n<span class=\"hljs-keyword\">UPDATE<\/span> vendors\n<span class=\"hljs-keyword\">SET<\/span>\n  website = <span class=\"hljs-string\">'https:\/\/www.google.com'<\/span>\n<span class=\"hljs-keyword\">WHERE<\/span>\n  <span class=\"hljs-type\">name<\/span> = <span class=\"hljs-string\">'Google'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=VVBEQVRFIHZlbmRvcnMgU0VUIHdlYnNpdGUgPSAnaHR0cHM6Ly93d3cuc2Ftc3VuZy5jb20nIFdIRVJFIG5hbWUgPSAnU2Ftc3VuZyc7IFVQREFURSB2ZW5kb3JzIFNFVCB3ZWJzaXRlID0gJ2h0dHBzOi8vd3d3LmFwcGxlLmNvbScgV0hFUkUgbmFtZSA9ICdBcHBsZSc7IFVQREFURSB2ZW5kb3JzIFNFVCB3ZWJzaXRlID0gJ2h0dHBzOi8vd3d3Lmdvb2dsZS5jb20nIFdIRVJFIG5hbWUgPSAnR29vZ2xlJzs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>If you have lots of data to update, you can write a script that reads data from an external source (an API, a CSV file, etc.) and loads it to the <code>website<\/code> column.<\/p>\n\n\n\n<p>Sixth, add the <code>NOT NULL<\/code> constraint to the <code>website<\/code> column:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors\n<span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">COLUMN<\/span> website <span class=\"hljs-keyword\">SET<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=QUxURVIgVEFCTEUgdmVuZG9ycyBBTFRFUiBDT0xVTU4gd2Vic2l0ZSBTRVQgTk9UIE5VTEw7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Finally, verify the changes:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">\\d vendors<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">                                 <span class=\"hljs-keyword\">Table<\/span> \"public.vendors\"\n <span class=\"hljs-keyword\">Column<\/span>  |          <span class=\"hljs-keyword\">Type<\/span>          | <span class=\"hljs-keyword\">Collation<\/span> | Nullable |           <span class=\"hljs-keyword\">Default<\/span>\n<span class=\"hljs-comment\">---------+------------------------+-----------+----------+------------------------------<\/span>\n id      | <span class=\"hljs-type\">integer<\/span>                |           | <span class=\"hljs-keyword\">not<\/span> <span class=\"hljs-keyword\">null<\/span> | <span class=\"hljs-keyword\">generated<\/span> <span class=\"hljs-keyword\">always<\/span> <span class=\"hljs-keyword\">as<\/span> <span class=\"hljs-keyword\">identity<\/span>\n <span class=\"hljs-type\">name<\/span>    | <span class=\"hljs-type\">character<\/span> <span class=\"hljs-type\">varying<\/span>(<span class=\"hljs-number\">255<\/span>) |           | <span class=\"hljs-keyword\">not<\/span> <span class=\"hljs-keyword\">null<\/span> |\n address | <span class=\"hljs-type\">text<\/span>                   |           |          |\n email   | <span class=\"hljs-type\">character<\/span> <span class=\"hljs-type\">varying<\/span>(<span class=\"hljs-number\">255<\/span>) |           | <span class=\"hljs-keyword\">not<\/span> <span class=\"hljs-keyword\">null<\/span> |\n phone   | <span class=\"hljs-type\">character<\/span> <span class=\"hljs-type\">varying<\/span>(<span class=\"hljs-number\">25<\/span>)  |           | <span class=\"hljs-keyword\">not<\/span> <span class=\"hljs-keyword\">null<\/span> |\n website | <span class=\"hljs-type\">character<\/span> <span class=\"hljs-type\">varying<\/span>      |           | <span class=\"hljs-keyword\">not<\/span> <span class=\"hljs-keyword\">null<\/span> |\nIndexes:\n    \"vendors_pkey1\" <span class=\"hljs-keyword\">PRIMARY KEY<\/span>, btree (id)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='postgresql-alter-table-add-column-adding-a-column-at-a-specific-position'>PostgreSQL ALTER TABLE ADD COLUMN &#8211; Adding a column at a specific position <a href=\"#postgresql-alter-table-add-column-adding-a-column-at-a-specific-position\" class=\"anchor\" id=\"postgresql-alter-table-add-column-adding-a-column-at-a-specific-position\" title=\"Anchor for PostgreSQL ALTER TABLE ADD COLUMN - Adding a column at a specific position\">#<\/a><\/h2>\n\n\n\n<p>PostgreSQL does not support adding a new column at a specified position. Fortunately, you have a workaround:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Step 1. Rename the existing table to a new one.<\/li>\n\n\n\n<li>Step 2. Recreate the table with the desired column order.<\/li>\n\n\n\n<li>Step 3. Copy data from the old table to the new table.<\/li>\n\n\n\n<li>Step 4. <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-drop-table\/\">Drop the old table<\/a>.<\/li>\n<\/ul>\n\n\n\n<p>For example, we&#8217;ll add a <code>contact_person<\/code> after the <code>phone<\/code> column to the <code>vendors<\/code> table:<\/p>\n\n\n\n<p>First, rename the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-23\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors\n<span class=\"hljs-keyword\">RENAME<\/span> <span class=\"hljs-keyword\">TO<\/span> vendors_copy;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-23\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=QUxURVIgVEFCTEUgdmVuZG9ycyBSRU5BTUUgVE8gdmVuZG9yc19jb3B5Ow%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Second, recreate the <code>vendors<\/code> table with the new <code>contact_person<\/code> column after the <code>phone<\/code> column:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-24\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">TABLE<\/span> vendors (\n  id <span class=\"hljs-type\">INT<\/span> <span class=\"hljs-keyword\">GENERATED<\/span> <span class=\"hljs-keyword\">ALWAYS<\/span> <span class=\"hljs-keyword\">AS<\/span> <span class=\"hljs-keyword\">IDENTITY<\/span> <span class=\"hljs-keyword\">PRIMARY KEY<\/span>,\n  <span class=\"hljs-type\">name<\/span> <span class=\"hljs-type\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>,\n  address <span class=\"hljs-type\">TEXT<\/span>,\n  email <span class=\"hljs-type\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>,\n  phone <span class=\"hljs-type\">VARCHAR<\/span>(<span class=\"hljs-number\">25<\/span>) <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>,\n  contact_person <span class=\"hljs-type\">VARCHAR<\/span>(<span class=\"hljs-number\">255<\/span>),\n  website <span class=\"hljs-type\">VARCHAR<\/span> <span class=\"hljs-keyword\">NOT<\/span> <span class=\"hljs-keyword\">NULL<\/span>\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-24\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=Q1JFQVRFIFRBQkxFIHZlbmRvcnMgKCBpZCBJTlQgR0VORVJBVEVEIEFMV0FZUyBBUyBJREVOVElUWSBQUklNQVJZIEtFWSwgbmFtZSBWQVJDSEFSKDI1NSkgTk9UIE5VTEwsIGFkZHJlc3MgVEVYVCwgZW1haWwgVkFSQ0hBUigyNTUpIE5PVCBOVUxMLCBwaG9uZSBWQVJDSEFSKDI1KSBOT1QgTlVMTCwgY29udGFjdF9wZXJzb24gVkFSQ0hBUigyNTUpLCB3ZWJzaXRlIFZBUkNIQVIgTk9UIE5VTEwgKTs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Third, copy data from the <code>vendors_copy<\/code> to the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-25\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">INSERT<\/span> <span class=\"hljs-keyword\">INTO<\/span>\n  vendors (\n    id,\n    <span class=\"hljs-type\">name<\/span>,\n    address,\n    email,\n    phone,\n    contact_person,\n    website\n  ) \n<span class=\"hljs-keyword\">OVERRIDING<\/span> <span class=\"hljs-keyword\">SYSTEM<\/span> <span class=\"hljs-keyword\">VALUE<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span>\n  id,\n  <span class=\"hljs-type\">name<\/span>,\n  address,\n  email,\n  phone,\n  <span class=\"hljs-keyword\">NULL<\/span>,\n  website\n<span class=\"hljs-keyword\">FROM<\/span>\n  vendors_copy;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-25\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=SU5TRVJUIElOVE8gdmVuZG9ycyAoIGlkLCBuYW1lLCBhZGRyZXNzLCBlbWFpbCwgcGhvbmUsIGNvbnRhY3RfcGVyc29uLCB3ZWJzaXRlICkgT1ZFUlJJRElORyBTWVNURU0gVkFMVUUgU0VMRUNUIGlkLCBuYW1lLCBhZGRyZXNzLCBlbWFpbCwgcGhvbmUsIE5VTEwsIHdlYnNpdGUgRlJPTSB2ZW5kb3JzX2NvcHk7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>The clause <code>OVERRIDING SYSTEM VALUE<\/code> allows insert values into the <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-identity-column\/\">identity column<\/a> with the <code>GENERATED ALWAYS AS IDENTITY<\/code> constraint.<\/p>\n\n\n\n<p>The <code>contact_person<\/code> will have <code>NULL<\/code> as the default value.<\/p>\n\n\n\n<p>Fifth, verify the column layout of the <code>vendors<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-26\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">\\d vendors<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-26\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-27\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">                                    Table <span class=\"hljs-string\">\"public.vendors\"<\/span>\n     Column     |          Type          | Collation | Nullable |           <span class=\"hljs-keyword\">Default<\/span>\n----------------+------------------------+-----------+----------+------------------------------\n id             | integer                |           | not <span class=\"hljs-keyword\">null<\/span> | generated always <span class=\"hljs-keyword\">as<\/span> identity\n name           | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\n address        | text                   |           |          |\n email          | character varying(<span class=\"hljs-number\">255<\/span>) |           | not <span class=\"hljs-keyword\">null<\/span> |\n phone          | character varying(<span class=\"hljs-number\">25<\/span>)  |           | not <span class=\"hljs-keyword\">null<\/span> |\n contact_person | character varying(<span class=\"hljs-number\">255<\/span>) |           |          |\n website        | character varying      |           | not <span class=\"hljs-keyword\">null<\/span> |\nIndexes:\n    <span class=\"hljs-string\">\"vendors_pkey\"<\/span> PRIMARY KEY, btree (id)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-27\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Finally, drop the old table <code>vendors_copy<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">DROP TABLE vendors_copy;<\/code><\/span><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?db=addColumn&amp;q=RFJPUCBUQUJMRSB2ZW5kb3JzX2NvcHk7\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='using-a-migration-library'>Using a migration library <a href=\"#using-a-migration-library\" class=\"anchor\" id=\"using-a-migration-library\" title=\"Anchor for Using a migration library\">#<\/a><\/h2>\n\n\n\n<p>When you want to add a column to a table in the production database, you should use a migration library so that your code and database structure go to the production simultaneously. If you change the table structure before changing our code, the app may not work as expected.<\/p>\n\n\n\n<p>For example, if you have an API that inserts data into a table. The table has a new <code>NOT NULL<\/code> column, but the API does not require it. Other systems using API can pass the validation check in the application layer but fail when it reaches the database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <code>ALTER TABLE ... ADD COLUMN<\/code> statement to add one or more columns to a table.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='quiz'>Quiz <a href=\"#quiz\" class=\"anchor\" id=\"quiz\" title=\"Anchor for Quiz\">#<\/a><\/h2>\n\n\n\n<iframe loading=\"lazy\"\n  name=\"quiz\"\n  src=\"\/quiz\/?quiz=add-column\"\n  height=\"700\"\n  width=\"600\"\n  class=\"iframe\"\n><\/iframe>\n\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"816\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL ALTER TABLE ADD COLUMN Statement\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"816\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL ALTER TABLE ADD COLUMN Statement\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you&#8217;ll learn how to use the PostgreSQLALTER TABLE ADD COLUMN statement to add a new column to a table. Getting Started with the PostgreSQL ALTER TABLE ADD COLUMN statement # Due to new requirements, you may need to add one or more columns to a table. In PostgreSQL, you can use [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":13,"menu_order":69,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-816","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PostgreSQL ALTER TABLE ADD COLUMN<\/title>\n<meta name=\"description\" content=\"In this tutorial, you&#039;ll learn how to use the PostgreSQL ALTER TABLE ADD COLUMN statement to add a new column to a table.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL ALTER TABLE ADD COLUMN\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you&#039;ll learn how to use the PostgreSQL ALTER TABLE ADD COLUMN statement to add a new column to a table.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/\" \/>\n<meta property=\"og:site_name\" content=\"PostgreSQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-03T08:01:08+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-alter-table-add-column\\\/\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-alter-table-add-column\\\/\",\"name\":\"PostgreSQL ALTER TABLE ADD COLUMN\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\"},\"datePublished\":\"2024-12-11T02:56:37+00:00\",\"dateModified\":\"2025-01-03T08:01:08+00:00\",\"description\":\"In this tutorial, you'll learn how to use the PostgreSQL ALTER TABLE ADD COLUMN statement to add a new column to a table.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-alter-table-add-column\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-alter-table-add-column\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-alter-table-add-column\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Tutorial\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PostgreSQL ALTER TABLE ADD COLUMN Statement\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/\",\"name\":\"PostgreSQL Tutorial\",\"description\":\"Learn PostgreSQL from Scratch\",\"alternateName\":\"PostgreSQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pgtutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL ALTER TABLE ADD COLUMN","description":"In this tutorial, you'll learn how to use the PostgreSQL ALTER TABLE ADD COLUMN statement to add a new column to a table.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL ALTER TABLE ADD COLUMN","og_description":"In this tutorial, you'll learn how to use the PostgreSQL ALTER TABLE ADD COLUMN statement to add a new column to a table.","og_url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/","og_site_name":"PostgreSQL Tutorial","article_modified_time":"2025-01-03T08:01:08+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/","url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/","name":"PostgreSQL ALTER TABLE ADD COLUMN","isPartOf":{"@id":"https:\/\/www.pgtutorial.com\/#website"},"datePublished":"2024-12-11T02:56:37+00:00","dateModified":"2025-01-03T08:01:08+00:00","description":"In this tutorial, you'll learn how to use the PostgreSQL ALTER TABLE ADD COLUMN statement to add a new column to a table.","breadcrumb":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-alter-table-add-column\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Tutorial","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":3,"name":"PostgreSQL ALTER TABLE ADD COLUMN Statement"}]},{"@type":"WebSite","@id":"https:\/\/www.pgtutorial.com\/#website","url":"https:\/\/www.pgtutorial.com\/","name":"PostgreSQL Tutorial","description":"Learn PostgreSQL from Scratch","alternateName":"PostgreSQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pgtutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/816","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/comments?post=816"}],"version-history":[{"count":14,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/816\/revisions"}],"predecessor-version":[{"id":1408,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/816\/revisions\/1408"}],"up":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/13"}],"wp:attachment":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/media?parent=816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}