Re: [SQL] control function pgsql with script bash

s of 3 when something goes wrong in your script. I don't know of a way, aside from parsing the output, that you can identify exactly where the problem arose. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] create geometry by lat/long

On Tue, Jan 25, 2011 at 03:10:59AM -0800, gargdevender74 wrote: > > how to create geometry (EPSG:4326) by lat/long. plz advice Try ST_Point and ST_SetSRID() http://www.postgis.org/docs/ST_Point.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signatu

Re: [SQL] "slow lock" log in addition to log_min_duration_statement ?

d reducing that to "a few hundredths of a second" like you're interested in might cause all kinds of load from the deadlock detector. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] Is there a conditional string-concatenation ?

WHEN $3 IS NULL OR $3 = '' THEN trim($1) ELSE trim($1) || trim(coalesce($2, '')) || trim($3) END; -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] join returns too many results...

ceptable. > For option 2; result B=NE70 or B=NE75 or B=NE80 is acceptable. > > I am lost trying to construct SQL to accomplish this... Try DISTINCT ON, after ensuring the results are ordered meaningfully. http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DISTINCT -- Josh

[SQL] available RPM package for 8.4.4?

Is it possible to just download the RPM package for postgresql server 8.4 for fedora and NOT install it through the yum tree? If so where can I go to get it. I am looking all through yum.pgrpms.org and I am coming up a bit short. Thanks. -- Joshua Gooding -- Sent via pgsql-sql mailing

[SQL] Some quick questions

is running on a Fedora Core - 12 machine. Is that an OS issue or is than a Postgres configuration question? Any advice or guidance would be greatly appreciated. -- Joshua Gooding -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] grouping subsets

01-01 00:02:20-07 | 8 9 | 2 | 1 | 2007-01-01 00:02:25-07 | 3 9 | 2 | 1 | 2007-01-01 00:02:30-07 | 3 (21 rows) ...and these results... a | b | c | d| sum ---+---+---++- 1 | 9 | 1 | 2007-01-01 00:00:05-07 | 8 9 | 2 | 1 | 2007-01-01 00:00:10-0

Re: [SQL] howto delete using a join ?

On Sat, Jul 24, 2010 at 06:05:57AM +0200, Andreas wrote: > Hi, > > is there a way to delete from a table using information from another > table to decide if a row should be dropped? Yes. See DELETE ... USING http://www.postgresql.org/docs/8.4/interactive/sql-delete.html -- J

Re: [SQL] Question on COUNT performance

che using a trigger on the plan_events table. Finally, you can try to improve performance of your function itself. If it's taking 4.6 sec. to read and process 2669 rows, either you're reading awfully slowly (VACUUM problems, perhaps?) or your f_plan_events_acl() function takes a long time to run. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] question about partitioning

I'm trying to figure out the logic behind the date parameters though. I don't have to worry at all about partition size. Joshua Gooding On 6/24/2010 3:37 PM, Little, Douglas wrote: I don't know how you would partition by size. Date is a good candidate, and roughly wouldn

Re: [SQL] question about partitioning

to do. Joshua Gooding On 6/24/2010 11:06 AM, Jasen Betts wrote: On 2010-06-24, Joshua Gooding wrote: Right now I am in the process of migrating an Oracle DB over to Postgres 8.4.3. The table is partitioned by size. Is there anyway to partition the new postgres table by size? I created

[SQL] question about partitioning

250M test records in one table. Any ideas or thoughts on how to build the rules for the table by size would be greatly appreciated. -- Joshua Gooding Software Engineer TTi Technologies Wheeling, WV 26003 w: 304-233-5680 x 308 c: 304-794-8341 -- Sent via pgsql-sql mailing list (pgsq

Re: [SQL] oracle to postgres migration question

ear up every issue I was having (both displaying and programatically) -- Joshua Gooding Software Engineer TTi Technologies Wheeling, WV 26003 w: 304-233-5680 x 308 c: 304-794-8341 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.post

[SQL] oracle to postgres migration question

Hello, I'm looking for the postgres equivalent of oracles: set numwidth command. Is there an equivalent? Thanks in advance! - Josh -- Joshua Gooding Software Engineer TTi Technologies Wheeling, WV 26003 w: 304-233-5680 x 308 c: 304-794-8341 -- Sent via pgsql-sql mailing list (pgsq

Re: [SQL] User function that returns a set of rows.

mething like "SELECT * FROM select_business_types()". You'll get that error if you instead say "SELECT select_business_types()". -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] Greetings folks, dumb question maybe?

+(floor(random()*36520)::int % 3652); ...and if you'd rather it were a function anyway, do this: CREATE FUNCTION populate_table() RETURNS VOID AS $$ INSERT INTO unpart_tbl_test SELECT GENERATE_SERIES(0,999), 'teststring data', date '1995-01-01&

Re: [SQL] Howto have a unique restraint on UPPER (textfield)

...@josh*# insert into c (d) values ('text'); INSERT 0 1 5432 j...@josh*# insert into c (d) values ('tExt'); ERROR: duplicate key value violates unique constraint "c_ix" -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] [GENERAL] DataBase Problem

r PostgreSQL logs. The logging documentation might be useful to you here. http://www.postgresql.org/docs/current/static/runtime-config-logging.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] Ask About SQL

ORDER BY field1, field2, field3; Here's an example. # select * from b order by f1, f2, f3; f1 | f2 | f3 ++ x1 | y1 | 5 x1 | y2 | 1 x1 | y2 | 3 x2 | y3 | 2 x2 | y3 | 4 (5 rows) # select distinct on (f1, f2) f1, f2, f3 from b order by 1, 2, 3 desc; f1 | f2 | f3 +

Re: [SQL] Field or record level encryption / decryption

On Sat, Aug 15, 2009 at 12:07:54AM +0800, Hengky Lie wrote: > Thanks to all who response my question. > > I have checked the doc, but it seems too advance for my postgresql > knowledge. > > Other question is where can i get pgcrypto modules ? You haven't told us how you installed PostgreSQL, but

Re: [SQL] Field or record level encryption / decryption

rypto extension: http://www.postgresql.org/docs/current/static/pgcrypto.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] How fetch multiple rows into one text-field?

array_to_string(array_accum(log_notes, '')) FROM... You might need to add array_accum manually; before 8.4 it wasn't built in. See http://www.postgresql.org/docs/8.3/static/xaggr.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] Bit by "commands ignored until end of transaction block" again

On Fri, Jul 24, 2009 at 12:54:31PM -0400, Alvaro Herrera wrote: > Joshua Tolley escribió: > > > Have you tried this? I expect if you give it a shot, you'll find you don't > > actually have this problem. Really, everything is always in a transaction. > > If >

Re: [SQL] Bit by "commands ignored until end of transaction block" again

into an error). Statements within functions are always executed within the same transaction, so you can issue SAVEPOINT commands anywhere in PL/pgSQL functions without problems, because you're always in a transaction. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature

Re: [SQL] Composite primary keys

On Tue, Jun 23, 2009 at 05:14:36PM +0200, Harald Fuchs wrote: > test=# CREATE TABLE t2 ( > test(# id int NOT NULL REFERENCES t1, > test(# language char(3) NULL, > test(# txt text NOT NULL, > test(# PRIMARY KEY (id, language) > test(# ); > CREATE TABLE > test=# INSERT INTO

Re: [SQL] drop PW

On Sat, Jun 13, 2009 at 04:51:20PM -0400, Mark Fenbers wrote: > So how do I turn off being prompted for a password for george. (I am > aware of the security risks...) Create a .pgpass file[1], or modify pg_hba.conf[2] - Josh / eggyknap [1] http://www.postgresql.org/docs/8.3/interactive/libpq-

Re: [SQL] Comparing two tables of different database

On Thu, Apr 30, 2009 at 08:20:02AM +1000, Adam Ruth wrote: >The simple answer is to pg_dump both tables and compare the output with >diff. >Other than that, I think you'll need a custom program. For all but the strictest definition of "identical", that won't work. Tables may easily con

Re: [SQL] cast bool/int

On Mon, Mar 23, 2009 at 10:18:31AM +0200, Achilleas Mantzios wrote: > Στις Monday 23 March 2009 09:59:12 ο/η Zdravko Balorda έγραψε: > > > > Hi, > > I need a casting operator from boolean to integer, > > tu put in ALTER TABLE statment after USING. > > > > Sorry in the above email i meant smth li

Re: [SQL] Permanent alias for postgresql table

On Thu, Mar 12, 2009 at 03:26:47PM +0100, Marco Lechner wrote: > Hi Mina, > > thanks for your answer. I thought about that, but don't > views decrease performance, because they are "calculated" > on access? The query gets rewritten a bit, but it's not a big deal. A more important concern might be

Re: [SQL] postgres server crashes unexpectedly

On Tue, 18 Mar 2008, Chadwick Horn wrote: Sorry about the lack of information on the system. We're running fedora (not for sure what version though) core (whitebox). This may not matter in the least bit, but have you tried running the DB on a real RHEL, or CentOS box? The kernel and libs on

[SQL] yet another simple SQL question

Ok, You guys must be getting sick of these newbie questions, but I can't resist since I am learning a lot from these email lists and getting results quick! Thanks to everyone for their contributions. Here is my questions I have a column that looks like this firstname - J

[SQL] another simple SQL question

Ok here is another simple question from a novice Here is what my table looks like firstname lastname fullname -- -- --- smith, john green, susan

[SQL] simple SQL question

I have a column with the following values (example below) 5673 4731 4462 5422 756 3060 I want the column to display the numbers as follows: 56.73 47.31 44.62 54.22 7.56 30.60 I have been playing around with string functions but cannot seem to figure out a quick solution. Does anyone have any

[SQL] current_date / datetime stuff

Hello, I was hoping someone here may be able to help me out with this one: Is there anything similiar to: SELECT current_date; that will return the date of the first Monday of the month? Please let me know. Thanks, Joshua ---(end of broadcast

Re: [SQL] problems with SELECT query results

table. I also appreciate you pointing out the COALESCE function. I will find that helpful in future work. I look forward to hopefully assisting you guys with some of your PostgreSQL dilemmas in the future! Thanks again!!! -Joshua Richard Huxton wrote: Joshua wrote: Hello, I am new to thi

[SQL] problems with SELECT query results

t find any rhyme or reason for this problem. I cannot have any blank rows in the query, and again the database is completely populated with values. Could someone give me an explanation or tell me why I am receiving blank rows here and there as a query result. If you need further clarificatio

[SQL] DATESTYLE and 0000-00-00

Greetings, I have my DATESTYLE set to ISO MDY. When I try to create a table with a default date of -00-00, psql says that this is an invalid date. Why, and can (or how can I) get it to accept -00-00 as a valid date? Thanks, -Josh ---(end of broadcast)-

Re: [SQL] Same question about PostgreSql

>How stable is PostgreSql ? >I know, PostgreSql doesn't support 'prepare ' operation, is it successful to >use one for OLTP databases ? Speaking from experience, I have personally stress tested Postgres under loads of over 512 persistent connections with our LXP application server with zero probl

Re: [SQL] outer joins

Hello, I believe these are supported in 7.1 On Wed, 4 Apr 2001, [iso-8859-4] Algirdas ©akmanas wrote: >Hi all, > >I'm new to postgre, I've changed my work and consequently now i'm moving >from MS plaform. >In MS SQL there are such constructs left or right outer join, in postgres >there are no

Re: [SQL] Postgres-HOWTO

Hello, The Postgres team from PGSQL, Inc. has agreed to provide us with a new version. J On Wed, 7 Feb 2001, Christopher Sawtell wrote: >On Tue, 06 Feb 2001 08:50, Poet/Joshua Drake wrote: >> Has been removed from the LDP website. > >Good news indeed! > >Now what are w

[SQL] Postgres-HOWTO

Has been removed from the LDP website. J -- -- CommandPrompt - http://www.commandprompt.com OpenDocs, LLC. - http://www.opendocs.org LinuxPorts - http://www.linuxports.com LDP - http://www.linuxdoc.org -- Instead of asking why a piece of software is using "19

Re: [SQL] PostgreSQL HOWTO

Hello, I have temporarily removed the PostgreSQL HOWTO, pending peer review. Joshua Drake On Thu, 18 Jan 2001, Brett W. McCoy wrote: >On Thu, 18 Jan 2001, Poet/Joshua Drake wrote: > >> >it seems that the author never used any other think then PHP ... >> >> I am

Re: [SQL] PostgreSQL HOWTO

>|> Best web-scripting (and compiling) language is PHP+Zend compiler >|> PHP is extremely powerful as it combines the power of Perl, >|> Java, C++, Javascript into one single language and it runs on >|> all OSes - unixes and Windows NT/95. > >it seems that the author never used any other think the

[SQL] PostgreSQL HOWTO & LDp

Hello, I am the Webmaster of the LDP... What should I know? Joshua Drake On Thu, 18 Jan 2001, Tom Lane wrote: >Kaare Rasmussen <[EMAIL PROTECTED]> writes: >> Whoever wrote this is putting the PostgreSQL community in a bad light: >> http://www.linux.org/docs/ldp/howto/Po

Re: [SQL] PostgreSQL HOWTO

Hello, I do not see how it puts the Postgres community in a bad light, although I do see how the author is a moron. J On Thu, 18 Jan 2001, Kaare Rasmussen wrote: >Whoever wrote this is putting the PostgreSQL community in a bad light: > >http://www.linux.org/docs/ldp/howto/PostgreSQL-HOWTO-4.ht

Re: [SQL] Using a postgres table to maintain unique id?

fast? (I need 10 or more IDs >generated each second.) Can I avoid having the table >gradually fill with "old" rows for this entry, and this >avoid the need to run VACUUM ANALYZE periodically? The only problem I have had with this type of thing is when a number gets deleted,

[SQL] 8K Limit, and Replication

Hello, I have heard of this infamous 8k limit. I have a couple of questions. 1. Does this mean that if I have a large object that I am inserting into a table, like an image it has to be 8k or less? 2. When will this be fixed? 3. Does anyone know the status of the replication capabilities in PGS

[SQL] [OT] Book on Postgres (Not a question)

Hello, I know this is off topic but I thought you might like to know that the following book explains how to compile, install and secure postgresql. It can be purchased from http://www.linuxports.com/ (just click on the books) and 20% goes back to the OpenSource Documentation Fund. THe Origina