Обсуждение: Server/Database/Schema Definitions
When coding table and function scripts, how do you specify which server/database/schema where you want new tables and functions to reside? Every time I execute my creation scripts, it puts everything in the public schema under the PostgreSQL server. I'm new to PostgreSQL and haven't found any documentation discussing these characteristics. Do you explicitly define the server.schema.table_name in the create table statement? Should the tablespace be set to pg_default and the owner to postgres? Any help you could provide would be greatly appreciated. Thanks, Brandon
On Tue, May 16, 2006 at 09:23:09AM -0500, Brandon E Hofmann wrote: > > When coding table and function scripts, how do you specify which > server/database/schema where you want new tables and functions to reside? > Every time I execute my creation scripts, it puts everything in the public > schema under the PostgreSQL server. I'm new to PostgreSQL and haven't > found any documentation discussing these characteristics. > > Do you explicitly define the server.schema.table_name in the create table > statement? Should the tablespace be set to pg_default and the owner to > postgres? You can do that, yes. You can also change search_path; any object not created in a specific schema will go into the first schema listed in search_path. You can set a default tablespace in postgresql.conf, at the database level, and I think at the session level as well. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On May 16, 2006, at 10:23 AM, Brandon E Hofmann wrote: > When coding table and function scripts, how do you specify which > server/database/schema where you want new tables and functions to > reside? > Every time I execute my creation scripts, it puts everything in the > public > schema under the PostgreSQL server. I'm new to PostgreSQL and haven't > found any documentation discussing these characteristics. > > Do you explicitly define the server.schema.table_name in the create > table > statement? Should the tablespace be set to pg_default and the > owner to > postgres? See this page for a discussion of search_path http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html I typically set the search path at the top of the script. But doing it like this can certainly trip you up in cases where you might make changes and do not execute the script from top to bottom (e.g. redefine a single function). If I have a specific user (or role) for a certain database or project, I like to associate the search path with the role so I don't have to remember to do it every time I work on the database. See ALTER ROLE SET syntax for details on this: http://www.postgresql.org/docs/8.1/interactive/sql-alterrole.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL