Postgresql ALTER common operations, postgresqlalter

Postgresql ALTER common operations, postgresqlalter

Postgresql version: psql (9.3.4)

1. ADD an alter table table_name ADD column_name datatype; 2. delete an alter table table_name DROP column_name; 3. Change the Data TYPE of the alter table table_name ALTER column_name TYPE datatype column; 4. RENAME the table alter table table_name rename to new_name; 5. Change the column name alter table table_name RENAME column_name to new_column_name; 6. set alter table table_name ALTER column_name {SET | DROP} not null for the not null field. 7. Add defaultALTER TABLE table_name ALTER column_name SET expression to the column to DEFAULT;

PostgreSQL: How to query parameters set based on the user (role)

The following example shows Method 1: Query through pg_user View -- 1.1 set the log_statement parameter S = # alter role francs set log_statement = "all "; alter role -- 1.2 verify ipvs = # select * From pg_user where usename = 'francs '; -[RECORD 1] Using usename | francsusesysid | 24920 usecreatedb | fusesuper | fusecatupd | fuserepl | fpasswd | ******* valuntil | useconfig | {log_statement = all} -- 1.3 set the user's maintenance_work_mem parameter postg Res = # alter role francs set maintenance_work_mem = "1 GB"; alter role--1.4 verify postgres again = # select * From pg_user where usename = 'francs '; -[RECORD 1] Using usename | francsusesysid | 24920 usecreatedb | fusesuper | fusecatupd | fuserepl | fpasswd | ******* valuntil | useconfig | {log_statement = all, maintenance_work_mem = 1 GB} Remarks: The above is queried through pg_user.useconfig. Method 2: query using the pg_db_role_setting catalog base Table -- 2.1 pg_db_role_setting Table structure Table "tables" Column | Type | Modifiers ------------- + -------- + ----------- setdatabase | oid | not null setrole | oid | not null setconfig | text [] | Indexes: "pg_db_role_setting_databaseid_rol_index" UNIQUE, btree (setdatabase, setrole), tablespace "pg_global" Remarks: we can see that pg_db_role_setting records the database and user level. -- 2.2 verify ipvs = # select oid, rolname from pg_authid where rolname = 'francs '; oid | rolname ------- + --------- 24920 | francs (1 row) S = # select * From pg_db_role_setting where setrole = 24920; setdatabase | setrole | setconfig -----------...... remaining full text>

Which postgresql statement can change the field name type? Alert table name alter column field Name field type is not acceptable

Alter table name alter column field name varchar (50) not null;
Or
Alter table name modify column field name varchar (50) not null;
Field Type customization, which can be varchar, int, and Other types. According to different database versions, the change command may be alter column or modify column.