Thread: Missing schema name
<div class="WordSection1"><p class="MsoNormal"> <p class="MsoNormal">We’re setting up a new machine. <p class="MsoNormal">Frompgadmin when I look at the old machine tables, the schema name is displayed in the screen<p class="MsoNormal"><imgalt="cid:image001.png@01CC580A.C04D7240" height="189" id="_x0000_i1027" src="cid:image001.png@01CC580A.FFB1A050"width="534" /><p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">Whenlooking at the new machine, the schema name is not displayed.<p class="MsoNormal"><img alt="cid:image002.png@01CC580A.C04D7240"height="241" id="Picture_x0020_2" src="cid:image002.png@01CC580A.FFB1A050" width="336"/><p class="MsoNormal"> <p class="MsoNormal">Visually, it’s not a problem. But I do lots of cut/paste and needthe schema name to show up. <p class="MsoNormal">Any thoughts why it’s not.<p class="MsoNormal"> <p class="MsoNormal">Thanks<pclass="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <pclass="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Doug Little</span></b><pclass="MsoNormal"><b><span style="font-size:12.0pt"> </span></b><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">Sr.Data Warehouse Architect | Business Intelligence Architecture| Orbitz Worldwide </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">500W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 |Fax 312.894.5164 | Cell 847-997-5741</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif""></span><pclass="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""><a href="mailto:Douglas.Little@orbitz.com"title="mailto:dlittle@orbitz.com"><b><span style="color:blue">Douglas.Little@orbitz.com</span></b></a></span><b><u><spanstyle="font-size:13.5pt;font-family:"Times NewRoman","serif";color:blue"></span></u></b><p class="MsoNormal"> <img alt="cid:image001.jpg@01CABEC8.D4980670" border="0"height="61" id="Picture_x0020_1" src="cid:image003.jpg@01CC580A.FFB1A050" width="83" /> <a href="http://www.orbitz.com/"title="http://www.orbitz.com/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">orbitz.com</span></a><span style="color:blue">|</span><a href="http://www.ebookers.com/" title="http://www.ebookers.com/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">ebookers.com</span></a><span style="color:blue">|</span><a href="http://www.hotelclub.com/" title="http://www.hotelclub.com/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">hotelclub.com</span></a><span style="color:blue">|</span><a href="http://www.cheaptickets.com/" title="http://www.cheaptickets.com/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">cheaptickets.com</span></a><span style="color:blue">|</span><a href="http://www.ratestogo.com/" title="http://www.ratestogo.com/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">ratestogo.com</span></a><spanstyle="color:blue"> |</span><a href="http://www.asiahotels.com/" title="http://www.asiahotels.com/"><span style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">asiahotels.com</span></a><p class="MsoNormal"> </div>
On Thu, 2011-08-11 at 09:42 -0500, Little, Douglas wrote: > We're setting up a new machine. > From pgadmin when I look at the old machine tables, the schema name is displayed in the screen > [cid:image001.png@01CC580A.FFB1A050] > > > When looking at the new machine, the schema name is not displayed. > [cid:image002.png@01CC580A.FFB1A050] > > Visually, it's not a problem. But I do lots of cut/paste and need the schema name to show up. > Any thoughts why it's not. > Guess you have different search_path on these servers. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
I have multiple schemas in my database. I do not have a search_path set for the database (so it wouold use the default: $user,public<br/>When I look at any table in any of the schemas, the table name has the schema prepended to it.<br /><br/>If it isn't PgAdmin doing it, then it must be in the Pg server itself.<br />How does PgAdmin get the DDL for a tablefrom the server?<br /><br /><div class="gmail_quote">On Thu, Aug 11, 2011 at 2:14 PM, Guillaume Lelarge <span dir="ltr"><<ahref="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">On Thu, 2011-08-11 at 09:42 -0500,Little, Douglas wrote:<br /> > We're setting up a new machine.<br /> > From pgadmin when I look at the old machine tables, the schema name is displayed in the screen<br /> > [cid:image001.png@01CC580A.FFB1A050]<br /> ><br/> ><br /> > When looking at the new machine, the schema name is not displayed.<br /> > [cid:image002.png@01CC580A.FFB1A050]<br/> ><br /> > Visually, it's not a problem. But I do lots of cut/paste and needthe schema name to show up.<br /> > Any thoughts why it's not.<br /> ><br /><br /> Guess you have different search_pathon these servers.<br /><br /><br /> --<br /> Guillaume<br /> <a href="http://blog.guillaume.lelarge.info" target="_blank">http://blog.guillaume.lelarge.info</a><br/> <a href="http://www.dalibo.com" target="_blank">http://www.dalibo.com</a><br/><font color="#888888"><br /><br /> --<br /> Sent via pgadmin-support mailinglist (<a href="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a>)<br /> To make changes toyour subscription:<br /><a href="http://www.postgresql.org/mailpref/pgadmin-support" target="_blank">http://www.postgresql.org/mailpref/pgadmin-support</a><br/></font></blockquote></div><br />
On Thu, 2011-08-11 at 15:26 -0500, Little, Douglas wrote: > That worked, but I'm confused why it worked. > On the new machine, I'm using gpadmin which does have different search_path. > When I switch to my regular id, the schema name shows up. > > In pgadmin, is it even possible to change the search_path? > Nope, I don't think you can. > Seems like it should be a server/db option. > Could be a a good way to fix this. > Thanks. > --- old > current_user=dlittle > show search_path > "$user",public,pg_catalog,dba_work > > -- new > current_user=gpadmin > dba_work, pg_catalog, public > > > current_user=gpadmin > "$user",public,pg_catalog,dba_work -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
If you right click on the database, and select properties, under the variables tab, there is a search_path variable in theVariable Name pulldown.<br /><br /><div class="gmail_quote">On Thu, Aug 11, 2011 at 4:01 PM, Guillaume Lelarge <span dir="ltr"><<ahref="mailto:guillaume@lelarge.info">guillaume@lelarge.info</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">On Thu, 2011-08-11 at 15:26 -0500,Little, Douglas wrote:<br /> > That worked, but I'm confused why it worked.<br /> > On the new machine, I'm usinggpadmin which does have different search_path.<br /> > When I switch to my regular id, the schema name shows up.<br/> ><br /> > In pgadmin, is it even possible to change the search_path?<br /> ><br /><br /> Nope, I don'tthink you can.<br /><br /></blockquote></div>
On Thu, 2011-08-11 at 14:38 -0500, Michael Shapiro wrote: > I have multiple schemas in my database. I do not have a search_path set for > the database (so it wouold use the default: $user,public > When I look at any table in any of the schemas, the table name has the > schema prepended to it. > > If it isn't PgAdmin doing it, then it must be in the Pg server itself. > How does PgAdmin get the DDL for a table from the server? > pgAdmin doesn't get the DDL, it builds it from all the properties it grabed in the system catalog. What's more interesting is how it gets the default schema. It's the first valid one in the search_path parameter (with one caveat, if you have $user in your search_path, pgadmin will replace it with your username, and check if it's a valid schema name). So, IOW, with search_path=a,$user,b,c,d,e, and connection as guillaume, with no a and guillaume schemas, but an existing c schema, the default schema will be c. Every object with this schema won't have their schema name prepended. Every other object will have. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Thu, 2011-08-11 at 16:08 -0500, Michael Shapiro wrote: > If you right click on the database, and select properties, under the > variables tab, there is a search_path variable in the Variable Name > pulldown. > Yes, but this changes every connection to this DB, not only the pgadmin ones (it does an ALTER DATABASE for that). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com