Re: [SQL] Index not recognized
Hello Greg, thanks for replying. I definitely lacked the expression you suggested in my 'SELECT' statement. > Is this *= operator from the contrib/array directory? It's not an indexable > operator at all using standard btree indexes. Yes, it is from contrib/array directory. > The GiST indexing does make indexable operators that can do things like *= but > that's a whole other ball of wax. I tried the btree_gist from contrib/, but I know I missed something because I got this error message: data type text[] has no default operator class for access method "gist". You must specify an operator class for the index or define a default operator class for the data type. > What are you really trying to do? I have tables with attributes whose datatype is TEXT[]. I'm interested to find out the time it will take to finish an array search with and without an index. --- Grace - Original Message - From: "Greg Stark" <[EMAIL PROTECTED]> To: "Grace C. Unson" <[EMAIL PROTECTED]> Cc: "PgSQL SQL" <[EMAIL PROTECTED]> Sent: Sunday, December 07, 2003 8:36 AM Subject: Re: [SQL] Index not recognized > > "Grace C. Unson" <[EMAIL PROTECTED]> writes: > > > Why is it that my index for text[] data type is not recognized by the > > Planner? > > > > I did these steps: > > > > 1. create function textarr(text[]) returns text language sql as 'select > > $1[1]' strict immutable > > 2. create index org_idx on EmpData (textarr(org)); > > This index will only be used if you use the expression textarr(org) in your > query. You would probably have some success if you did: > > select * from empdata where textarr(org) = 'math' > > > 3. vacuum full > > 4. explain analyze select name from EmpData where org *= 'math'; > > Is this *= operator from the contrib/array directory? It's not an indexable > operator at all using standard btree indexes. > > The GiST indexing does make indexable operators that can do things like *= but > that's a whole other ball of wax. > > What are you really trying to do? > > > Result: > > = > > Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488) > > (actual time=3.71.35..371.35 rows=0 loops=1) > > > > Filter: (org[0]='math'::text) > > Well that's awfully odd. I don't know how that expression came out of the > query you gave. You'll have to give a lot more information about how you're > defining *= and why you think it's related to the function you used to define > the index. > > -- > greg > > > ---(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] postgresql and ColdFusion
Benoît BOURNON wrote: Oh dear a man using CF with postgresql ! ... OH YES ! ... try to use jdbc pilot to connect to postgresql with CFMX ... maybe you can use cfstoredproc with cfprocparam and cfprocrsult Roberto Mello wrote: On Mon, Dec 08, 2003 at 05:38:56PM -0800, viola wrote: Hi, How to call PostgreSql function from ColdFusion, if that function returns result set using cursor? Through ODBC. Don't know it they have native access now. -Roberto
[SQL] Values like ''
Hi folks! Why does the following query work on pgsql 7.2.3 and not in pgsql 7.3.4? SELECT cronograma_evento.nr_projeto ,cronograma_evento.dt_inic ,cronograma_evento.nr_sala ,cronograma_evento.tm_hora_inicial ,cronograma_evento.tm_hora_final ,cronograma_evento.cd_turma ,cronograma_evento.cd_discipl ,locais_sala.cd_tipo_sala FROM cronograma_evento, locais_sala WHERE cronograma_evento.dt_crono = '1/12/2003' AND cronograma_evento.cd_local = '' AND cronograma_evento.cd_local = locais_sala.cd_local AND cronograma_evento.nr_sala = locais_sala.nr_sala ORDER BY cronograma_evento.nr_sala, cronograma_evento.tm_hora_inicial; >\\\!/< 55 11 5080 9258 !_"""_! Elielson Fontanezi (O) (o) PRODAMoOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0( )--( ) \ ( ) / \_/ \_/ <> ---(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] Values like ''
Elielson Fontanezi wrote: > Why does the following query work on pgsql 7.2.3 and not in pgsql > 7.3.4? Please be more detailed on your idea of "works" and "does not work". ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] postgresql and ColdFusion
Oh dear a man using CF with postgresql ! ... OH YES ! ... try to use jdbc pilot to connect to postgresql with CFMX ... maybe you can use cfstoredproc with cfprocparam and cfprocrsult Roberto Mello wrote: On Mon, Dec 08, 2003 at 05:38:56PM -0800, viola wrote: Hi, How to call PostgreSql function from ColdFusion, if that function returns result set using cursor? Through ODBC. Don't know it they have native access now. -Roberto
[SQL] Fetch a single record
I'm looking for the fastest way to fetch a single record from a table. I don't care what record it is. Here are two techniques I've explored: 1. Use LIMIT SELECT * FROM myTable LIMIT 1 2. Get a valid OID and then get the record. SELECT MIN(oid) AS anOID FROM myTable SELECT * FROM myTable WHERE oid = anOID The 1st technique is slow. (I think PostgreSQL fetches all records and then returns just one.) The 2nd is faster, but not fast enough. Any other ideas? Please reply to me personally ([EMAIL PROTECTED]) as well as to the list. Thanks. -David ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to specify the beginning of the month in Postgres SQL syntax?
hi [EMAIL PROTECTED] wrote, On 12/7/2003 5:16 PM: Hello, I need to create a view in Postgres that has a where clause of the date < beginning of month. i.e.: SELECT supplier_number, Sum(amount) AS due FROM purchase_orders WHERE date < '2003-12-1' AND paid = 0 GROUP BY supplier_number ORDER BY supplier_number ASC As you can see, I've specified the 1st of December this year as the where clause. What I want is an SQL statement that automatically generates the first of the month. How do I do this? a simple way: create function month_begin() returns date as ' SELECT to_date(current_date,'-MM'); ' language sql STABLE; ... where date < month_begin() C. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to specify the beginning of the month in Postgres SQL syntax?
Hello, I need to create a view in Postgres that has a where clause of the date < beginning of month. i.e.: SELECT supplier_number, Sum(amount) AS due FROM purchase_orders WHERE date < '2003-12-1' AND paid = 0 GROUP BY supplier_number ORDER BY supplier_number ASC As you can see, I've specified the 1st of December this year as the where clause. What I want is an SQL statement that automatically generates the first of the month. How do I do this? Thanks. ---(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] Fetch a single record
David Shadovitz wrote: > I'm looking for the fastest way to fetch a single record from a > table. I don't care what record it is. > > Here are two techniques I've explored: > > 1. Use LIMIT > SELECT * FROM myTable LIMIT 1 > > 2. Get a valid OID and then get the record. > SELECT MIN(oid) AS anOID FROM myTable > SELECT * FROM myTable WHERE oid = anOID > > The 1st technique is slow. (I think PostgreSQL fetches all records > and then returns just one.) The 2nd is faster, but not fast enough. This is hard to believe. The first technique should be the fasted, but might suffer from a bad plan. Please make sure that you have run ANALYZE, and then post EXPLAIN ANALYZE output. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Faster performance when using where cust_id = '123' vs cust_id = 123. Does 7.4 fix this??
We have got used to the problem that queries of the format: select * from customer where cust_id = '123' are much much faster than select * from customer where cust_id = 123 (where cust_id is defined as bigint). a. Why is this. b. Will moving to v7.4 change this so we can avoid the whole '123' casting thing. Tx folks, D ---(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] Faster performance when using where cust_id = '123' vs cust_id = 123. Does 7.4 fix this??
David B wrote: > We have got used to the problem that queries of the format: > > select * > from customer > where cust_id = '123' are much much faster than > > select * > from customer > where cust_id = 123 > > (where cust_id is defined as bigint). > > a. Why is this. Because in the second case, the 123 gets resolved too early to type integer, which is not index compatible with type bigint. > b. Will moving to v7.4 change this so we can avoid the whole '123' > casting thing. No, but 7.5 will be able to handle it. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Values like ''
On Fri, 5 Dec 2003, Elielson Fontanezi wrote: > AND cronograma_evento.cd_local = '' Is cd_local an integer type? If so, '' no longer works as an integer (IIRC it used to mean 0). The exact error message and possibly schema information would be useful if that's not what's going on. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Fetch a single record
On 05/12/2003 21:51 David Shadovitz wrote: I'm looking for the fastest way to fetch a single record from a table. I don't care what record it is. [snip] Have you also tried SELECT * from mytable limit 1 If you genuinely don't care what the record is (I assume you're justing testing that table is not empty?) then this might be the way to go. My rather limited knowledge of PG internals leads me to believe that this will generally cause just one page being read from disk (I'm assuming the 99% case of no mega-sized text/bytea/whatever columns here). I'd be interested to know just how far off the mark by understanding is... -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller 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
Re: [SQL] [ADMIN] Field Size
There is no harm in having a field size of 1000 character provided u use the proper datatype. Don’t user char(1000). Try using varchar(1000). For character data it is recommended to use the VARCHAR type, since PostgreSQL manages it very well. On the contrary, if you use the CHAR datatype, the size you specify for the string is pre-allocated in the pages and you allocate space which may not be used. So your database is bigger than what it should be and performance is lowered, because this way you need more I/O to read the data. Som -Original Message- From: Ganesan Kanavathy [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 6:26 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [ADMIN] Field Size Can anyone tell me, what harm would setting a field size to 1000 characters do to the size? By increasing the size, will the database require more space? Or does it only consume space if there is data in the field only? Regards, Ganesan
[SQL] Field Size
Can anyone tell me, what harm would setting a field size to 1000 characters do to the size? By increasing the size, will the database require more space? Or does it only consume space if there is data in the field only? Regards, Ganesan
Re: [SQL] [ADMIN] Field Size
If you define the field as CHAR, 1000 bytes will be consumed.
If you define the field as VARCHAR, on sufficient bytes to store the contents will be used.
Marc
--Original Message Text---
From: Ganesan Kanavathy
Date: Mon, 8 Dec 2003 20:56:06 +0800
Clean Clean DocumentEmail MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";}
Can anyone tell me, what harm would setting a field size to 1000 characters do to the size?
By increasing the size, will the database require more space? Or does it only consume space if there is data in the field only?
Regards,
Ganesan
Marc A. Leith
President
redboxdata inc.
e-mail: [EMAIL PROTECTED]
cell:(416) 737 0045
Re: [SQL] Fetch a single record
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I'm looking for the fastest way to fetch a single record from a table. > I don't care what record it is. That's a strange request. If you really don't care what comes back, you don't even need to query a table: SELECT 1; If you perhaps want the column names, query the system tables. Otherwise the LIMIT 1 should be very fast, especially if you have no ordering. Avoid the "SELECT *" if you don't need all the columns for a little more speed. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200312091943 -BEGIN PGP SIGNATURE- iD8DBQE/1mynvJuQZxSWSsgRAk0HAKDKTHglcodYw2G9j5Il60e96Vv/xwCfcZ6p ffIBwsqFtqW0UABYttqzT3U= =JV2a -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to specify the beginning of the month in Postgres SQL syntax?
On 07/12/2003 16:16 [EMAIL PROTECTED] wrote: Hello, I need to create a view in Postgres that has a where clause of the date < beginning of month. i.e.: SELECT supplier_number, Sum(amount) AS due FROM purchase_orders WHERE date < '2003-12-1' AND paid = 0 GROUP BY supplier_number ORDER BY supplier_number ASC As you can see, I've specified the 1st of December this year as the where clause. What I want is an SQL statement that automatically generates the first of the month. How do I do this? I do shed-loads of these date-related queries and although it's feasible to write some SQL/UDF function to do what you're asking, in my experience it is better to process the date in your app and pass it across as a parameter. That way you could use the same piece of SQL to get, for example, data which is > month owing just by passing 2003-11-01 as the date. Probably what you need is to write a function which takes an arbitary date and returns the first date in that month/year. You _could_ write this as PostgreSQL User Defined Function but writing it as part of your app will give you a) greater flexibility as the function will be easily available to other parts of your application b) if your app language/dev environment has a source-level debugger, you will be able to benefit from it when debugging your function and c) someone trying to maintain your app in 4 years time will only need to know your application language, SQL and a possible (very!) few PostgreSQL-specific variations from the SQL language definition. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to specify the beginning of the month in Postgres SQL syntax?
Hi Jeff, You can use WHERE date < to_date( to_char(current_date,'-MM') || '-01','-mm-dd') Thanx Denis - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, December 07, 2003 9:46 PM Subject: [SQL] How to specify the beginning of the month in Postgres SQL syntax? > Hello, > > I need to create a view in Postgres that has a where clause of the > date < beginning of month. > > i.e.: > SELECT supplier_number, Sum(amount) AS due > FROM purchase_orders > WHERE date < '2003-12-1' AND paid = 0 > GROUP BY supplier_number > ORDER BY supplier_number ASC > > > As you can see, I've specified the 1st of December this year as the > where clause. What I want is an SQL statement that automatically > generates the first of the month. How do I do this? > > Thanks. > > ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
