Re: [GENERAL] LIBPQ Exception
On Dec 12, 2007 7:36 AM, Abraham, Danny [EMAIL PROTECTED] wrote: Any other alternative? There are no anonymous blocks in PostgreSQL: You could try creating a function: create or replace function shootmyselfinthefoot() returns boolean as $$ declare (...); begin execute 'SOME DML HERE'; execute 'SOME MORE DML HERE'; execute 'ETC.'; (...) exception when others then -- whatever... end; $$ language plpgsql; ... and call that from your C program. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Extract last 4 characters from string?
On Dec 12, 2007 4:11 PM, D. Dante Lorenso [EMAIL PROTECTED] wrote:
Is there an easy (preferred) method that I'm missing?
select substring('ABCDEFGHIJKLMNOP' from '$');
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Trigger - will not perform INSERT
On Dec 11, 2007 3:35 PM, smiley2211 [EMAIL PROTECTED] wrote: Thanks...Michelle Please post DDL plus sample code/data to increase the odds of having someone help you. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] TIMESTAMP difference
On Dec 10, 2007 2:13 PM, rihad [EMAIL PROTECTED] wrote: Hi, is there a way to get the difference in hours between two timestamps? SELECT (EXTRACT (EPOCH FROM TIMESTAMP '20071211 00:00') - EXTRACT (EPOCH FROM TIMESTAMP '20071209 01:00')) * INTERVAL '1 second'; ---(end of broadcast)--- TIP 1: 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: [GENERAL] Array index not used for query on first element?
On Dec 7, 2007 4:12 PM, John D. Burger [EMAIL PROTECTED] wrote: This is under 7.4. Urgh! Is this different on less paleolithic versions of PG, or is there some other issue? Same here: select version(); PostgreSQL 8.3beta4, compiled by Visual C++ build 1400 select * from temppaths where path[1] = 43; Seq Scan on temppaths (cost=0.00..26.38 rows=7 width=32) (actual time=0.005..0.005 rows=0 loops=1) Filter: (path[1] = 43) Total runtime: 0.065 ms Maybe you could use an expression index: create index axo on temppaths((path[1])); select * from temppaths where path[1] = 43; Bitmap Heap Scan on temppaths (cost=4.30..14.45 rows=7 width=32) (actual time=0.018..0.018 rows=0 loops=1) Recheck Cond: (path[1] = 43) - Bitmap Index Scan on axo (cost=0.00..4.30 rows=7 width=0) (actual time=0.012..0.012 rows=0 loops=1) Index Cond: (path[1] = 43) Total runtime: 0.106 ms Good luck. ---(end of broadcast)--- TIP 1: 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: [GENERAL] Q: using generate_series to fill in the blanks
On Dec 6, 2007 10:44 PM, Ow Mun Heng [EMAIL PROTECTED] wrote: I've got a desired output which looks something like this.. vdt| count +--- 1 | 514 2 |27 3 |15 4 | NULL 5 |12 6 |15 SELECT i.i AS vdt, CASE WHEN COUNT(vdt)=0 THEN NULL ELSE COUNT(vdt) END AS COUNT FROM generate_series (1, 7) i LEFT JOIN footable f ON i.i = f.vdt AND c_id = '71' GROUP BY i.i ORDER BY i.i; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Declaring multidimensional arrays in pl/pgsql
I wrote: You can declare arbitrary-sized, n-dimensional arrays: Sorry, I re-read your post. You want to programatically define the array dimensions depending on function arguments. You could try building a string, then casting to the correct array type (not tested). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Declaring multidimensional arrays in pl/pgsql
On Nov 29, 2007 3:34 AM, Max Zorloff [EMAIL PROTECTED] wrote: According to the docs it seems that only way would be to declare it as something like : myArray := ARRAY[[1,2], [3,4], [5,6]]; You can declare arbitrary-sized, n-dimensional arrays: ... DECLARE myArray integer[][]; -- two-dimensional integer array BEGIN ... END; ... See: http://www.postgresql.org/docs/8.1/static/arrays.html ---(end of broadcast)--- TIP 1: 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
[GENERAL] ERROR: failed to find conversion function from unknown to integer[]
t=# select version();
version
PostgreSQL 8.3beta3, compiled by Visual C++ build 1400
(1 row)
t=# -- foo is of type unknown
t=# select '{1,2,3}' as foo;
foo
-
{1,2,3}
(1 row)
t=# -- OK. foo is of type int[]
t=# select ('{1,2,3}')::int[] as foo;
foo
-
{1,2,3}
(1 row)
t=# -- OK. foo is of type unknown
t=# select (('{1,2,3}'::text)::unknown) as foo;
foo
-
{1,2,3}
(1 row)
t=# -- Barfs. Why?
t=# select (('{1,2,3}'::text)::unknown)::int[] as foo;
ERROR: failed to find conversion function from unknown to integer[]
Thanks for your time.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] I have a select statement on the issue.
On Nov 28, 2007 1:58 AM, [EMAIL PROTECTED] wrote: 1. Why the default output changes after I execute the update statement? 2. Qustion, sorting as main keys when query, how to do? See: http://www.postgresql.org/docs/8.2/static/queries-order.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Table filter
On Nov 21, 2007 9:21 AM, Reg Me Please [EMAIL PROTECTED] wrote: Hi all. I've the following concept. snip This smells like EAV. Please read http://joecelkothesqlapprentice.blogspot.com/2006/04/using-one-table-vs-many.html and consider reevaluating the schema according to valid relational design (tables, columns, check constraints, etc.). In any case, good luck. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Populating large DB from Perl script
On 11/3/07, Mikko Partio [EMAIL PROTECTED] wrote: On Nov 2, 2007 8:45 PM, Kynn Jones [EMAIL PROTECTED] wrote: It would be great if there was a stored proc-archive somewhere in the web where people could post their procedures. I know there are some code examples in the official documentation but they are few in numbers. In a somewhat related note, what happened to the old PostgreSQL cookbook site? Does anyone care to revive it? ---(end of broadcast)--- TIP 1: 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: [GENERAL] newbie: update timestamp trigger?
On 10/23/07, ahnf [EMAIL PROTECTED] wrote: Whenever a row is updated in a table with a timestamp column. How do I write a trigger to set that timestamp column to now() or the current timestamp? Straight from the horse's mouth: http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Array intersection
On 10/17/07, Josh Trutwin [EMAIL PROTECTED] wrote:
nm - I just wrote a function - though curious if this is the most
effecient way:
If you only want TRUE or FALSE, you can use '':
t=# SELECT '{1,2}'::INT[] '{2,3}'::INT[];
?column?
--
t
(1 row)
---(end of broadcast)---
TIP 1: 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: [GENERAL] Query problem
On 10/12/07, Clemens Schwaighofer [EMAIL PROTECTED] wrote: I probably need to approach the problem different. So both are read independent or something like this. Also, email_a, email_b, etc. indicate that you need to consider refactoring your schema. You can find a lot of tutorials on normalization online. ---(end of broadcast)--- TIP 1: 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: [GENERAL] good sql tutorial
On 10/4/07, Geoffrey [EMAIL PROTECTED] wrote: Anyone have a recommendation for a good sql tutorial? Looking for a book, but online would be useful as well. This is for a financial user who will need to have an understanding of sql in order to generate reports with a report writer like openrpt. Thanks for any suggestions. http://www.google.com/search?q=sql+tutorial ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] good sql tutorial
On 10/4/07, Geoffrey [EMAIL PROTECTED] wrote: I know how to use google, I'm looking for recommendations. What an ass. If you found my reply to be lacking, you can say so without being rude... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] good sql tutorial
On 10/4/07, Geoffrey [EMAIL PROTECTED] wrote: Stating the obvious google search to me is just as rude. I was looking for recommendations based on others' experiences. That was not my intention, so I'm sorry if you felt that way. Peace. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unexpected behavior with CASE statement
On 10/3/07, Jimmy Choi [EMAIL PROTECTED] wrote: I expect to get the following result set: metric_type | result +--- 0 | 2 1 | 3 Try: SELECT metric_type , SUM(CASE metric_type WHEN 0 THEN 1 / val WHEN 1 THEN val END) AS RESULT FROM metrics GROUP BY metric_type ORDER BY metric_type ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Select too many ids..
On 10/1/07, Abandoned [EMAIL PROTECTED] wrote: Hi.. I have a id list and id list have 2 million dinamic elements.. I want to select what id have point.. I try: SELECT id, point FROM table WHERE id in (IDLIST) This is working but too slowly and i need to performance.. I'm sorry my bad english. King regards.. DDL please... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] not in clause too slow?
On 9/21/07, Ottavio Campana [EMAIL PROTECTED] wrote: My problem is that if I run the same command on another table with 378415 rows, it is terribly slow. How much is terribly slow? Did you VACUUM ANALYZE? Anyways, try this: SELECT * FROM MYTABLE T1 LEFT JOIN COPY_MYTABLE T2 ON T1.ID = T2.ID WHERE T2.ID IS NULL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER
On 9/18/07, Ow Mun Heng [EMAIL PROTECTED] wrote: On Tue, 2007-09-18 at 06:01 -0400, Bill Moran wrote: * (with newer version) reduce the fill factor and REINDEX What is fill factor? See Index Storage Parameters: http://www.postgresql.org/docs/8.2/static/sql-createindex.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recall: August Monthly techdata split fi
On 9/18/07, Geoffrey [EMAIL PROTECTED] wrote: In my experience, attempting to 'recall' an email message is a fruitless endeavor. Seems to me that this is a 'Microsoft' creation. I really don't understand the purpose, because by the time you consider 'recalling' the email message, it's already sitting in 1000s of inboxes... It's an Exchange feature. It only works if you send the email within the Exchange domain. So, yeah, it's a brain-dead feature... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ON INSERT = execute AWK/SH/EXE?
On 9/17/07, Bima Djaloeis [EMAIL PROTECTED] wrote: Thanks for reading, any help is appreciated. Triggers + Untrusted PL/Perl, see: 1) http://www.postgresql.org/docs/8.2/static/plperl-triggers.html 2) http://www.postgresql.org/docs/8.2/static/plperl-trusted.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Locking entire database
On 9/14/07, Panagiotis Pediaditis [EMAIL PROTECTED] wrote: ... there is a specific case where i need it. Don't really know, but, explain what the case is, and maybe someone could help you. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to recover database instance from a disaster
On 9/14/07, Chansup Byun [EMAIL PROTECTED] wrote: Is there any other way? See: http://www.postgresql.org/docs/8.2/static/backup.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] query help
On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: query is?? http://www.w3schools.com/sql/default.asp ---(end of broadcast)--- TIP 1: 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: [GENERAL] Cannot declare record members NOT NULL
On 9/12/07, Cultural Sublimation [EMAIL PROTECTED] wrote: Thanks for the help! Not really following you, but try these: CREATE OR REPLACE FUNCTION GET_MOVIES () RETURNS SETOF MOVIES LANGUAGE SQL STABLE AS $$ SELECT MOVIE_ID, MOVIE_NAME FROM MOVIES; $$; -- OR -- CREATE OR REPLACE FUNCTION GET_MOVIES (OUT MOVIE_ID INT4, OUT MOVIE_NAME TEXT) RETURNS SETOF RECORD LANGUAGE SQL STABLE AS $$ SELECT MOVIE_ID, MOVIE_NAME FROM MOVIES; $$; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about a query with two count fields
On 9/11/07, Jeff Lanzarotta [EMAIL PROTECTED] wrote: I appreciate the help... SELECT TO_CHAR(ts, 'MM/DD/') AS day, str, proc , SUM(CASE WHEN z 0 THEN 1 ELSE 0 END) AS good, 0 AS ajaa , SUM(CASE WHEN z = 0 THEN 1 ELSE 0 END) AS bad FROM foobar WHERE str 9 GROUP BY str, DAY, proc ORDER BY str ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about a query with two count fields
Remove the , 0 AS ajaa, that was some filler that got thru by mistake. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] avg() of array values
On 9/11/07, Alban Hertroys [EMAIL PROTECTED] wrote: I would have expected an avg of 2.0 and a sum of 4, where am I going wrong? This works for me: select avg(a) from explode_array(array[1, 3]) a; avg 2. (1 row) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] oracle rank() over partition by queries
On 9/11/07, sharmi Joe [EMAIL PROTECTED] wrote: Hi, Is there a way to get the oracle's rank() over partition by queries in postgresql? For example if I have a query like Select Col1, Col2, RANK() OVER(PARTITION BY Col1 order by Col3 desc) as rank from table1 Thanks in advance See: http://troels.arvin.dk/db/rdbms/#select-top-n ---(end of broadcast)--- TIP 1: 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: [GENERAL] audit sql queries
On 9/9/07, Dan99 [EMAIL PROTECTED] wrote: Any help with this would be greatly appreciated. http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] an other provokative question??
On 9/7/07, Greg Smith [EMAIL PROTECTED] wrote: ... renaming the database Horizontica. Following the naming convention, wouldn't it be Horizonta? ---(end of broadcast)--- TIP 1: 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: [GENERAL] stored procedure
On 9/7/07, genesis [EMAIL PROTECTED] wrote: appreciate any help, links etc... See: http://www.postgresql.org/docs/8.2/static/xfunc-sql.html#AEN36437 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What is the best way to merge two disjoint tables?
On 9/7/07, Chansup Byun [EMAIL PROTECTED] wrote: Can someone show me an example SQL statement? I suppose you could add a constant, non-overlapping number to add to the duplicate IDs, say 1000, and then this: SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER , COALESCE(CASE WHEN EXISTS(SELECT 1 FROM TABLEB WHERE U_ID = T1.U_ID AND U_USER T1.U_USER) THEN T1.U_ID + 1000 ELSE T1.U_ID END , T2.U_ID ) AS U_ID FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER will generate a new list of U_USERs and U_IDs. Good luck. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What is the best way to merge two disjoint tables?
On 9/7/07, Chansup Byun [EMAIL PROTECTED] wrote: One more question: Is there a way to make the T2.U_ID + 1000 number to be incremental from a given number instead of adding 1000? See here: http://archives.postgresql.org/pgsql-sql/2007-05/msg00194.php Then, say we want to start from 49: SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER , COALESCE(T1.U_ID , CASE WHEN T2.U_ID IS NOT NULL THEN 48 + ROWNUM() END ) AS U_ID FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER ORDER BY U_ID ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Partition Reindexing
On 9/4/07, Nik [EMAIL PROTECTED] wrote: This is on PostgreSQL 8.1.3 on Windows 2003 Advanced Server. Only 8.2 or newer has CREATE INDEX CONCURRENTLY. Maybe you could schedule a maintenance window for this. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] now() vs current_user
On 9/5/07, Ottó Havasvölgyi [EMAIL PROTECTED] wrote: What is the cause that now() works but now does not and current_user works but current_user() does not. From http://www.postgresql.org/docs/8.2/static/functions-info.html : Note: current_user, session_user, and user have special syntactic status in SQL: they must be called without trailing parentheses. See: http://www.google.com/search?q=sql+current_user ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Querying database for table pk - better way?
On 9/5/07, Josh Trutwin [EMAIL PROTECTED] wrote: Curious if there is a better/cheaper way to get the data I'm looking for though? SELECT conname FROM pg_constraint c JOIN pg_class l ON c.conrelid = l.relfilenode JOIN pg_namespace n ON n.OID = l.relnamespace WHERE contype = 'p' AND relname = '$table' AND nspname = '$schema' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Querying database for table pk - better way?
Sorry, just realized that I misread the query's requirements, but you can play with PG's system catalogs to complete it. See: http://www.postgresql.org/docs/8.1/static/catalogs.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT question (splitting a field)
On 9/4/07, Madison Kelly [EMAIL PROTECTED] wrote: I am sure I am missing something simple. :) Yeah... '[EMAIL PROTECTED]' '@test.com' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] return 1 formatted result instead of multiple results
On 8/31/07, Acm [EMAIL PROTECTED] wrote: and I want one result to be Mike,John,Dennis. SELECT ARRAY_TO_STRING(ARRAY(SELECT * FROM PEOPLE),',') AS FOO ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to print a schema
On 8/27/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Does anybody know how to print a schema in PostgreSQL? I know you can look at one table at at time, but is there a way to print all columns and rows at once? 1. pg_dump / pg_dumpall http://www.postgresql.org/docs/8.2/static/backup-dump.html 2. PostgreSQL Autodoc http://www.rbt.ca/autodoc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is there a better way to do this?
On 8/28/07, Wei Weng [EMAIL PROTECTED] wrote: Is there a more concise way to do this? CREATE OR REPLACE FUNCTION ADDDAYS (TIMESTAMP WITHOUT TIME ZONE, INT) RETURNS TIMESTAMP WITHOUT TIME ZONE AS ' SELECT $1+($2 * ''1 DAY''::INTERVAL) ' LANGUAGE SQL; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] problem Linking a TTable component to a pgsql view using BCB5
On 8/21/07, JLoz [EMAIL PROTECTED] wrote: I have not been able to find a workaround for this? Does the table have a unique index/primary key? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Yet Another COUNT(*)...WHERE...question
On Aug 16, 5:19 am, [EMAIL PROTECTED] (Decibel!) wrote: On Thu, Aug 16, 2007 at 12:12:03PM +0200, Rainer Bauer wrote: Scott Marlowe wrote: But if you go to eBay, they always give you an accurate count. Even if the no. of items found is pretty large (example: http://search.ebay.com/new). And I'd bet money that they're using a full text search of some kind to get those results, which isn't remotely close to the same thing as a generic SELECT count(*). http://www.addsimplicity.com/downloads/eBaySDForum2006-11-29.pdf Search for the text Scaling Search. Interesting stuff. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function with Integer array parameter
On Aug 16, 11:06 am, [EMAIL PROTECTED] (Ranjan Kumar Baisak) wrote: Decibel! wrote: On Thu, Aug 16, 2007 at 11:14:25AM -0400, Ranjan Kumar Baisak wrote: Postgres Gurus, Please suggest me what is wrong with this function. This function tries to retrieve set of rows from description table based on set of ID fields passed as array. The error, I get is : ERROR: only one AS item needed for language plpgsql CREATE OR REPLACE function get_description_list(integer[]) RETURNS SETOF type_description AS 'DECLARE ... WHERE d_base.id in array_to_string(ints_desc_ids alias,',') Note the quotes. Use dollar quoting... it$$s your friend. I tries wir $$ as well as ''(two single quotes instead of one single quote) but still got the same error. I think the error is with WHERE d_base.id in array_to_string(ints_desc_ids alias,','). I need a way using integer array in where clause. ... WHERE d_base.id = ANY(ints_desc_ids) ... See: http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5865 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Fastest way to import only ONE column into a table? (COPY doesn't work)
On Aug 15, 11:46 pm, [EMAIL PROTECTED] (Phoenix Kiula) wrote: Appreciate any tips, because it would be nasty to have to do this with millions of UPDATE statements! - Create an interim table - COPY the data into it - Do an UPDATE ... FROM ... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Trigger not working as expected, first row gets a null value
On Aug 8, 3:20 pm, [EMAIL PROTECTED] (novnov) wrote: ... I also don't understand ... DDL + sample data, please... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Select question..... is there a way to do this?
On Aug 3, 11:17 pm, [EMAIL PROTECTED] (Karl Denninger) wrote: Ideas? SELECT item.user, item.subject, item.number FROM item, seen WHERE item.user = seen.user AND item.number = seen.number AND item.changed seen.lastviewed UNION SELECT item.user, item.subject, item.number FROM item, seen WHERE item.user = seen.user AND seen.number IS NULL AND item.changed seen.lastviewed; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Optimising SELECT on a table with one million rows
On Jul 30, 12:01 pm, [EMAIL PROTECTED] (Cultural Sublimation) wrote: Hash Join (cost=28.50..21889.09 rows=988 width=14) (actual time=3.674..1144.779 rows=1000 loops=1) Hash Cond: ((comments.comment_author)::integer = (users.user_id)::integer) - Seq Scan on comments (cost=0.00..21847.00 rows=988 width=8) (actual time=0.185..1136.067 rows=1000 loops=1) Filter: ((comment_story)::integer = 100) - Hash (cost=16.00..16.00 rows=1000 width=14) (actual time=3.425..3.425 rows=1000 loops=1) - Seq Scan on users (cost=0.00..16.00 rows=1000 width=14) (actual time=0.068..1.845 rows=1000 loops=1) Total runtime: 1146.424 ms Create an index on comments.comment_story column. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Count(*) throws error
On Jul 11, 4:00 pm, [EMAIL PROTECTED] (Jasbinder Singh Bali) wrote: I don't know why isn't count(*) working Works for me: create table tbl_concurrent(a int); insert into tbl_concurrent values (1); insert into tbl_concurrent values (9); insert into tbl_concurrent values (4); create or replace function foo() returns int as $$ declare no_rows int := 0; begin select into no_rows count(*) from tbl_concurrent; return no_rows; end; $$ language plpgsql; select foo(); foo - 3 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Simple backup utility like mysqldump?
On Jun 29, 12:32 am, Bjorn Boulder [EMAIL PROTECTED] wrote: Hello People, I'm running PostgreSQL 8.1.1 on my freebsd box. I'm curious if PostgreSQL has a utility for backing up small databases like mysqldump or Oracle's export utility. -b See: http://www.postgresql.org/docs/8.2/static/backup.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need help with generic query
On Jun 20, 5:55 am, David Abrahams [EMAIL PROTECTED] wrote: The problem is, psql is complaining: ERROR: wrong record type supplied in RETURN NEXT Try: ... RETURNS SETOF RECORD ... ---(end of broadcast)--- TIP 1: 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: [GENERAL] Why does this work?
On Jun 15, 1:56 pm, [EMAIL PROTECTED] (Ian Harding) wrote: I accidentally formatted a string for tsearch before trying to cast it to a date, and it worked! select 'June152007'::date date 2007-06-15 (1 row) Is this a happy accident, or is it OK to count on it continuing to work this way? Thanks, Ian select 'June152007'::date ,'June.15.2007'::date ,'June__15__2007'::date See: http://www.postgresql.org/docs/8.2/static/x71171.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Historical Data Question
On Jun 14, 12:57 pm, Lza [EMAIL PROTECTED] wrote: Can anyone help me with this problem? I have a table in my database that holds information on policies and this table is linked to a number of other tables. I need to be able to keep a history of all changes to a policy over time. The other tables that are linked to policy also need to store historical data. When I run a query on the policy table for a certain period, I also need to be able to pull the correct related rows (i.e. the information that would have been in the table at that time) from the tables linked to it. Does anyone have any suggestions on how to store historical information in databases? Any good resources (books, etc..) that cover this information? Thanks for your time. See: http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html Search for: Example 37-3. A PL/pgSQL Trigger Procedure For Auditing ---(end of broadcast)--- TIP 1: 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: [GENERAL] pointer to feature comparisons, please
On Jun 13, 8:57 am, [EMAIL PROTECTED] (Kevin Hunter) wrote: So, motivation aside, what I'm wanting is a couple of pointers to feature comparisons of Postgres vs Oracle. What else is going to bite him while he works on this project? Would be handy to have this reference since neither of us are really DB wizards. (Besides! Isn't it good to tout what Postgres does better than it's competition? :-) ) This might help a bit on the SQL side: Comparison of different SQL implementations http://troels.arvin.dk/db/rdbms/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Regular expressions in procs
On Jun 13, 9:02 am, [EMAIL PROTECTED] (Steve Manes) wrote: I apologize if I'm having a rookie brain block, but is there a way to massage a string inside a proc to, for instance, strip it of all non-alpha characters using a regular expression? regexp_replace() could work for you, see: http://www.postgresql.org/docs/8.2/static/functions-string.html http://www.postgresql.org/docs/8.2/static/functions-matching.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] parametered views
On Jun 8, 7:59 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: i have 4 tables : date_table (date_id,.) A_table(A_table_id, something1,something2.) A1_table(A1_table_id references A_Table(A_Table_id),A11,A12) A2_table(A2_table_id references A_Table(A_table_id),A21,A22,...) so i want to create a view with date_id,A_table_id,something1, if something2=x then my view containts (A1_table.A11 and A1_table.A12) else it's containts (A2_table.A21 and A2_table.A22)) so my view depends of something2 value. I hope i'm specific Thx Lhaj create view foobar as select date_id,A_table_id,something1 ,case when something2=x then A1_table.A11 else A2_table.A21 end as foo ,case when something2=x then A1_table.A12 else A2_table.A22 end as bar from (... etc., etc. ...) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] parametered views
[EMAIL PROTECTED] ha escrito: Hello, my problem is : in depend of the value of a field in a table A, I want to select other fields coming from a table B, or a table C. I want to know if it's possible to create a parametred view in postgresql to resolve this problem Thx, Lhaj You really should be more specific... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] list all columns in db
Jonathan Vanasco ha escrito: Does anyone have a trick to list all columns in a db ? SELECT * FROM INFORMATION_SCHEMA.COLUMNS ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT all fields except bad_field from mytbl;
On May 29, 5:42 pm, [EMAIL PROTECTED] wrote: Hi! Title says it pretty much. I am wondering if there is a short way to form a query that retrieves all fields of a table (of which I do not know names and number beforehand) except for one (or more, of which I know the name(s)). I have stumbled across the need for this a couple of time during the last few years. Maybe there is a way and I am just being blind .. For instance, I would use this in a plpgsql function to select all fields but bad_field from a number of tables. I know the name of the field I DO NOT want, but number and names of the other fields (which I want) are not known at the time of writing the function. Thanx for your hints and please cc answers to my email. Regards Erwin create table my_t ( good_field1 int , good_field2 int , bad_field int ); select column_name from information_schema.columns where table_name = 'my_t'; column_name - good_field1 good_field2 bad_field select column_name from information_schema.columns where table_name = 'my_t' and column_name 'bad_field'; column_name - good_field1 good_field2 --- Now see: http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT all fields except bad_field from mytbl;
On May 29, 11:35 pm, Erwin Brandstetter [EMAIL PROTECTED] wrote: EXECUTE 'SELECT ' || (SELECT array_to_string(ARRAY( SELECT a.attname FROM pg_class c, pg_namespace nc, pg_attribute a WHERE c.relname = 'v_event' AND c.relnamespace = nc.oid AND nc.nspname = 'stdat' AND a.attrelid = c.oid AND a.attname 'log_up'), ', ')) || ' FROM stdat.v_event'; You might want to add: AND a.attnum =1 to remove tableoid and friends from the output. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to create trigger if it does not exist
On May 26, 5:58 pm, Andrus [EMAIL PROTECTED] wrote: Thank you. This doc says that dropping trigger drops depending objects also. Only if you use CASCADE (default is RESTRICT). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to create trigger if it does not exist
Andrus ha escrito: CREATE TRIGGER mycheck_trigger BEFORE INSERT OR UPDATE ON mytbl FOR EACH ROW EXECUTE PROCEDURE mycheck_pkey(); aborts transaction if trigger already exists. There in no CREATE OR REPLACE TRIGGER command in PostgreSQL How to create trigger only when it does not exist ? Andrus. DROP TRIGGER IF EXISTS... See: http://www.postgresql.org/docs/8.2/static/sql-droptrigger.html ---(end of broadcast)--- TIP 1: 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: [GENERAL] Limiting number of rows returned at a time in select query
Jon Clements ha escrito: Hi All. Is there a way inside a query (or connection) to limit the amount of records returned each chunk by the server? At the moment, I have 22 million records trying to be returned in one-go as the result set. I have a .NET driver that has a FetchSize option which allows the above (say returning 10k rows at a time without holding them all in memory); I'm not sure though if that's a property of the driver / server, as none of the other interfaces I have seem to include it. It just makes sense it might be server-side; although, if it's not I'm quite happy to be corrected. Any pointers are appreciated. Cheers, Jon. See: http://www.postgresql.org/docs/8.2/static/queries-limit.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] doverlaps() returns null
On May 20, 1:39 pm, Andrus [EMAIL PROTECTED] wrote: I need to check when two date intervals overlap. Some date interval values may be null. I created the following function but select doverlaps( null, null, null, null); returns null. How to fix this so that null values are allowed in parameters ? Andrus. -- returns true when date interval $1 .. $2 overlaps with $3 .. $4 -- null values are allowed in parameters. CREATE OR REPLACE FUNCTION public.doverlaps(date, date, date, date, out bool) immutable AS $_$ SELECT coalesce($1, timestamp '-infinity'::date) =coalesce($4, timestamp 'infinity'::date) AND coalesce($2, timestamp 'infinity'::date)=coalesce($3, timestamp '-infinity'::date); $_$ language sql; You cannot cast +/- infinity timestamp to date, but you can cast date to timestamp. And what's wrong with OVERLAPS? e.g. : CREATE OR REPLACE FUNCTION PUBLIC.DOVERLAPS (DATE, DATE, DATE, DATE, OUT BOOL) IMMUTABLE AS $_$ SELECT (COALESCE($1::TIMESTAMP, TIMESTAMP '-INFINITY') , COALESCE($2::TIMESTAMP, TIMESTAMP 'INFINITY') ) OVERLAPS( COALESCE($3::TIMESTAMP, TIMESTAMP '-INFINITY') , COALESCE($4::TIMESTAMP, TIMESTAMP 'INFINITY')); $_$ LANGUAGE SQL; t=# SELECT doverlaps( NULL, NULL, NULL, NULL); doverlaps --- t ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] doverlaps() returns null
On May 21, 4:49 am, Andrus [EMAIL PROTECTED] wrote: I'm using this for emplyment, vacation, illness etc. period calculation. OVERLAPS produces invalid result in this case for DATE as discussed in other thread. select doverlaps(date '2007-01-01',date '2007-01-02',date '2007-01-02',date '2007-01-04'); returns FALSE When first period end and second period start dates are the the same, doverlaps() must return TRUE. Andrus. OR $2 = $3 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Indice en Date
Arturo Munive ha escrito:
tengo un indice sobre una columna date.
cuando hago una consulta ...
*Select *
id
*from *
ventas
*WHERE *
fecha = date('12-JAN-2007')
el planificador usa el indice
pero cuando la restriccion es WHERE fecha date('12-JAN-2007')
se efectua un barrido secuencial.
ni e indice ni la tabla ni la consulta son nada complejos
que me olvido o que debo hacer para que se utilize el indice cuando uso el
operador menor
Depende de la distribución de información en (y estadísticas de) la
tabla en cuestión.
1. ¿Haz ejecutado ANALYZE? El planificador depende de estadísticas
recientes para tomar una mejor decisión.
2. ¿En la tabla, la mayoría de los datos son menor que la fecha de
ejemplo? Si es así, y haz analizado, de igual forma el planificador
puede juzgar que el barrido secuencial es lo más indicado, ya que se
visitan la mayoría de las páginas de datos.
3. Si haz analizado y la distribución no es acorde a (2), entonces
puedes jugar con los parámetros de costo del archivo de configuración,
o incrementar el target de estadísticas de la columna en cuestión.
Consulta la documentación para ver estos casos.
Suerte.
---(end of broadcast)---
TIP 1: 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: [GENERAL] How to implement GOMONTH function
Andrus ha escrito: I need to create function GOMONTH which returns date by given number of month before or forward using sql or pgsql in 8.1+ For example, GOMONTH( DATE '20070513', 1 ) should return date '20070613' GOMONTH( DATE '20070513', -2 ) should return date '20070313' I tried CREATE OR REPLACE FUNCTION public.gomonth(date, integer, out date) IMMUTABLE AS $_$ SELECT $1 + $2'months'; $_$ language sql but got error ERROR: syntax error at or near 'months' How to implement this ? Andrus. CREATE OR REPLACE FUNCTION PUBLIC.GOMONTH(DATE, INTEGER, OUT DATE) IMMUTABLE AS $_$ SELECT ($1 + ($2 || 'MONTHS')::INTERVAL)::DATE; $_$ LANGUAGE SQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
