The best way to separate users and applications within the same base is by using schematics:
http://www.postgresql.org/docs/current/static/ddl-schemas.html
A scheme is part of the qualification of an object. When a table t
is created on the basis b
your full name will be b.[esquema].t
:
b=> create table t (c text);
CREATE TABLE
b=> \d t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
b=> select * from b.public.t;
c
---
(0 rows)
The scheme used in the name, when not specified, is the current scheme:
b=> select current_schema;
current_schema
----------------
public
The current scheme is the first scheme in the search path that exists:
b=> show search_path;
search_path
-----------------
"$user", public
If there was a scheme with the session user name this would be the current scheme.
To make a scheme the current scheme point the search path to that scheme:
b=> set search_path to s1;
SET
b=> show search_path;
search_path
-------------
s1
If we select the current scheme it will come back null because there is no scheme S1:
b=> select current_schema;
current_schema
----------------
(1 row)
To create the scheme:
b=> create schema s1;
CREATE SCHEMA
b=> select current_schema;
current_schema
----------------
s1
Now we can create a table t
in the scheme s1
without having to qualify the scheme:
b=> create table t (c text);
CREATE TABLE
As there was already a table t
in the scheme public
now there are two. One in the scheme public
and the other in the scheme s1
:
b=> \d *.t
Table "public.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
Table "s1.t"
Column | Type | Modifiers
--------+------+-----------
c | text |
How the current scheme is s1
it is not necessary to qualify the name to enter in s1.t
:
b=> insert into t (c) values ('texto');
INSERT 0 1
b=> select * from t;
c
-------
texto
(1 row)
b=> select * from public.t;
c
---
(0 rows)
If the scheme is no longer necessary:
b=> drop schema s1 cascade;
NOTICE: drop cascades to table t
DROP SCHEMA
The cascade
drops all dependent objects. Go back to the default search path:
b=> set search_path to "$user", public;
SET
b=> select current_schema;
current_schema
----------------
public
You have a database/table that is normal/permanent, but you want to do operations that should be temporary? Is there a reason for this? Why not do it separately?
– Maniero
@bigown I wanted to create a separate bank for all this process, but the below did not allow, so to use the resources of a bank I would have to use the current, but should not be "traces" in it.
– Guilherme Lautert
What if you only create separate tables in the same database? What is the database?
– Maniero
@bigown edited, putting the tag, yes would be temporary tables as well as their data. The purpose of all this is to generate a files. Same as what the SPED does, only in case the SPED validates this file.
– Guilherme Lautert