Re: [GENERAL] Question about COPY command
On fim, 2009-01-08 at 08:39 -0500, Josh Harrison wrote:
Hi,
A basic question about the COPY command syntax
This is the syntax in the postgres manual.
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
..
.
What is the difference between copying from 'filename' and copying from
'stdin' ???
The most important distinction is that 'filename' refers to a file
residing on the *server*, but STDIN is clientside.
For security, the file variant requires superuser privileges.
gnari
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invocation overhead for procedural languages
On mið, 2008-08-06 at 20:48 +0200, Giorgio Valoti wrote: On 06/ago/08, at 16:04, Pavel Stehule wrote: it's depend. Start of interpret is only one overhead. Other is date conversions to language compatible types (without C and plpgsql). So is plpgsql slower on date conversion than other languages? Just curious: why does shared evaluation add some overhead? I am sure he meant data conversion , not date gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] is a 'pairwise' possible / feasible in SQL?
On mán, 2008-08-04 at 17:00 -0400, Rajarshi Guha wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Aug 4, 2008, at 4:55 PM, Francisco Reyes wrote: On 2:08 pm 08/04/08 Rajarshi Guha [EMAIL PROTECTED] wrote: paircount - - 123 456 1 667 879 2 snip select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join aic_cid b on a.cid b.cid and a.id = b.id where b.cid is not null group by a.cid, b.cid order by a.cid; ac | bc | count -+-+--- 123 | 456 | 1 123 | 667 | 1 ... Is that what you are looking for? Thanks a lot - this is very close. Ideally, I'd want unique pairs, so the row 879 | 999 | 1 is the same as 999 | 879 | 1 Can these duplicates be avoided? just add a acbc condition: select a.cid as ac, b.cid as bc, count(*) from aic_cid a left outer join aic_cid b on a.cid b.cid and a.id = b.id where b.cid is not null AND a.cid b.cid group by a.cid, b.cid order by a.cid; gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Must be table owner to truncate?
On mið, 2008-07-30 at 07:36 -0400, Kevin Hunter wrote: At 3:45p -0400 on Mon, 28 Jul 2008, Said Ramirez wrote: According to the documentation, http://www.postgresql.org/docs/current/interactive/sql-truncate.html , only the owner can truncate a table. Which means the non-owner must either log in/ switch roles as the owner, or they can just run a DELETE. Well that's interesting. From a security standpoint, what's the difference between an unqualified DELETE and a TRUNCATE? lack of triggers and RULEs spring to mind. gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem running script
On fös, 2008-07-25 at 10:20 -0700, Sushma Sagaram wrote: My requirement is 1) to write a query in a file, read the input from that file, run in postgresql and write the output(query results) into a file. I'm not able to use combination of \i and \o see psql --help in particular the options -f , -c and -o gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimizer ignoring primary key and doing sequence scan
On þri, 2008-07-15 at 08:19 +0200, Edoardo Panfili wrote: Scott Marlowe ha scritto: On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy [EMAIL PROTECTED] wrote: select * from industries where industryid = 1; Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual time=0.011..0.013 rows=1 loops=1) According to this there's only one row in the table. why WOULD postgresql use an index when it can just scan the one row table in a split second. I agree with you that it can depend on the size of the table but where you can read that the table contains only one row? it does not really say 1 row, but you can infer from the estimated cost, that the table is only 1 block (cost=0.00..1.02). that is the smallest read unit. using an index would cost 2 random reads. I try with my table (39910 rows, no index on column note) explain analyze select * from table where note='single example'; Seq Scan on table (cost=0.00..2458.88 rows=13 width=327) (actual time=10.901..481.896 rows=1 loops=1) surely this is not the same table gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query Problem
On lau, 2008-07-05 at 23:04 +, Sheikh Salman Ahmed wrote: Hi Fellows I still have problem to access my databank.It shows syntax problem,I am using VC++ 2005 with postgresql 8.3.My table name is Person and it has three column,Person ID,first name and last name (testing version).whole c++ code is more precise schema definition would be more helpful ... res = PQexec(conn, INSERT INTO public.Person VALUES (221,'Siddiqi','Umer')); ... It shows no relation between public and person,if i write only person ,it show ,Person doesn't exist. and real error messages are preferred. As someone already told you a few days ago, the problem could be that the table was created Person (mixed case with double quotes). you have not confirmed or denied this. If that is the case, you need to quote the name in your SQL: INSERT INTO public.Person VALUES (221,'Siddiqi','Umer') (of course, you need to escape those quotes for your c) If this is not your problem, please suply us with more information, and someone may be able to help you. gnari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
Hello apart from the increasing OFFSET method, you only need to traverse the results sequentially, you can do a variant of this: let us assume your resultset has a a unique column pk, and is ordered on column o: initial select: select * from foo order by o limit 10; next page select * from foo where (o,pk)(o,?) order by o limit 10; (where the ? is the last pk value in previous select) this method will be able to make use of an index on (o,pk) gnari On fös, 2008-06-27 at 14:14 -0600, Bill Thoen wrote: What I want to do is present the results of a query in a web page, but only 10 rows at a time. My PostgreSQL table has millions of records and if I don't add a LIMIT 10 to the SQL selection, the request can take too long. The worst case scenario is when the user requests all records without adding filtering conditions (e.g. SELECT * FROM MyTable;) That can take 10-15 minutes, which won't work on a web application. What I'm wondering is how in PostgreSQL do you select only the first 10 records from a selection, then the next 10, then the next, and possibly go back to a previous 10? Or do you do the full selection into a temporary table once, adding a row number to the columns and then performing sub-selects on that temporary table using the row id? Or do you run the query with Limit 10 set and then run another copy with no limit into a temporary table while you let the user gaze thoughtfully at the first ten records? I know how to get records form the database into a web page, and I know how to sense user actions (PageDown, PageUp, etc.) so I'm basically looking for techniques to extract the data quickly. Also, if this isn't the best forum to ask this sort of question, I'd appreciate being pointed to a more appropriate one. TIA, - Bill Thoen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] first message: SELECT column FROM t
On lau, 2008-02-02 at 15:43 -0200, Aílsom F. Heringer wrote: At pgAdmin III Query, when I send SELECT * FROM USUARIOS, I get all columns correctly. But when I try to get only one column, SELECT senha FROM USUARIOS, I get the error message: ERROR: column senha does not exist SQL state: 42703 Character: 8 if you created the column name mixedcase with quotes, you need to access it the same way. so if the column name is really Senha do: SELECT Senha FROM USUARIOS; if that is not the problem, can you repeat this in the command-line tool psql ? gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing
Hi Usama yes, currently I am reading a brochure about Continuent uni/cluster for PostgreSQL. Looks quite interesting. Another product sounds promising: Cybercluster from www.postgres.at English Product Description: http://www.postgresql.at/picts/download/dokumentation/documentation_cybe rcluster.pdf Anyone has made experiences with it? thanks Ragnar From: Usama Dar [mailto:[EMAIL PROTECTED] Sent: Freitag, 30. November 2007 10:35 To: Ragnar Heil Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing On 11/29/07, Ragnar Heil [EMAIL PROTECTED] wrote: Hi our customer has got the following requirements: Req1) Master master replication supported, not only master / slave replication with only the master being writable. If you do have multiple slave systems they are only useful from a backup and standby perspective. Our Application must have a db-connection it can write to. Req2) Replication of schema should also be possible, not only data Req3) Not only a hot-standby-solution is needed. Load Balancing is wanted for the future. Currently I am looking at EnterpriseDB but it seems that they dont support multiple master-replication best regards Ragnar ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Have you looked at pgCluster or Continuent's uni/Cluster? -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing
WHich solution are you using now, Guido? Has anyone made experiences with cybercluster? I am thankful to hear comments and especially comparision to other products thanks Ragnar -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Guido Neitzer Sent: Freitag, 30. November 2007 21:03 To: Shane Ambler Cc: Postgresql General Subject: Re: [GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing On 30.11.2007, at 12:50, Shane Ambler wrote: I project where the latest news page shows the newest entry from March 2005 and the install talks only about PostgreSQL 8.0 isn't really inspiring confidence ... Although they aren't the fastest with releases, they really aren't as bad as that. Nicely said ... ;-) Yeah, I was looking at the wrong site. Maybe there should be a redirect to the new page to avoid that. I have last looked at pgcluster back in 2005 when I was trying to find an affordable multi master solution an the setup sounded so horrific that we spent about 10k EUR to get a different solution that is actually working fine now. Setting this up on just two machines was about 10 minutes work ... I hope the setup has changed to the better. cug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Fault Tolerance Master-master-Replication Solution needed
Hi, I am searching for a Fault Tolerance Master-master-Replication Solution which fulfills these requirements Req1) Master master replication supported, not only master / slave replication with only the master being writable. If you do have multiple slave systems they are only useful from a backup and standby perspective. Ad Mo must have a db-connection it can write to. Req2) A plan is needed for deploying changes on the database structure. What needs to be done to apply a change saved into a live db environment? Req3) Not only a warm-standby-solution is needed which is not scalable. Load Balancing is wanted for the future. Do you have experience with Cybercluster or Continuent uni/cluster? Sounds very promising. best regards Ragnar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Postgres High Availablity Solution needed for hot-standby and load balancing
Hi our customer has got the following requirements: Req1) Master master replication supported, not only master / slave replication with only the master being writable. If you do have multiple slave systems they are only useful from a backup and standby perspective. Our Application must have a db-connection it can write to. Req2) Replication of schema should also be possible, not only data Req3) Not only a hot-standby-solution is needed. Load Balancing is wanted for the future. Currently I am looking at EnterpriseDB but it seems that they dont support multiple master-replication best regards Ragnar ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Are these two creation commands functionally identical?
On fim, 2007-08-09 at 20:55 +, [EMAIL PROTECTED] wrote: I want to to know if these two are functionally equivalent. Is this: Create table sales ( saleid BigSerial NOT NULL, userid Bigint NOT NULL, parent_saleid Bigint NOT NULL, primary key (saleid) ) Without Oids; Alter table sales add foreign key (userid) references users (userid) on update restrict on delete restrict; Alter table sales add foreign key (parent_saleid) references sales (saleid) on update restrict on delete restrict; this constraint seems a bit strange to me. are you going to special-case the first insert into this table? Is the above functionally identical to: Create table sales ( saleid BigSerial NOT NULL, userid bigint references users(userid), parent_saleid bigint references sales(saleid), primary key (saleid) ) Without Oids; no these 2 are not fuctionally identical, because the second one does not have a NOT NULL constraint on the foreign keys, allowing you to insert: INSERT INTO sales (saleid,userid,parent_saleid) VALUES (100,null,100); gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [SQL] Using function like where clause
On mán, 2007-08-06 at 16:44 -0300, Ranieri Mazili wrote: 1) Can I use a function that will return a string in a where clause like bellow? select * from table where my_function_making_where() and another_field = 'another_think' you could have your function return a boolean instead of a string 2) Can I use a function that will return a string to return the list of columns that I want to show like below? select my_function_making_list_of_columns() from table where field_test = 'mydatum' no gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] create table liek view
On þri, 2007-08-07 at 15:23 +0200, Filip Rembiałkowski wrote: [EMAIL PROTECTED] create view v as select 1::int; CREATE VIEW [EMAIL PROTECTED] select * from v; int4 -- 1 (1 row) [EMAIL PROTECTED] create table t ( like v); ERROR: inherited relation v is not a table Why? Is there any logical reason for this? View is just a table with some rule ON SELECT... i do not know about the reason this is not allowed, but you can: create table t as select * from v limit 0; gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help with date math
On fös, 2007-07-20 at 11:08 -0400, Chris Hoover wrote: I need some help. I am trying to replicate a function from Sybase ASA, and am having difficulty. I need to be able to subtract 2 date (or timestamps) and return the results expressed in days, weeks, month, quarters, or years. How do I do this? I believe Postgres is returning the number of days when you subtract to days. However, I can not figure out how to get the results expressed as 7 months, would not 6 months be more logical? or 2 quarters, x weeks, or 0 years. have you tried to use extract() ? there is an old compatibility project on pgfoundry that has a datediff function, but i have no idea how well it copies the mssql behaviour. http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/mssqlsupport/datetimefunctions.sql?rev=1.1.1.1content-type=text/x-cvsweb-markup it looks a bit simplistic to me, but maybe that is exactly what you need. gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] count the number of bits set to 1 in a bit string field
On sun, 2007-07-15 at 15:35 -0400, Rajarshi Guha wrote: Hi, is there a built in function that will give me the number of bits that are set to 1 in a bit string field? no, but it should be trivial to do with pl/pgsql a naive implementation could be: create or replace function bitsetlen(bit) returns int as $$ declare i int; c int; begin c:=0; for i in 1..length($1) loop if substring($1,i,1)=B'1' then c:=c+1; end if; end loop; return c; end; $$ language plpgsql; gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Database Insertion commitment
On mán, 2007-07-09 at 11:24 +0400, Viatcheslav Kalinin wrote: Jasbinder Singh Bali wrote: If I have a series of Insert statements within a loop in a function on the same table. Would an Insert be able to see the values of previous insert in that table ? Functions are run in a single separate transaction this is not correct. functions run in the transaction where they were called, not in a separate one. gnari ---(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] Database Insertion commitment
On mán, 2007-07-09 at 03:34 -0400, Jasbinder Singh Bali wrote: My scenario is something like this. I'll try to make it modular and simple. Start Function A (Written in plperlu with no subtransactions) Insert 1 on tbl_abc; (fires trigger A) Insert 2 on tbl_abc; (fires trigger A) End Function A Start Trigger A check the value of col_abc in tbl_abc Start Activity A if col_abc in tbl_abc doesn't is not duplicated. End Trigger A Now, if Insert 1 inserts col_abc = 'xyz' in tbl_abc and Insert 2 inserts the same value of col_abc ='xyz' the its not able to see the value of insert 1 and erroneously starts Activity A that it should not actually. Do you think I am missing something vital here? I'm kind of stuck and confused because fundamentally Insert 2 should be able to see the value of Insert 1 as there is no subtransaction involved. maybe you should provide us with a simple test case, to illustrate your problem. gnari ---(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] How to obtain a sequence's increment value from the system catalog
have you tried: select * from my_seq; or are you looking for the functions currval() or lastval() http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html gnari On mán, 2007-07-09 at 09:43 +0200, Wouter Gazendam wrote: Hi, I'd like to query a sequence's increment value from the system catalog. I tried looking at http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html but I'm stuck here. Any ideas/help would be appreciated. Thanks, Wouter Gazendam ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Database Insertion commitment
On mán, 2007-07-09 at 05:22 -0400, Jasbinder Singh Bali wrote: The only difference between you test case my a sample test case that I would provide is the Perform part in the trigger function. In my program Perform part is about opening a socket connection with a Unix Tools server that runs traceroute and populates records in table test. then the inserts into table test are made in a separate transaction. One reason I see that new insert does't see the values of old insert is because as soon as socket connection is established, my trigger returns and 1st insert is complete even though I'm not sure whether the unix tools server has already inserted values in table test or not. There might be a time lag based on how fast traceroute returns, though right now its very fast. you should be able to see the row if the tools server has committed before the next trigger fires gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Duplicate Key Violates Unique Contraint when Updating a table
On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: I am experiencing thgis problem since this morning. I seldom heard about unique key constraint violation to happen when updating a table. It happens to 1 or few records at early stage, but then to all. My updating command is as following: update mytable set my_status='Y' where myid='ABC123567778'; Can anyone help? All helps and solutions are appreciated. sounds like you have a UNIQUE constraint involving the column my_status. did you recently create a new index on this table? what does psql say to: \d mytable gnari ---(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] Duplicate Key Violates Unique Contraint whenUpdating a table
On sun, 2007-06-24 at 17:58 +0800, carter ck wrote: From: Ragnar [EMAIL PROTECTED] On sun, 2007-06-24 at 16:55 +0800, carter ck wrote: I am experiencing thgis problem since this morning. I seldom heard about unique key constraint violation to happen when updating a table. It happens to 1 or few records at early stage, but then to all. My updating command is as following: update mytable set my_status='Y' where myid='ABC123567778'; Can anyone help? All helps and solutions are appreciated. sounds like you have a UNIQUE constraint involving the column my_status. did you recently create a new index on this table? what does psql say to: \d mytable No. The only unique key is the myid, which is also the primary key. I have extracted the records out from this database and dump all to a new database. Everything is fine. But, this is the second time I have encountered this problem. do you still have the old database around? if you do, does a REINDEX mytable; make your problem go away? gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to implement unusual constraint
On sun, 2007-06-24 at 09:54 +, danmcb wrote: Say I have a table, say my_table, that is self-referencing. It looks like this : id integer pk, orig_id integer references my_table(id), Now this set of rows would be legal id/orig_id 1 /1 2/1 3/1 4/4 5/4 but this not: id/orig_id 1 /1 2/1 3/1 4/1 5/4 in other words: the row pointed to by orig_id cannot reference any row other than itself. How might I implement this as a constraint? you can get around the limitation that subqueries are not allowed in CHECK constraints by using a function. this might get you on the right track: test=# create table foo (i int, o int); CREATE TABLE test=# create function foo_check(int) returns int language SQL AS 'select o from foo where i=$1'; CREATE FUNCTION test=# alter table foo ADD CHECK (foo_check(o)=o); ALTER TABLE test=# insert into foo values (1,1); INSERT 0 1 test=# insert into foo values (2,1); INSERT 0 1 test=# insert into foo values (3,1); INSERT 0 1 test=# insert into foo values (4,3); ERROR: new row for relation foo violates check constraint foo_o_check test=# insert into foo values (4,4); INSERT 0 1 test=# insert into foo values (5,4); INSERT 0 1 of course this example was very incomplete. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] about cursors
On lau, 2007-06-16 at 18:58 -0700, Ottavio Campana wrote: Martijn van Oosterhout wrote: Cursors are attached to the transactio and session, if either ends, the cursor dies with it... Have a nice day, another question: since they live in a transaction, how can they be used in web apps? as a rule, cursors are not used for web apps. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] initdb
On sun, 2007-06-17 at 19:59 +0100, John K Masters wrote: I feel somewhat embarrassed to post this but I can't get past the first post with Postgresql. I have installed onto a Debian testing system, created a space for the database cluster on /usr/local/pgsql/data, changed owner to postgres and changed permissions to 0700. However, when I try `initdb -D /usr/local/pgsql/data' I get Command not found i believe the debian packages supplies a wrapper for this operation. try: man pg_createcluster gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] initdb
On sun, 2007-06-17 at 21:50 +, Ragnar wrote: On sun, 2007-06-17 at 19:59 +0100, John K Masters wrote: I feel somewhat embarrassed to post this but I can't get past the first post with Postgresql. I have installed onto a Debian testing system, created a space for the database cluster on /usr/local/pgsql/data, changed owner to postgres and changed permissions to 0700. However, when I try `initdb -D /usr/local/pgsql/data' I get Command not found i believe the debian packages supplies a wrapper for this operation. try: man pg_createcluster sorry, of course you are not using the debian package. Andrej's answer is the correct one. gnari ---(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] Suppress checking of chmod 700 on data-dir?
On fim, 2007-06-07 at 10:38 +0200, Johannes Konert wrote: Hi postgresql-listmembers, for a backup-scenario I need to have access to the pgdata-directory as a different shell-user, but postgresqul refuses to start if chmod is not 700 on the directory. Is there a way to prevent postgres to check the data-dirs chmod 700 on startup (and while running) ? use sudo in your backup scenario, or run you backup as postgres gnari ---(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] Foreign keys and indexes
On þri, 2007-06-05 at 11:49 +0200, Marc Compte wrote: Does PostgreSQL create an implicit index also for foreign keys? no or must I create it explicitly? if you want one, yes. not everyone wants an index on all their foreign keys, but they can be useful in some circumstances. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Join field values
On þri, 2007-06-05 at 19:33 +0300, veejar wrote: Hello! I have such field in my table: field1 --- 1 2 3 4 5 I want to get such result from select: '1,2,3,4,5' - join all values in field1 with ',' // result must be varchar. Help to write SELECT-query for this task. create an aggregate function and use that in your select. http://www.postgresql.org/docs/8.2/static/sql-createaggregate.html google reminded me of the mysql compatibility project, whose implementation for group_concat() can be found here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/mysqlcompat/mysqlcompat/aggregate.sql?rev=1.2content-type=text/x-cvsweb-markup gnari ---(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] simple select question
On mán, 2007-06-04 at 12:12 +0300, Erol KAHRAMAN wrote:
hi guys,
i am newbie in postgresql. I need some help; i am trying to write
like this:
select * from TABLE where IN ('value1','valeue2',)
... WHERE what IN (...) ?
but is it possible to give values from file.
select * from TABLE where IN file
not really. you'd have to import your file
into a table first, possibly with COPY.
gnari
---(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] Return rows from function with expressions
On fim, 2007-05-24 at 13:59 -0700, novnov wrote: I've written many stored procedures in ms sql and a good many functions in postgres, but I'm rather unsure of how to get a list back from a postgres function which is not based on a table. Example from sql server: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[procPatient] @PatStatusID int = 0 AS BEGIN SELECT PatID, PatFName + ' ' + PatLName as pname FROM tblPatient WHERE PatStatusID = @PatStatusID END Output from the sproc above is like PatID pname 123Merton Baffled 129Jim Puzzled test=# create table pats (patid int, patfname text, patlname text, patstatus int); CREATE TABLE test=# insert into pats values (123,'Merton','Baffled',2); INSERT 0 1 test=# insert into pats values (129,'Jim','Puzzled',2); INSERT 0 1 test=# insert into pats values (132,'Joe','Confused',1); INSERT 0 1 test=# create type patrec as (patid int, patname text); CREATE TYPE test=# create or replace function getpats(int) returns setof patrec as $$ select patid,patfname|| ' ' || patlname from pats where patstatus=$1 $$ language SQL; CREATE FUNCTION test=# select * from getpats(2); patid |patname ---+ 123 | Merton Baffled 129 | Jim Puzzled (2 rows) hope this helps gnari ---(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] Views- Advantages and Disadvantages
On mið, 2007-05-09 at 12:46 +0530, Ashish Karalkar wrote: I have found out some of them n are as follows, but I want more reasons for not using views . I only got one Disadvantages: 1) Performance : If a view is defined by complex multitable query,then simple query against that view becomes a coplecated join, and it may take a long time to complete 2) UPDATES on a view are more tricky. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] turn off cache option
On fim, 2007-04-05 at 16:31 -0400, jungmin shin wrote: I'm executing a query invoking a UDF. It looks that Postgres use a cache for executing UDFs. Postgresql is not using a cache specially for executing UDFs, apart from only compiling the function once for each session. select a() Excution time of above statement is different each time. What is happening inside of the Postgres when I invoke a UDF in a query? It is taking 200ms or 11688ms . Postgresql relies on the filesystem and operating system to cache fisk buffers efficiently, so you are probably just seeing the effects of that, assuming that your function is doing the same thing each time. If a cache is used for this execution , can I turn off the cache option? why would you want to do that? gnari ---(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] queries stop using indexes
On lau, 2007-03-31 at 18:51 -0400, Jonathan Vanasco wrote: I have a table with 15 cols , there is an joint index on 5 of them there are ~15million records in the table, and it gets heavy use via update insert i recently noticed that after a day of use, the index is no longer being respected in some/all queries and a seq scan on the table is used i ran analyze, no change. i ran vacuum analyze, no change. i dropped the index, and recreated it -- its now used again. if you analyze the table after recreating the index, does it then stop using it? possibly you need to increase statistic targets on some or all of the index columns. a EXPLAIN ANALYZE could help figuring this out. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] stored queries and quoted strings
On fös, 2007-03-30 at 00:31 -0700, filippo wrote: Hello, I have a strange problem with stored queries like this $sql = qq/ SELECT city, country FROM countries WHERE city LIKE ? ORDER BY city /; $sthCity= $dbh-prepare($sql); my $tempCity = $dbh-quote(n%); $sthCity-execute($tempCity); the query doesn't return any value. It works only if I remove the - quote(). you do not have to use quote() on the parameters of a prepared statement, as this is already done for you. gnari ---(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 that does not use indexes
On þri, 2007-03-27 at 12:54 +0200, Denis Gasparin wrote: I have a query that performs a multiple join between four tables and that doesn't use the defined indexes. If I set enable_seqscan to off, the query obviously uses the indexes and it is considerable faster than normal planned execution with enable_seqscan=true. a few things you might try: fiddle with random_page_cost make sure effective_cache_size is set to a realistic value increase statistics target on critical columns, like ORDER.ORDER_DATE and join columns (and re-analyze) possibly get change the cast in your where clause: ORDER.ORDER_DATE::date between '2007-03-01' and '2007-03-27' to ORDER.ORDER_DATE between '2007-03-01'::timestamp and '2007-03-27'::timestamp gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question - Query based on WHERE OR
On mið, 2007-01-10 at 17:38 -0800, Mike Poe wrote: I'm a rank newbie to Postgres am having a hard time getting my arms around this. I'm trying to construct a query to be run in a PHP script. I have an HTML form were someone can enter either a last name or a social security number then query the database based on what they entered. My query looks like this: SELECT foo, baz, bar FROM public.table WHERE lastname ~* '$lastname' OR ssn='$ssn' I need to leave the last name a wildcard in case someone enters a partial name, lower case / upper case, etc. note that you really should not be using values directly from user input for $lastname and $ssn without doing some sanity checks on them. consider for example the user submitting a string containing a quote character. most languages provide functions to make such input safe. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Sorting
On mán, 2007-01-08 at 17:59 +0100, A. Kretschmer wrote: am Mon, dem 08.01.2007, um 10:21:38 -0600 mailte Bart McFarling folgendes: I have a column that is a varchar(6) I need to sort it by the rows that are integers 1st then the character ones or vice versa, I just need the values that can be converted to integer to sort by their numeric value. i.e 1, 2, 3, 4, 5, 10, 11, A, B, C instead of 1, 10, 11, 2, 3, 4, 5, A, B, C Any suggestions? perhaps something like this: test=*# select * from foo; w 10 1 A 3 C (5 rows) Time: 1.349 ms test=*# select w, case when w ~ '^[0-9]*$' then w::int else 1 end from foo order by 2,1; possible improvements: a) w ~ '^[0-9]+$' b) use NULL instead of 1 gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Database versus filesystem for storing images
On fös, 2007-01-05 at 15:49 -0500, Andrew Chernow wrote: I 100% agree. Use the database as a lookup into the filesystem. Don't load the database up with terabytes of non-searchable binary data? not sure how that would help you? I mean, how do you handle integrity with data outside the database? You don't, the file system handles integrity of the stored data. Although, one must careful to avoid db and fs orphans. Meaning, a record with no corresponding file or a file with no corresponging record. Always write()/insert an image file to the system within a transaction, including writing the image out to the fs. Make sure to unlink any paritally written image files. what happens if you rollback a transaction that just updated an image file? for that matter, what happens if one transaction is using or even reading an image while another is updating it? gnari ---(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] About auto_increment
On mán, 2007-01-01 at 21:21 -0800, Yesh wrote: I need to know how to increment a primary key field automatically in run time. Will SERIAL do what you want? gnari ---(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] Problem with index in OR'd expression
On mán, 2007-01-01 at 14:21 -0600, [EMAIL PROTECTED] wrote: Within the context of the function (after calling), these variables are constant and I'm attempting to use my OR syntax as shorthand to avoid having to use a dynamic statement *only* because of this situation. As I've mentioned, this approach seems to work with MSSQL 6.5+, which I assume we consider as a valid competitor to PG... if this didn't work anywhere else, I probably wouldn't even have brought it up. I'll re-iterate another question I attempted to pose which was: what have other PG application developers done in this situation? Is it most common to just use dynamic statements? ps/pgsql will prepare the statement caching the plan for subsequent calls, thus making this not easily optimized. to avoid this in ps/pgsql , I believe it is common to to make the statement dynamic by using EXECUTE, thus in effect making sure the query is planned each time. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] could not open relation:no such file or directory
[ you should reply to the list, not only to single posters. this way, more people see your reply,and can help you. Putting the list back, and quoting whole message. ] On lau, 2006-12-30 at 15:17 +0530, karthik nandagiri wrote: hello ragnar, i am using postgresql version8.0. it is installed on debean linux 2.2 OS. we have intalled dspace on that system.and its a live website running since 2003. we have nearly 2055 fulltext records loaded on this server. since 4 months the application was not running properly so we made a research of the problem . then we came to know that a table named item2bundle is not getting opened. the Query i executed was dspace=# select * from item2bundle; after pressing enteri get the below error Error:Could not open relation item2bundle. no such file or directory i am not able to retrieve records from table. it looks to me that you have experienced corruption, and some files may be missing in the data directory. you can check it yourself: your data directory might be something like /var/lib/postgresql/8.0/main/ this will contain a subdirectory base/ which will contain a directory for each of our databases to get the directory name for database dspace, do: select oid from pg_database where datname='dspace'; this database directory will contain several files. to find the file(s) making upa particular table do: select relfilenode from pg_class where relname='item2bundle'; hope this helps you investigate this. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] could not open relation:no such file or directory
On þri, 2006-12-26 at 02:43 -0800, karthik wrote: i facing a problem when trying to select values from a table in postgresql. do you face this problem with any table or only from a particular table? when i execute a query like select title from itemsbytitle; what do you mean by 'a query like' ? please show us the exact query i get error as Error:Could not open relation itemsbytitle. no such file or directory. please show us the exact error. what version postgres are you using ? are you using psql for your tests ? if would be best is you cut'n'pasted a short psql session that demonstrates the problem. something like: +++ $ psql -d test Welcome to psql 8.1.4, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# create table blah (i int); CREATE TABLE test=# insert into blah values (1); INSERT 0 1 test=# select * from blah; i --- 1 (1 row) test=# select * from blax; ERROR: relation blax does not exist ++ this makes it easier for us to guess at what your problem is, and simplifies reproducing it. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with index in OR'd expression
On mið, 2006-12-27 at 11:02 -0600, [EMAIL PROTECTED] wrote: I was referring to the situation where one has this in a WHERE clause: ((vConstant IS NULL) OR (Table.IndexedCol = vConstant)) where vConstant is a *constant* parameter in a pl/pgsql function. Reguardless of the issue whether pl/pgsql could be expected to optimize this case, I find it difficult to imagine a scenario where this kind of coding makes sense. I understand that in some cases on would like to do this with a *variable* to simplify logic, but what possible gain can be had from doing this with a constant, other that obfuscation? It does not make sense to spend CPU-time on optimizing a case that is not useful or common. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Needed files - embedded postgres
On fös, 2006-12-15 at 11:41 +0100, Henrik Zagerholm wrote: Hello list, I'm working on a project which tries to run postgre as a embedded database. I'm know looking at start up scripts and initdb scripts. What are the necessary files for running initdb and running postgres as an embedded database? All binary files and their deps are already fixed so the question is more related to files like postgres.bki and such. I think you should first figure out if postgres is suitable as an embedded database. (depends on what exactly you mean by an embedded database of course). Postgres has been designed as a server, and lots of implementation details might not make sense in an embedded context. you might be better served by SQLite, or some other such library. gnari ---(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] Needed files - embedded postgres
On fös, 2006-12-15 at 16:59 +0100, Henrik Zagerholm wrote: I think I need to specify what I mean with embedded. Its not that we try to embed it into an application. It is just run from a flash disk and the datafiles are put on standard raid attached disks. Its an embedded device not an embedded application. :) will you have concurrent connections? gnari ---(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] Online index builds
On mið, 2006-12-13 at 11:05 +0100, Csaba Nagy wrote: Yeah, we could add defenses one by one for the cases we could think of, but I'd never feel very secure that we'd covered them all. What you all forget in this discussion is that reindexing concurrently would have to be a highly administrative task, controlled by the DB admin... so whoever has a big index to be reindexed can schedule it so that no other schema changes occur to the table until the reindex is finished. well, if this is a command that would nly be made manually by an administrator, why do we need a separate command for this. the DBA can just create a new index concurrently, and then perform the DROP and rename in a transaction whenever he thinks it is safe to take the exclusive lock needed for a short while. the only functionality missing compared to a REINDEX CONCURRENTLY, is the handling of a PRIMARY key constraint linked to the index while this happens, but that seems a much simpler problem to solve separately. gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a question on SQL
On þri, 2006-12-12 at 16:47 +, Tomi N/A wrote: Don't really know where to ask this...the general mailing list sounds like the closest. Let's say I have three tables: owner, factory and product with a 1:N relationship at each step. Assuming that a product has a production date, how would you go about returning a factory for every owner, where the returned factory is the factory that produced the oldest product of it's owner? Hints? someting like this maybe: select distinct on (owner.id,factory.factoryid) * from owner,factory,product where your join contitions order by owner.id,factory.factoryid,production_date gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to use outer join in update
On fös, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote: Andrus wrote: In my current DBMS I can use create table t1 ( f1 int, f2 int ); create table t2 ( f3 int, f4 int ); update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 is this not effectively an INNER JOIN ? the OP needed a LEFT JOIN. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to use outer join in update
On fös, 2006-12-08 at 10:17 +, Ragnar wrote: On fös, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote: Andrus wrote: In my current DBMS I can use create table t1 ( f1 int, f2 int ); create table t2 ( f3 int, f4 int ); update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 is this not effectively an INNER JOIN ? the OP needed a LEFT JOIN. this can be done in 2 operations easily: update t1 set f1=t2.f3 from t2 where f2 = t2.f4; update t1 set f1=null where not exists (select f3 from t2 where f2=f4); it can also be done in one operation with a self join: update t1 set f1=j.f3 from (t1 t1b left join t2 on t1b.f2=t2.f4) as j where t1.f2=j.f2; gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] error with Subquery
On fös, 2006-12-08 at 13:58 +0300, Max Bondaruk wrote: Hi! error with Subquery alias... SELECT *,(SELECT COUNT(id) FROM articles a WHERE a.lft articles.lft AND a.rgt articles.rgt) AS depth FROM articles where (depth 3) ORDER BY lft you cannot refer to depth in the where because it is not an attribute of the table in the FROM list. it may be more obvious if we replace the subquery with a constant: SELECT *, 999 as depth FROM articles WHERE (depth 3) however you should be able to do SELECT * FROM ( SELECT *, (SELECT COUNT(id) FROM articles a WHERE a.lft articles.lft AND a.rgt articles.rgt ) AS depth FROM articles ) AS foo WHERE (depth 3) ORDER BY lft gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Anything I can do to speed up this query?
[ Marcus, you should folow up to the lists, so that other people can benefit from the discussion ] On fim, 2006-12-07 at 09:25 +0100, Marcus Engene wrote: Ragnar skrev: On mið, 2006-12-06 at 17:34 -0500, Wei Weng wrote: On Tue, 2006-12-05 at 15:56 -0500, Wei Weng wrote: I have a table that has roughly 200,000 entries and many columns. SELECT Field1, Field2, Field3... FieldN FROM TargetTable ORDER BY Field1 DESC; you might consider CLUSTER Would putting the index on a separate tablespace on another harddisk have a similar effect? we haven't had any real information from the OP. as far as I can tell, he has not answered any questions about his case, so we really have no idea where his problem is. if he has extra harddisks that are not used, there may be many ways of taking advantage of that. gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Online index builds
On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote: On Wed, 2006-12-06 at 15:00 -0500, Chris Browne wrote: Let me add another question to this; this might possibly be worthy of a TODO for 8.3 or so... What if I wanted to: ALTER TABLE distributors ADD PRIMARY KEY CONCURRENTLY (dist_id); Interesting, I was just thinking about this today as well. I am thinking it would be nice if we could: ALTER TABLE SET PRIMARY KEY INDEX foo_pkey; If it's already got a primary key we switch the primary key to be the new primary key (throwing an error if the columns don't match up to the existing primary key, not sure what you mean by this or if it's not unique). must also be NOT NULL If not, the primary key attribute is added to the existing index and the columns in the index now make up the primary key (throwing an error if the index is not unique). What about existing foreign key constraints ? as the only function of the PRIMARY key property of an index is making it the default target of a foreign key reference, you would have to decide what implications this has. Possibly none, as I am not sure the foreign key constraint remembers if the target was a primary key or not. also, your proposed syntax muddies the relationship between the PRIMARY KEY constraint and the existence of an INDEX. There is no such relationship in the SQL standards. possibly more appropriate would be ALTER TABLE SET PRIMARY KEY (columns) and an error issued if no UNIQUE NOT NULL index is found on the relevant columns one other question is what shuld happen to the original index that was implicitly created. should it be dropped automatically ? gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] VACUUM and transactions in different databases
On fim, 2006-12-07 at 11:28 -0500, Tom Lane wrote: Bill Moran [EMAIL PROTECTED] writes: In response to Alvaro Herrera [EMAIL PROTECTED]: Of course they are able to complete, but the point is that they would not remove the tuples that would be visible to that idle open transaction. I would expect that, but the OP claimed that vacuum full waited until the other transaction was finished. No, she didn't claim that. As far as I see she was just complaining about the failure to remove dead tuples: If I have a running transaction in database1 and try to vacuum database2 but the dead tuples in database2 cannot be removed. well actually, there was also this: On fim, 2006-12-07 at 00:57 +0100, Cornelia Boenigk wrote: Hi Bill Can you run a vacuum full, and does it reclaim the space? I tried but it hangs. and also this: On fim, 2006-12-07 at 01:03 +0100, Cornelia Boenigk wrote: as soon as I committed the open transaction the hangig vacuum full completed and the table was vacuumed: gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Online index builds
On fim, 2006-12-07 at 09:27 -0800, Jeff Davis wrote:
On Thu, 2006-12-07 at 12:26 +, Ragnar wrote:
On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote:
Interesting, I was just thinking about this today as well. I am thinking
it would be nice if we could:
ALTER TABLE SET PRIMARY KEY INDEX foo_pkey;
If it's already got a primary key we switch the primary key to be the
new primary key
(throwing an error if the columns don't match up to the
existing primary key,
not sure what you mean by this
In my suggestion, if the table already has a primary key, then you can
only set the primary key index to be an index with exactly the same
columns as the existing primary key index.
Why would you do that?
I saw the use-case of when you have a primary key and a
surrogate key , and decided you wanted the surrogate key to be the
primary key after all, maybe because the
natural key you had used turned out not to be a good
candidate.
or if it's not unique).
must also be NOT NULL
Indexes can't be NOT NULL; NOT NULL is a constraint.
Sorry, I got confused by the UNIQUE in the create index syntax:
CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]
...
What about existing foreign key constraints ?
as the only function of the PRIMARY key property of an
index is making it the default target of a foreign key
reference, you would have to decide what implications
this has. Possibly none, as I am not sure the foreign
key constraint remembers if the target was a primary key
or not.
Doesn't matter. Foreign keys don't reference an index, they reference a
set of attributes. I am just trying to provide an ability to change the
underlying unique index that is used to implement the unique constraint
that is necessary for all primary keys.
I was still imagining here that you would want a
different set of attributes froyour primary key.
gnari
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] VACUUM and transactions in different databases
On fim, 2006-12-07 at 20:04 +0100, Cornelia Boenigk wrote: Sorry, i was out [ snip demonstration of blocked vacuum full] running on pg 8.1.4 on Fedora 5 could not duplicate this. can you show us the contents of pg_locks and pg_stat_activity while the VACUUM is blocked? gnari ---(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] Online index builds
On fim, 2006-12-07 at 13:57 -0800, Jeff Davis wrote: On Thu, 2006-12-07 at 20:07 +, Ragnar wrote: On fim, 2006-12-07 at 09:27 -0800, Jeff Davis wrote: On Thu, 2006-12-07 at 12:26 +, Ragnar wrote: On mið, 2006-12-06 at 18:22 -0800, Jeff Davis wrote: Interesting, I was just thinking about this today as well. I am thinking it would be nice if we could: ALTER TABLE SET PRIMARY KEY INDEX foo_pkey; You've got a valid use-case, but it's completely different from the one I suggested. I wanted to be able to build an index concurrently (with the new functionality in 8.2) and then switch the primary key to use that new index, and then drop the old index. The reason is because that allows a 0-downtime index rebuild on a primary key's index without losing it's primary key status. my point was just that 'primary key' is really just a property of a set of attributes, and it is just incidental that postgres enforces this property with an index. so if if a ALTER TABLE SET PRIMARY KEY is implemented, it should involve a set of attributes, but not an index. in your use case, the ALTER should not really be needed. lets say you have PRIMARY KEY (a,b) on some table. you decide you want to rebuild the primary key concurrently. just build a new index on (a,b). if you then drop the old index, the primary key constraint can still be enforced by the new index, so the DROP should be allowed to proceed, without affecting the constraint. on the other hand, the PRIMARY KEY property is really only there because the standards say so, but does not have a great value in my opinion, so the ability to alter it would not be high on my priority lists. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Anything I can do to speed up this query?
On mið, 2006-12-06 at 17:34 -0500, Wei Weng wrote: On Tue, 2006-12-05 at 15:56 -0500, Wei Weng wrote: I have a table that has roughly 200,000 entries and many columns. ... I think I have discovered the reason for why the query runs so slow. The original query has an ORDER BY Field1 clause that I forgot to put in my email. SELECT Field1, Field2, Field3... FieldN FROM TargetTable ORDER BY Field1 DESC; in that case you did not provide us with a useful explain analyze What is the effective way to optimize this query(or to optimize the system) to run a little faster than it does now? you might consider CLUSTER gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.2 Now Available
On þri, 2006-12-05 at 14:41 -0500, Josh Berkus wrote: After eight months of development and five months of integration and testing, the PostgreSQL Global Development Group now announces the availability of PostgreSQL version 8.2 (our 14th public release). ... For highlights of the release, please see the press kit: http://www.postgresql.org/about/press/presskit82.html.en Now that this has been announced, should not http://www.postgresql.org/docs/current/ and co be redirected to http://www.postgresql.org/docs/8.1/ instead of http://www.postgresql.org/docs/8.2/ in particular, the press release's link to the Release Notes brought me to http://www.postgresql.org/docs/current/static/release.html which showed the 8.1 Release Notes. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Anything I can do to speed up this query?
On þri, 2006-12-05 at 15:56 -0500, Wei Weng wrote: I have a table that has roughly 200,000 entries and many columns. SELECT Field1, Field2, Field3... FieldN FROM TargetTable; The thing is on this machine with 1Gig Ram, the above query still takes about 20 seconds to finish. And I need it to run faster, ideally around 5 seconds. Test= show shared_buffers ; shared_buffers 60800 do you mean that you want the data to be cached, or do you need it to be fast the first time? if you want it to be cached you might be better served with a lower shared buffers, to leave more memory tothe OS cache. If you just need more speed reading from the disks, you probably just need a faster disk subsystem, although should should make sure the table does not contain a lot of dead rows, by doing a VACUUM FULL or a CLUSTER once before trying again. If Field1, Field2 ... FieldN are a small subset of the tables row-width, you should consider vertical partitioning, to minimize IO needed for this particular query, although this will not help much if the subset is not fixed. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Data corruption problem...
I haven't seen any replies to this, so I will, although I am not a j2ee/jdbc person. On fim, 2006-11-30 at 14:14 -0700, Nathan Wilhelmi wrote: Server: 8.0.3 on Solaris 9 JDBC Driver: 8.0.311 ... delete from X where id in (select bad_id from Z where name='qwerty'); delete from Y where id in (select bad_id from Z where name='qwerty'); delete from Z where name='qwerty'); ... Now we have J2EE (Tomcat hosted) app that access this database via connection pool / JDBC driver. tomcat version ? 1) Start the J2EE app and do some work to access the database, although I don't think this matters. 2) Shut down the app server, and as result the connection pool starts to shut down. 3) Once the connection pool has started to shut down execute the statements listed above from PGAdmin. 4) PGAmin will hang for a second like it's waiting for locks. 5) The statement will return normally, reporting the rows affected from the last statement. 6) Tables X and Y are now empty. This is not good. ... Has anyone seen this before, any suggestions how to debug this or where to start looking? I would start with enabling statement logging, and see exactly what SQL the server is executing during the scenario. gnari ---(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] sequence help
On þri, 2006-11-28 at 01:12 -0800, Matthew Peter wrote:
I'll jump right in with an example
create sequence foo start with 1;
create view foobar as select *, nextval('foo') from bar;
The problem is I need a nextval()-like method that returns the iterations
without
updating the foo sequence. Therefore, maintaining the sequences original
value by
starting at 1 each time the foobar VIEW is invoked.
sounds like you do not really want sequences. (think of
concurrent selects from such a view)
the most natural way to do this is in the application
layer, but if you have to do this in the backend, you
could do it with a pl/pgsql user defined set returning
function, I guess.
gnari
---(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] NULLs ;-)
On þri, 2006-11-28 at 09:42 -0500, John D. Burger wrote: Scott Ribe wrote: where a b or (a is null and b is not null) or (a is not null and b is null) In the absence of IS DISTINCT FROM, I think this has the same semantics: where coalesce(a, b) coalesce(b, a) sorry, but no. test=# create table logic (a int, b int); CREATE TABLE test=# insert into logic values (null,null); INSERT 34495399 1 test=# insert into logic values (null,1); INSERT 34495400 1 test=# insert into logic values (1,null); INSERT 34495401 1 test=# insert into logic values (1,1); INSERT 34495402 1 test=# select a,b, coalesce(a, b) coalesce(b, a) as coal, a IS DISTINCT FROM b as dist from logic; a | b | coal | dist ---+---+--+-- | | | f | 1 | f| t 1 | | f| t 1 | 1 | f| f (4 rows) test=# gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to implement backup protocol
On þri, 2006-11-28 at 19:23 +0200, Andrus wrote: Richard, I really do'nt want to open separate port for backup only. Pelase, can you recomment a solution which uses port 5432 owned by Postgres I do not want to advice you to do things that might be counter your company's security policies, but you could set up a portforwarder on your database machine to pass incoming port 5432 requests from the backup machine to sshd, but let all other source ips go to postgres alternatively, if you have control of cron, and if the firewall restrictons are for incoming only, and if you have open ssh port on some other machine, such as the backupserver, you can have cron do the compressed backup, and send it via scp to the backupserver. How to force postmaster to run the tool which system provides when it receives backup request instead of postgres child process ? if you cannot control cron, but the firewall restrictions are only for incoming requests, you might try using some untrusted procedural language to start a backup script locally, that finishes by copying the compressed backup to the backupserver. whatever you choose to do, you should still get permission to do it. security admins are not amused when users use tricks to get around restrictons. maybe they would consider opening the ssh port if you make it clear that they may restrict it to requests from the backup machine? gnari ---(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] NULLs ;-)
On þri, 2006-11-28 at 12:28 -0700, Scott Ribe wrote: where a b or (a is null and b is not null) or (a is not null and b is null) In the absence of IS DISTINCT FROM, I think this has the same semantics: where coalesce(a, b) coalesce(b, a) sorry, but no. So it would have to be where coalesce(a, b, 0) coalesce(b, a, 0) for your example with ints, and likewise some default value for other column types... no cigar. test=# select a,b, coalesce(a, b, 0) coalesce(b, a, 0) as john, a IS DISTINCT FROM b as dist from logic; a | b | john | dist ---+---+--+-- | | f| f | 1 | f| t 1 | | f| t 1 | 1 | f| f (4 rows) gnari ---(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] PGDATA
On fös, 2006-11-24 at 16:14 -0800, [EMAIL PROTECTED] wrote: Well.. you are correct! After studying the contents of /etc/init.d/postfresql, I put PGDATA=/home3/mylargedisk/data in /etc/sysconfig/pgsql/postgresql and it now works. So what the . is this PGDATA!! It must be a trivial concept cause nobody else is worried about how it works!! And How do I get each database to be in a different directory?!! are you looking for: CREATE TABLESPACE myts LOCATION '/foo'; CREATE DATABASE mydb WITH TABLESPACE=myts; ? gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] IN clause
On fös, 2006-11-24 at 10:10 -0800, Richard Broersma Jr wrote:
That is fine
but what I was actually expecting is this
if
select * from table where col_name in (null, 'a', 'b');
to return those rows where col_name is null or if it = a or if it is = b
But i think in does not not support null queries , am i right?
that is correct: if col_name was actually 'a' then you would get:
'a' in ( null, 'a', 'b', ...) works the same as:
'a' = null ~ resolves to Unknown
or
'a' = 'a' ~ resovles to true
or
'a' = 'b' ~ resovles to false
or
...
so you end up with:
(unknown or true or false) = true
but if you have
(unknown or false or false) = false
yes, except I think you meant:
(unknown or false or false) = unknown
as can be demonstrated by:
test=# \pset null 'null'
Null display is null.
test=# select (null or true);
?column?
--
t
(1 row)
test=# select (null or false);
?column?
--
null
(1 row)
and indeed the IN operator does behave this way:
test=# select 'a' in (null,'a');
?column?
--
t
(1 row)
test=# select 'a' in (null,'b');
?column?
--
null
(1 row)
test=# select 'a' in ('a','b');
?column?
--
t
(1 row)
test=# select 'a' in ('b','c');
?column?
--
f
(1 row)
and finally: NULL IN (NULL,'b') will return NULL
because it will translate to
(NULL = NULL) or (NULL = 'b')
test=# select null in (null,'b');
?column?
--
null
(1 row)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] BUG #2772: Undefined Subroutine Pg::connectdb ( );
On mið, 2006-11-22 at 13:28 -0600, Bruno Wolff III wrote: This isn't a bug and you should have asked on a different list. I am going to move this over to pgsql-general. On Mon, Nov 20, 2006 at 12:07:25 +, S.Balaji [EMAIL PROTECTED] wrote: In Redhat 7.2 use Pg; command in perl Scripts will working with out any Problem.But in CentOS use Pg command will not Working it shows error is cannot Locate Pg.pm in Include Directory. this probably means that the package containing the old Pg module has not been installed. have you checked if such a CentOS package exists ? for example in debian, this modules comes with the package libpg-perl After I am Solved this Problem by export PERLLIB=/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/DBD/ command. this certainly does not Solve any problem, as this would fool perl into loading the module DBD::Pg instead of Pg. you should not do this, as this may well interfere with the operation of the correct Pg module if you manage to install it from a rpm ,or by source as suggested by Bruno below. After it shows error is undefined subroutine Pg::connectdb unfortunately DBD::Pg does not define connectdb() :-) command.how can i solve this Problem without changing the use Pg command or any other equivalent command is there.Please help me to solve this Problem The Pg perl module is not getting much support, but you can get a copy of the source from ftp://gborg.postgresql.org/pub/pgperl/stable/Pg-2.1.1.tar.gz and build it yourself. yep gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Table design - unknown number of column
On fim, 2006-11-09 at 10:56 +0100, Alban Hertroys wrote: Richard Ollier wrote: For a project I have a table containing products and flags. The columns of this table are of 2 kinds : - Not null data (id, column1, column2) - Flags (100 different flags set to 1 or 0) Over the time the number of flag will increase from 100 to 200 or more. Having 200 flags as 200 fields... Writing queries on that is going to be painful. I would probably look at bitwise operations, although 200 bits is quite a bit larger than a bigint. Maybe an array of ints would work... maybe the BIT VARYING datatype could be useful. http://www.postgresql.org/docs/8.1/interactive/datatype-bit.html gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strange behavior on non-existent field in subselect?
On þri, 2006-10-17 at 15:58 -0700, Ken Tanzer wrote: We're a little puzzled by this (apparently) strange behavior, and would be curious to know what you folks make of it. Thanks. not sure exactly what you are referring to, but: (rearranged quotes to group output with SQL) SELECT foo_field FROM par; psql:strangefield.sql:11: ERROR: column foo_field does not exist hopefully, no mystery here. SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par); if par is empty, then this SELECT will return 0 rows, otherwise it is equivalent to SELECT foo_field from foo foo_field --- (0 rows) foo is empty, so no rows returned INSERT INTO foo VALUES (1); SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par); foo_field --- (0 rows) par is empty, so the IN operator fails for the foo row INSERT INTO par VALUES (1); SELECT foo_field FROM foo WHERE foo_field IN (SELECT foo_field FROM par); foo_field --- 1 (1 row) when par contains at least one row, the subselect will return foo_field once per row of par. the IN operator will ignore duplicates, so the result is the same for any number of rows in par greater than 0 gnari ---(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] serial column
On sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote: I need to develop a serial column that always starts at 1 and is sequential even after deletes. what exactly do you mean? say you have rows where your columns has values 1,2,3 and 4. you now delete the row where the value is 2. what do you want to happen? a) the rows with values 3 and 4 are changed tocontain 2 and 3 ? b) the next 2 values to be inserted to be 2 and then 5 ? c) something else ? gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] serial column
On sun, 2006-09-24 at 14:29 -0700, Bob Pawley wrote: Choice a. I am using the numbers to identify devices. If a device is deleted or replaced with another type of device I want the numbering to still be sequential. have you tried to implement ths using triggers? gnari ---(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] Can i switch to the use of persistent connections
On fim, 2006-09-21 at 23:40 -0700, Najib Abi Fadel wrote: i have an already running web application with a lot of users. All the connections to the postgresql database are not persistent. I was wondering if it is possible to switch to persistent connection without having problems in my current in production application in order to boost performance ? take a look at pgpool http://pgpool.projects.postgresql.org/ gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Select Cast Error
On fim, 2006-09-21 at 21:57 -0400, Brad Budge wrote: I have a field that is varchar(15) type and an example of data I'm working with is (PROJ-0001-06) select cast((max(substring(test.test from 6 for 4))) as integer) + 1 FROM test where max(substring(test.test from 11 for 2)); as someone already pointed out, this where clause is just equivalent to WHERE '06' and does not mean anything List below is a better idea of what my table looks like and the result I need. PROJ-0004-05 PROJ-0001-06 PROJ-0002-06 PROJ-0003-06 When I run my select statement I want to return the number 4. The idea is that I need the next highest number in the middle but take in consideration that the highest trailing numbers take president. if all the values are really formatted like this, you could ORDER BY to get the value you want: SELECT 1 + CAST(substring(test.test from 6 for 4) AS INTEGER) FROM test ORDER BY substring(test.test from 11 for 2) DESC, substring(test.test from 6 for 4) DESC LIMIT 1; gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
Thanks for the advice, I tried to install the language-packs that ubuntu supports. I can no initialize a se_SV.utf8 cluster, but it seems there is no support form latin1. Best whishes /Ragnar ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
Hi, When I try to initialize a new cluster like this: /usr/lib/postgresql/8.1/bin/initdb --locale=sv_SE.ISO-8859-1 -D sv_SE_data/ I get the error: initdb: invalid locale name sv_SE.ISO-8859-1 This is on Ubuntu that it fails. It works fine on my slackware installation. I tried compile postgresql from source on ubuntu, as I thought it might had been the binary installation that was not correct, but the source installation failed the same way. I have a locale for sv in /usr/share/locales. What on earth can it be that is wrong? /Ragnar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] initdb: invalid locale name sv_SE.ISO-8859-1
On þri, 2006-09-12 at 09:43 +0200, Ragnar Österlund wrote: When I try to initialize a new cluster like this: /usr/lib/postgresql/8.1/bin/initdb --locale=sv_SE.ISO-8859-1 -D sv_SE_data/ I get the error: initdb: invalid locale name sv_SE.ISO-8859-1 This is on Ubuntu that it fails. It works fine on my slackware installation. I tried compile postgresql from source on ubuntu, I think that nowadays Ubuntu does only create UFT-8 locales. try man locale-gen gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] counting days
On þri, 2006-08-29 at 19:35 +0100, garry saddington wrote: I need to count the days between two dates that are not saturdays or sundays. I have read the manual and searched the lists but I am struggling. I can count the days but am finding difficulty excluding sat and sun from the count. I need this without reference to any tables. Does anyone have any pointers please. how about something like: # assuming d id number of days # and dow is day of week of first day (0-7;sun=0) wholeweeks=days div 7 partial=days mod 7 # adjust dow to mon=0,tue=1...sun=6 dow=(dow+6) mod 7 # count 5 weekdays for each whole week wd=5*wholeweeks # add all days of partial week wd=wd+partial # substract 1 if saturday was included if dow+partial=6 then wd=wd-1 # substract 1 if sunday was included if dow+partial=7 then wd=wd-1 # now wd is result hope this helps gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] counting days
On þri, 2006-08-29 at 17:32 -0400, AgentM wrote: Perhaps you are trying to count business days? Those vary around the world and you would certainly need a table to hold the holidays and such. If you just want to count the number of non-weekend-days, then get the interval as days and then it's a simple matter of math: 2*daysInterval/7+(daysInterval % 7 == 6 ? 1 : 0) looks like you are calculating the number of weekend days here, not the number of weekdays. looks like you are assuming the interval started on a monday, too. gnari This holds assuming you are using dates and not timestamps- you may wish to subtract 1 depending on what the dates represent. -M On Aug 29, 2006, at 14:35 , garry saddington wrote: I need to count the days between two dates that are not saturdays or sundays. I have read the manual and searched the lists but I am struggling. I can count the days but am finding difficulty excluding sat and sun from the count. I need this without reference to any tables. Does anyone have any pointers please. Regards ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] optimising UNION performance
On mán, 2006-08-28 at 10:23 +0200, Rafal Pietrak wrote: Hi all, Is there a way to speed up the query to my 'grand total' logfile, constructed as a UNION of smaller (specialised) logfiles? I do not know if this is relevant to your case, but possibly you can use a UNION ALL instead of a UNION. In many cases the UNION ALL gives petter performance, as the unique step can be skipped, as well as a sort needed by the unique. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] speeding up big query lookup
On fös, 2006-08-25 at 18:34 -0400, Silvela, Jaime (Exchange) wrote: This is a question on speeding up some type of queries. I have a very big table that catalogs measurements of some objects over time. Measurements can be of several (~10) types. It keeps the observation date in a field, and indicates the type of measurement in another field. I often need to get the latest measurement of type A for object X. The table is indexed by object_id. one popular way is to create a composite index: CREATE INDEX object_val_id_type_date ON object_val(object_id, object_val_type_id, observation_date); then you could SELECT * FROM object_val WHERE object_id=? AND object_val_type_id=? ORDER BY observation_date DESC LIMIT 1; Hope this helps gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] HOW SELECT
On fös, 2006-08-25 at 13:20 +0530, shyju c.k wrote: hai all i have table ,as follows [reformatted] id_int | vid_int | name_chv | address_txt 2 | 12 | ram| address1 3 | 12 | joy| address2 4 | 14 | shyju | address3 5 | 14 | shyju | address4 6 | 30 | thomas | address5 7 | 30 | muhamd | address6 8 | 30 | rahim | address7 here only vid_int=14 , have name=shyju repeated [ how query for list the following records ] 4 | 14 | shyju | address3 5 | 14 | shyju | address4 if I understand you correctly, you want duplicate (vid_int,name_chv). to just find the duplicated values, you could do: SELECT vid_int,name_chv FROM mytable GROUP BY vid_int,name_chv HAVING count(*) 1; to get the full rows, you could for example do a join to this. SELECT t.* FROM mytable AS t NATURAL JOIN ( SELECT vid_int,name_chv FROM mytable GROUP BY vid_int,name_chv HAVING count(*) 1 ) AS g; test=# create table mytable (id_int int,vid_int int, name_chv text, address_txt text); CREATE TABLE test=# insert into mytable VALUES (2,12,'ram','address1'); INSERT 34480915 1 test=# insert into mytable VALUES (3,12,'joy','address2'); INSERT 34480916 1 test=# insert into mytable VALUES (4,14,'shyju','address3'); INSERT 34480917 1 test=# insert into mytable VALUES (5,14,'shyju','address4'); INSERT 34480918 1 test=# insert into mytable VALUES (6,30,'thomas','address5'); INSERT 34480919 1 test=# insert into mytable VALUES (7,30,'muhamd','address6'); INSERT 34480920 1 test=# insert into mytable VALUES (8,30,'rahim','address7'); INSERT 34480921 1 test=# select * from mytable; id_int | vid_int | name_chv | address_txt +-+--+- 2 | 12 | ram | address1 3 | 12 | joy | address2 4 | 14 | shyju| address3 5 | 14 | shyju| address4 6 | 30 | thomas | address5 7 | 30 | muhamd | address6 8 | 30 | rahim| address7 (7 rows) test=# SELECT t.* test-#FROM mytable AS t test-# NATURAL JOIN test-# ( SELECT vid_int,name_chv test(#FROM mytable test(#GROUP BY vid_int,name_chv test(#HAVING count(*) 1 test(# ) AS g; id_int | vid_int | name_chv | address_txt +-+--+- 4 | 14 | shyju| address3 5 | 14 | shyju| address4 (2 rows) hope this helps. gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] B+ versus hash maps
On fös, 2006-06-16 at 11:39 +0530, surabhi.ahuja wrote: [in response to Jim advising not to set random_page_cost=1] in that case, should i set enable_seqscan parameter to off at the time of starting postmaster? that is unlikely to be a good stategy. because i have seen that even thou the index exists it still goes for seq scan there can be many situations where a sequential scan is the correct thing to do. it is not clear whether you have a case that needs to be optimized, or if you are just assuming that a sequential scan must is wrong. things that may be causing wrong choice of seqscan include: table has not been ANALYZED lately some columns need higer statistics target table contain few rows table is not correctly indexed search using non-indexable operators query phrased in a way that prevents use of indexes so before jumping to setting wild global settings, you should make sure you understand what your problem really is. the best way to do that is by looking at the output of EXPLAIN ANALYZE. show us the output of EXPLAIN ANALYZE yourquery, along with details about relevant columns, indexes, so that we can give more concrete advice. gnari ---(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] [ODBC] information request on postgresql -- oracle
On mán, 2006-05-29 at 10:21 +0200, Glauco Mancini wrote: i'm looking for a method to connect natively a postgresql db to oracle ( maybe via odbc ? ) with something similar to the oracle dblink. I connected successfully a oracle instance to a postgresql instance using unix-odbc, now i need to proceed on the other way. Can you please help me with some suggestion ? maybe plperlu and DBD::Oracle ? gnari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] move from 1 database to another
On fim, 2006-05-25 at 11:52 -0700, P.M wrote: I would like to know how can i move from 1 database to another one ? basically i want to run a *.sql script in which i connect under a particular database (for example : template1 or postgres) and my *.sql script should create a new database (for example : test) and after execute several SQL command under test database. I guess you are asking how to connect to a new database in the middleof a sql script Assuming the script will be run by psql, you can use \connect test gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL Binary Data Questions
On fös, 2006-05-19 at 12:21 -0700, Siah wrote: Some pointers could help. any arguments pro/against saving bin data in db? pro: backups can be made with pg_dump only. if binary data is stored in filesystem, your backup procedure gets more complicated, specially if your binary files can get updated during backup. gnari ---(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] How would I write this query...
On sun, 2006-04-30 at 11:32 -0700, Jim Fitzgerald wrote: I have two tables, one of them has names of people and an associated integer ID. The other table is a list of the people (from the first table) by their ID number that have signed up for a class. How would I write a query that would list all the people from the first table that do not have any entries in the second table? Basically, I want a listing of all my people who have not signed up for the class. try SELECT * FROM people LEFT JOIN classes using (peopleid) WHERE classes.classid IS NULL; gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] regarding contains operator
On mið, 2006-03-08 at 15:13 +0530, surabhi.ahuja wrote: if it is not ther can i write my own operators? abd use them please send me the link where i can find documnetation on the same http://www.postgresql.org/docs/8.1/interactive/extend.html http://www.postgresql.org/docs/8.1/interactive/xoper.html gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] query timeout
On fim, 2006-03-02 at 11:03 -0700, Rick Gigger wrote: Never-mind that. I'm assuming statement_timeout is what I need? Yes, but take care if you change this in postgresql.conf: some queries might reasonaby be expected to take longer than 5 minutes, such as VACUUM. gnari On Mar 2, 2006, at 11:01 AM, Rick Gigger wrote: Is there a way to put a timeout on a query so that if it runs longer than 5 minutes or something it is just automatically terminated? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Physical column size
On fös, 2006-03-03 at 11:03 +0100, Paul Mackay wrote: Hi, I've created a table like this : CREATE TABLE tmp_A ( c char, i int4 ); And another one CREATE TABLE tmp_B ( i int4, ii int4 ); I then inserted a bit more than 19 million rows in each table (exactly the same number of rows in each). The end result is that the physical size on disk used by table tmp_A is exactly the same as table tmp_B (as revealed by the pg_relation_size function) ! Given that a char field is supposed to be 1 byte in size and a int4 4 bytes, shouldn't the tmp_A use a smaller disk space ? Or is it that any value, whatever the type, requires at least 4 bytes to be stored ? the int4 needs to be aligned at 4 bytes boundaries, making wasted space after the char. this would probably be the same size: CREATE TABLE tmp_C ( c char, cc char, i int4 ); and this would be smaller: CREATE TABLE tmp_D ( c char, cc char, ccc char, ); P.S.: I did not actually check to see if the char type needs to be aligned, by I assumed not. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is there a way to check which indexes are being used
On fim, 2006-02-23 at 14:15 +0100, Dragan Matic wrote: We have a 50 GB database (currently using postgresql 8.1.1) with a few hundred tables. There are a few larger (2-5 million rows) tables with multiple indexes on them, some being unique, some not. Now, I am pretty sure some of the indexes are pretty useless and are never used but is there a way to see which indexes have been used on a table (and how many times) and which haven't? Statistics is turned on for a database, I can see number of sequential scans and index scans for instance, but I would like to know which indexes have been used and how many times. select * from pg_stat_user_indexes ; Tnx in advance Dragan Matic ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
On fim, 2006-02-23 at 15:45 -0500, Emi Lu wrote: I am waiting for your clues. Maybe it is us that need some clues from you. gnari May I know where I can find some online documents about mapping the integer values to the following SQL types please? For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC. Data Types The following data types are supported: SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_NUMERIC SQL_DECIMAL SQL_SMALLINT SQL_INTEGER SQL_REAL SQL_FLOAT SQL_DOUBLE SQL_BIT SQL_TINYINT SQL_BIGINT SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY SQL_TYPE_DATE SQL_TYPE_TIME SQL_TYPE_TIMESTAMP SQL_INTERVALS (all types) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] SQL TYPE MAP such as SQL_CHAR, SQL_NUMERIC , etc
On fim, 2006-02-23 at 16:31 -0500, Emi Lu wrote: We use perl DBI to read table names, column names, and column types from Oracle rdb 7.3 through ODBC, and then try to create tables into postgresql. Through perl DBI, we got: Column Name Type Precision Scale Nullable? -- - - - col1 1 4 0 Yes col2 1 4 0 Yes col3 1 2 0 Yes col44 11 0 Yes col53 4 2 Yes col693 13 0 Yes ... ... I'd like to know how to map the integer type value 1, 3, 4, 93, etc to SQL_type? For example, if I have value 1 , so that I know 1 is mapped to SQL_CHAR; if I have value 3, so that I know 3 is mapped to SQL_NUMERIC. Data Types The following data types are supported: SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR SQL_NUMERIC these are not really Oracle type names so I guess these are ODBC type names, and the mapping you talk of is maybe some ODBC thing. in DBD::Oracle found on CPAN I find this nice little SQL to generate columns listing, which might give you some clues: my $Sql = SQL; SELECT * FROM ( SELECT /*+ RULE*/ to_char( NULL ) TABLE_CAT , tc.OWNERTABLE_SCHEM , tc.TABLE_NAME TABLE_NAME , tc.COLUMN_NAME COLUMN_NAME , $typecase decode( tc.DATA_TYPE , 'MLSLABEL' , -9106 , 'ROWID', -9104 , 'UROWID' , -9104 , 'BFILE',-4 -- 31? , 'LONG RAW' ,-4 , 'RAW' ,-3 , 'LONG' ,-1 , 'UNDEFINED', 0 , 'CHAR' , 1 , 'NCHAR', 1 , 'NUMBER' , decode( tc.DATA_SCALE, NULL, 8, 3 ) , 'FLOAT', 8 , 'VARCHAR2' ,12 , 'NVARCHAR2',12 , 'BLOB' ,30 , 'CLOB' ,40 , 'NCLOB',40 , 'DATE' ,93 , NULL ) $typecaseend DATA_TYPE -- ... , tc.DATA_TYPETYPE_NAME -- std.? , decode( tc.DATA_TYPE , 'LONG RAW' , 2147483647 , 'LONG' , 2147483647 , 'CLOB' , 2147483647 , 'NCLOB', 2147483647 , 'BLOB' , 2147483647 , 'BFILE', 2147483647 , 'NUMBER' , decode( tc.DATA_SCALE , NULL, 126 , nvl( tc.DATA_PRECISION, 38 ) ) , 'FLOAT', tc.DATA_PRECISION , 'DATE' , 19 , tc.DATA_LENGTH ) COLUMN_SIZE , decode( tc.DATA_TYPE , 'LONG RAW' , 2147483647 , 'LONG' , 2147483647 , 'CLOB' , 2147483647 , 'NCLOB', 2147483647 , 'BLOB' , 2147483647 , 'BFILE', 2147483647 , 'NUMBER' , nvl( tc.DATA_PRECISION, 38 ) + 2 , 'FLOAT', 8 -- ? , 'DATE' , 16 , tc.DATA_LENGTH ) BUFFER_LENGTH , decode( tc.DATA_TYPE , 'DATE' , 0 , tc.DATA_SCALE ) DECIMAL_DIGITS -- ... , decode( tc.DATA_TYPE , 'FLOAT', 2 , 'NUMBER' , decode( tc.DATA_SCALE, NULL, 2, 10 ) , NULL ) NUM_PREC_RADIX , decode( tc.NULLABLE , 'Y', 1 , 'N', 0 , NULL ) NULLABLE , cc.COMMENTS REMARKS , tc.DATA_DEFAULT COLUMN_DEF -- Column is LONG! , decode( tc.DATA_TYPE , 'MLSLABEL' , -9106 , 'ROWID', -9104 , 'UROWID' , -9104 , 'BFILE',-4 -- 31? , 'LONG RAW' ,-4 , 'RAW' ,-3 , 'LONG' ,-1 , 'UNDEFINED', 0 , 'CHAR' , 1 , 'NCHAR', 1 , 'NUMBER' , decode( tc.DATA_SCALE, NULL, 8, 3 ) , 'FLOAT', 8 , 'VARCHAR2' ,12 , 'NVARCHAR2',12 , 'BLOB' ,30 , 'CLOB' ,40 , 'NCLOB',40 , 'DATE' , 9 -- not 93! , NULL ) SQL_DATA_TYPE -- ... , decode( tc.DATA_TYPE , 'DATE' , 3 , NULL ) SQL_DATETIME_SUB -- ... , to_number( NULL ) CHAR_OCTET_LENGTH -- TODO , tc.COLUMN_IDORDINAL_POSITION , decode( tc.NULLABLE , 'Y', 'YES' , 'N', 'NO' , NULL ) IS_NULLABLE FROM ALL_TAB_COLUMNS tc , ALL_COL_COMMENTS cc WHERE tc.OWNER =
Re: [GENERAL] A question about Vacuum analyze
On fös, 2006-02-17 at 12:06 -0500, Emi Lu wrote: In another way, whenever we delete/truncate and then insert data into a table, it is better to vacuum anaylze? ... So, your suggestion is that after the population of table A, the query planner should be able to find the most efficient query plan because we do truncate but not delete, and we do not need to do vacuum analyze at all, right? ... Thank you gnari for your answer. But I am a bit confused about not running vacuum but only analyze. Can I seperate these two operations? I guess vacuum analyze do both vacuum and analyze. Or EXPLAIN ANALYZE can do it for me? VACUUM ensures that dead rows can be reused. Dead rows are created by DELETE and UPDATE. If you have done a significant number of DELETEs or UPDATEs, you might want to VACUUM ANALYZE collect statistical information about your tables. this helps the planner make good plans. After having changed your data significantly, you might want to ANALYZE, for example after lots of INSERTs, UPDATEs or DELETEs TRUNCATE does not create dead rows, so you do not need to VACUUM just because of that, but you still might have to ANALYZE. If you TRUNCATE a table and then repopulate it with similar data as before, you do not have to ANALYZE, as plans based on the old statistics would assumedly be just as good. EXPLAIN dislays the plan that will be chosen for a query, along with some estimated cost information. EXPLAIN ANALYZE actually executes the query, and shows same info as EXPLAIN, and in addition actual cost information Hope this makes it more clear gnari ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Converting an ASCII database to an UTF-8 database
On fös, 2006-02-17 at 05:21 -0800, [EMAIL PROTECTED] wrote: Hi All, I have a database in PostgreSQL which is ASCII. Due to some internationalization issues, I need to convert the database to the UTF-8 format. So my question is: How do I convert a database in the ASCII format into one of the UTF-8 format? using pg_dump ? gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Converting an ASCII database to an UTF-8 database
On fös, 2006-02-17 at 22:38 +0100, Peter Eisentraut wrote:
[EMAIL PROTECTED] wrote:
How do I convert a database in the ASCII format into one of the UTF-8
format?
ASCII is a subset of UTF-8, so you don't need to do anything. Just
change the encoding entry in the pg_database table. Of course, using
pg_dump would be the official way to convert a database between any two
encodings.
This will only work correctly if the database
definitely does not contain non-ASCII characters.
Assuming by ASCII format we mean that the database was
created SQL_ASCII, then it is possible that it contains
invalid UTF-8 characters, as SQL_ASCII is a 8 bit
encoding.
consider:
template1=# create database test with encoding='SQL_ASCII';
CREATE DATABASE
template1=# \connect test
You are now connected to database test.
test=# create table a (x text);
CREATE TABLE
test=# insert into a values ('á');
INSERT 33304378 1
test=# select * from a;
x
---
á
(1 row)
test=# update pg_database set encoding =
pg_catalog.pg_char_to_encoding('UTF8') where datname='test';
UPDATE 1
test=# select * from a;
x
---
á
(1 row)
test=# \connect template1
You are now connected to database template1.
template1=# \connect test
You are now connected to database test.
test=# select * from a;
x
---
(1 row)
test=#
gnari
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [SQL] to count no of columns in a table
On fim, 2006-02-16 at 14:43 +0530, AKHILESH GUPTA wrote: i just want to know one thing that is there any function in PGSQL which gives me the total number of columns in a table. OR just like we are using count(*), it gives us total number or rows in a table, just like i want to know the total number of columns present in the table this is the kind of thing the information_schema is for select count(*) from information_schema.columns where table_name='x'; gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] A question about Vacuum analyze
On fim, 2006-02-16 at 09:12 -0500, Emi Lu wrote: Hello, We have a daily cronjob and in the cronjob we do: 1. truncate one table A 2. insert into table A 3. do comparision table A and table B and update table B accordingly The doc says VACUUM ANALYZE command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries. So, I believe after insertion at step 2, comparision will be speed up if we do a vacuum analyze right? after only truncate and inserts , vacuum is not needed, but ANALYZE is good. In another way, whenever we delete/truncate and then insert data into a table, it is better to vacuum anaylze? after deleting or updating a significant percentage of rows, VACUUM can be called for, but usually VACUUM is done as part of regular maintenance, or by autovacuum. gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
