Re: [SQL] uniqueness constraint with NULLs

n the real app., c is a date field and I require it to be NULL for > some rows. Oh. Er, In that case, the partial unique index is your best bet (but 'a' and 'b' should ne NOT NULL, right). > in case I am missing some other solution that > doesn't involve the use o

Re: [SQL] uniqueness constraint with NULLs

bles, though, you'll still have a mix of expired and unexpired rows in the table heap. The data of interest will be scattered across more blocks, so the index will be bigger and more data will have to be read in to satisfy a `where c is not null' constrained query. So a partial index isn&#

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

ction. To a PL/PgSQL function there is NO difference between: begin; select my_function(); commit; and a standalone: select my_function(); in both cases the statement executes in a transaction, and in both cases individual statements within the function are within the same transaction. That's why any function can EXCEPTION blocks, etc, which rely on savepoints. -- Craig Ringer -- 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] Bit by "commands ignored until end of transaction block" again

big difference. Most applications with this sort of thing will have app-level transaction APIs that contain and manage the DB-level ones anyway. > RELEASE SAVEPOINT would only COMMIT the transaction *if* the savepoint > that it's releasing started it. So, what you're really asking

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

t; RELEASE SAVEPOINT User } # SQL => 'COMMIT' ... so that inner transactions could ROLLBACK TO SAVEPOINT on error , and so that asking for a rollback would give you a ROLLBACK TO SAVEPOINT if the transaction is a subtransaction. -- Craig Ringer -- 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] Multiple simultaneous queries on single connection

use explicitly declared cursors and FETCH to interleave requests for results from one or more queries in the same transation using the one connection, but only one FETCH may be active at a time. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to

Re: [SQL] Hibernate, web application and only one sequence for all primary keys

B. The Hibernate documentation is pretty good, and covers this sort of thing well. -- Craig Ringer -- 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] Random Unique Id

x27;s what I'm using: http://wiki.postgresql.org/wiki/Pseudo_encrypt thanks to the incredibly helpful folks on this list, in this case particularly Daniel Verite. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.post

Re: [SQL] @@Error equivalent in Postgresql

SQL statement, causing the JDBC driver to throw a Java SQLException that in turn propagates up to code that's in a position to do something about the problem. -- Craig Ringer -- 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] Why don't I get a LATIN1 encoding here with SET ENCODING?

processed. In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd in a string, I don't want the server to decide that I must've meant something else because I have a different client encoding. If I wanted encoding conversion, I wouldn't have written it

Re: [SQL] Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote: > > > Craig Ringer wrote: >> In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd >> in a string, I don't want the server to decide that I must've meant >> something else because I have a different client

Re: [SQL] Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote: > Craig Ringer wrote: >> Yes - but you are *not* presenting a Latin-1 character. You're >> presenting four Latin-1 characters: >> >> '\', '3', '7', '5' > Well, then I have a different question. If

Re: [SQL] Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote: > Craig Ringer wrote: >> Yes - but you are *not* presenting a Latin-1 character. You're >> presenting four Latin-1 characters: >> >> '\', '3', '7', '5' > Well, then I have a different question. If

Re: [SQL] Profiling tool for postgres under win32

ssibly via JPA)? If you're using Hibernate, just enable its query logging features via log4j and watch what happens. -- Craig Ringer -- 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] Profiling tool for postgres under win32

al with the latency. Sometimes queries are slow, especially over slow links. Do your work in a background worker thread, and keep the UI responsive. (Doesn't make sense for web apps, but is important for normal GUI apps). - Get faster disks, more RAM for caching, etc. -- Craig Ringer -- S

Re: [SQL] Using || operator to fold multiple columns into one

statement, eg: SELECT COALESCE( t_number::text, t:string::text, t_date::text, t_boolean::text) AS value; Also: You do have a CHECK constraint on the table that asserts that at most one of those entries may be non-null, right? If not, you're very likely to land up with entries with more than o

Re: [SQL] short-cutting if sum()>constant

ectly machine-executable code as you suggested, by the way. It'd really require a just-in-time compiler akin to what Java uses, though the ability to compile once and cache would help get rid of some of the complexity of Java's. It'd quickly become attractive to just use PL/Java ins

Re: [SQL] Using || operator to fold multiple columns into one

On 24/12/2009 5:04 AM, Rosser Schwarz wrote: On Wed, Dec 23, 2009 at 1:44 AM, Craig Ringer wrote: SELECT COALESCE( t_number::text, t:string::text, t_date::text, t_boolean::text) AS value; Your invocation of COALESCE is incorrect -- it is n-ary, but it returns its first non-NULL argument

Re: [SQL] Foreign key - Indexing & Deadlocking.

x, the referencing table will be sequentially scanned 100 times. Needless to say, you want to add indexes to your foreign keys if you expect to delete from the parent, or (for some reason) update the primary key value for rows in the parent table. -- Craig Ringer -- Sent via pgsql-sql

Re: [SQL] [GENERAL] DataBase Problem

proxool logs and see why the connection was closed. The error message its self gives you the next step. I suggest reading this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems for some hints in case you need to ask a more detailed follow-up. -- Craig Ringer -- Sent via pgsql-sql mai

Re: [SQL] indexes

urrent data by eliminating the need for tree-like multiple self joins. Updates to these views are cheap, because the triggers on the source tables can selectively update only the mat.view entries they know are affected by a given update/insert/delete. -- Craig Ringer -- 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] kind of RULE on SELECT depending on existence of a WHERE condition

ers(param1, param2) etc. Each one would substitute parameters into existing SQL using `EXECUTE USING'. Possibly-null params can be handled using COALESCE or CASE to avoid string-building. -- Craig Ringer -- 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]

this list for MS Access. I eventually ditched Access entirely as the user who was demanding the use of MS Access relented (phew!), so I put together a simple web-app to do what they wanted in a day. Hopefully I'll never need to go near ODBC again, because it's a truly "special

Re: [SQL] Using SKYLINE command on PostgreSQL

8.4devel, but I suspect that since 8.4 has been out for ages and 9.0 is almost released, they've probably lost interest and dropped the work. -- Craig Ringer -- 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] Jagged/ragged arrays

On 21/09/2010 3:42 AM, Dmitriy Igrishin wrote: Hey Florian, What do you mean by "ragged" arrays? At a guess: craig=> SELECT '{ {1}, {1,2}, {1}, {1,2,3} }'::integer[][]; ERROR: multidimensional arrays must have array expressions with matching dimensions (OP) Co

Re: [SQL] Slow response in select

.ringways.co.uk/explain.txt Woah. That's an interesting plan. When concerned about execution time, it's probably best to post EXPLAIN ANALYZE rather than plain EXPLAIN results. EXPLAIN ANALYZE provides more timing information and information about how rowcount estimates differed from r

Re: [SQL] HowTo divide streetname from house-nr ?

IMO you're better off using existing tools that do this with free-form addresses using national phone databases, postcode databases, etc. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to

Re: [SQL] find and replace the string within a column

ching \([^)]+\) (in other words "an open parenthisis, then a sequence of one or more of any character other than a close parenthesis, followed by a close parentheis) and replacing with an empty string ? -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql ma

Re: [SQL] check files .backup

On 25/01/11 08:18, manuel antonio ochoa wrote: > Do you know if exist a function to check my file.backup created by > pgdump. ? What do you want to check? That it restores correctly? That it's complete and not truncated? That it hasn't been modified since being originally written? That it matches

Re: [SQL] check files .backup

fix it! __END__ fi Second: after writing the backup, calculate an md5sum or (preferably) digitally sign the backup using gpg. An md5sum is only really protection against corruption unless you store it somewhere separate and secure. I prefer to digitally sign my backups with detached gpg signatu

Re: [SQL] PosgreSQL - TSQL2 question

On 06/02/11 06:26, Sarbu Anca wrote: > Could you please tell me what I need to do to run TSQL2 on PostrgreSQL > for Windows? What do I need to install? Where can I found it? How do I > do the installation. The temporal support extension is at : http://temporal.projects.postgresql.org/ and is

Re: [SQL] Performance of NOT IN and <> with PG 9.0.4

On 05/16/2011 06:05 PM, Jasmin Dizdarevic wrote: Hi, is there a reason why Not IN-performance is so poor in 9.0.4 compared to 8.4? Example queries? EXPLAIN ANALYZE output? http://wiki.postgresql.org/wiki/SlowQueryQuestions -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql

Re: [SQL] client-side lo_import() provided by libpq ?

On 05/19/2011 04:51 AM, Emi Lu wrote: About client-side lo_import(), is there an online doc about install lo_import? It's in the manual. http://www.postgresql.org/docs/current/static/lo-examplesect.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To

Re: [SQL] column type for pdf file

ystem integrates into database transactions, so you kind of get the best of both worlds. Very cool. If reiser4 hadn't gone the way of the dodo such a thing might've become possible on Linux, but I'm not aware of any other Linux file systems that safely support transactions. -

Re: [SQL] column type for pdf file

, you need another way to talk to the server than just the Pg connection, and most importantly your backups become more complicated because you have two things to back up. It's not simple, and it depends a lot on how much the data changes, how big the files are, etc. -- Craig Ringer --

Re: [SQL] unnest in SELECT

set-returning function, and it doesn't really make that much sense to have them in the SELECT list anyway. Few databases support it, and PostgreSQL's behavior is a historical quirk that I think most people here hope will go quietly away at some point. Use unnest in a FROM clause. -

Re: [SQL] extracting location info from string

t to impossible to distinguish it from a country. Not least because some places are both, eg: Luxembourg The Vatican Singapore (The Grand Duchy of Luxembourg has other cities, but still serves as an example). -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Se

Re: [SQL] Which version of PostgreSQL should I use.

On 23/05/2011 11:19 PM, jasmin.dizdare...@gmail.com wrote: Just be careful with pg_dump, if you have binary data stored in your 8.4 db. In default mode it just export text. Er ... what? Can you elaborate on that? Do you mean large objects? bytea fields? Something else? -- Craig Ringer

Re: [SQL] extracting location info from string

sy to INSERT a record into a side table that flags it for later examination if necessary, and to RAISE NOTICE or to issue a NOTIFY if you need to do closer-to-realtime checking. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-sql mailing list (pgs

Re: [SQL] Which version of PostgreSQL should I use.

t doesn't understand the new hex format. -- Craig Ringer -- 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] Which version of PostgreSQL should I use.

an application's own dump/load feature rather than pg_dump and the app wasn't ready for the new bytea format. -- Craig Ringer -- 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] Problems Pgdump

ase folding, see: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS http://sql-info.de/postgresql/postgres-gotchas.html http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php -- Craig Ringer -- 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] best performance for simple dml

O foo VALUES (1,'joe','dean'), (4,'fred','bob'), (11,'anne','smith'); There'll be a threshhold above which the COPY protocol becomes faster, though. -- Craig Ringer -- 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] Looking for a "show create table " equivalent

ystem. If the INFORMATION_SCHEMA isn't sufficient for the OP's purposes or is too clumsy for re-creating DDL from, there's another option. It's ugly, but you can add a plpythonu or plperlu server-side function that calls the server's copy of pg_dump and spits the resulting text back

Re: [SQL] (pgsql8.4) DATA Corruption

. People here will normally advise you to do that first and I'm kind of surprised nobody did. -- Craig Ringer

Re: [SQL] FW: Hi

. You want the FIRST error. -- Craig Ringer -- 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] how to temporally disable foreign key constraint check

that enforce the checks on particular tables. This must be done table-by-table, there's no global way to do it. Use ALTER TABLE ... DISABLE TRIGGER to do it. See: http://www.postgresql.org/docs/current/static/sql-altertable.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsq

Re: [SQL] Handling mutliple clients access with views

way of doing what I try to do ? Or maybe am I a > little bit too paranoïde, and maybe should I handle all my clients with > a single postgresql user, handling all safety aspect in my php script ? Nope, I heartily approve of doing security in-database, especially if you can do it declarativel

Re: [SQL] Handling mutliple clients access with views

nce row-level security is implemented you could switch to that without the app caring. -- Craig Ringer -- 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] How to write sql to access another odbc source.

able to do it even more easily using ODBC FDW (SQL/MED). -- Craig Ringer -- 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] How to implement Aggregate Awareness?

nhanced to recognise queries against a base table and rewrite it to use a materialized view of the table when it sees that the query only touches data collected by the materialized view. Right now, there isn't really anything for query rewriting like this to /target/ . -- Craig Ringer --

Re: [SQL] pg_dump : problem with grant on table columns

On Nov 15, 2011 9:46 PM, "Brice André" wrote: > > Hello, > > I sometimes define some access rights on table columns instead of whole table. Everything works fine except when I perform a dump. > > When I dump a databse that contains such access rights, the pg_dump utility generates commands like th

Re: [SQL] No response from the backend

they connected? etc. -- Craig Ringer

Re: [SQL] No response from the backend

same host, it's unlikely to be network connectivity. Check the server logs. -- Craig Ringer

Re: [SQL] partitions versus databases

-postgresql-are-partitions-or-multiple-databases-more-efficient/ That'll help avoid duplication of effort, and make it easier for people searching for similar topics later to find out more. -- Craig Ringer

Re: [SQL] Query Timeout Question

ether the ODBC driver uses that or not. Examine the ODBC `mylog' output after enabling psqlODBC debugging, and examine the server log after turning on query logging, so you can see what the ODBC driver actually asks the server for when you set a query timeout. -- Craig Ringer

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

meout+&l=&d=-1&s=d> IIRC there are server backend changes required to make it possible to implement setQueryTimeout, and nobody's come up with an acceptable patch. I haven't followed the issue so I could easily be mistaken, though. -- Craig Ringer -- Sent via pgsql

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

an be done in a container-independent way (*shudder*). I'm quite sure that using EJB timers is NOT the right way to do it - they're not supported by web profile containers and are really intended for "business level" timers that should be persistent across redeploy/relaunch of

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

On 12/14/2011 11:30 PM, Sylvain Mougenot wrote: Thank you Craig Ringer for the detailed list of post (I found some by myself). Specially, I'm glad to see it is #1 "TODO" on the compliance matters. http://jdbc.postgresql.org/todo.html#Compliance As a reminder, I found post (on th

Re: [SQL] JDBC Statement.setQueryTimeout : is there plan to implement this?

Replied on pgsql-jdbc; please follow the discussion there. -- Craig Ringer -- 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] possible bug in psql

l(SIGWINCH) its ppid since it is so often invoked as a helper. I guess they probably have good reasons not to. -- Craig Ringer -- 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] Partitioned Tables

in the parent and report that. As a workaround, ignore the insert count for partitioned tables, or insert directly into the appropriate partition(s). -- Craig Ringer -- 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] how to use schema with data type

ong, and when? What error messges did you get? The first form looks reasonable to me, though I haven't tested. If you need to quote the schema for caps reasons, you'd use: "xchromasun"."weekly_mpr" -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-s

Re: [SQL] Insane behaviour in 8.3.3

nt than SW upgrades. ... and a lot more difficult :S Log monitoring is often the most imporant part - monitoring for NMIs and other hardware notifications, checking the kernel log for odd issues or reports of unexpected segfaults from userspace programs, etc. -- Craig Ringer -- Sent via

Re: [SQL] Is there a similarity-function that minds national charsets?

); SELECT similarity( flatten_accent('Müller'), target_column ); Note that the flatten_accent function must be IMMUTABLE and can't access or refer to data in other tables, columns, etc nor SET (GUC) variables that might change at runtime. -- Craig Ringer -- Sent via pgsql-sql mai

Re: [SQL] How to limit access only to certain records?

w.postgresql.org/docs/9.1/static/sql-createfunction.html> http://www.postgresql.org/docs/current/static/sql-grant.html <http://www.postgresql.org/docs/9.1/static/sql-grant.html> http://www.postgresql.org/docs/current/static/sql-revoke.html <http://www.postgresql.org/docs/9.1/static/sql-revoke.html> Hope this helps. -- Craig Ringer

Re: [SQL] Simple Upgrade from PostgreSQL version 8.1.11 (With schemas)

nd see what breaks. For simpler apps that can be a reasonable choice. -- Craig Ringer

Re: [SQL] Selecting data from XML

/static/functions-xml.html#FUNCTIONS-XML-PROCESSING You should be able to do it with some xpath expressions. It probably won't be fast or pretty. Consider using PL/Python, PL/perl, PL/Java, or something like that to do the processing and return the resultset. -- Craig Ringer

Re: [SQL] Tablesample Function on Postgres 9.1

l.org/wiki/TABLESAMPLE_Implementation) Sorry, that wiki page is just blue-sky speculation. If the feature were supported, you would find it in the main documentation. Wiki page updated to make that clearer. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make chang

Re: [SQL] ORDER BY COLUMN_A, (COLUMN_B or COLUMN_C), COLUMN_D

as an aggregate: regress=# SELECT min(x.a) FROM ( VALUES ('blah'),('blah2'),('') ) x(a); min -- (1 row) -- Craig Ringer -- 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] Calling the CTE for multiple inputs

uery where required. Without a schema to test with and some understanding of what the query does it's hard to say exactly. Wrapping it in a function is likely to be less efficient, but probably easier. -- Craig Ringer WITH cal AS ( SELECT generate_series('2

Re: [SQL] find sudstring on text (data type) field failure

On 11/07/2012 04:36 PM, pantelis vlachos wrote: > I was trying to find a substring on a text (data type) column like > 'cat foo dog ...'. > I use the query below > SELECT id FROM table WHERE name LIKE '% foo %'; > Sometimes the query return with nTuples=0 but there are matching rows. > On retry, th

Re: [SQL] organizing cron jobs in one function

ing PgAgent. Why not use PgAgent instead? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] organizing cron jobs in one function

On 11/19/2012 01:11 AM, Louis-David Mitterrand wrote: > On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote: >> On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote: >>> Hi, >>> >>> I'm planning to centralize all db maintenance jobs from a single &

Re: [SQL] Select into

Index Cond: (id = 5912) -> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=4) Index Cond: (public.x.id = 4123) (5 rows) Will that do the job? -- Craig Ringer -- 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] Select into

ike in the following valid but rather nonsensical query: UPDATE x SET val = (SELECT id+1) WHERE id = 1; Using the table alias will not change the query plan at all, it just makes the reference to "id" within the subquery unambiguous to the reader. Sorry for the repeat post. --

Re: [SQL] Select into

es with the subquery-based one I proposed. You should probably use this one instead of the subquery one. -- Craig Ringer -- 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] Rollback locks table - why?

nually (after you get the constraint error or a "current transaction is aborted" message) does it work as you would expect? -- Craig Ringer -- 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] Rollback locks table - why?

aybe you should tell the readers of this list a little more about what you're trying to do and why? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Deferrable NOT NULL REFERENCES ... for two-way referential relationship

there some better way that I'm missing? Is what I'm trying to do crazy for some reason I haven't spotted? -- Craig Ringer -- 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] Deferrable NOT NULL REFERENCES ... for two-way referential relationship (SOLVED?)

Craig Ringer wrote: Hi all I'm running into an issue with my schema where I'm trying to establish a mandatory two-way relationship between two entities, and I'm hoping for some external wisdom. Using "customer" and "address" by way of example: CREATE TABLE

Re: [SQL] link to Jsp

t:port/database where database is the database to connect to, host the server to connect to, and port the port number. If left out, host defaults to localhost (not 127.0.0.1 see applets!) and port to 5432 (configurable at build time). --- -- Craig Ringer -- Sent via pgsql-sql maili

Re: [SQL] Removing redundant itemsets

right now. It's interesting, though, so I might keep fiddling. -- Craig Ringer -- 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] Removing redundant itemsets

Craig Ringer wrote: > Allan Kamau wrote: >> Hi all, >> I have a list of purchases (market basket) and I would like to select >> non redundant longest possible patterns by eliminating >> (creating/populating other table to contain only non redandant itemsets) >>

Re: [SQL] Removing redundant itemsets

tained_by FROM togo a INNER JOIN togo b ON (a.tid <> b.tid) GROUP BY a.tid, b.tid HAVING EVERY(EXISTS( SELECT 1 FROM togo x WHERE x.tid = b.tid AND x.item = a.item )); ... but I'm a bit of an idiot, and couldn't figure out why the EVERY(EXISTS(subq)) wasn't working when test

Re: [SQL] Sequential non unique IDs

of course test and investigate before doing anything as crazy as taking my word for it. > Anyone know a solution for this? This can't be the first time anyone has > ever tried to do this. Thanks! If I'm not mistaken about the similarity, you might want to search the archives for

Re: [SQL] Problem commit in function

[EMAIL PROTECTED] wrote: Hi, I need to execute COMMIT in a function pgsql, there is a way? A function runs in a transaction. It cannot, as far as I know, commit one. Why do you need to do that, anyway? What're you trying to achieve? -- Craig Ringer -- Sent via pgsql-sql mailing

Re: [SQL] Commit every processed record

PL/PgSQL, is there any reason you can't do all the work in one transaction and commit when the function finishes? -- Craig Ringer -- 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] count question

no_of_bays, meter_id::text||'-'||i::text AS bay_id FROM meter, generate_series(1, (select max(no_of_bays) from meter)) as i WHERE i <= no_of_bays; -- Craig Ringer -- 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] Testing for null record in plpgsql

e containing query as a subquery I don't see much to be done. I'm still curious about the purpose of using null values like this is, though. -- Craig Ringer -- 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] How to find double entries

cy data set to PostgreSQL I found the `fuzzystrmatch' contrib module immensely helpful. http://www.postgresql.org/docs/current/static/fuzzystrmatch.html -- Craig Ringer -- 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] How to find double entries

with other apps that need to handle addressing information, and I've had some truly horrible experiences with apps that try to be too strict in their address checking. -- Craig Ringer -- 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] using string functions on plpgsql

Marcelo Edgardo Paniagua Lizarraga wrote: declare i integer; begin ... i = length(texto) ^^^ i := length(texto) -- Craig Ringer -- 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] using string functions on plpgsql

Craig Ringer wrote: Marcelo Edgardo Paniagua Lizarraga wrote: declare i integer; begin ... i = length(texto) ^^^ i := length(texto) Whoops, I spoke too soon - it seems both are valid for assignment. Has that always been true? The one time I don't write a small

Re: [SQL] What does mod - in "mod statement" - stand for?

Daniel CAUNE wrote: > Hi, > > Could you please tell me what does mod - in "mod statement" - stand for? `mod' is shorthand for "modify" or "modifying", ie statements that modify the data. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql

Re: [SQL] error codes

un: javac -d /out/dir JdbcTest.java and to run (all on one line): java -classpath /path/to/postgresql-8.3-603.jdbc4.jar:/out/dir JdbcTest where /out/dir is wherever you want the generated .class file to be put. -- Craig Ringer -- 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] error codes

Craig Ringer wrote: > Marcin Krawczyk wrote: >> Hi guys. Does anyone know the error code for '/currval of sequence * is >> not yet defined in this session/' error ? Is there one at all? > > A quick JDBC test program shows: > > ERROR: currval of sequence &q

Re: [SQL] Problems with function while migrating from Windows to Linux

a guesss, you were using PostgreSQL 8.3 under Windows and the production Linux system has PostgreSQL 8.2 or earlier, which does not support EXECUTE ... INTO . -- Craig Ringer -- 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] After updating dataset the record goes to the end of the dataset

cause it usually works in some databases doesn't mean it won't break just rarely enough to drive you insane while debugging... -- Craig Ringer -- 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] LEFT OUTER JOIN question

to be present. If you absolutely must perform comparisons where NULL should be considered equal to NULL use `IS DISTINCT FROM` ... but as everybody here says, use of that often suggests design problems in your queries and schema. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@po

  1   2   >