[ADMIN] field names

I'd like to check field names for various tables in our code. Is there a system table that lists the field names of each table? ...some way to query this information to run an error check? Thanks Jodi Kanter -- ___ ___ Jodi L Kanter

[ADMIN] [Fwd: Majordomo results: unsubscribe]

is [EMAIL PROTECTED]. Both may be listed because I frequently get emails in duplicate. Any help would be greatly appreciated. Jodi Kanter Original Message Subject: Majordomo results: unsubscribe Date: Wed, 15 Dec 2004 15:55:43 +

[ADMIN] error on start

Our Linux admin just upgraded our server to fedora Core 2 which apparently upgraded postgres to version 7.4.2-1 Now on trying to start the postgres server we get the following error: An old version of the database format was found.\nYou need to upgrade the data format before using

[ADMIN] dropping a table constraint

I needed to add a FK constraint to a table where one was forgotten. I found an add constraint command and used it: ALTER TABLE file_info add foreign key (al_fk) references arraylayout(al_pk); However, I wanted to name the constraint. It now looks like this: $1 FOREIGN KEY (al_fk) REFERENCES

[ADMIN] check constraints

realize that I can do this with specific SQL in the code but was wondering if it's possible to implement a sort of general database wide check of this nature. That way if future tables are added with this FK field then the code would not have to be altered. Thanks. Jodi Kanter

[ADMIN] no dup rows

I have a linking table that has only two fields in it and both are foreign keys to other tables. I'd like to ensure that there are never any duplicate rows in this table. I was planning on creating a multi-column unique index to satisfy this requirement. Is there a more appropriate way to

[ADMIN] adding defaults

Can field specific defaults easily be added to a table that is already in existence and has data in it? I cannot seem to locate the correct ALTER command in any of my books. Thanks Jodi -- ___ Jodi L Kanter BioInformatics

[ADMIN] seeing tables across databases

databases. I have several databases on my system and do not have this problem. Is there some setting that I am not aware of that could explain this situation? thoughts? Thanks Jodi Kanter -- ___ Jodi L Kanter BioInformatics Database

[ADMIN] cross databases?

I am working with someone who uses MySQL and says that he has two separate databases in which he can query across. I am curious if that is something that can be done in postgres? Any comments on whether or not that is a good thing? Thanks Jodi Kanter

[ADMIN] rename

Is it possible to simply rename a database without affecting the structure of it? -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]

[ADMIN] table check

We are working on checking to see whether a database has been created from a Perl/Pg interface. Is there an easy way to count tables such that if the count comes back at zero the script knows that the database has not yet been created? We tried simply using \d but cannot get that to work. I

[ADMIN] upgrade

We are currently on version 7.3.3 and I was thinking of upgrading. Is the 7.4.2 version considered a stable version? I read some info regarding changes to the system tables. Can someone touch on how this might affect us? Do I need to restore my database with this upgrade? Thanks so much, Jodi

[ADMIN] XTG Data Modeller

Has anyone used the XTG Data Modeller with postgres? If so, can you comment on it's functionality? Thanks Jodi Kanter -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL

[ADMIN] constraints and performance

Do constraints effect performance significantly? Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]

Re: [ADMIN] [Fwd: binary tree query]

someone else has better insight about inner join's vs where's. Hope this sheds some light and not too many shadows, yuji On Mon, 26 Jan 2004, Jodi Kanter wrote: I have a biochemist telling me that this query below is a typical one for crawling through a taxonomic tree

[ADMIN] [Fwd: binary tree query]

I have a biochemist telling me that this query below is a typical one for crawling through a taxonomic tree and that this is how I should represent some peptide information we have. Is there anyone on this list familiar with such data? I am weak in the science department but this query looks

Re: [ADMIN] Database diagram

I use Microsoft Visio. It also has reverse engineering. I ma having trouble with the refresh option which is quite frustrating but other than that it works fine. I used Erwin in the past and really like that but it is pricey and we already had a license available for Visio. Jodi Ganesan

[ADMIN] constaint addition to existing schema

I missed a constraint the last time that I loaded my database. Can I add an additional constraint without having to drop and rerun the table? If not, can I do just that one table without having to do the entire schema? Jodi --

[ADMIN] character type modification

Can I alter a column from character varying(128) to text without having to create a temp table? I am running 7.3.3. Thanks -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846

[ADMIN] lost database

in retreiving his tables from backup tapes? I am not sure how or where that data is stored. Thanks Jodi Kanter -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 [EMAIL PROTECTED]

[ADMIN] data restore

Are there limits to how much can be restored using the \i switch is psql? I have been doing dumps for some time now using copy commands instead of inserts. I then go in to psql to run my backup script using \i. I am now having issues all of a sudden with it hanging. Maybe I ought to attempt

[ADMIN] one other thought

I am trying to restore data from a database that did not have any PK and FK constraints to one that now does. Could this be my issue? I would have expected to get errors if any constraint violations occured but not a hanging of the system. How does the dump order the tables? I reordered my

[ADMIN] data restore

Ok. sorry to confuse everyone...I got my restore to fun to completion so apparently my question about running restores in psql and the ordering of the data are not relevant. I do, however, have one other question I'd like to pose. I am getting serveral ref integrity errors which state that a

Re: [ADMIN] data restore

It appears that some of these errors are due to the parent table being populated after the child table. Can I control the order of the copy statements in my script when I run the pg_dump? Stephan Szabo wrote: On Fri, 10 Oct 2003, Jodi Kanter wrote: Ok. sorry to confuse everyone

[ADMIN] large type table

I am creating a database that will have about 12 type tables associated with it. Someone has suggested making one type table that would have a field that identifies which types go with each other (like separating them into individual tables). Is this a wise idea? It seems to me that it could

[ADMIN] new script

So I now have my new schema script that includes PK and FK constraints. It runs without a problem but I get too many NOTICES regarding implicit indexes that have been created. I am running this script from within psql using the \i command. Can I run this and suppress these NOTICES? I find it

[ADMIN] table constraints vs column

Title: Is there any argument to support using table constraints over column constraints in a situation where there are no PKs or FK references that are composed of more than one column? Is the automatic PK index generated on for table constraints or for either?

[ADMIN] steps to ensure referential integrity

Title: I have inherited a database that was originally created in postgres version 6.? There were no steps taken at that time to implement constraints to ensure referential integrity. There are no FK constraints, PK constraints, etc.. Most of what currently

[ADMIN] extended view of sql

Title: I seem to remember there being a switch that I could set in psql that would show me the sql that the system is using when I run certain commands. For example, what the system is doing when I do a \d tablename. Does anyone know what I am referring to? I

Re: [ADMIN] changing field length

Title: how do I copy from one field to another? Gaetano Mendola wrote: "Jodi Kanter" [EMAIL PROTECTED] wrote: I am running 7.3.3. Can I change a field that is varying character(128) to varying character(250)? nope.You can a) cr

Re: [ADMIN] changing field length

, 2003 at 10:16:38 -0400, Jodi Kanter [EMAIL PROTECTED] wrote: how do I copy from one field to another? update tablename set newcol = oldcol; -- ___ Jodi L Kanter BioInformatics Database

Re: [ADMIN] changing field length

Title: please ignore last message. I had my columns switched. sorry about that! Thanks for the quick response. jodi Bruno Wolff III wrote: On Wed, Aug 20, 2003 at 10:16:38 -0400, Jodi Kanter [EMAIL PROTECTED] wrote: how do I copy from one field

Re: [ADMIN] changing field length

but was wondering if there were another solution. Logically it makes sense to list it toward the top of the table. Thanks Jodi Bruno Wolff III wrote: On Wed, Aug 20, 2003 at 10:16:38 -0400, Jodi Kanter [EMAIL PROTECTED] wrote: how do I copy from one field to another? update tablename set

Re: [ADMIN] changing field length

order. Can I do this by listing the fields in my insert in the order in which I want them placed? Thanks Jodi Bruno Wolff III wrote: On Wed, Aug 20, 2003 at 10:49:08 -0400, Jodi Kanter [EMAIL PROTECTED] wrote: can I now make this field not null?is it possible to move a field up

[ADMIN] changing field length

Title: I am running 7.3.3. Can I change a field that is varying character(128) to varying character(250)? Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846

[ADMIN] version 7.3.3 upgrade

to initdb again. This release, as with all minor releases, does not *require* a dump/reload to be upgraded to, but there is a change to pg_proc, as pertains timestamptz_izone that only takes effect after an initdb ... Thanks Jodi Kanter -- ___ Jodi L

[ADMIN] checking currval

Title: I cannot view the currval because it is not set in the session yet. Is there a way to set it without having to increment the value? Thanks Jodi -- ___ Jodi L Kanter BioInformatics Database Administrator University

Re: [ADMIN] checking currval

your_sequence; Jodi Kanter wrote: I cannot view the currval because it is not set in the session yet. Is there a way to set it without having to increment the value? Thanks Jodi ---(end of broadcast)--- TIP 7: don't

[ADMIN] psql sequence question

Title: If I'm usingtransactions (not autocommit), are sequences atomic? In other words, after inserting a record to a table that uses sequence A, am I guaranteed that select last_value on sequence A is atomic, and cannot be interfered with by other

[ADMIN] list ranking

Title: We have a set of records in a table that needs to be ranked. We thought of adding a ranking field, but the problem is that the ranking could change often and there could be additions and deletions from the list. Updating this ranking field is quickly

[ADMIN] createdb

Title: We just upgraded one of our machines to posgresql 7.3.2 and I can no longer use the "createdb" command from the Linux command line. Is there something I need to install for this feature or has it been eliminated in this version? I realize of course that I

Re: [ADMIN] indexes not being used!

..4.23 rows=78 width=8) (actual time=1.08..1.56 rows=299 loops=1) - Seq Scan on grouplink (cost=0.00..1.78 rows=78 width=8) (actual time=0.02..0.46 rows=78 loops=1) Total runtime: 13.30 msec - Original Message - From: Joe Conway [EMAIL PROTECTED] To: Jodi Kanter [EMAIL PROTECTED] Cc

[ADMIN] test message

I seem to be having trouble getting message to the list. Can someone please reply if this message makes it. Thank you, Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]

Re: Followup Re: [ADMIN] Performance question

Is restarting postmaster on a regular basis necessary for performance? - Original Message - From: Bob Smith [EMAIL PROTECTED] To: pgsql-admin [EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 2:20 PM Subject: Followup Re: [ADMIN] Performance question OK, I seem to have fixed my own

[ADMIN] proper db standard

I am creating a simple database that will hold information about various publications. There are keywords that are associated with these publications and there can be anywhere from 1 to about 6 of these different keywords. As I see it I have two choices: 1) create keyword fields 1-6 in the

Re: [ADMIN] VACUUM

Do you have a URL for that document you mention? I get nothing when I search on pgsql-general. Thanks for the help. Jodi - Original Message - From: Robert Treat [EMAIL PROTECTED] To: Jodi Kanter [EMAIL PROTECTED] Cc: Postgres Admin List [EMAIL PROTECTED] Sent: Thursday, November 14, 2002

[ADMIN] table locking?

If a Vacuum Full is done in the midst of a user transaction what happens to the transaction? Will it complete once the vacuum full releases the tables? Thanks Jodi ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL

[ADMIN] query

I have a query that pulls two fields across three tables such that the results are in two columns: layout.probe spot.signal value1 some value value2 some value value3 some value value1 some value value2 some value value3 some value I have three tables: spot, measurement, layout. The

[ADMIN] VACUUM

I am trying to set up my cron job to run the appropriate vacuums on our database. I understand from reading the documentation that regular vacuums, vacuum full, and vacuum analyze are all important. Can someone assist in telling me what is the most efficient order? I would assume that

[ADMIN] constraint notices

Is there a way to hide the constraintnotices when running a script to create a database? I would like the screen output to show a clean install and only list errors that make have occurred. Thanks Jodi ___Jodi L KanterBioInformatics Database

[ADMIN] schema design

e repeating data in the extensions table. Various analyses can be associated with the same file types so the extension field in the extensions table will have repeating values. I have attached a small schema with these few tables to assist with your understanding our issue. I would apprec

[ADMIN] joins

Can anyone tell me if there is any real difference between the two different ways to create tables joins? select field1, field2 fromtable1 jointable2 using (field3) vs. select field1, field2 fromtable1, table 2 where table1.field3=table2.field3 Thanks Jodi

Fw: [ADMIN] joins

Title: Message I think this was meant for the list (see below). - Original Message - From: tachung_h To: 'Jodi Kanter' Sent: Monday, November 04, 2002 2:44 PM Subject: RE: [ADMIN] joins I need to work with a group of volunteer developers over the Internet. Developers might

[ADMIN] array character types

about various analyses that are being run - i.e user and system parameter values. It would be nice not to have to create a separate table for each of these fields. Any thoughts would be greatly appreciated. Thank you, Jodi Kanter ___Jodi L KanterBioInformatics

[ADMIN] password encryption

We are currently using the pg_passwd utility to encrypt passwords. Someone here told me that I can now encrypt passwords within postgresql so that they are not stored in plain text in the pg_shadow file. Is this true? I cannot seem to find any mention of it in my postgresql books but it's

[ADMIN] two databases

I'd like to set up two databases on one server but need information on securing them separately. The databases will be accessed by different groups, who cannot have access to the data on the other database. I assume that this can be done using Postgres 7.1.3. Am I correct? The database that

[ADMIN] string PK vs. interger PK

Can anyone offer some insight as to what should be considered when choosing between a primary key that is an integer vs. a primary key that is a string value? Does one or the other affect indexing speed? update speed? Is a serial integer value better than using a PK that has some "value"

[ADMIN] table dump

I just completed a data only dump of a table by using the following command: pg_dumpgenex -Rau -tspecies species.sql I noticed that the first line of the file seems to be disabling some postgres trigger. It reads: UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'species';

[ADMIN] join vs. IN statement

thoughts? thanks Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]

[ADMIN] pg_restore

? and if so what is the appropriate syntax becasue I have tried this and its not working. thanks Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]

[ADMIN] broken view?

ng the new table but when I try to do that I get the following error: ERROR: Attempt to insert rule "_RETam_spots_usf_view" failed: already exists I cannot seem to determine what this last error is telling me. Any thoughts? Thanks Jodi Kanter ___Jodi L

[ADMIN] in addition to last posting

I should also mention that the view no longer states its definition when \d is used. Thanks Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]

Re: [ADMIN] broken view?

got it workingI was trying to create and rename the view but apparently that breaks some sort of link. It worked when I completely removed the existing view and then recreated with original name from scratch. thanks anyway... Jodi - Original Message - From: Jodi Kanter

[ADMIN] slow inserts

performance tuning trick that I have not thought of? Thanks for your help, Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]

Re: [ADMIN] slow inserts

None of the data is actually committed to the database until the scripts complete so I believe that autocommit is turned off. - Original Message - From: Nick Fankhauser [EMAIL PROTECTED] To: Jodi Kanter [EMAIL PROTECTED]; Postgres Admin List [EMAIL PROTECTED] Sent: Wednesday, March 20

[ADMIN] Fw: --fast switch

Did my message below make it out to the list? or is it that no one has any idea what I am referring to? If not, could someone point me to ways to improve insertion time. I am vacuuming regularly. thanks Jodi - Original Message - From: Jodi Kanter To: Postgres Admin List Sent

[ADMIN] --fast switch

Can anyone offer me some insight into a --fast switch that can be used to improve speed on Poastgres inserts. I have been vacuuming database often but still our inserts seem to take quite a bit of time. Someone mentioned this switch to me but I cannot seem to find any documentation on it.

[ADMIN] drop column?

I cannot seem to locate any documentation about removing a column that I just added to a table. There is no data in that particular column but there is in the rest of the table. Can I remove this column or will I need to drop the entire table and restore from backup? Thanks Jodi

[ADMIN] adding fields

I need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise. Thanks Jodi ___Jodi L KanterBioInformatics Database

Re: [ADMIN] adding fields

Thank you. I found this doc just after I sent the email. I appreciate the quick response. Jodi - Original Message - From: Florian Helmberger To: Jodi Kanter Cc: [EMAIL PROTECTED] Sent: Friday, January 18, 2002 10:14 AM Subject: RE: [ADMIN] adding fields

[ADMIN] granting all to user

Can I grant a user all privileges to all the tables without having to type a grant command for each table individually? I know that I can use: grant all on object to username but I would like to set this person up as a superuser for now so that he can assist with development. It appears that

[ADMIN] unknown index

I am working with a database that was not created by our team. There are some indexes set up on a table and I need to understand what type they are and how they are functioning. How can I look at this in Postgres? I have a situation where we trying to update data and are receiving an error based

[ADMIN] user passwords

why is it that I can sign in to the postgres database using PSQL without having to specify a password for a user even thought one has been specified in pg_shadow? I signed in to Linux with my own personal id (which is NOT a postgres account) and used the following command to get in: psql -U

[ADMIN] found alternate solution

Thanks to all those that sent messages to help with my TCPIP socket problem. I still cannot get the -i switch to work with postmaster but was able to correct the problem by placing tcpip_socket = 1 in my postgresql.conf file. Thanks Nick for sending me that link to the JDBC info

[ADMIN] setting up ODBC

I am trying to connect to postgres via ODBC so that I can use Visio. I have installed psqlODBC on my windows 2000 workstation and am trying to get to our postgres database located on a Linux machine. Do I need to enable ODBC on the Linux side? or just configure the driver on my workstation?

[ADMIN] setting up ODBC

Thanks to those that have responded to my issue. I have done all that I think was recommended: My Windows workstation has the psqlODBC driver I added ODBC extensions to the base catalogs I added the -i switch to my postmaster startup in order to turn on TCPIP connections I modified my

[ADMIN] Visio

, etc... Thanks Jodi Kanter ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(804) 924-2846[EMAIL PROTECTED]

[ADMIN] database cluster

corrected that and now have LC_COLLATE=C but I believe that I have to run initdb again. However, the directory structure already exists and it will nto allow me to initialize into the same location. Thanks Jodi Kanter ---(end of broadcast)--- TIP 6: Have