[SQL] Boolean and Bit
Hello All, This is my first post (so be gentle with me)... Is there a searchable archive? I would like suggestions and examples of adding SQL-92 data type BIT compatibility to a PostgreSQL schema. >From the doc's I gather you can "CREATE TYPE bit" with storage int or int4... but I don't know about the input/output for zero and one. Should SQL (ODBC) be able to ask "WHERE bitfield;" or should it ask "WHERE bitfield = 1;" ? Any response gratefully recognized... Keith
[SQL] Re: Boolean and Bit
Josh Berkus wrote: > > > The solution to this is not to use BLOBs, but rather to use file system > handles for the location of the binary data on the server. This way, > all you need is DOS-to-UNIX and UNIX-to-DOS translation for the > filesystem handles, something easily accomplished through > string-manipulation functions or stored procedures. > > -Josh Berkus Do you have an example for implementing this?
[SQL] DROP Column
Is DROP Column implemented in 7.x? Keith
[SQL] Query Limitations
PostgreSQL 6.4 seems to have limitations in Query Length when I "CREATE VIEW" ... is this limit defined further, when I create a query on a query... it seems to compound the queries and reach the limit sooner!! Is this limit programmable? Is it default higher in 7.0? What is the most stable 7.X release? Keith ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Index on View ?
Is it possible (feasible) to create an index on a view. We have a large table and a defined sub-set (view) from this table, would it be possible to keep an index of the sub-set. Keith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Index on View ?
Richard Huxton wrote: > > Indexes on underlying tables should be used though. Difficult to suggest > what indices you might need without knowing the view/tables/queries > involved. As an example I may have an "Invoice" table with several thousand invoices. I could design a query/view "Aged" to get all unpaid invoices greater than 15 days old. I would often look for Invoices per Client and should have an index on Invoice(ClientID). e.g. CREATE INDEX Invoice_ClientID ON Invoice(ClientID); Is there any advantage in having an index on ClientID for the Aged query? e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID); Would this index be continually maintained by the RDBMS or only on lookup? Keith ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Escape Quotes
When using apostrophies the PostgreSQL string seems to like an escape character as follows: update client set code = 'O\'SHEA' where clientid = 2; The ANSI-92 standard seems to suggest that this could/should be handled by ::= ' ::= update client set code = 'O''SHEA' where clientid = 2; Is it possible to get/configure PostgreSQL to handle as within a dleimited string? Keith Gray ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Escape Quotes
Tom Lane wrote: > > Keith Gray <[EMAIL PROTECTED]> writes: > > Is it possible to get/configure PostgreSQL to handle > > as within a dleimited string? > > We already do. > > regression=# select 'O''SHEA'; > ?column? > -- > O'SHEA > (1 row) > > regards, tom lane Sorry, This may be a problem in "ipgsql" then?? ...or is it different in update from select? Keith ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Escape Quotes
> > > Is it possible to get/configure PostgreSQL to handle > > > as within a delimited string? > > > > We already do. > > > > This may be a problem in "ipgsql" then?? > ...or is it different in update from select? The problem is in ipqsql... it doesn't handle update, but does handle select. Both work fine for psql (linux). Keith. ---(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
[SQL] ipsql (was - Escape Quotes)
Roberto Mello wrote: > > What the heck is ipsql?? > > -Roberto ipgsql A Win32 client for interactive Postgres session Keith README Interactive PostgreSQL presents comfortable windows environment to execute sql queries, edit tables data, view tables list and structure, execute sql scripts, etc. This application design on Delphi using PostgreSQL components, which included in Winzeos Library: http://www.zeos.dn.ua/download/winzeos-latest.zip PARAMETERS Connect parameters for Interactive PostgreSQL stored in the ipgsql.ini file in the same directory that ipgsql.exe You can change its manually or using File/Options dialog Parameters short description: [Preferences] PgSQLPort= ; PostgreSQL port number (default 5432) IsAlive=1 ; Open alive queries (default 1) IsCached=0; Use cached updates (default 0) QueryAll=0; Query all records when open (default 0) AutoCommit=0 ; Auto commit updates (default 0) AutoRecovery=0; Auto rollback transaction when ; errors occured (default 1) Login= ; PostgreSQL login(no default) Host= ; Host name (no default) DataBase=; Database name (no default) EXTRA FEATURES Program supports PostgreSQL Large Objects. In postgreSQL database they represented as Oid fields which store LO handle. In IPgSql these fields translate to Blob field. You may store in Blob fields text, images or any binary data. Open blob field editor by double click in grid or choose View/Blob Editor menu item. LICENCING Zeos Library is distributed with the GPL licence and costs you nothing. DOWNLOADS The latest version can be found on: http://www.zeos.dn.ua/download/ipgsql-latest.zip KNOWN BUGS 1. When your sql query crash and AutoRecovery set to false end transaction manually. If AutoRecovery set to true after crash transaction is auto rollbacked. 2. IPgSql works only with PostgreSQL 6.5+ CREDITS - Steve Wei for idea and support designing PostgreSQL components Yours, Sergey Seroukhov, Chief Engineering of Capella Development Group. <[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])
[SQL] Re: where's ALTER TABLE table DROP [ COLUMN ] column???
Bruno Boettcher wrote: > > Hello! > > as far as i can tell, i can add columns to a table, but can't remove > them later on. > > is this true? My thought would be that somebody must have written an automated version of select [all columns except named] from [named table] into droptemp; drop [named table]; select * from droptemp into [named table]; Would this be available or easily written as a "rule" ?? Could anyone familiar with the code comment? -- Keith Gray ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] ODBC 7.1
Greetings All, Is this the correct forum to discuss ODBC driver issues? I have a Linux/PostgreSQL server which has been upgraded to run 7.1 - the main reason was to be able to use the TOAST extensions. We have some documentation attached to fields which is up to 64kb. After upgrading the server I installed the current (7.1) ODBC driver for Win32. It now seems that concurrent table writes are limited? My write times are fairly slow. (significantly slower than 6.4/7.0) Some tables which are updated simultaneously - like sales and general ledger are not staying in synch. Any suggestions would be welcomed (including upgrades) -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] PgAdmin
The install for PgAdmin wont run without MDAC 2.5?? MDAC 2.5 is installed as part of an upgrade(?) to Internet Explorer 5.5 I have IE5.5 but cannot install PgAdmin. What is the exact dependency? Can I force an Install? -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[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])
[SQL] Alias Join Table
If I have the following 'Hierachy' table... Child | Parent -- 1 | 0 2 | 1 3 | 1 4 | 3 5 | 4 6 | 3 7 | 4 How do I return a list 5,4,3,1 ? SELECT a.Child FROM Hierachy AS a, Hierachy AS b WHERE a.Parent = b.Child AND a.Child = 5; ... is obviously a bit simplistic in my approach. -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Beginner's List
Josh Berkus wrote: > > To help remedy this, ... > Can anyone suggest something? > Could we set-up a forum with a product like Request Tracker where a group of experienced users could take questions from a web-based queue? -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(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] ORDER BY case insensitive?
Jason Earl wrote: > > You can, however, create an index like: > > create index MyTable_lower_idx on MyTable > (lower(name)); > > It won't help with your particular query, but it > certainly would help for queries like: > > SELECT * FROM MyTable WHERE lower(name) = 'jason'; > How would PostgreSQL know to use the index MyTable_lower_idx when I do a ... SELECT * FROM MyTable WHERE lower(name) LIKE 'jas%'; -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 2 Selects 1 is faster, why?
Masaru Sugawara wrote: > > You are right. And this type of optimising are not yet implemented. > Tom said it in the prior discussions. > ...but is it true that if you place the filter clause first, the join will not have to complete the whole table? eg. SELECT item.description, stock.available FROM item, stock WHERE item.itemid = '1234' AND item.itemid=stock.itemid; ...would be more efficient than, SELECT item.description, stock.available FROM item, stock WHERE item.itemid=stock.itemid AND item.itemid = '1234'; -- Keith Gray Technical Services Manager Heart Consulting Services P/L mailto:[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] Problem with my query whithout double-quotes
Stephan Szabo wrote: > On Wed, 24 Jul 2002, ROUWEZ Stephane wrote: > > >>Hi, >>My pgsql runs on WinNT Server 4. When I try to >>SELECT nom, prenom FROM individu WHERE numero=2 >>I have : ERROR: Relation "individu" does not exist >>It only works if I write : >>SELECT "Individu"."Nom","Individu"."Prenom" FROM "Individu" WHERE >>"Individu"."NumIndiv"=2 >>Can someone help me ? >> > > It looks like you created the table with double quotes around the > names at which point you should always use double quotes to refer > to it (yes, if the name was "foo" you *can* refer to it as foo, but > you really shouldn't). Your table names are case sensitive. PostgreSQL will make them all lowercase by default, unless you quote them. -- Keith Gray Technical Services Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Timestamp Error - 7.2
We have moved from 7.1 to 7.2 and get the following error when
extracting dates.
Bad timestamp external representation ' '
eg. INSERT INTO mytable VALUES('1', '2001-09-24')
Seems to accept dd/mm/
(What about ISO default?)
--
Keith Gray
Technical Services Manager
Heart Consulting Services
---(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] IN, EXISTS or ANY?
Josh Berkus wrote: >>But EXISTS is an entirely different animal which is often faster >>... isn't that in the FAQ? There is no reference to EXISTS in the SELECT documentation?? Is this explained somewhere else? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Slow performance on MAX(primary_key)
Help, I have just been comparing some large table performance under 7.1 using the select max(primary key)from table; We are using this for various functions including sequence. It is taking 9 seconds to return this from around 1 million records. Shouldn't this be an instantaneous lookup? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Slow performance on MAX(primary_key)
Ludwig Lim wrote: >>I have just been comparing some large table >>performance under 7.1 using the >> >> select max(primary key)from table; >> > > Try using the following as alternative : > > SELECT primary_key > FROM table > ORDER BY primary_key desc > LIMIT 1; > > This should work if primary_key is indexes. > > As of now, Max() doesn't utilizes the indices hence > it always do a sequential scan. Thanks Ludwig, That does help performance, but I was using a "standard" SQL command wrapped in a VB6 ADO ODBC program. Is this likely to be sorted in 7.2 ? Is anyone looking at this? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Slow performance on MAX(primary_key)
Richard Huxton wrote: >>> As of now, Max() doesn't utilizes the indices hence >>>it always do a sequential scan. >>Is this likely to be sorted in 7.2 ? >>Is anyone looking at this? > As I understand, the problem is that the optimisation only applies for simple > cases... Getting MIN() adn MAX() seems fairly trivial to me. When is on an index or more importantly Primary Key it must be a common SQL. Would it be possible in the code to look at the field in MIN() or MAX() and if it is indexed use a similar method to the suggested SQL work around? Can I help this to happen? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
