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] 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] 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] 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] 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] 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] 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] 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] [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] 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] 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] 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] 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] 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] 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] 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
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] "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] 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] 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] 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] 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] 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
