Skip to main content
67 votes

Database "frozen" on ALTER TABLE

The command you wish to run does take an ACCESS EXCLUSIVE lock on the table, preventing all other access to that table. But the duration of this lock should be just a few milliseconds, as adding a ...
jjanes's user avatar
  • 39.9k
38 votes
Accepted

How to add a column with a foreign key constraint to a table that already exists?

You just have to add another step - in fact PostgreSQL is already telling you that: column "sender" referenced in foreign key constraint does not exist. The FOREIGN KEY (aka parent) column ...
Vérace's user avatar
  • 29.9k
28 votes
Accepted

How can I set a column to NOT NULL without locking the table during a table scan?

Another answer from another postgresql contributor. PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented. Proper implementation ...
Melkij's user avatar
  • 3,169
24 votes
Accepted

Altering a column: null to not null

The syntax you used is from a SQL Server example, not from MySQL. It would be good to check the MySQL documentation about ALTER TABLE syntax. In MySQL, the ALTER COLUMN subclause can only be used for ...
ypercubeᵀᴹ's user avatar
24 votes

Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance. It is important to understand that MySQL introduced new feature - Online DLL processing in version ...
Stoleg's user avatar
  • 2,388
23 votes

How to add a column with a foreign key constraint to a table that already exists?

I'm not sure why everyone is telling you that you have to do this in two steps. In fact, you don't. You tried to add a FOREIGN KEY which assumes, by design, the column is there and throws that error ...
Evan Carroll's user avatar
  • 63.2k
23 votes
Accepted

Why is adding a NOT NULL column with a default constraint instantaneous?

Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table,...
Aaron Bertrand's user avatar
20 votes
Accepted

Is ALTER TABLE ... DROP COLUMN really a metadata only operation?

There are certain circumstances where dropping a column can be a meta-data-only operation. The column definitions for any given table are not included in each and every page where rows are stored, ...
Hannah Vernon's user avatar
  • 70.1k
17 votes
Accepted

How to set a NOT NULL column into NULL in PostgreSQL?

You can simply drop NOT NULL ALTER TABLE app_client_users ALTER COLUMN client_id DROP NOT NULL;
Shakeer Mirza's user avatar
16 votes

Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

Yes, with PostgreSQL 11 This feature is new and landed in Version 11. ALTER TABLE your_table ADD COLUMN new_column integer NOT NULL DEFAULT 0; The above is one such command that will be ...
Evan Carroll's user avatar
  • 63.2k
16 votes

Fast alter column NVARCHAR(4000) to NVARCHAR(260)

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the ...
Joe Obbish's user avatar
  • 32.2k
16 votes

Fast alter column NVARCHAR(4000) to NVARCHAR(260)

Is there any way to alter the column data type as a metadata-only operation? I don't think so, this is how the product works right now. There are some really great workarounds to this limitation ...
Josh Darnell's user avatar
  • 29.3k
15 votes
Accepted

How to add a PostgreSQL 10 identity column to an existing table?

You need to declare the type of the column, too (INT or BIGINT or SMALLINT): ALTER TABLE sourceTable ADD COLUMN ogc_fid int -- int or bigint or smallint GENERATED BY ...
ypercubeᵀᴹ's user avatar
15 votes
Accepted

Add column with default in postgresql without table level lock

This problem is solved in PostgreSQL 11, which was released on October 18, 2018. There is a more detailed explanation in this blog post, but the short version is that in Version 11 the table doesn't ...
Mad Scientist's user avatar
14 votes
Accepted

Database "frozen" on ALTER TABLE

DDL operations usually lock the object they are acting upon, so should not be performed outside planned maintenance windows (when your users are expecting disruption or the system to be completely ...
David Spillett's user avatar
14 votes
Accepted

Cannot alter MYSQL Table (stuck in a invalid default value loop)

Try changing both columns defaults at the same time: ALTER TABLE yourtable CHANGE campaign_start_date campaign_start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CHANGE ...
Philᵀᴹ's user avatar
  • 31.8k
12 votes
Accepted

alter column size take very long time?

Assuming all previously-created columns are fixed-width like char and datetime, adding column C above just added it to the end of the fixed-width section of the record (effectively a meta-only change)....
MattyZDBA's user avatar
  • 1,933
12 votes

Increase speed altering column on large table to NON NULL

Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single ...
Joe Obbish's user avatar
  • 32.2k
11 votes

Add NOT NULL constraint to large table without table scan

One potential alternative is to create a check constraint using NOT VALID, then validating the check constraint later. This method requires holding an ACCESS EXCLUSIVE lock only for the duration to ...
Marcus's user avatar
  • 111
11 votes

SQLServer add a default value for a column

It's your quotes. If you remove those, and change your literal to use single quotes, you should be fine. SQL Servers DEFAULT is SET QUOTED_IDENTIFIER ON and with this literals must be delimited by ...
S3S's user avatar
  • 3,578
9 votes
Accepted

Will altering an nvarchar(max) column to nvarchar(50) lock the table?

There's no way for us to fully answer your question. Depends on way too many factors, including the table definition, other columns in the table, if the data is off page, the T-SQL generated by your ...
Joe Obbish's user avatar
  • 32.2k
9 votes
Accepted

How do I move all tables from one Postgres schema to another

Use PL/pgSQL in a DO statement: DO $$DECLARE p_table regclass; BEGIN SET LOCAL search_path='xyz'; FOR p_table IN SELECT oid FROM pg_class WHERE relnamespace = 'xyz'::regnamespace ...
Laurenz Albe's user avatar
  • 51.9k
8 votes

I need to run VACUUM FULL with no available disk space

Quick and dirty: Stop Postgres Move the main database directory to another disk where there's enough room for vacuuming In the original location of main, add a symlink to the new location Vacuum ...
Roger Dahl's user avatar
8 votes
Accepted

Nvarchar to varchar to nvarchar characters conversion

No, there is no way to "fix" the data because the data is no longer there. When you converted to VARCHAR, the underlying values for each character were changed into the ASCII value for ?. This is not ...
Solomon Rutzky's user avatar
8 votes
Accepted

Is it bad practice to add columns to an existing table?

ALTER TABLE is there for a reason. More seriously, unless you plan to have a really big data set, you shouldn't worry about adding new columns on demand. Dropping and recreating the table is only ...
András Váczi's user avatar
8 votes

Alter large table without downtime

You are much better off running the ALTER TABLE like this ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; What could be the benefit ? Defining a ...
RolandoMySQLDBA's user avatar
8 votes

Disadvantages of converting column with 2 billion data from varchar to int

Even if the real length of a column is 11, you are not passing from the column of the same type to the column of the same type but with smaller size. So it will be not "metadata-only" operation. ...
sepupic's user avatar
  • 11k
8 votes
Accepted

How long can this ALTER TABLE statement take?

Nobody can tell you with any degree of certainty or accuracy how long this operation will take on your system. However I suggest the change will likely have less impact than you think. I set up a ...
Aaron Bertrand's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible