Re: [SQL] No triggers visible for different user in information_schema.triggers
Am Donnerstag, 17. Februar 2005 18:43 schrieb Michael Fuhr: SQL:2003 (Working Draft) says: Identify the triggers on tables in this catalog that are accessible to a given user or role. Apparently PostgreSQL implements the SQL:1999 specification of information_schema.triggers. While that is true, superuserdom consistently does not play any role in the information schema for the purpose of figuring out what is accessible to a certain user. This may be considered a bug, but it would also create inconsistencies because the state accessible to is defined by other information schema tables where superuserdom cannot be represented. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] trrouble inserting stuff like é
I wrote a program to read my database (from MSSQL) and write it to Postgres.
It appeared to work great the first time I ran it, but the database was
SQL_ASCII (It defaulted to this when I created it from the command line on
the linux box using createdb.
When I tried to restore it I used PGAdmin on my XP machine which created a
Unicode database.
I was unable to restore the SQL_ASCII to the Unicode, so I re-ran my import
on a Unicode database.
Now I get all kinds of errors from the .net program (which I have write out
the actual SQL on each error).
When I cut and paste the SQL into PGADMIN it runs ok, so it only gives an
error using .net ODBC.
Any one have any clues for me to follow?
Here is an example of a SQL line that did not run in .net.
insert into
tblSuspectedActivity(ClientNum,ID,Value,IsDeleted,PresentationID)
values('FREN',4,'Paiement à
account',False,2)
Joel Fradkin
---(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] trrouble inserting stuff like é
Hi,
I use LATIN1 encoding and it works fine with accented characters. So try
creating your db with -E LATIN1 switch. Or even initdb -E LATIN1 if you wan't
your dbs default to that.
Best regards,
Aarni
On Friday 18 February 2005 16:59, you wrote:
I wrote a program to read my database (from MSSQL) and write it to
Postgres. It appeared to work great the first time I ran it, but the
database was SQL_ASCII (It defaulted to this when I created it from the
command line on the linux box using createdb.
When I tried to restore it I used PGAdmin on my XP machine which created a
Unicode database.
I was unable to restore the SQL_ASCII to the Unicode, so I re-ran my import
on a Unicode database.
Now I get all kinds of errors from the .net program (which I have write out
the actual SQL on each error).
When I cut and paste the SQL into PGADMIN it runs ok, so it only gives an
error using .net ODBC.
Any one have any clues for me to follow?
Here is an example of a SQL line that did not run in .net.
insert into
tblSuspectedActivity(ClientNum,ID,Value,IsDeleted,PresentationID)
values('FREN',4,'Paiement à
account',False,2)
Joel Fradkin
---(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
--
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] RE: [SQL] trrouble inserting stuff like é
I dont think it has anything to do with the data base as I can add fine
from pgadmin its an odbc question.
How do I tell the connection to use Unicode?
It worked ok using SQL_ASCHII also with the driver, but I thought if we get
a Chinese client down the road I couldnt store those chars unless its
Unicode?
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.
© 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: Aarni Ruuhimäki [mailto:[EMAIL PROTECTED]
Sent: Friday, February 18, 2005 10:25 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] trrouble inserting stuff like é
Hi,
I use LATIN1 encoding and it works fine with accented characters. So try
creating your db with -E LATIN1 switch. Or even initdb -E LATIN1 if you
wan't
your dbs default to that.
Best regards,
Aarni
On Friday 18 February 2005 16:59, you wrote:
I wrote a program to read my database (from MSSQL) and write it to
Postgres. It appeared to work great the first time I ran it, but the
database was SQL_ASCII (It defaulted to this when I created it from the
command line on the linux box using createdb.
When I tried to restore it I used PGAdmin on my XP machine which created a
Unicode database.
I was unable to restore the SQL_ASCII to the Unicode, so I re-ran my
import
on a Unicode database.
Now I get all kinds of errors from the .net program (which I have write
out
the actual SQL on each error).
When I cut and paste the SQL into PGADMIN it runs ok, so it only gives an
error using .net ODBC.
Any one have any clues for me to follow?
Here is an example of a SQL line that did not run in .net.
insert into
tblSuspectedActivity(ClientNum,ID,Value,IsDeleted,PresentationID)
values('FREN',4,'Paiement à
account',False,2)
Joel Fradkin
---(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
--
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Comments on subquery performance
Hi Richard and group, Thanks for the alternative approach. The code is certainly cleaner and easier to follow, but I do have a couple outer joins for fields #2 and #3 that could contain null values that are not captured in your example. Also, I ran an 'explain query' and the performance differences were negligable. Any further thoughts or should I just stick with what I have and move on? Thanks in advance. Cheers, Jim From: Richard Huxton dev@archonet.com To: T- Bone [EMAIL PROTECTED] CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Comments on subquery performance Date: Thu, 17 Feb 2005 19:15:14 + T- Bone wrote: (second attempt in two days to post this message...I appologise if for some reason a duplicate appears) Hello all, I created a query that contains two subqueries and joins and would like some feedback on whether: 1) this approach is logical; and, 2) if this is an optimal approach (performance wise) to return the records I seek. Well you could just do: SELECT l.*, c1.catname, c2.catname, c3.catname FROM tbl_listing l, tbl_categories c1, tbl_categories c2, tbl_categories c3 WHERE l.catid1 = c1.catid AND l.catid2 = c2.catid AND l.catid3 = c3.catid -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ Scan and help eliminate destructive viruses from your inbound and outbound e-mail and attachments. http://join.msn.com/?pgmarket=en-capage=byoa/premxAPID=1994DI=1034SU=http://hotmail.com/encaHL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN® Premium right now and get the first two months FREE*. ---(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] Comments on subquery performance
Yea we examined it here as a group as we are facing the same kind of stuff and found exactly the same thing. It does what MSSQL called a Cartesian join, and ran no faster other then removing the outer join logic. Using a regular join statement and only inner joins was the same speed and I think little less confusing when you have several joins as our system does. We may have to look at re-engineering our system as the original design has dozens of table whith a field set like ID, Value to hold stuff like apprehension type, jobtitle etc. So we hold a main record with dozens of id's (and joins when reporting). Joel Fradkin ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] RE: [SQL] trrouble inserting stuff like é
Looking deeper into the matter (which I admit I am a bit ignorant on) I
think you hit the nail on the head.
Coming from MSSQL which is using Latin I may have to use Latin1.
It works ok as SQL_ASCHII, but lower does not work, so hopefully using
LATIN1 I can store the chars and also get lower to work etc.
Thanks so much for the help.
I have been very confused as I could create a Unicode db and even save the
French values using pgadmin or .net connection, but the odbc would not work.
I hope it works ok with the LATIN1.
-Original Message-
From: Aarni Ruuhimäki [mailto:[EMAIL PROTECTED]
Sent: Friday, February 18, 2005 10:25 AM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] trrouble inserting stuff like é
Hi,
I use LATIN1 encoding and it works fine with accented characters. So try
creating your db with -E LATIN1 switch. Or even initdb -E LATIN1 if you
wan't
your dbs default to that.
Best regards,
Aarni
On Friday 18 February 2005 16:59, you wrote:
I wrote a program to read my database (from MSSQL) and write it to
Postgres. It appeared to work great the first time I ran it, but the
database was SQL_ASCII (It defaulted to this when I created it from the
command line on the linux box using createdb.
When I tried to restore it I used PGAdmin on my XP machine which created a
Unicode database.
I was unable to restore the SQL_ASCII to the Unicode, so I re-ran my
import
on a Unicode database.
Now I get all kinds of errors from the .net program (which I have write
out
the actual SQL on each error).
When I cut and paste the SQL into PGADMIN it runs ok, so it only gives an
error using .net ODBC.
Any one have any clues for me to follow?
Here is an example of a SQL line that did not run in .net.
insert into
tblSuspectedActivity(ClientNum,ID,Value,IsDeleted,PresentationID)
values('FREN',4,'Paiement à
account',False,2)
Joel Fradkin
---(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
--
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] RE: [SQL] trrouble inserting stuff like é
On Feb 18, 2005, at 11:15 AM, Joel Fradkin wrote: How do I tell the connection to use Unicode? Try SET client_encoding TO 'UNICODE'; http://www.postgresql.org/docs/8.0/interactive/sql-set.html But it should default to the database encoding, so I'm not sure if that is the problem. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] RE: [SQL] RE: [SQL] trrouble inserting stuff like é
I did that and it did not work. On the .net driver I had to do it on the connect string. I also just tested latin and see the lower and upper do not work on the French chars, while upper and lower do work on the French chars on a Unicode database. So the problem is getting the odbc driver to work with Unicode. Worst case I will just use SQL_ASCHII, I am guessing the ODBC driver does not have a encoding command like the .net one does. 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. © 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: John DeSoi [mailto:[EMAIL PROTECTED] Sent: Friday, February 18, 2005 3:42 PM To: Joel Fradkin Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org Subject: Re: [SQL] RE: [SQL] trrouble inserting stuff like é On Feb 18, 2005, at 11:15 AM, Joel Fradkin wrote: How do I tell the connection to use Unicode? Try SET client_encoding TO 'UNICODE'; http://www.postgresql.org/docs/8.0/interactive/sql-set.html But it should default to the database encoding, so I'm not sure if that is the problem. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] what does ONLY do
On Fri, 18 Feb 2005, Bret Hughes wrote: I can't seem to find an explanation of what adding ONLY does for an sql statement for instance : ALTER TABLE [ONLY] ADD COLUMN ... or what ever. Does anyone have a pointer to docs on this. I am simply curious since there is obviously something I am missing. Using the ALTER TABLE page as an example... name The name (possibly schema-qualified) of an existing table to alter. If ONLY is specified, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are updated. * can be appended to the table name to indicate that descendant tables are to be altered, but in the current version, this is the default behavior. (In releases before 7.1, ONLY was the default behavior. The default can be altered by changing the configuration parameter sql_inheritance.) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] what does ONLY do
On Fri, 2005-02-18 at 21:15, Stephan Szabo wrote: On Fri, 18 Feb 2005, Bret Hughes wrote: I can't seem to find an explanation of what adding ONLY does for an sql statement for instance : ALTER TABLE [ONLY] ADD COLUMN ... or what ever. Does anyone have a pointer to docs on this. I am simply curious since there is obviously something I am missing. Using the ALTER TABLE page as an example... name The name (possibly schema-qualified) of an existing table to alter. If ONLY is specified, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are updated. * can be appended to the table name to indicate that descendant tables are to be altered, but in the current version, this is the default behavior. (In releases before 7.1, ONLY was the default behavior. The default can be altered by changing the configuration parameter sql_inheritance.) Well, (*^$%. I have read that very page, parts if it several times. B I was adding a column to a table, thought about it again and rather than look again I decided to ask. I hate that. I pride myself on not asking questions that can be easily answered but blew it this time. Thanks for the pointer. And for doing so without making me feel stupider than necessary. Bret ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
