Re: [SQL] control function pgsql with script bash
On Tue, Jan 25, 2011 at 10:46:58AM -0600, manuel antonio ochoa wrote: > Good morning > > I want to check if the function pgsql execute correctly into my bash > script . > I have something like this : > > /var/lib/pgsql/bin/./psql -U 'USER' -d DATABSE -p 5432 -h iphost -c > "select antros.changethenames( )" > > how can i get the error if the function changethenames() send me one. ? > > thnks See http://www.postgresql.org/docs/9.0/static/app-psql.html#AEN74212 Turn on ON_ERROR_STOP, and psql will give you an exit status 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 signature.asc Description: Digital signature
Re: [SQL] "slow lock" log in addition to log_min_duration_statement ?
On Wed, Nov 10, 2010 at 12:28:46PM -0800, Bryce Nesbitt wrote: > I have a cluster with log_min_duration_statement set to log slow > queries. Presently what I'm tracking down is almost certainly a lock > problem. Is there any analog of log_min_duration_statement for locks? > If there is a lock on a certain critical tables for more than a few > hundredths of a second I want to know. You could try log_lock_waits: http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html#GUC-LOG-LOCK-WAITS It's defined in terms of deadlock_timeout, and 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 ?
On Tue, Oct 12, 2010 at 06:09:07AM +0200, Andreas wrote: > Hi, > Is there a conditional string-concatenation ? Perhaps this: CREATE OR REPLACE FUNCTION mycat(text, text, text) RETURNS TEXT LANGUAGE sql IMMUTABLE AS $$ SELECT CASE WHEN $1 IS NULL OR $1 = '' THEN trim($3) 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...
On Sun, Oct 03, 2010 at 02:54:41PM -0400, Frank Bax wrote: > Whatever clause I choose to add I *must* ensure that final result set > contains only one-to-one join between tables. Either of these two > results is acceptable: > > For option 1; result C=All or C=Centre is acceptable. > 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 -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
[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 list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Some quick questions
I am using Postgres 8.4 with 10 partition tables. We'll call them reports_00 through reports_09. I have a field that is a BIGINT which is a 13 digit number that is the epoch time, which is the constraint that the table is partitioned on. (Between time x and y). All of the partitions hold 10 weeks of data. The idea is that I would like to write a script that would truncate and drop the oldest week's table (after 10 weeks), rename the oldest remaining 9 tables, create a new table, with the current and future epoch date in the constraint, and continue on my merry way. Is there anything like this already in postgres? Secondly can it be done without manual intervention? Can I do this in a function and have it auto run at a certain "time" based on epoch? This is something that I have never gotten into so this is new territory for me, so please forgive me if I am asking any newbie questions here. I've tweaked the server that I am testing postgres on. I'm basically doing side by side comparisons with Oracle, trying to see if we can get the same or close to Oracle's performance. I've read the Wiki article on tuning the PostgreSQL server, and I believe that I have gotten it close, but there is still a substantial gap. Say I have a machine with a 4 core processor and 16GB of ram (across 4 sticks), can I tweak the configuration to use all 4 cores and 1GB of ram from each physical stick on the machine? This 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
On Thu, Jul 22, 2010 at 11:31:23AM +, Tim Landscheidt wrote:
> Richard Huxton wrote:
>
> >>> What I want to get is the values grouped by "subset", where a subset is a
> >>> set of rows with identical column until the colum changes.
> >>> Is there a way to get
>
> >>> | 2 | B |
> >>> | 4 | C |
> >>> | 4 | B |
> >>> | 3 | D |
>
> >>> by SQL only?
>
> >> I think, the problem is that there are 2 identical groups. I think, you
> >> can write a pl/pgsql-proc, selecting all ordered by the date-field and
> >> walking through the result to do the grouping, checking if the 2nd
> >> column is different from the previous.
>
> >> With plain SQL it's maybe possible too, but i don't know how ...
>
> > It should be do-able in 8.4 onwards, look into windowing
> > functions. In particular the lag() function:
>
> > SELECT
> > mycode,
> > mydate,
> > lag(mycode) OVER (ORDER BY mydate) AS prev_code
> > FROM
> > mytable
> > ORDER BY mydate;
>
> > It should be possible to use that as a subquery with an
> > outer query that compares mycode=prev_code to get a run
> > length.
>
> Hmmm. Can the outer query be done without using "WITH
> RECURSIVE"?
How about this:
select
a, b, c, d, sum
from (
select
a, b, c, d, new_partition,
sum(e) over (partition by partition_num)
from (
select
a, b, c, d, e,
case when
lag(a, 1, null) over (order by d) is null or
lag(a, 1, null) over (order by d) != a or
lag(b, 1, null) over (order by d) is null or
lag(b, 1, null) over (order by d) != b or
lag(c, 1, null) over (order by d) is null or
lag(c, 1, null) over (order by d) != c
then nextval('a')
else currval('a')
end as partition_num,
case when
lag(a, 1, null) over (order by d) is null or
lag(a, 1, null) over (order by d) != a or
lag(b, 1, null) over (order by d) is null or
lag(b, 1, null) over (order by d) != b or
lag(c, 1, null) over (order by d) is null or
lag(c, 1, null) over (order by d) != c
then 'T'::boolean
else 'f'::boolean
end as new_partition
from foo
) bar
) baz
where
new_partition;
Here's my test table:
5432 j...@postgres# select * from foo;
a | b | c | d| e
---+---+---++---
1 | 9 | 1 | 2007-01-01 00:00:05-07 | 8
9 | 2 | 1 | 2007-01-01 00:00:10-07 | 4
9 | 2 | 1 | 2007-01-01 00:00:15-07 | 6
9 | 2 | 1 | 2007-01-01 00:00:20-07 | 2
6 | 5 | 7 | 2007-01-01 00:00:25-07 | 3
4 | 9 | 0 | 2007-01-01 00:00:30-07 | 0
4 | 9 | 0 | 2007-01-01 00:00:35-07 | 7
5 | 2 | 7 | 2007-01-01 00:01:25-07 | 7
5 | 2 | 7 | 2007-01-01 00:01:30-07 | 7
5 | 2 | 7 | 2007-01-01 00:01:35-07 | 9
5 | 2 | 7 | 2007-01-01 00:01:40-07 | 2
5 | 2 | 7 | 2007-01-01 00:01:45-07 | 5
5 | 2 | 7 | 2007-01-01 00:01:50-07 | 8
5 | 2 | 7 | 2007-01-01 00:01:55-07 | 5
5 | 2 | 7 | 2007-01-01 00:02:00-07 | 9
7 | 8 | 8 | 2007-01-01 00:02:05-07 | 7
7 | 8 | 8 | 2007-01-01 00:02:10-07 | 8
9 | 3 | 0 | 2007-01-01 00:02:15-07 | 0
9 | 3 | 0 | 2007-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-07 | 12
6 | 5 | 7 | 2007-01-01 00:00:25-07 | 3
4 | 9 | 0 | 2007-01-01 00:00:30-07 | 7
5 | 2 | 7 | 2007-01-01 00:01:25-07 | 52
7 | 8 | 8 | 2007-01-01 00:02:05-07 | 15
9 | 3 | 0 | 2007-01-01 00:02:15-07 | 8
9 | 2 | 1 | 2007-01-01 00:02:25-07 | 6
(8 rows)
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
signature.asc
Description: Digital signature
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 -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [SQL] Question on COUNT performance
On Wed, Jul 14, 2010 at 02:30:29PM +0200, Reinoud van Leeuwen wrote: > On Wed, Jul 14, 2010 at 09:58:10PM +1000, Anders ??stergaard Jensen wrote: > > SELECT count(*) AS count_all FROM "plan_events" WHERE (f_plan_event_acl(17, > > plan_events.id)) > > > >QUERY PLAN > > > > > > Aggregate (cost=2859.77..2859.78 rows=1 width=0) (actual > > time=4641.720..4641.720 rows=1 loops=1) > >-> Seq Scan on plan_events (cost=0.00..2851.44 rows=3331 width=0) > > (actual time=32.821..4640.116 rows=2669 loops=1) > > Filter: f_plan_event_acl(17, id) > > Total runtime: 4641.753 ms > > (4 rows) > > > > > > > > What can I do to improve the performance? > > Have you tried 'select count (1)..."? If this helps at all, it's unlikely to help much. I remember having seen discussion somewhere that there's an optimization such that count(*) and count(1) do the same thing anyway, but I can't find it in the code immediately. In any case, if your WHERE clause frequently includes this function with 17 and id as arguments, and if f_plan_event_acl is immutable, you can create an index: CREATE INDEX foo_ix ON plan_events (f_plan_event_acl(17, id)); If PostgreSQL thinks that function will be true for a sufficiently small proportion of the rows in the table, it will use the index instead of a sequential scan, which might end up being faster. But the index won't help you when you want to check values other than 17, e.g. SELECT count(*) FROM plan_events WHERE f_plan_event_acl(42, id) Another option might be to precalculate these data, if you have a relatively small set of values you pass to f_plan_event_acl(). A table somewhere could store the f_plan_events_acl() argument (17, as well as any other values you want to precalculate), and a count of plan_events rows where f_plan_events_acl() returns true with that argument. A set of triggers would ensure that whenever someone INSERTs, UPDATEs, or DELETEs a plan_events row, these counts are updated accordingly. Then you can refer to that table when you need a count. If values in other tables can change the results of f_plan_events_acl(), you'd need triggers there, too (and this method would probably start to become unworkably complicated). As an alternative to the precalculation option, you could also cache the results of this query somewhere, and presumably invalidate that cache 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't you have the same number of tx's/day You'll only benefit query performance if you include the partitioning column in the where clause. If you have a surrogate pk, you could also use this to partition. Using a range key you would probably get relatively constant partition size. Doug -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Joshua Gooding Sent: Thursday, June 24, 2010 2:31 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] question about partitioning I think I replied to the individual and not to the list before As of right now size doesn't matter, I need to partition it via a date. 10 partitions, 10 weeks worth of data. I was thinking of partitioning it off every 32GB of data, but that is not exactly what I am looking 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 some partitions for the new table, but I didn't give postgres any rules to partition by, so I have 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. by size of what? -- 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] question about partitioning
I think I replied to the individual and not to the list before As of right now size doesn't matter, I need to partition it via a date. 10 partitions, 10 weeks worth of data. I was thinking of partitioning it off every 32GB of data, but that is not exactly what I am looking 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 some partitions for the new table, but I didn't give postgres any rules to partition by, so I have 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. by size of what? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] question about partitioning
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 some partitions for the new table, but I didn't give postgres any rules to partition by, so I have 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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] oracle to postgres migration question
On 6/16/2010 1:02 AM, silly sad wrote: On 06/16/10 02:45, Bruce Momjian wrote: Scott Marlowe wrote: Note that psql automagically right justifies numerics and dynamically sizes all columns so you don't have to do as much of this stuff. Oracle always made me feel like I was operating the machine behind the curtain in the Wizard of Oz, lots of handles and switches and knobs I had to mess with to get useful output. Yeah, I have heard that description many times in other forms. count me in :-) i even suspect this exactly is a Secret of the oracle Power. "higher performance through lower level of control" sorry for the delay guys, and thank you for all the replies. The problem I was having, is the data in field 'track_start' was a number type in oracle. I switched it out to a real in postgres, however when I displayed the column (via psql) it was printing out for example (1.23546e12). I know you could format the output via Sql Plus but I was not sure what (if anything) you could do in postgres for that. That being said, I then re-modified the field type and it displays properly. I went from a real to a integer type and it seemed to clear 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.postgresql.org/mailpref/pgsql-sql
[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 (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] User function that returns a set of rows.
On Mon, May 24, 2010 at 09:33:35PM +0300, David Harel wrote: >When I tried it from the shell I got a nasty error message about that I am >not in an environment to receive a set ??? (can't see it now. Office >restrictions). > >Any idea? Your query should say something 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?
On Thu, May 13, 2010 at 10:09:51AM +, Jasen Betts wrote: > On 2010-05-12, Josh wrote: > don't need a function for that one. > > INSERT INTO unpart_tbl_test > SELECT GENERATE_SERIES(0,999), > 'teststring data', > date '1995-01-01' +(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' +(floor(random()*36520)::int % 3652); $$ LANGUAGE SQL; -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [SQL] Howto have a unique restraint on UPPER (textfield)
On Sun, Jan 31, 2010 at 03:26:14AM +0100, Andreas wrote:
> Hi,
>
> is there a way to define a unique restraint on UPPER (textfield)?
>
> E.g. mytable (
>name_id serial PRIMARY KEY,
>name varchar(255),
>UNIQUE ( upper (name) )
> )
>
> psql throws a syntax error because of the upper() function.
>
> I need to prohibit that 2 of strings like cow, Cow, CoW appears in
> the name-column.
Like this:
5432 j...@josh# create table c (d text);
CREATE TABLE
5432 j...@josh*# create unique index c_ix on c (upper(d));
CREATE INDEX
5432 j...@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
On Wed, Dec 30, 2009 at 09:49:52AM +0800, Premila Devi wrote: >I am having problem as : > > > >Caused by: org.springframework.transaction.TransactionSystemException: >Could not roll back Hibernate transaction; nested exception is >org.hibernate.TransactionException: JDBC rollback failed ...snip... >Caused by: org.hibernate.TransactionException: JDBC rollback failed ...snip... >Caused by: java.sql.SQLException: Couldn't perform the operation rollback: >You can't perform any operations on this connection. It has been >automatically closed by Proxool for some reason (see logs). Without some idea of why the connection was "automatically closed by Proxool" there's likely little we can do to help. If PostgreSQL is to blame, there's probably useful information about the problem in your 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
On Wed, Aug 19, 2009 at 05:17:07PM +0700, Otniel Michael wrote: >Hi All. > >Can help to create sql queries for this data : > >tabel A >field1, field2, field3 >x1, y1, 5 >x1, y2, 1 >x2, y1, 2 >x2, y3, 4 >x1, y3, 4 > >I want to get 2 record with the max value at field3 for each kombination >of field1 : > >tabel B >field1, field2, field3 >x1, y1, 5 >x1, y3, 4 >x2, y3, 4 >x2, y1, 2 > >Anyone have an ideas? >Thanks anyway. > >-- >--- >"He who is quick to become angry will commit folly, and a crafty man is >hated" Your example doesn't match your description (the combination of x1 and y2 isn't listed). However, from your description it looks like what you want is DISTINCT ON http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-DISTINCT Something like this: SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM A 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 ++ x1 | y1 | 5 x1 | y2 | 3 x2 | y3 | 4 (3 rows) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
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 you probably did it with some operating system package. In that case, there's most likely a postgresql-contrib package (or some other similarly named package) you can install that will include pgcrypto. Within that package there should be some SQL file full of "CREATE FUNCTION" statements, which you need to run in each database where you'd like to use pgcrypto. > For simple record encryption that contains date field, varchar, integer > and text, what encryption i can use ? As far as i know, there is no way > to return MD5 result back to its original value. Is this true or not ? That's a bit beyond the scope of this mailing lists. Different methods and algorithms have different attributes, benfits, and drawbacks. I'd recommend studying the subject if you're trying to protect anything seriously. As to MD5, you're correct that the idea is you can't recreate the original value without an awful lot of computing time. But again, study the field before trying to do something serious. It's complex, and easy to get wrong. - Josh / eggyknap signature.asc Description: Digital signature
Re: [SQL] Field or record level encryption / decryption
On Fri, Aug 14, 2009 at 05:20:58PM +0800, Hengky Lie wrote: > Hi, > > Anyone know what function i can use to encrypt / decrypt field or > record ? > > When record saved, it saved in encrypt format. When i need to read data, > i just call decrypt function. See the pgcrypto 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?
On Fri, Jul 31, 2009 at 10:09:46PM +0200, Andreas wrote: > Hi, > > how can I fetch multiple rows into one text-field? > I need the contents of some log-infos condensed into a single text to > show in a report. It sounds like you might want something like this: SELECT ... 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 > > you haven't explicitly opened one, PostgreSQL opens one for you before each > > statement, and issues a COMMIT afterwards (or a ROLLBACK, if your statement > > ran 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. > > No, actually you can't call SAVEPOINT inside a PL/pgSQL function (or any > SPI user for that matter -- have you tried savepoints in LOLCODE?) > Inside PL/pgSQL the only way to use savepoints is with EXCEPTION blocks. > > You are correct that you're always in a transaction, but in this context > not all transactions are equal :-( > > (The problem, as we found out, is that the function must always have > control at the same level of transaction nestedness in SPI; you can't > just let the user define and release savepoints arbitrarily.) That makes sense -- and although I did try this before sending the email, apparently I didn't try it well enough. :) - Josh / eggyknap signature.asc Description: Digital signature
Re: [SQL] Bit by "commands ignored until end of transaction block" again
On Thu, Jul 23, 2009 at 02:04:53AM -0400, Glenn Maynard wrote: > On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton wrote: > >> - Let me use SAVEPOINT outside of a transaction, > > > > You are never outside a transaction. All queries are executed within a > > transaction. > > "Transaction block", then, if you insist. > > > I think this is the root of your problem - all queries are within a > > transaction so either: > > 1. You have a transaction that wraps a single statement. If you get an error > > then only that statement was affected. > > 2. You have an explicit BEGIN...COMMIT transaction which could use a > > savepoint. > > Savepoints can only be used inside transaction blocks. My function > has no idea whether it's being called inside a transaction block. > > From inside a transaction block, my function would need to call > SAVEPOINT/RELEASE SAVEPOINT. > > If it's not in a transaction block, it needs to call BEGIN/COMMIT > instead. SAVEPOINT will fail with "SAVEPOINT can only be used in > transaction blocks". 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 you haven't explicitly opened one, PostgreSQL opens one for you before each statement, and issues a COMMIT afterwards (or a ROLLBACK, if your statement ran 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 t2 (id, language, txt) VALUES (2, NULL, 'text 1 no > language'); > ERROR: null value in column "language" violates not-null constraint Primary keys are NOT NULL and UNIQUE. You can't have null values in a primary key. - Josh / eggyknap signature.asc Description: Digital signature
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-pgpass.html [2] http://www.postgresql.org/docs/8.3/interactive/client-authentication.html signature.asc Description: Digital signature
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 contain the same information, in different on-disk order, and pg_dump will most likely give the data to you in an order similar to its ordering on disk. Something like a COPY () TO , where includes an ORDER BY clause, might give you a suitable result from both tables, on which you could then take a checksum. - Josh / eggyknap signature.asc Description: Digital signature
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 like > CASE WHEN column='t' THEN 1 ELSE 0 END Or just CASE WHEN column THEN 1 ELSE 0 END. - Josh / eggyknap signature.asc Description: Digital signature
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 that to make it so you can add / modify data in the table, you'll need to create rules to rewrite UPDATE and INSERT queries on that view to instead affect the underlying table. - Josh / eggyknap signature.asc Description: Digital signature
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 such a box would most likely be more stable than those on Fedora-based boxen... Cheers, -Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[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 - John B Mark A Jennifer D Basically I have the first name followed by a middle initial. Is there a quick command I can run to strip the middle initial? Basically, I just need to delete the middle initial so the column would then look like the following: firstname --- John Mark Jennifer Thanks again for all of your help today. Everything you guys have been sending has produced successful results. Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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 white, jeff How can I break the fullname field into firstname lastname fields so it looks like the following: firstname lastname fullname - - - john smith smith, john susan green green, susan jeff white white, jeff Please let me know. Sorry for such simple novice questions, I appreciate your support. THANKS! ---(end of broadcast)--- TIP 6: explain analyze is your friend
[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 suggestions? Please let me know. Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[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)--- 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: [SQL] problems with SELECT query results
Hello,
Thank you all for your assistance. I did end up finding NULL in the
'onorder' column which should have been zero's this was in a test
table that happened to have some NULL in it for one reason or another
but I should not find this in the production version of the 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 this list and have been working with PostgreSQL since
January.
Here is my problem, I hope someone here has some experience or can
point me in the right direction. I am writing the following query for
a C# program I am writing:
SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' ||
round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' ||
round(onorder) || ',' || ',' || ',' || binone || ',' || ',' ||
round(backorderqty) || ',' || ',' || round(onhold) || ',' || ',' ||
',' || ',' || ',' || ',' || ',' || round(qtyperjob) || ',' ||
round(ordermax) AS gmrim FROM slparts WHERE vendor LIKE 'CH%'
The query does work and I am getting results from the database. There
are values for all 'partnum' in the database, however, the query
results include blank fields here and there in between the returned
records. Why am I receiving blank fields for 'gmrim' This
absolutely defies logic and I cannot 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.
I'm not sure it is - I think you've got a NULL somewhere.
Since NULL means "unknown" ('text' || NULL) = NULL
Wrap all your column-references in COALESCE: e.g. COALESCE(partnum,'')
and see if that solves it. If so, go back and find rows WHERE partnum
IS NULL and correct them. Then set the NOT NULL constraint on the
relevant columns.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] problems with SELECT query results
Hello, I am new to this list and have been working with PostgreSQL since January. Here is my problem, I hope someone here has some experience or can point me in the right direction. I am writing the following query for a C# program I am writing: SELECT 'PV.LINEITEM:' || partnum || ',' || round(onhand) || ',' || round(qm5) || ',' || round(lsm4) || ',' || ',' || ',' || round(onorder) || ',' || ',' || ',' || binone || ',' || ',' || round(backorderqty) || ',' || ',' || round(onhold) || ',' || ',' || ',' || ',' || ',' || ',' || ',' || round(qtyperjob) || ',' || round(ordermax) AS gmrim FROM slparts WHERE vendor LIKE 'CH%' The query does work and I am getting results from the database. There are values for all 'partnum' in the database, however, the query results include blank fields here and there in between the returned records. Why am I receiving blank fields for 'gmrim' This absolutely defies logic and I cannot 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 clarification of this problem or have any questions for me to arrive at a conclusion please feel free to send them to me and I will be more than happy to open a dialog in order to solve this problem. Thanks in advance for assistance. Cordially, Joshua Neil ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[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)--- TIP 5: don't forget to increase your free space map settings
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 problems. J > > >Thanks for responds. > >Good luck. > >Sergey. > > > > > > > >---(end of broadcast)--- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
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 such thing > >Can You offer me strategy to make query that selects from table (a) and >joins to it another (b) >on e.g. a.id=b.aid but joins so that in case there is no value in table b >NULL is left in result: >in MS SQL i've used query: > >select a.id, b.name from a left outer join b on a.id=b.aid > >table a table b > >id | aid | name >--- >11 | Tom >23 | Sam > >result: >a.id | b.name >- >1 | Tom >2 | NULL > >thank you in advance > >Algirdas ©akmanas >IT manager >+370 99 90369 >[EMAIL PROTECTED] >Grafton Entertainment >http://www.tvnet.lt > > > >---(end of broadcast)--- >TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 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 we going to do with it? > >Can the original document's source be made available so that >somebody can do the needed work without having to re-key. > >There is a _lot_ of very good information in there buried underneath the >... um ... > > > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
[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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
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 afraid I would disagree. I have used all of the languages he metions >> and for the Web, PHP is the best. > >I think it all depends on what you are building. PHP is good for small >projects, but I would go with something more scalable for large systems, >like EJB/servlets or Mason -- something that has more content management & >templating features. > >-- Brett > http://www.chapelperilous.net/~bmccoy/ >--- >Did you know the University of Iowa closed down after someone stole the book? > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
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 then PHP ... I am afraid I would disagree. I have used all of the languages he metions and for the Web, PHP is the best. > > >Ciao > Alvar > > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
[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/PostgreSQL-HOWTO-4.html >> Maybe someone can change the document, or make the author change it? > >(Rolls eyes...) See the archives for past discussion of this. We have >been unable to persuade the LDP that the maintainer of the Postgres >HOWTO is unfit to be trusted with sharp objects, let alone a HOWTO. >If we could take it away from him, we'd gladly do so. > > regards, tom lane > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
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.html > >Maybe someone can change the document, or make the author change it? > > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
Re: [SQL] Using a postgres table to maintain unique id?
>However, I also use Postgres (7.0.2) throughout this >application and it seems cleaner to me to keep the current >id value in a table and just use postgres to provide access >(with a trigger function to increment the id on access). Why not a sequence? >Is this reasonable? Is it 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, it does not get recycled. Joshua Drake > >Any tips on how to implement the trigger would be >appreciated. (Is it possible to generate an int8 sequence >value?) > >Thanks! >-- >Steve Wampler- SOLIS Project, National Solar Observatory >[EMAIL PROTECTED] > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
[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 PGSQL? J On Wed, 6 Sep 2000, Craig May wrote: >Hi, > >I have a tables having this structure: > >ID (int) | _ID (int) | Name (String) > > >_ID is the parent of ID. > >I'm looking for the best method to perform a cascade delete. For example, I >delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it >would continue through the chain. > >For example: > >0 0 Base >1 0 Sib1 >2 0 Sib2 >3 0 Sib3 >4 1 Sib1_1 >5 1 Sib1_2 > > >Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 >and Sib1_2. >Can anyone help out here? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au > -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
[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 Original Announcement: This email is to announce the new OpenDocs Publication: Securing and Optimizing Linux: RedHat Edition Including with the book are the Linux Central CD's of: RedHat 6.2 RedHat 6.2 Powertools The retail price for the book is 49.95 but if you preorder it no the price is only 39.95!. And of course since it is an OpenDocs Publication a percentage of the Gross Profits go to the Open Source Documentation Fund. Below is an abbreviated Table of Contents: Linux Installation Descriptions of programs packages we must uninstall for securities reasons Descriptions of programs that must be uninstalled after installation of the server Linux General Security Linux General Optimization Linux Kernel Linux TCP/IP Network Management Linux IPCHAINS Linux Masquerading and Forwarding Linux Compiler functionality Linux sXid Linux Logcheck Linux PortSentry Linux OpenSSH Client/Server Linux SSH2 Client/Server Linux Tripwire 2.2.1 Linux Tripwire ASR 1.3.1 Linux GnuPG Set Quota on your Linux system Linux DNS and BIND Server Linux Sendmail Server (includes 8.10.1) Linux IMAP & POP Server Enable IMAP or POP via the tcp-wrappers inetd super server Linux OPENSSL Server Linux FreeS/WAN VPN Linux OpenLDAP Server Linux PostgreSQL Database Server Linux Squid Proxy Server Linux MM - Shared Memory Library for Apache Linux Apache Web Server Linux Webalizer Linux FAQ-O-Matic Linux Webmail IMP Linux Samba Server Linux FTP Server Linux Backup and Restore Tweaks, Tips and Administration tasks Obtaining Requests for Comments (RFCs) Thanks! [EMAIL PROTECTED] -- -- 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 "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
