Thread: schema name in SQL statement.
I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: "SELECT col FROM schema.table" I like to be able to use generic SQL statement like: "SELECT col FROM table"
-------------- Original message ---------------------- From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com> > I have to explicitly specify the schema name to make SQL statement to > work. > Can I set the schema before the query, or set a default schema? > My current statement: > "SELECT col FROM schema.table" > I like to be able to use generic SQL statement like: > "SELECT col FROM table" > > > > -- See search_path in: http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html -- Adrian Klaver aklaver@comcast.net
On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: > -------------- Original message ---------------------- > From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com> > > > I have to explicitly specify the schema name to make SQL statement to > > work. > > Can I set the schema before the query, or set a default schema? > > My current statement: > > "SELECT col FROM schema.table" > > I like to be able to use generic SQL statement like: > > "SELECT col FROM table" > > > > > > > > -- > > See search_path in: > http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html > > > -- > Adrian Klaver > aklaver@comcast.net I see answers like this all the time. When I review the doc's I still don't know how to set the "search_path" because there is no example in the doc's. Do I do something like this: "select search_path=(public)" or "select search_path="public" . So how is the search_path set? -- John Fabiani
On Tue, Aug 19, 2008 at 10:53 PM, johnf <jfabiani@yolo.com> wrote: > On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: >> From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com> >> >> > I have to explicitly specify the schema name to make SQL statement to >> > work. >> > Can I set the schema before the query, or set a default schema? >> > My current statement: >> > "SELECT col FROM schema.table" >> > I like to be able to use generic SQL statement like: >> > "SELECT col FROM table" >> >> See search_path in: >> http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html > > I see answers like this all the time. When I review the doc's I still don't > know how to set the "search_path" because there is no example in the doc's. > Do I do something like this: > "select search_path=(public)" or "select search_path="public" . So how is > the search_path set? But there is a link on that page under search_path that points here: http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html which has much better info on search_path in it.
On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote: > On Tue, Aug 19, 2008 at 10:53 PM, johnf <jfabiani@yolo.com> wrote: > > On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: > >> From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com> > >> > >> > I have to explicitly specify the schema name to make SQL statement to > >> > work. > >> > Can I set the schema before the query, or set a default schema? > >> > My current statement: > >> > "SELECT col FROM schema.table" > >> > I like to be able to use generic SQL statement like: > >> > "SELECT col FROM table" > >> > >> See search_path in: > >> http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.htm > >>l > > > > I see answers like this all the time. When I review the doc's I still > > don't know how to set the "search_path" because there is no example in > > the doc's. Do I do something like this: > > "select search_path=(public)" or "select search_path="public" . So how > > is the search_path set? > > But there is a link on that page under search_path that points here: > > http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html > > which has much better info on search_path in it. Thanks - I did not note the link. -- John Fabiani
-----Original Message----- From: johnf <jfabiani@yolo.com> To: pgsql-general@postgresql.org Subject: Re: [GENERAL] schema name in SQL statement. Date: Tue, 19 Aug 2008 22:25:14 -0700 On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote: > On Tue, Aug 19, 2008 at 10:53 PM, johnf <jfabiani@yolo.com> wrote: > > On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: > >> From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com> > >> > >> > I have to explicitly specify the schema name to make SQL statement to > >> > work. > >> > Can I set the schema before the query, or set a default schema? > >> > My current statement: > >> > "SELECT col FROM schema.table" > >> > I like to be able to use generic SQL statement like: > >> > "SELECT col FROM table" > >> > >> See search_path in: > >> http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.htm > >>l > > > > I see answers like this all the time. When I review the doc's I still > > don't know how to set the "search_path" because there is no example in > > the doc's. Do I do something like this: > > "select search_path=(public)" or "select search_path="public" . So how > > is the search_path set? set search_path = 'xmxmxmxmxmxm'
On Tuesday 19 August 2008 9:53:11 pm johnf wrote: > On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: > > -------------- Original message ---------------------- > > From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com> > > > > > I have to explicitly specify the schema name to make SQL statement to > > > work. > > > Can I set the schema before the query, or set a default schema? > > > My current statement: > > > "SELECT col FROM schema.table" > > > I like to be able to use generic SQL statement like: > > > "SELECT col FROM table" > > > > > > > > > > > > -- > > > > See search_path in: > > http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html > > > > > > -- > > Adrian Klaver > > aklaver@comcast.net > > I see answers like this all the time. When I review the doc's I still > don't know how to set the "search_path" because there is no example in the > doc's. Do I do something like this: > "select search_path=(public)" or "select search_path="public" . So how is > the search_path set? > > -- > John Fabiani From postgresql.conf.sample in ~pgsql/share: #--------------------------------------------------------------------------- # CLIENT CONNECTION DEFAULTS #--------------------------------------------------------------------------- # - Statement Behavior - #search_path = '"$user",public' # schema names This is how the initial postgresql.conf is set up when you do an initdb. I have found the postgresql.conf to be quite well commented. -- Adrian Klaver aklaver@comcast.net
On Wed, Aug 20, 2008 at 8:13 AM, Adrian Klaver <aklaver@comcast.net> wrote: > From postgresql.conf.sample in ~pgsql/share: > > > #--------------------------------------------------------------------------- > # CLIENT CONNECTION DEFAULTS > #--------------------------------------------------------------------------- > > # - Statement Behavior - > > #search_path = '"$user",public' # schema names > > > This is how the initial postgresql.conf is set up when you do an initdb. I > have found the postgresql.conf to be quite well commented. I have a question (not necessarily to you...) . How come that syntax parses for postgresql.conf, but in psql it doesn't: set search_path = '"$user",public' ; ERROR: schema ""$user",public" does not exist I have to use this: set search_path = "$user",public ; SET
--- On Tue, 8/19/08, Masis, Alexander (US SSA) <alexander.masis@baesystems.com> wrote: > From: Masis, Alexander (US SSA) <alexander.masis@baesystems.com> > Subject: [GENERAL] schema name in SQL statement. > To: pgsql-general@postgresql.org > Date: Tuesday, August 19, 2008, 10:52 PM > I have to explicitly specify the schema name to make SQL > statement to > work. > Can I set the schema before the query, or set a default > schema? > My current statement: > "SELECT col FROM schema.table" > I like to be able to use generic SQL statement like: > "SELECT col FROM table" > > > > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general you can change the search path of the schema SET search_path TO myschema