Re: [SQL] New book on Postgres
On Wed, 07 Jun 2000, Ted Kennedy wrote: > We are evaluating a ten-page proposal for a new book on Postgres and are > looking for a few people to advise us on its viability. If interested, > please email me at [EMAIL PROTECTED] with a brief description of your > credentials and a statement that you agree to non-disclosure of it and I > will send you more information. How are you getting on with this? Havn't heard from you for a while? -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
[SQL] on line numbers, drop table errors, and log files
Hello- A few quick questions, I'm interested to know how people solve these issues. Thanks! (BTW: Pg 7.0.2) 1) Is there a way to find discover the line number of a script that causes an error? I mean, if I "\i script.sql" a 2000 line script, it is often difficult to track down that it was line #1211 that has a problem. What I have been doing recently (don't laugh) is to iteratively chop the script up into smaller pieces until I find the offensive command. 2) Is there a way to write a script such that it only drops a table IF it exists? Currently, I just place a "drop table blah" in front of a "create table blah" and live with the error message. I've noticed that some people make a "drop table" script and a "create table" script, is this the generally best way to do things? The problems arise in that sometimes I do not know (because things are automated) if the table exists yet or not. 3) Is it possible to have psql make a log file of a session? This would solve problem #1 above, because one could look in the log file to find the first error message from the large script. 4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump main_v0_6" in order to create a backup of my "main_v0_6" database. In order to regenerate the database, I first run a "\i create_tables.sql" script, then I run "\i backup.dump". However, I end up having to hand edit the "backup.dump" file to place "drop sequence" statements before the "create sequence" statements because they are created by my "create_tables.sql" script. I'm sure you Postgres gurus out there are not doing it this way, what is your method of database backup/recovery? TIA, Jon -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas Universidade Federal do Rio de Janeiro (UFRJ) - Brasil email: [EMAIL PROTECTED] ***-*--**---***---
[SQL] copy with libpq?
I want to use copy ( file -> db ) with a command in C. How do i? Thanks.
[SQL] PL/Tcl
Hi all, I hope I am asking this in the right list... I apologize if I am not. Is there a way to loop through a query in PL/Tcl, like the FOR .. IN in PL/PGSQL ? I need to write some functions that do DROP and DELETE and AFAIK those can't be handled from PL/PGSQL. I could write a PL/Tcl function to call from PL/PGSQL but PL/Tcl is way easier to write and debug I think. Thanks, -Roberto Mello -- Roberto Mello, [EMAIL PROTECTED] - GNU/Linux Reg.User #96240 Computer Science - Utah State University USU Free Software and GNU/Linux Club, President http://fslc.usu.edu - http://www.brasileiro.net
Re: [SQL] on line numbers, drop table errors, and log files
Hi. Here are the options for pg_dump: -a dump out only the data, no schema -c clean (drop) schema prior to create -d dump data as INSERT, rather than COPY, commands -D dump data as INSERT commands with attribute names -hserver host name -i proceed when database version != pg_dump version -n suppress most quotes around identifiers -N enable most quotes around identifiers -o dump object ids (oids) -p server port number -s dump out only the schema, no data -t dump for this table only -u use password authentication -v verbose -x do not dump ACL's (grant/revoke) So you are dumping it with the following: Proper INSERT commands/attribs, pwd auth, data only I would consider running: pg_dump -D -u -c -f backup.dump This will dump the schema and drop the objects before creating them. -Michael > 4) I run pg_dump this way: "pg_dump -D -u -a -f backup.dump > main_v0_6" in order to create a backup of my "main_v0_6" database. > In order to regenerate the database, I first run a "\i > create_tables.sql" script, then I run "\i backup.dump". However, > I end up having to hand edit the "backup.dump" file to place "drop > sequence" statements before the "create sequence" statements > because they are created by my "create_tables.sql" script. I'm > sure you Postgres gurus out there are not doing it this way, what > is your method of database backup/recovery? _ http://fastmail.ca/ - Fast Free Web Email for Canadians
[SQL] SQL Question
Hello,
I hope this question is not too novice for this group.
I have a table
portfolio=# \d questions
Table "questions"
Attribute |Type | Modifier
---+-+-
qid | integer | not null default nextval('questions_qid_seq'::text)
optiona | text| default 'Z'
optionb | text| default 'Z'
optionc | text| default 'Z'
optiond | text| default 'Z'
optione | text| default 'Z'
optionf | text| default 'Z'
optiong | text| default 'Z'
optionh | text| default 'Z'
and other table named quizes, which contains references to the
questions. Basically a quiz is composed of up to three questions.
portfolio=# \d quizes
Table "quizes"
Attribute |Type | Modifier
---+-+--
quiznum | integer | not null default nextval('quizes_quiznum_seq'::text)
question1 | integer |
question2 | integer |
question3 | integer |
So my question is if I want to querry out a particular quiz and all its
questions and question options, how can I do that in one command. I know
that I could do it with a couple of loops, but I think the SQL is much
more eliquent. Maybe I have my tables incorrectly set up? And
suggestions?
Thanks all,
Daryl
