Internal dependencies between tables and / or other objects are never bound to the object name. Internally, every object is stored in a catalog table and the OID (internal primary key) of the object is used for everything else. Accordingly, a FOREIGN KEY reference is stored in the catalog tables pg_constraint (the constraint itself incl. … Read more
Please read this article on codingvila.com for a detailed explanation. Rename Column Only If Exists in PostgreSQL DO $$ BEGIN IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name=”your_table” and column_name=”your_column”) THEN ALTER TABLE “public”.”your_table” RENAME COLUMN “your_column” TO “your_new_column”; END IF; END $$;
Change Column Name/Type/Position/Comment: ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] Example: CREATE TABLE test_change (a int, b int, c int); // will change column a’s name to a1 ALTER TABLE test_change CHANGE a a1 INT;
Use: ALTER TABLE dbo.mytable ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn For more info, see: Working with Default Constraints
You could do something like the following, however it is better to include it in the create table as a_horse_with_no_name suggests. if NOT exists (select constraint_name from information_schema.table_constraints where table_name=”table_name” and constraint_type=”PRIMARY KEY”) then ALTER TABLE table_name ADD PRIMARY KEY (id); end if;
Your belief about what will happen is not correct. Setting a default value for a column will not affect the existing data in the table. I create a table with a column col2 that has no default value SQL> create table foo( 2 col1 number primary key, 3 col2 varchar2(10) 4 ); Table created. SQL> … Read more
Regardless of what error Oracle SQL Developer may indicate in the syntax highlighting, actually running your alter statement exactly the way you originally had it works perfectly: ALTER TABLE TEST_PROJECT2 MODIFY proj_name VARCHAR2(300); You only need to add parenthesis if you need to alter more than one column at once, such as: ALTER TABLE TEST_PROJECT2 … Read more
ALTER TABLE can do multiple table alterations in one statement, but MODIFY COLUMN can only work on one column at a time, so you need to specify MODIFY COLUMN for each column you want to change: ALTER TABLE webstore.Store MODIFY COLUMN ShortName VARCHAR(100), MODIFY COLUMN UrlShort VARCHAR(100); Also, note this warning from the manual: When … Read more
The different sorts of locks and when they’re used are mentioned in the doc in Table-level Locks. For instance, Postgres 11’s ALTER TABLE may acquire a SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, or ACCESS EXCLUSIVE lock. Postgres 9.1 through 9.3 claimed to support two of the above three but actually forced Access Exclusive for all … Read more
I have run into this problem and couldn’t find any way around it. Unfortunately, as best I can tell, one must drop the views, alter the column type on the underlying table, and then recreate the views. This can happen entirely in a single transaction. Constraint deferral doesn’t apply to this problem. In other words, … Read more