![]() This means, that you potentially can have many temporary schemas. Postgres=# create temporary table hh ( a int ) One more thing to know: If someone, in another session, also creates a temporary object there will be a new temporary schema created: :/home/postgres/ psql Postgres=# select relnamespace::regnamespace from pg_class where relname = 'xx' A schema is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names can duplicate those of other objects existing in other schemas. The schema name must be distinct from the name of any existing schema in the current database. The temporary schema will be re-used for future temporary objects: postgres=# create temporary table xx ( a int ) CREATE SCHEMA enters a new schema into the current database. The temporary table, however, was dropped: postgres=# \qġ4:52:39 :/home/postgres/ psqlĮRROR: relation "pg_temp_3.tt" does not exist This temporary schema will not be dropped once we end our session and will still be there if we reconnect. … or we can always ask pg_class: postgres=# select relnamespace::regnamespace from pg_class where relname = 'tt' To verify that the temporary table really is in the schema we can either full reference the table in a select: postgres=# select * from pg_temp_3.tt We got two new schema after we created the temporary table: One for the temporary table itself and another one for toasted data, if any. ![]() The answer becomes clear once we list the system schemas as well: postgres=# \dnS Where did it go to? If we ask for the list of schemas there is only public, and we already know from the error message above that it cannot be public: postgres=# \dn Once more, let’s create a temporary table, but this time we do not specify a schema: postgres=# create temporary table tt ( a int ) This somehow needs to happen automatically. The error message clearly states that these types of objects need to go into a temporary schema, but a temporary schema cannot be created: postgres=# \h create schemaĬREATE SCHEMA schema_name ]ĬREATE SCHEMA AUTHORIZATION role_specification ]ĬREATE SCHEMA IF NOT EXISTS schema_name ĬREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification LINE 1: create temporary table public.tt ( a int ) To confirm that we are not allowed to specify a schema for a temporary object, we try to do so: postgres=# create temporary table public.tt ( a int ) ĮRROR: cannot create temporary relation in non-temporary schema But when you try to explicitly specify a schema for a temporary object, you’ll get an error message. As all objects in PostgreSQL are stored in a schema, temporary objects need to be created in a schema as well. ![]() When the drop will happen depends on the options you used when you created the object. Temporary means, that the temporary table, view or sequence will be dropped when you either end your session or you transaction. As you might know, you can create temporary tables, views and sequences in PostgreSQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |