Re: [SQL] how to turn off autocommit in psql
On 26/03/2004 01:25 Kemin Zhou wrote: I search far and wide and found a lot of disscussions about the autocommit, but none about how to do it. After reading 50 pages, my brain is numb. Could any one give me a simple help? Thanks Kemin Use tranactions. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Newbie Query question
Hi all, I have the following problem which I will illustrate with a simplified example. I have two tables A and B. Both tables contain three columns named "objid", "owner", and "val" all of type integer. I want to select all records in A for which A.val=0 and all records in B for which both B.val=0 and B.owner=A.objid. I thought that the following query would work: SELECT * FROM A,B WHERE (A.VAL = 0) OR (B.VAL = 0 AND B.OWNER = A.OBJID); However, this query does not give me the result I expected. It appears that the database engine first calculates the cartesian product of the tables A and B and then evaluates the query. Hence, I get multiple matches for A.VAL=0 (N times the number of matching records in table A, where N is the number of records in table B). I had hoped I could somehow coerce the database engine to only use table A when evaluating the first part of the query, and use both tables A and B when evaluating the second part of the query. Is there any way to do this, other than using UNION?? Kind regards, Marcel Loose (mailto loose at astron dot nl) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] could not create shared memory segment: Invalid argument
I'am also getting this same problem with 10.3.3 Server. Worked fine with 10.3.2. kern.sysv.shmmax: 4194304 kern.sysv.shmmin: 1 kern.sysv.shmmni: 32 kern.sysv.shmseg: 8 kern.sysv.shmall: 1024 Those are the Values i get from the command posted. Have you figured out a fix for this error? Thanks. beyaNet Consultancy wrote: > *Hi, > I have just installed the latest version of OS X panther (10.3.3) > and > am now getting the following error message: > > postgres$ /usr/local/pgsql/bin/postmaster -i -D > /usr/local/pgsql/data > FATAL: could not create shared memory segment: Invalid argument > DETAIL: Failed system call was shmget(key=5432001, size=10444800, > 03600). > HINT: This error usually means that PostgreSQL's request for a > shared > memory segment exceeded your kernel's SHMMAX parameter. You can > either > reduce the request size or reconfigure the kernel with larger > SHMMAX. > To reduce the request size (currently 10444800 bytes), reduce > PostgreSQL's shared_buffers parameter (currently 1000) and/or its > max_connections parameter (currently 100). > If the request size is already small, it's possible that it is > less than your kernel's SHMMIN parameter, in which case raising the > request size or reconfiguring SHMMIN is called for. > The PostgreSQL documentation contains more information about > shared memory configuration. > > Any ideas on how i can resolve this issue? > > many thanks in advance > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster * -- xvx Posted via http://www.webservertalk.com View this thread: http://www.webservertalk.com/message151918.html ---(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] Newbie Query question
> However, this query does not give me the result I expected. It appears that > the database engine first calculates the cartesian product of the tables A > and B and then evaluates the query. Hence, I get multiple matches for Yup.. WHERE filters the results of the join. > Is there any way to do this, other than using UNION?? This is what you want. I think you could come out with what you want in other ways, but this is by far the most appropriate. ---(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] Import from Ms Excel
Kumar wrote: Dear Friends, Is possible to import data from MS Excel sheet into postgres database 7.3.4 running on Linux 7.2 Install the postgress ODBC drivers. Create a new access database. Create two linked tabels, one to a table in de database and one to the excel sheet. Use a insert into query to transfer the data. -- Hans de Bruin http://eratosthenes.xs4all.nl ---(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] Database reporting tool
Hi Jerome - We have developed a web based ad hoc report builder (LGX Ad Hoc) that seems to fit your requirements rather well at first glance. http://www.logixml.com/products/AdHoc/adhoc.htm LGX Ad Hoc is a zero footprint .NET and XML based web application. It provides both an end-user and administrator module. End-users are provided an easy-to-use wizard that allows them the ability to build their reports off of business objects (tables, views) that you can easily set up in a meta data layer. With the wizard users can do things like: * Add charts to their report * Choose dynamic sorting for columns * Choose Export options like PDF, Excel, Word * Set up paging and printing options * Build parameters including calendar controls * Add a grouping level to reports for sub or drill down reports LGX Ad Hoc can connect to PostgresSQL via an ODBC driver. We can also work with databases like Oracle, MySQL, DB2, and SQL Server. Thanks Bill Kotraba LogiXML > On Wed, Mar 10, 2004 at 02:17:31PM +0800, [EMAIL PROTECTED] wrote: > > > > i'm using PostgreSQL as my database and now i'm looking for a good > > reporting tools(can do ad-hoc queries & ease of use) > > that can connect to several database instead of PostgreSQL. > > thanks in advance > > see : http://www.openoffice.org > > bye > > Jerome Alet > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Invalid Unicode Character Sequence found
Hi All, One very strange problem within the PostgreSql database. When you issue the following command: select id, title from docs where title like 'z%'; or select id, title from docs where title like 'Z%'; It reports the following error: ERROR: Invalid UNICODE character sequence found (0xc000) I tried the same query on different tables in different databases - gives the same error message. The same error message appears to be even when the table is completely empty! Any ideas? We run postgres 7.3.2 and default charset is UNICODE. Cheers Natasa ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] order of results
Hi, i have a table called "people" (name:varchar, lastname:varchar). i do a select on it: select * from people where name like '%n1%' or lastname like '%l1%'. i would like the results in this order: first the results that satisfy only the (name like '%n1%') condition, then the ones that satisfy only the (lastname like '%l1%') condition and last the results that satisfy both conditions. Is this possible in only one SQL? (note that the search conditions n1 and l1 differ from search to search. TNX, Gregor ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] date_part stored procs
Title: Message
Making me lose my
cool. Here is how it goes:
I have the following
Stored Proc, Which I intend to find the age in months of a
date...
CREATE FUNCTION
interval_months(TIMESTAMP) RETURNS INTEGER AS
' DECLARE docDate ALIAS FOR $1; len
INTEGER; BEGIN SELECT INTO len * FROM (SELECT
((date_part(''year'',age(docDate))*12)+(date_part(''month'',age(docDate AS
a; RETURN len; END' LANGUAGE
'plpgsql';
The problem is that
it does not run and I cannot for the life of me tell why.
I run it with the
following in psql:
select
interval_months(file_date) from (select min(file_date) as file_date from
fileattributes) AS a;
And I get the
folllowing error:
NOTICE: Error
occurred while executing PL/pgSQL function interval_monthsNOTICE: line
5 at select into variablesERROR: parser: parse error at or near
""
I can't see the
error, I've lost my mind It's friday
Tsoloane MoahloliVerity South
Africa (Pty) LtdP +27 (11) 475 1718C +27 (83)
400-2998[EMAIL PROTECTED]http://www.verity.com/Over 80% of the FORTUNE 50 trust Verity to power their Business Portal
and e-Commerce sites.Verity is the leader in the Gartner 2002 Enterprise
Search Magic Quadrant
Disclaimer
The information contained in this communication is
confidential and may be legally privileged. It is intended solely for the use of
the individual or entity to whom it is addressed and others authorized to
receive it. If you are not the intended recipient you are hereby notified that
any disclosure, copying, distribution or taking action in reliance of the
contents of this information is strictly prohibited and may be unlawful. Views
and opinions are those of the sender unless clearly stated as being that of Mimecast
(Pty) Ltd. Mimecast (Pty) Ltd is neither liable for the proper,
complete transmission of the information contained in this communication, nor
any delay in its receipt or that the mail is virus-free. Mimecast (Pty) Ltd is
not liable whatsoever for loss or damage resulting from the opening of this
message and/or attachments and/or the use of the information contained in this
message and/or attachments.
Re: [SQL] date_part stored procs
"Tsoloane Moahloli" <[EMAIL PROTECTED]> writes:
> SELECT INTO len * FROM (SELECT
> ((date_part(''year'',age(docDate))*12)+(date_part(''month'',age(docDate
> AS a;
> The problem is that it does not run and I cannot for the life of me tell
> why.
I think you miscounted parentheses.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] order of results
On Thu, Mar 25, 2004 at 14:23:00 +0100, Gregor Rot <[EMAIL PROTECTED]> wrote: > Hi, > > i have a table called "people" (name:varchar, lastname:varchar). > > i do a select on it: > > select * from people where name like '%n1%' or lastname like '%l1%'. > > i would like the results in this order: > > first the results that satisfy only the (name like '%n1%') condition, > then the ones that satisfy only the (lastname like '%l1%') condition and > last the results that satisfy both conditions. > > Is this possible in only one SQL? > (note that the search conditions n1 and l1 differ from search to search. Yes. You can order by true/false results from conditions to get the results in the desired order. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Invalid Unicode Character Sequence found
"Bulatovic Natasa" <[EMAIL PROTECTED]> writes: > select id, title from docs where title like 'z%'; or > select id, title from docs where title like 'Z%'; > It reports the following error: > ERROR: Invalid UNICODE character sequence found (0xc000) This is fixed in 7.3.6. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] autocommit
This might be a wish list or add feature. Could we add one simple switch to psql (the client front end or the library) --noautocommit? This will require you to type commit at the end of a query. Most of the time I would be using psql to do simple stuff. Some times I need to update databases. I kind get used to the autocommit. When forgot to type BEGIN I some times destroy the content of certain table when updating tables. the I have go for the backup. Is my request reasonable or I have to cultivate a good habit? Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] autocommit
Kemin Zhou <[EMAIL PROTECTED]> writes: > Could we add one simple switch to psql (the client front end or the > library) --noautocommit? psql already has this, see \set AUTOCOMMIT. 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])
