Re: [SQL] problem while converting sybase quries to postgres
imam wrote: The following is a sybase query and i am trying to convet it into postgres but in postgres the join will be specified in from clause and we cannot specify any logical operator i would be grateful if you could tell me the solution for it You don't actually say what your query is trying to do. SELECT t1.SR_TRAN_HEAD_PK AS ReceiptHeaderPK, t1.ADJ_TRAN_ID AS AdjustmentTransactionID, ...other columns... FROM PE_POP_SR_POSTED_TRAN_HEAD t1, PE_POP_PO_HEADER t2, PE_POP_SR_POSTED_TRAN_DET t3 Where t1.SR_TRAN_HEAD_PK = t3.SR_TRAN_HEAD_PK and (t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *= t2.PO_HEADER_PK) If this is a left outer join, you could do something like: SELECT ... FROM pe_pop_sr_posted_tran_head t1 LEFT JOIN pe_pop_po_header t2 ON t1.po_header_pk = t2.po_header_pk LEFT JOIN pe_pop_sr_posted_tran_det t3 ON t2.po_header_pk = t3.po_header_pk AND t1.sr_tran_head_pk = t3.sr_tran_head_pk But you'll want to test it because I'm not clear what your query is doing. -- Richard Huxton Archonet Ltd ---(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] problem while converting sybase quries to postgres
Please cc: the list when you reply to me - that way others can help too. imam wrote: If this is a left outer join, you could do something like: SELECT ... FROM pe_pop_sr_posted_tran_head t1 LEFT JOIN pe_pop_po_header t2 ON t1.po_header_pk = t2.po_header_pk LEFT JOIN pe_pop_sr_posted_tran_det t3 ON t2.po_header_pk = t3.po_header_pk AND t1.sr_tran_head_pk = t3.sr_tran_head_pk But you'll want to test it because I'm not clear what your query is doing. -- Richard Huxton Archonet Ltd Thanking for you reply the problem is that i have a "or" condition and left outer join between two table.A po_header_table is comman in both the join as given below t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *= t2.PO_HEADER_PK) Ah! Hadn't spotted that. Would this be what you're after? SELECT ... FROM ( SELECT t1.po_header_pk AS t1_phpk t3.po_header_pk AS t3_phpk ... FROM t1, t3 WHERE t1.sr_tran_head_pk = t3.sr_tran_head_pk ) AS first LEFT JOIN t2 ON ( first.t1_phpk = t2.po_header_pk OR first.t3_phpk = t2.po_header_pk ) Not sure what happens if t1_phpk and t3_phpk both match different rows in t2 or whether that's what you want. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] pg primary key bug?
Hi , I am using pg 7.4.5 on RedHat AS 3.0. I am using it via jdbc and jboss. I have found big problem about the way pg supports primary keys. The bug was reported from my customers ( we are installed pg with our ERP on ~ 500 costomers) and I do not know hot it is happen. sklad21=# \d a_constants_str Table "public.a_constants_str" Column | Type | Modifiers +---+--- constname | character varying(30) | not null fid| integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) sklad21=# select * from a_constants_str ; constname | fid | constvalue ---+-+- AACCGRID | 0 | SOF_3 AKLTYPID | 0 | SOF_3 ADARID| 0 | SOF_2 AOBLASTID | 0 | SOF_6 AUSERID | 0 | SOF_17 ANMGRID | 0 | SOF_21 LOCAID| 0 | SOF_41 DOCID | 0 | SOF_1585254 DOCPLAID | 0 | SOF_1052900 AKLIID| 0 | SOF_18740 DOCRID| 0 | SOF_2268142 DOCPOGPLA | 0 | SOF_324586 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AKLGRID | 0 | SOF_45 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 ASETUPID | 0 | SOF_4605 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 TDOCID| 0 | SOF_337 TDOCRID | 0 | SOF_19450 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AGRADID | 0 | SOF_256 DOCID | 0 | SOF_1585254 ASLUID| 0 | SOF_46 NASTRF| 0 | SOF_88 ANOMID| 0 | SOF_1200 (30 rows) Pls., see the records with 'DOCID' and note we have primary key defined. At this moment we will create a new db and dump and reload the data. The old one will stay and if needet we will be able to study the files (the access to this customer is not very simple, but possible). regards, ivan. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] pg primary key bug?
On Feb 3, 2005, at 21:53, pginfo wrote: I am using pg 7.4.5 on RedHat AS 3.0. sklad21=# \d a_constants_str Table "public.a_constants_str" Column | Type | Modifiers +---+--- constname | character varying(30) | not null fid| integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) sklad21=# select * from a_constants_str ; constname | fid | constvalue ---+-+- AACCGRID | 0 | SOF_3 AKLTYPID | 0 | SOF_3 ADARID| 0 | SOF_2 AOBLASTID | 0 | SOF_6 AUSERID | 0 | SOF_17 ANMGRID | 0 | SOF_21 LOCAID| 0 | SOF_41 DOCID | 0 | SOF_1585254 DOCPLAID | 0 | SOF_1052900 AKLIID| 0 | SOF_18740 DOCRID| 0 | SOF_2268142 DOCPOGPLA | 0 | SOF_324586 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AKLGRID | 0 | SOF_45 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 ASETUPID | 0 | SOF_4605 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 TDOCID| 0 | SOF_337 TDOCRID | 0 | SOF_19450 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AGRADID | 0 | SOF_256 DOCID | 0 | SOF_1585254 ASLUID| 0 | SOF_46 NASTRF| 0 | SOF_88 ANOMID| 0 | SOF_1200 (30 rows) Pls., see the records with 'DOCID' and note we have primary key defined. It's unclear from just this data, but there's a chance that there are varying numbers of spaces after 'DOCID', which would appear as separate values for the index, even though they aren't readily apparent. Could you show us the results of the following query? select constname, length(constname) as constname_length from a_constants_str; Here's another one which would show if PostgreSQL is treating them equally: select constname, count(constname) from a_constants_str; The results of these queries might shed some light on the issue. Hope this helps. Michael Glaesemann grzm myrealbox com ---(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] pg_restore problem
So in the current version I'm running (7.4.6) and I do a pg_dump I have to then manually manipulate the order by doing a -l to get a table of contents and then reorder (just changing the first number; or the oid also??) just to get it to work right? Does anyone else have these issues? How exactly can I use this on a mission critical app with flaws like this? How do other people work with this? Do they just not dump the files and restore? On Feb 2, 2005, at 3:24 PM, Tom Lane wrote: Bradley Miller <[EMAIL PROTECTED]> writes: I'm attempting to restore a dump from one server to another (one is a Mac and one is a Linux base, if that makes any difference). I keep running into issues like this: pg_restore: [archiver (db)] could not execute query: ERROR: function public.random_page_link_id_gen() does not exist Is this a problem of items in the dump being in the wrong order (ie, there's a forward reference to random_page_link_id_gen())? Any suggestions on how to get around this problem? Use 8.0 ... or use pg_restore's -L/-l options to manually adjust the load order. Pre-8.0 versions of pg_dump are easily fooled if you use ALTER to make earlier-created objects reference later-created objects. regards, tom lane Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
Re: [SQL] pg primary key bug?
Hi, It is not spaces problem. I needet to dump the data and I deleted all rows for 'DOCID' with delete from a_constants_str where constname= 'DOCID'; and the pg reported 10 rows deleted. Sorry I can not execute the commend now ( I dropped the data, becaus I needet the uniqu constnames for the restore). regards, ivan. Michael Glaesemann wrote: On Feb 3, 2005, at 21:53, pginfo wrote: I am using pg 7.4.5 on RedHat AS 3.0. sklad21=# \d a_constants_str Table "public.a_constants_str" Column | Type | Modifiers +---+--- constname | character varying(30) | not null fid| integer | not null constvalue | character varying(30) | Indexes: "a_constants_str_pkey" primary key, btree (constname, fid) sklad21=# select * from a_constants_str ; constname | fid | constvalue ---+-+- AACCGRID | 0 | SOF_3 AKLTYPID | 0 | SOF_3 ADARID| 0 | SOF_2 AOBLASTID | 0 | SOF_6 AUSERID | 0 | SOF_17 ANMGRID | 0 | SOF_21 LOCAID| 0 | SOF_41 DOCID | 0 | SOF_1585254 DOCPLAID | 0 | SOF_1052900 AKLIID| 0 | SOF_18740 DOCRID| 0 | SOF_2268142 DOCPOGPLA | 0 | SOF_324586 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AKLGRID | 0 | SOF_45 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 ASETUPID | 0 | SOF_4605 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 TDOCID| 0 | SOF_337 TDOCRID | 0 | SOF_19450 DOCID | 0 | SOF_1585254 DOCID | 0 | SOF_1585254 AGRADID | 0 | SOF_256 DOCID | 0 | SOF_1585254 ASLUID| 0 | SOF_46 NASTRF| 0 | SOF_88 ANOMID| 0 | SOF_1200 (30 rows) Pls., see the records with 'DOCID' and note we have primary key defined. It's unclear from just this data, but there's a chance that there are varying numbers of spaces after 'DOCID', which would appear as separate values for the index, even though they aren't readily apparent. Could you show us the results of the following query? select constname, length(constname) as constname_length from a_constants_str; Here's another one which would show if PostgreSQL is treating them equally: select constname, count(constname) from a_constants_str; The results of these queries might shed some light on the issue. Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PERFORM] Tunning postgresql on linux (fedora core 3)
sorry about cc ... this is the site: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html but I gues is not right ... hmm Adrian Din On Thu, 03 Feb 2005 14:52:04 +, Richard Huxton wrote: I'll repeat myself: Please CC the mailing list as well as replying to me, so that others can help too. Din Adrian wrote: On Thu, 03 Feb 2005 13:56:50 +, Richard Huxton wrote: Please CC the mailing list as well as replying to me, so that others can help too. b) in docs say that after 7.2 seting this to false does'n turn off the wall ...!? wich option does? The docs don't say that, as far as I can see. It doesn't make sense to turn off the WAL. hmm this is the doc about ... ' NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop checkpointing, however. This is a change in the notes that follow Turn WAL off (fsync=false) only for a read-only database or one where the database can be regenerated from external software. While RAID plus UPSes can do a lot to protect your data, turning off fsync means that you will be restoring from backup in the event of hardware or power failure.' I don't know what this is, and you don't give a URL, but it DOES NOT appear to be in the manuals. You should probably read the sections of the manuals regarding "run-time configuration" and "write ahead logs". The manuals are quite extensive, are available online at http://www.postgresql.org/ and also in most distributions. This is probably a good place to start. http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-WAL If you turn it off you should have more speed ... !!!??? Basically, as I said in my last email - fsync=true makes sure transaction details are safely stored on disk. If you turn this off, the database doesn't have to wait for the data to physically be written to the disk. But, if power fails then data might be in OS or disk cache and so lost when you restart the machine. Please CC the mailing list if you reply to this message. -- Richard Huxton Archonet Ltd -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [PERFORM] Tunning postgresql on linux (fedora core 3)
Din Adrian wrote: sorry about cc ... this is the site: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html but I gues is not right ... hmm It's not that it's incorrect, just that you should always use the manuals as a starting point. On Thu, 03 Feb 2005 14:52:04 +, Richard Huxton wrote: I'll repeat myself: Please CC the mailing list as well as replying to me, so that others can help too. Din Adrian wrote: On Thu, 03 Feb 2005 13:56:50 +, Richard Huxton wrote: Please CC the mailing list as well as replying to me, so that others can help too. b) in docs say that after 7.2 seting this to false does'n turn off the wall ...!? wich option does? The docs don't say that, as far as I can see. It doesn't make sense to turn off the WAL. hmm this is the doc about ... ' NOTE: Since 7.2, turning fsync off does NOT stop WAL. It does stop checkpointing, however. This is a change in the notes that follow Turn WAL off (fsync=false) only for a read-only database or one where the database can be regenerated from external software. While RAID plus UPSes can do a lot to protect your data, turning off fsync means that you will be restoring from backup in the event of hardware or power failure.' I don't know what this is, and you don't give a URL, but it DOES NOT appear to be in the manuals. You should probably read the sections of the manuals regarding "run-time configuration" and "write ahead logs". The manuals are quite extensive, are available online at http://www.postgresql.org/ and also in most distributions. This is probably a good place to start. http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-WAL If you turn it off you should have more speed ... !!!??? Basically, as I said in my last email - fsync=true makes sure transaction details are safely stored on disk. If you turn this off, the database doesn't have to wait for the data to physically be written to the disk. But, if power fails then data might be in OS or disk cache and so lost when you restart the machine. Please CC the mailing list if you reply to this message. -- Richard Huxton Archonet Ltd -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_restore problem
Bradley Miller wrote: So in the current version I'm running (7.4.6) and I do a pg_dump I have to then manually manipulate the order by doing a -l to get a table of contents and then reorder (just changing the first number; or the oid also??) just to get it to work right? Does anyone else have these issues? How exactly can I use this on a mission critical app with flaws like this? How do other people work with this? Do they just not dump the files and restore? The problem(s) are only apparent if you define/redefine objects in a certain order. I've tended to encounter them on databases where I've extensively reworked elements (particularly functions/views). In particular, dumping a restored database always seems OK for me. With the -l file, you just need to cut & paste the lines into the correct order. In practice, I tend to just move half-a-dozen lines to the end of the file to get things to work. The crucial bit then is to make sure you keep a backup copy of the working order somewhere - you have no idea how often I've deleted the file as soon as I've finished restoring. Of course, if you have dynamic functions in say perl/tcl and then base views on them there's probably no way for pg_dump to ever figure out the correct dependencies. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem with backup and restore (probaly stupit newb thing)
Can anyone help me out on this (I will need to backup and restore the data base, but am not savy on the proper save and restore syntax). I used the default ones in PGadmin as detailed below and it blew up on pg_restore: restoring data for table "tblaction" pg_restore: ERROR: invalid byte sequence for encoding "UNICODE": 0xe9 CONTEXT: COPY tblaction, line 1799, column value: "Chargé" pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Backup command= D:\Program Files\pgAdmin III\pg_dump.exe -i -h 192.168.123.112 -p 5432 -U postgres -F c -b -v -f "D:\backups\postgrescompressed.backup" wazagua Restorecommand= D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U postgres -d waztest -v "D:\backups\postgrescompressed.backup" Using ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2 Resulting error text = D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U postgres -d waztest -v "D:\backups\postgrescompressed.backup" pg_restore: connecting to database for restore pg_restore: creating DUMP TIMESTAMP DUMP TIMESTAMP pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating FUNCTION plpgsql_call_handler() pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 9; 1255 16207920 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "OWNER" at character 46 Command was: ALTER FUNCTION public.plpgsql_call_handler() OWNER TO postgres; pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating FUNCTION inserttabledata(character varying, character varying) pg_restore: [archiver (db)] Error from TOC entry 10; 1255 16227934 FUNCTION inserttabledata(character varying, character varying) postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "$" at character 94 Command was: CREATE FUNCTION inserttabledata(character varying, character varying) RETURNS boolean AS $_$ declare @clinum ALIAS FOR... pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "OWNER" at character 77 Command was: ALTER FUNCTION public.inserttabledata(character varying, character varying) OWNER TO postgres; pg_restore: creating TABLE tblcase pg_restore: [archiver] Error from TOC entry 1672; 1259 12375856 TABLE tblcase postgres pg_restore: [archiver] could not set default_tablespace to "": ERROR: unrecognized configuration parameter "default_tablespace" pg_restore: [archiver] could not set default_with_oids: ERROR: unrecognized configuration parameter "default_with_oids" pg_restore: creating TABLE tblexportmarkedrecords pg_restore: creating VIEW case_marked_exp pg_restore: creating TABLE tbllocation pg_restore: creating VIEW casenumber_wrong_loc pg_restore: creating TABLE tblcdacases pg_restore: creating TABLE tblcompany pg_restore: creating TABLE tbllastexportedcaseid pg_restore: creating VIEW casesforcda pg_restore: creating TABLE tblassociate pg_restore: creating VIEW csi_associates pg_restore: creating VIEW dupe_cli_case_incda pg_restore: creating VIEW dupecasenumbers pg_restore: creating VIEW duped_assoc pg_restore: creating TABLE tblaction pg_restore: creating TABLE tblcasesource pg_restore: creating TABLE tblcasetype pg_restore: creating TABLE tblcustomer pg_restore: creating TABLE tbldistrict pg_restore: creating TABLE tbldivision pg_restore: creating TABLE tblethnicity pg_restore: creating TABLE tblidentificationtype pg_restore: creating TABLE tbljobtitle pg_restore: creating TABLE tblmerchandise pg_restore: creating TABLE tblmethod pg_restore: creating TABLE tblmilitarybranch pg_restore: creating TABLE tblmilitarystatus pg_restore: creating TABLE tbloffensetype pg_restore: creating TABLE tblotherperson pg_restore: creating TABLE tblpatrontype pg_restore: creating TABLE tblregion pg_restore: creating TABLE tblsex pg_restore: creating TABLE tblstaffexec pg_restore: creating TABLE tblstatus pg_restore: creating TABLE tblsubjecttype pg_restore: creating TABLE tbluser pg_restore: creating TABLE tblworktype pg_restore: creating VIEW viwcaseexport pg_restore: creating VIEW nrma_view pg_restore: WARNING: column "affiliatecode" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "affilateincidentnumber" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "middlename" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "address" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "city" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "state" has type "unknown" DETAIL:
Re: [SQL] pg primary key bug?
There are not enough spaces available in the column to allow for the number of DOC_IDs. There are three spaces, allowing for at most four occurrences of DOC_ID, but there are eleven. --Rick Michael Glaesemann <[EMAIL PROTECTED]To: pginfo <[EMAIL PROTECTED]> > cc: pgsql-sql@postgresql.org Sent by: Subject: Re: [SQL] pg primary key bug? [EMAIL PROTECTED] tgresql.org 02/03/2005 09:14 AM On Feb 3, 2005, at 21:53, pginfo wrote: > I am using pg 7.4.5 on RedHat AS 3.0. > sklad21=# \d a_constants_str > Table "public.a_constants_str" > Column | Type | Modifiers > +---+--- > constname | character varying(30) | not null > fid| integer | not null > constvalue | character varying(30) | > Indexes: >"a_constants_str_pkey" primary key, btree (constname, fid) > > sklad21=# select * from a_constants_str ; > constname | fid | constvalue ---+-+- > AACCGRID | 0 | SOF_3 > AKLTYPID | 0 | SOF_3 > ADARID| 0 | SOF_2 > AOBLASTID | 0 | SOF_6 > AUSERID | 0 | SOF_17 > ANMGRID | 0 | SOF_21 > LOCAID| 0 | SOF_41 > DOCID | 0 | SOF_1585254 > DOCPLAID | 0 | SOF_1052900 > AKLIID| 0 | SOF_18740 > DOCRID| 0 | SOF_2268142 > DOCPOGPLA | 0 | SOF_324586 > DOCID | 0 | SOF_1585254 > DOCID | 0 | SOF_1585254 > DOCID | 0 | SOF_1585254 > AKLGRID | 0 | SOF_45 > DOCID | 0 | SOF_1585254 > DOCID | 0 | SOF_1585254 > ASETUPID | 0 | SOF_4605 > DOCID | 0 | SOF_1585254 > DOCID | 0 | SOF_1585254 > TDOCID| 0 | SOF_337 > TDOCRID | 0 | SOF_19450 > DOCID | 0 | SOF_1585254 > DOCID | 0 | SOF_1585254 > AGRADID | 0 | SOF_256 > DOCID | 0 | SOF_1585254 > ASLUID| 0 | SOF_46 > NASTRF| 0 | SOF_88 > ANOMID| 0 | SOF_1200 > (30 rows) > > > Pls., see the records with 'DOCID' and note we have primary key > defined. It's unclear from just this data, but there's a chance that there are varying numbers of spaces after 'DOCID', which would appear as separate values for the index, even though they aren't readily apparent. Could you show us the results of the following query? select constname, length(constname) as constname_length from a_constants_str; Here's another one which would show if PostgreSQL is treating them equally: select constname, count(constname) from a_constants_str; The results of these queries might shed some light on the issue. Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(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] PL/PgSQL - returning multiple columns ...
Perfect, worked like a charm ... but the RETURNS still needs to be a
SETOF, other then that, I'm 'away to the races' ... thanks :)
On Wed, 2 Feb 2005, George Weaver wrote:
Hi Marc,
One option is to create a simple data type and return the rowtype of the
datatype
eg CREATE TYPE tserverload AS ("server_name" text, "load_avg" int4);
CREATE FUNCTION getserverload()
RETURNS tserverload
AS
'DECLARE
r tserverload%rowtype;
etc.
You would then return r, comprised of r.server_name and r.load_avg.
George
- Original Message - From: "Marc G. Fournier"
<[EMAIL PROTECTED]>
To:
Sent: Wednesday, February 02, 2005 3:10 PM
Subject: [SQL] PL/PgSQL - returning multiple columns ...
I have a function that I want to return 'server_name, avg(load_avg)' ... if
I wanted to return matching rows in a table, I can do a 'setof ',
with a for loop inside ... but what do I set the 'RETURNS' to if I want to
return the results of query that returns only two fields of a table, or, in
the case of the above, one column and oen 'group by' column?
thanks ...
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] problem with backup and restore (probaly stupit newb thing)
Joel Fradkin wrote: Can anyone help me out on this (I will need to backup and restore the data base, but am not savy on the proper save and restore syntax). I used the default ones in PGadmin as detailed below and it blew up on pg_restore: restoring data for table "tblaction" pg_restore: ERROR: invalid byte sequence for encoding "UNICODE": 0xe9 CONTEXT: COPY tblaction, line 1799, column value: "Chargé" pg_restore: [archiver (db)] error returned by PQendcopy pg_restore: *** aborted because of error Looking at your output, that's not the only error. Backup command= D:\Program Files\pgAdmin III\pg_dump.exe -i -h 192.168.123.112 -p 5432 -U postgres -F c -b -v -f "D:\backups\postgrescompressed.backup" wazagua Restorecommand= D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U postgres -d waztest -v "D:\backups\postgrescompressed.backup" Using ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2 Good to see all the information needed - means we don't need to make any guesses. Resulting error text = D:\Program Files\pgAdmin III\pg_restore.exe -i -h 192.168.123.112 -p 5432 -U postgres -d waztest -v "D:\backups\postgrescompressed.backup" pg_restore: connecting to database for restore pg_restore: creating DUMP TIMESTAMP DUMP TIMESTAMP pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating FUNCTION plpgsql_call_handler() pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 9; 1255 16207920 FUNCTION plpgsql_call_handler() postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "OWNER" at character 46 Command was: ALTER FUNCTION public.plpgsql_call_handler() OWNER TO postgres; OK - let's start here. The syntax here is fine, I just tested it on an 8.0 database. Are you sure the DB you are restoring to is version 8? pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating FUNCTION inserttabledata(character varying, character varying) pg_restore: [archiver (db)] Error from TOC entry 10; 1255 16227934 FUNCTION inserttabledata(character varying, character varying) postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "$" at character 94 Command was: CREATE FUNCTION inserttabledata(character varying, character varying) RETURNS boolean AS $_$ This is odd too - another version 8 feature. I've skipped to the end here: pg_restore: restoring data for table "tblaction" pg_restore: ERROR: invalid byte sequence for encoding "UNICODE": 0xe9 CONTEXT: COPY tblaction, line 1799, column value: "Chargé" Well, it's complaining that the data you're restoring isn't unicode (UTF-8). Was the database you dumped from set up to store UTF-8 or was it SQL-ASCII or ISO? I suppose it might be something to do with a linux<=>windows transfer, but the place to start is running "psql -l" on the server. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] PL/PgSQL - returning multiple columns ...
On Thu, 3 Feb 2005 12:48:11 -0400 (AST), Marc G. Fournier <[EMAIL PROTECTED]> wrote: Perfect, worked like a charm ... but the RETURNS still needs to be a SETOF, other then that, I'm 'away to the races' ... thanks :) No SETOF necessary : CREATE TYPE mytype AS ( number INTEGER, blah TEXT ); CREATE OR REPLACE FUNCTION myfunc( INTEGER ) RETURNS mytype LANGUAGE plpgsql AS $$ DECLARE _retval mytype; BEGIN _retval.number=$1; _retval.blah='yeah'; RETURN _retval; END;$$; SELECT myfunc(22); myfunc --- (22,yeah) (1 ligne) SELECT * FROM myfunc(22); number | blah +-- 22 | yeah (1 ligne) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] problem with backup and restore (probaly stupit newb thing)
Richard Huxton writes: > Joel Fradkin wrote: >> ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, >> compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2 > OK - let's start here. The syntax here is fine, I just tested it on an > 8.0 database. Are you sure the DB you are restoring to is version 8? Looks to me like he's trying to use 8.0 pg_dump/pg_restore to reload into a 7.4 server. This definitely won't work without specifying --disable-dollar-quoting to pg_dump; and if you care about restoring object ownership correctly, also --use-set-session-authorization. I don't recall if there are any other gotchas. The unicode issue I'm not sure about. Perhaps the original database was SQL_ASCII encoding and so was allowed to contain byte sequences that aren't legal unicode? regards, tom lane ---(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] plpgsql functions to 'C' functions
On Thu, Feb 03, 2005 at 05:31:47AM -0800, Peter Manchev wrote: > I believe the functionality I need (hiding the function code from users) Why do you want to hide the function code? Does it contain sensitive data? As I asked before, what problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_restore problem
Interestingly, I made a new database on my test server and then was able to do a pg_dump from my mac box to the test server and I think it got just about everything . . . I've got some constraint issues and other oddities happening, but at least my functions came in fine. I used the pipe command to pipe it directly to the server rather than using pg_restore. Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
Re: [SQL] Information about the command SQL " create synonym".
On Tue, 12 Oct 2004 15:04:14 +0200, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hello ! > > I am trying to find some informations about the SQL command "create > synonym". > > The command "create synonym" does not exit in the Postgres database. > > I had tested with the latest version (postgres8.0 beta3) and this command > is not present. > > I supposed that this command "create synnonym" is an extention of the SQL92 > on the other database like Oracle, Informix etc.. > > Could you say if this command will be implemanted in a future version of a > postgres database ? > > Best regards. > > Hi Gerald. Much like yourself, I have been searching for a way to create synonyms in Postgres. I think that I have found a hack / solution to our problem. Please bare in mind that I have not had an opportunity to fully test this from every angle, but the basics of SELECT, INSERT, UPDATE, and DELETE all work just fine. What I did, was to make use of the Postgres inheritance feature. The documentation's example uses this to create a new table that adds a new column/field to an existing table. However it seems quite happy to let you inherit without changing the structure of the original table at all. This in turn effectively creates an alias. For example, if you create a table as follows ... CREATE TABLE foo (bar int not null); but you need a synonym called "gerald", then you could do the following ... CREATE TABLE gerald () INHERITS (foo); You can now use the object "gerald" to do everything (that I tried) you want with "foo". I would love to hear from the developers, or other gurus, if there are any serious issues with doing this. Will it create any serious performance problems, or will there be functionality gaps between a real table and an inherited table that might limit the usefulness of this workaround? Best of luck, Philip ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] problem with backup and restore (probaly stupit newb thing)
I am not running version 8 (I did try this on the linux box as well, but the version I documented was the server is linux and the client is XP). I ran it from PG admin in both cases, maybe I need to just run from the command line on the linux box (this is fine as long as I can backup the file and restore it if need be). I did the dump and restore from the PGadminIII program so maybe the data base is not (UTF-8). I am new to this so I do not know how to determine and set the types so they match up. I did a create database and am running a .net app to read the data from MSSQL and add it to Postgres. If anyone know a quick to determine this and what the syntax mods are for the dump and restore I would be very happy, if not I can play around. Thanks so much for all the help, maybe I should load version 8 (I was not sure there were rpms for fedora 3 that worked, I tried to load it and was obviously not doing it correctly). Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, February 03, 2005 1:17 PM To: Richard Huxton Cc: Joel Fradkin; pgsql-sql@postgresql.org Subject: Re: [SQL] problem with backup and restore (probaly stupit newb thing) Richard Huxton writes: > Joel Fradkin wrote: >> ServerVersion: 07.03.0200 PostgreSQL 7.4.6 on i386-redhat-linux-gnu, >> compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2 > OK - let's start here. The syntax here is fine, I just tested it on an > 8.0 database. Are you sure the DB you are restoring to is version 8? Looks to me like he's trying to use 8.0 pg_dump/pg_restore to reload into a 7.4 server. This definitely won't work without specifying --disable-dollar-quoting to pg_dump; and if you care about restoring object ownership correctly, also --use-set-session-authorization. I don't recall if there are any other gotchas. The unicode issue I'm not sure about. Perhaps the original database was SQL_ASCII encoding and so was allowed to contain byte sequences that aren't legal unicode? regards, tom lane ---(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] Calendar Function
Muhyiddin A.M Hayat wrote:
Ok, thanks
But if i would like to display date in one Month,
e.g :
date in feb 2005
You can do that in Pg date arithmetic:
# select '1 oct 2004'::date + '1 month'::interval - '1 day'::interval;
?column?
-
2004-10-31 00:00:00
(1 row)
# select '1 nov 2004'::date + '1 month'::interval - '1 day'::interval;
?column?
-
2004-11-30 00:00:00
(1 row)
# select '1 feb 2004'::date + '1 month'::interval - '1 day'::interval;
?column?
-
2004-02-29 00:00:00
(1 row)
Thus, given the original response to your question:
select * from calendar('1 feb 2004', ( '1 feb 2004'::date +
'1 month'::interval - '1 day'::interval )::date);
--
Jeff Boes vox 269.226.9550 ext 24
http://www.nexcerpt.comfax 269.349.9076
...Nexcerpt... Extend your Expertise
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Information about the command SQL " create synonym".
> Much like yourself, I have been searching for a way to create synonyms > in Postgres. I think that I have found a hack / solution to our > problem. ... > What I did, was to make use of the Postgres inheritance feature. > This in turn effectively creates an alias: > CREATE TABLE foo (bar int not null); > > but you need a synonym called "gerald", then you could do the following ... > > CREATE TABLE gerald () INHERITS (foo); > I would love to hear from the developers, or other gurus, if there are > any serious issues with doing this. Primary keys and foreign keys will likely show unexpected behaviour (as in being out of sync between foo and gerald and any other "alias" child tables of foo). Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] pg_restore problem -- MORE?!?
This is getting ridiculous . . . I dump from the database into another one (just for grins) on the same server to a different name and I don't even get all the info? My functions are coming through fine (finally) but now I've got missing tables and missing data. Could someone point me somewhere for some more info? I'm having Postgres 8 installed on my production server now and I'll see if I can do a complete dump directly to it and see what happens. Bradley Miller NUVIO CORPORATION Phone: 816-444-4422 ext. 6757 Fax: 913-498-1810 http://www.nuvio.com [EMAIL PROTECTED]
[SQL] getting back autonumber just inserted
I could have sworn I kept a copy of prior emails that discussed how to get back a value that was just inserted into a autonumber (or in postgresql case a sequence number) any help will be appreciated thanks Lori ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] getting back autonumber just inserted
On Thu, 2005-02-03 at 16:16, lorid wrote:
> I could have sworn I kept a copy of prior emails that discussed how to
> get back a value that was just inserted into a autonumber (or in
> postgresql case a sequence number)
If you know the name of the sequence the number came from you can use
currval():
insert into table1 (info) values ('abc');
select currval('table1seq');
Assuming table1seq is the name of the sequence here.
In 8.0 there's a function to do this (I'm not sure of the name, but a
quick search of the 8.0 docs should turn it up.)
---(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] getting back autonumber just inserted
On Feb 3, 2005, at 5:16 PM, lorid wrote: I could have sworn I kept a copy of prior emails that discussed how to get back a value that was just inserted into a autonumber (or in postgresql case a sequence number) See here: http://www.postgresql.org/docs/8.0/interactive/functions- sequence.html#FUNCTIONS-SEQUENCE-TABLE Sean ---(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] getting back autonumber just inserted
Perhaps you meant: http://www.postgresql.org/docs/8.0/interactive/functions-info.html#FUNCTIONS-INFO-SCHEMA-TABLE in particular |pg_get_serial_sequence|(table_name, column_name) Sean Davis wrote: On Feb 3, 2005, at 5:16 PM, lorid wrote: I could have sworn I kept a copy of prior emails that discussed how to get back a value that was just inserted into a autonumber (or in postgresql case a sequence number) See here: http://www.postgresql.org/docs/8.0/interactive/functions- sequence.html#FUNCTIONS-SEQUENCE-TABLE Sean ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Edmund Bacon <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Determining Rank
I have a query that shows the top N count(*)'s. So it's basically: select some_val, count(*) from big_table group by some_val order by count(*) limit 50 Now, I would like to have the rank included in the result set. The first row would be 1, followed by 2, etc. all the way to 50. I can do this in PHP, but stuffing it into the DB query makes other things easier. Any ideas? Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ 312-560-1574 ---(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] Determining Rank
On Thu, Feb 03, 2005 at 09:06:36PM -0600, Don Drake wrote:
> select some_val, count(*)
> from big_table
> group by some_val
> order by count(*)
> limit 50
>
> Now, I would like to have the rank included in the result set. The
> first row would be 1, followed by 2, etc. all the way to 50.
Maybe use a temporary sequence?
CREATE TEMPORARY SEQUENCE rank_seq;
SELECT nextval('rank_seq') AS rank, *
FROM (SELECT some_val, count(*)
FROM big_table
GROUP BY some_val
ORDER BY count(*)
LIMIT 50) AS s;
DROP SEQUENCE rank_seq;
I don't know if row order is guaranteed to survive a subquery,
however.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(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] Determining Rank
On Feb 4, 2005, at 12:06, Don Drake wrote:
I have a query that shows the top N count(*)'s.
So it's basically:
select some_val, count(*)
from big_table
group by some_val
order by count(*)
limit 50
Now, I would like to have the rank included in the result set. The
first row would be 1, followed by 2, etc. all the way to 50.
There are a couple of different ways to go about this. One is just to
append an extra column that's basically a line number, but I find it
doesn't handle ties very elegantly. The following example uses a
correlated subquery using HAVING to determine the rank as "the number
of items that have a total quantity greater than the current item + 1".
Note that items bar and baz have exactly the same totals and are tied,
while the rank of bat shows that there are 3 items that have totals
greater than bat.
Joe Celko's "SQL for Smarties" has a bunch of things like this in it.
I've found it quite helpful.
Hope this helps.
Michael Glaesemann
grzm myrealbox com
create table items (
item text not null
, qty integer not null
) without oids;
insert into items (item, qty) values ('foo', 1);
insert into items (item, qty) values ('foo', 2);
insert into items (item, qty) values ('foo', 1);
insert into items (item, qty) values ('foo', 3);
insert into items (item, qty) values ('foo', 3);
insert into items (item, qty) values ('foo', 20);
insert into items (item, qty) values ('foo', 1);
insert into items (item, qty) values ('bar', 3);
insert into items (item, qty) values ('bar', 1);
insert into items (item, qty) values ('bar', 3);
insert into items (item, qty) values ('bar', 13);
insert into items (item, qty) values ('baz', 2);
insert into items (item, qty) values ('baz', 4);
insert into items (item, qty) values ('baz', 14);
insert into items (item, qty) values ('bat', 3);
insert into items (item, qty) values ('bat', 4);
select item, sum(qty) as tot_qty
from items
group by item
order by tot_qty desc;
item | tot_qty
--+-
foo | 31
bar | 20
baz | 20
bat | 7
(4 rows)
select i1.item
, i1.tot_qty
, ( select count(*)
from (
select item
, sum(qty) as tot_qty
from items
group by item
having sum(qty) > i1.tot_qty
) as i2
) + 1 as rank
from (
select item
, sum(qty) as tot_qty
from items
group by item
) as i1
order by i1.tot_qty desc;
item | tot_qty | rank
--+-+--
foo | 31 |1
bar | 20 |2
baz | 20 |2
bat | 7 |4
(4 rows)
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] plpgsql functions to 'C' functions
I believe the functionality I need (hiding the function code from users) will be provided with the implementation of the the following item from the Postgresql todo list: SERVER-SIDE LANGUAGES -- Add Oracle-style packages May be another future functionality will also help out, especially if the permission system can hide all objects but the function signatures from non-authorized: -- Set proper permissions on non-system schemas during db creation Until then, I have to figure out a way to implement it with the resources available... like (hand) re-coding the plpgsql functions to 'C' functions :/ Here is where I'd like to get your expert opinions (thank you very much in advance for all your efforts), so I can get started fast. Thanks, Peter From: Michael Fuhr <[EMAIL PROTECTED]> To: Peter Manchev <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] plpgsql functions to 'C' functions Date: Wed, 2 Feb 2005 23:21:07 -0700 ... On Wed, Feb 02, 2005 at 05:57:10AM -0800, Peter Manchev wrote: > I would like to convert all my plpgsql functions to their 'C' equivalent > coded functions. Why? What problem are you trying to solve? > Does anyone have experience in this matter? See "C-Language Functions" in the documentation: http://www.postgresql.org/docs/8.0/static/xfunc-c.html If you're having a specific difficulty then please show the code you're using, explain what you want it to do, and describe what acutally happens. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
