Database Reference
In-Depth Information
You can also use the SQL commands directly or make them use a tool like pgAdmin3. Following
those steps could cause problems, as the changes aren't within a transaction that other users
can see the changes when they are only half finished. So, it would be better to do this in a single
transaction, using BEGIN and COMMIT . Also, those four changes require us to make two passes
of the table. However, we can perform the whole transformation in one pass of the table by
using multiple clauses on the ALTER TABLE command. So instead, do the following:
BEGIN;
ALTER TABLE cust
ALTER COLUMN firstname SET DATA TYPE text
USING firstname || ' ' || lastname,
ALTER COLUMN firstname SET NOT NULL,
DROP COLUMN lastname;
ALTER TABLE cust RENAME firstname TO custname;
COMMIT;
This is a great example of why I personally prefer to use scripts to make such changes on
large production databases rather than making the changes directly using a GUI.
Adding/Removing schemas
Separating groups of objects is a good way of improving administration efficiency. We need to
know how to create new schemas and remove schemas that are no longer required.
How to do it...
To add a new schema, issue the following command:
CREATE SCHEMA sharedschema;
If you want that schema to be owned by a particular user, then you can add the
following option:
CREATE SCHEMA sharedschema AUTHORIZATION scarlett;
Or, if you want to create a new schema which has the same name as an existing user, so that
the user becomes the owner, then try the following:
CREATE SCHEMA AUTHORIZATION scarlett;
In many database systems, the schema name is the same as the owning user. PostgreSQL
allows schemas owned by one user to have objects owned by another user within them. That
can be especially confusing when you have a schema of the same name as the owning user.
To avoid this you should have two types of schema: schemas named the same as the owning
user should be limited to just objects owned by that user. Other general schemas can have
shared ownership.
 
Search WWH ::




Custom Search