Re: [SQL] Problems with PG_DUMP and restore
Hi, >In an effort to do some general cleanup in my database functions, I >dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text >files. I'm using a similar method for my own project. But I dump the data with the -d or -D option. This ist not as fast as the raw copy but more flexible. Maybe you should give it a try. If you rename some colums of your table, you should use -d (propert INSERT commands instead of COPY). If you reorder the colums of your tables you should use -D (propert INSERT with explicit Attributenames). You cannot do both (rename and reorder columns)! Andre BEGIN:VCARD VERSION:2.1 N:Schnabel;Andre FN:Andre Schnabel NICKNAME:Thalion X-WAB-GENDER:2 BDAY:19720509 EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20010710T193556Z END:VCARD ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How can we match a condition among 2 diff. tables?
Hi, if you translate your (english) idea striktly to SQL, you'll make it ;-) > Now, i need to get the details of all employees who did > receive NONE of the salesorders. ie.. i wish to select the > records of table 'employee' whose 'emp_id' are not > there in table 'salesorder'. SELECT * FROM employee WHERE emp_id NOT IN ( SELECT emp_id FROM salesorder ); ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] WHERE on an alias
- Original Message - From: "Joseph Shraibman" <[EMAIL PROTECTED]> Subject: Re: [SQL] WHERE on an alias > If I try to put a distinct on in my subselect int the from I get: > > ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions > > what does that mean? You need to (at least) ORDER BY the expression you want to have the DISTINCT ON. Tom's example would work fine: > > select distinct on (ml.f1,ml.f2) * from ut,ml > > where ut.f1 = ml.f1 and ut.f2 = ml.f2 > > order by ml.f1, ml.f2, ml.f3 desc; Wrong: select distinct on (ml.f1,ml.f2) * from ut,ml where ut.f1 = ml.f1 and ut.f2 = ml.f2 order by ml.f2, ml.f1, ml.f3 desc; Andre ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3
Can you post the exact errormessage? - Original Message - From: "Richard NAGY" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, September 14, 2001 12:11 PM Subject: [SQL] SQL request change when upgrade from 7.0.2 to 7.1.3 > Hello, > > Recently, I have upgraded my postgresql server from 7.0.2 to 7.1.3. But, > now, one request which was 'good' before, don't want to work any more > now. > > It was : (pretty long) > > SELECT aes.ent_id, e.type, e.nom, aes.sect_id as voulu, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id = 56 > UNION > SELECT distinct on (aes.ent_id) aes.ent_id, e.type, e.nom, aes.sect_id, > cvd_new(current_date, e.date_entree, 'new') FROM ass_entrep_sectact aes, > > entreprise e WHERE e.id = aes.ent_id and aes.sect_id <> 3 and > aes.sect_id <> 9 > and aes.sect_id <> 1 and aes.sect_id <> 13 and aes.sect_id <> 56 and > aes.ent_id > not in (SELECT ent_id FROM ass_entrep_sectact WHERE sect_id = 56 and > sect_id <> 3 > and sect_id <> 9 and sect_id <> 1 and sect_id <> 13) ORDER BY e.type, > e.nom > > Now, if I want that my request works well, I have to remove the order by > statement. But, of course, it is not ordered any more. > > So how can I translate this request to one which can work with an order > by statement ? > > Thanks. > > -- > Richard NAGY > Presenceweb > > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] To query many tables.
- Original Message - From: "trebischt" <[EMAIL PROTECTED]> Subject: [SQL] To query many tables. > Hi, > > The query looks like this: > > select * from table1 f, table2 s, table3 t, table4 fo, table5 fi, > table6 si, table 7 se, table8 e, table9 n, table10 ten, table 11 el, > table 12 tw ... > where f.id=s.id > and f.id=t.id > and f.id=fo.id > and f.id=fi.id > and so on... > > Is this the right way, or are there any better solution? > How do the professionals make that big queries? Yes, this is ONE right way. Professionals do it the same way ;-). But you can use the "JOIN"-Clause instead. It's your desicion, what's more readable. With JOIN it would look like this: select * FROM table1 fJOINtable2 s ON f.id = s.id JOINtable 3 t ON f.id = t.id more joins here WHERE xxx If you use your way or this simple joins, there must be a row with the same id in each table, to have it shown by your query. If you want to see all records, that have an entry in at least one table you have to use outer joins. More details on SELECT statemants and joins are here: http://www.postgresql.org/idocs/index.php?queries.html. Andre ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Diferent databases on same query...
"Douglas Rafael da Silva" <[EMAIL PROTECTED]> schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > Hi, Andre ! > > Maybe yes, maybe not. I have 6 companies of the same group, little but > they are like as, and they share the main database. But there are data > that are specific of each one. The design of tables is the same for all > them. So, on mysql I had on diferent databases. Thus, on the same query, > I select the name of employee, the truck and invoice of the specific > company. Two companies have server separated. How can I do to do > distribute database server with postgres ? You have a suggestion ? Hi Douglas, ok, that's a good point to have different databases in one query. Unfortunately there is no way to do this with postgresql. At least no way with the standard distribution. Maybe it's worth to try replication. You should be able to replicate the tables of your main db between the others. But I never tried that. AFAIK, he most advanced replication tooll is PostgreSQL Replicator (http://pgreplicator.sourceforge.net/). Maybe it's the better way to go back(?) to MySQL or have a look at Interbase. Andre ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Diferent databases on same query...
"Douglas Rafael da Silva" <[EMAIL PROTECTED]> schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hi, > > I'd like to do a query where can be possible I access tables from > diferent databases on the same query. > On MySQL, I do: > > SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM > Database1.People1, Database2.Result1 WHERE ... > > I think on ORACLE works like as: > > SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM > People1@Database1, Result1@Database2 WHERE ... > > But Who I can to do this on Postgresql ? > You CANNOT do that with PostgreSQL. But why do you want to do that? IMHO it's a rather bas design to hold data in different places, if you need to select them in one query. Is there a real reason to hold the tables in different databases? Andre ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] nvl() function
Hi Steven, you may use COALESCE. This function should have the same behaviour as Oracle's nvl. For documentation look at http://www.postgresql.org/idocs/index.php?functions-conditional.html Andre "Steven Dahlin" <[EMAIL PROTECTED]> schrieb im Newsbeitrag 9qj13u$2v5l$[EMAIL PROTECTED]">news:9qj13u$2v5l$[EMAIL PROTECTED]... > I am trying to find the equivalent in Postgresql to the Oracle sql function > nvl(). With nvl() you give two parameters. The first may be a field/column > or variable. If the value is not null then it is returned by the function. > For example the with query below if the :ClientParameter is passed then only > those rows which have a clientdesc matching the parameter are returned. If > the :ClientParameter is null then those rows which have clientdesc = > clientdesc are returned (all rows): > > selectclientid, > clientdesc > from clients > where ( clientdesc = nvl( :ClientParameter, clientdesc ) ) > > I have looked thru all the documentation I could find but nowhere were any > built-in SQL functions delineated. Does anyone know where the documentation > can be found? > > Thanks, > Steve > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
