Re: [SQL] [GENERAL] How to split a table?

split a table? am Tue, dem 17.10.2006, um 1:53:35 -0700 mailte Gregory S. Williamson folgendes: > Perhaps something like: > > CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60); Then we have 2 tables: one with 100% data and one with around 60% ;-) If the table cont

Re: [SQL] [GENERAL] How to split a table?

Perhaps something like: CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60); ? HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Andreas Kretschmer Sent: Tue 10/17/2006 1:34 AM To: pgsql-general@postgresql.org; pgsql-sq

Re: [SQL] files or DataBase

As Jim N. already pointed out, extra layers do have a cost in time, and for some things straight disk is way faster. A few points to consider (you don't give enough details for me to tell if this is true for you or not) ... a) ACID -- what databases do best, making sure that everything that was

Re: [SQL] Evaluation of if conditions

Daniel, AFAIK there is no short-circuiting of evaluations in postgres and I don't think you can depend on the order they appear in to determine the order in which they are checked, although more knowledgable people may have better info than I ... so the rewritten form is the way to go. Greg Wi

Re: [SQL] Logging in Stored Procedure

Well, perhaps the NOTICE functionality, e.g. RAISE NOTICE ''report id of % for date %'', rpt_rec.report_id, rpt_rec.report_s_date; This will show on the terminal if running from a console, and also writes to the log, IIRC. See the documentation for your version for details. HTH, Greg Williams

Re: [SQL]Linked List

Ben, The pgsql function is compiled and wouldn't know how to handle a table name as a variable. If you rewrite the SQL to use the 'EXECUTE' statement I think you could do this, something along the lines of (untested): EXECUTE ''INSERT INTO '' || quote_ident(tmplist) || '' (SELECT * FROM links

[SQL] Truncate and Foreign Key References question

This is in postgres 8.1: PostgreSQL 8.1.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) I've got a table in one schema (work.client_collect_rates) which has an FK constraint with a table, content.collections_l (definitions shown below). There's a

Re: [SQL] Sequential scan where Index scan expected (update)

Perhaps it is clogged with dead tuples -- has it been vacuumed recently with enough FSM space ? It seems unlikely but maybe try an explict cast for the thing_id call, e.g. explain update xx_thing_event set thing_color='foo' where thing_event_id=1::bigint; It may also be that 5842 rows is eno

Re: [SQL] Como ejecutar una funcion insert en plpgsql....

W. ps be sure to send questions, etc. to the list as a whole so that more people have a chance to see the question and answer, and because any one individual might be gone for a while. -Original Message- From: Fernando Garcia [mailto:[EMAIL PROTECTED] Sent: Thu 11/10/2005 6:23 AM To:

Re: [SQL] Como ejecutar una funcion insert en plpgsql....

arcia [mailto:[EMAIL PROTECTED] Sent: Thu 11/10/2005 5:23 AM To: Gregory S. Williamson Cc: Subject:Re: [SQL] Como ejecutar una funcion insert en plpgsql thanks very much, jeje, my english its very rusry... i try explain to you what i can to do in the first question.remember

Re: [SQL] Como ejecutar una funcion insert en plpgsql....

Fernando -- I am not sure about the first question -- my spansih is rusry. postgres does force all column, table and schema names (I think) to lower case (there have been recent long discussions about this on this list IIRC). If you want to preserve case put the column name in double quotes: "

Re: [SQL] Help with simple SQL query?

Joost -- You are correct in stating that the problem is that the subquery returns more than 1 row -- try using the NOT IN syntax ... it is not likely to be very efficient but at least avoids the syntax error: select order_id from order, orderline where order_id = parent_order_id and order_price

Re: [SQL] using pg_tables and tablename in queries

Yasir -- You wrote: >If I generate a temporary table instead of returning the results how >long will that table exist for? Excuse the OOP terminology but would >it be correct to create a 'Singleton' to access the temporary table, >where if it exists and is less than 30 minutes old use that one,

Re: [SQL] delphi access question?

Something is not translating CHAR values correctly -- all the fields that you "see" are not char values. Have no idea off hand *why* this would be ... character encoding differences maybe ? Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Joel Fradkin [mailto:[EMAIL PRO

Re: [SQL] dblink versus schemas. What to use in this case?

Robert, A couple of possible issues -- Running two different databases (on the same server) implies more use of system resources, but may be slightly more robust (i.e. one could go down but the other would still work). dblink is certainly slower than refering to a table in a schema, but it se

Re: [SQL] how can I query for unset timestamps

Bret -- A test for NULL uses the IS statement, e.g.: SELECT * FROM tablefoo WHERE last_timestamp IS NULL; HTH, Greg Williamson DBA GlobeXPlorer LLC -Original Message- From: Bret Hughes [mailto:[EMAIL PROTECTED] Sent: Tue 2/22/2005 1:09 PM To: postgresql sql list Cc: Subject

Re: [SQL] commit inside plpgsql function

Already been answered (by me) -- you can't do this in postgres 7.x or earlier -- procedures may not have begins/commits or rollbacks. Version 8.0 *may* be different -- I don't have it installed yet; it does allow for some nesting of transactions but I don't know if this is allowed. The 8.0 docu

Re: [SQL] commit in plpgsql function?

I don't think you can do transactions within a procedure in postgres, at least in version 7.x, which I am most familiar with. Page 579 on the postgres 7.4 manual says: "Functions and trigger procedures are always executed within a transaction established by an outer query --- they cannot start

Re: [SQL] NULLS and string concatenation

Someone on this list provided me with a rather elegant solution to this a few weeks ago: CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE $1 || '' '' || $2 END' LANGUAGE sql; CREAT

[SQL] Comparing two (largish) tables on different servers

This is probably a silly question. Our runtime deployment of database servers (7.4) involves some redundant/duplicate databases. In order to compare tables (about 5 gigs each) on different servers I unload the things (takes a while etc.), sort them with a UNIX sort and then do a cksum on them.

Re: [SQL] explain analyze results are different for each iteration

Are you forcing stuff out of cache (both postgres and OS) ? I've found that this can make a huge difference with some queries ... not sure that this would make it have different plans, though. My ill-informied $0.02 worth ... fwiw Greg Williamson DBA GlobeXplorer LLC -Original Message

Re: [SQL] Copy command freezes but INSERT works fine with trigger oninsert.

The copy command will run as a single transaction -- all succeed or all fail, I think, so if you interrupt it no rows will be loaded. The inserts -- unless wrapped into a transaction with a "begin; ... commit;" block will each be a single transaction. Check you postgres log file for the time wh

Re: [SQL] Sorry too many conecctions

In the root directory in which postgres stores the data is a file: postgresql.conf Edit the file with a text editor (vi / ed / etc.) and change : max_connections = ### to max_connections = 1000 (or more since other applications or a DBA may need to connect as well) and also change shared_bu

Re: [SQL] Queries across multiple database

Although postgres itself doesn't seem to have a thing such as Informix' "synonym" which allows more-or-less transparent access to a table in a remote database, there is a utility in contrib directory of the postgres source called dblink, which allows access to different instance of postgres by o

Re: [SQL] feature request ?

Programming languages, perhaps, but clearly not uncommon in SQL ... Informix certainly allows a column to be of type boolean but with a value of NULL for given rows (unless precluded by a not-null constraint). Should we question integers, which can be positive, negative, or -- gasp ! -- NULL ?

Re: [SQL] begin update ... syntax error

try a semicolon after the begin ? begin; commit; Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Kemin Zhou [mailto:[EMAIL PROTECTED] Sent: Sat 4/10/2004 2:32 PM To: [EMAIL PROTECTED] Cc: Subject:[SQL] begin update ... syntax error I was trying to spe

Re: [SQL] function definition documentation

Thanks muchly for the excellent tip. Lots of useful references there. I seem to have battled through this thicket -- onwards ! Greg -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 23, 2004 12:09 AM To: Gregory S. Williamson; [EMAIL PROTECTED

[SQL] function definition documentation

I am having an impossible time porting some simple Stored Procedures from Informix to postgres. The documentation is almost self defeating. Are there any better descriptions of how to define functions that return several tuples to a wide variety of calling programs (e.g. I can't count on my user

Re: [SQL] Database diagram

ERWin is likely to be expensive, judging by the cost for an Informix license. It is also a serious tool; has its quirks but can be used to reverse engineer as well as define a database with detailed support for triggers and the like. Greg Williamson DBA GlobeXplorer LLC -Original Message--

Re: [SQL] COPY command

This sounds as if the last character in each line might be a delimiter (a ",") which is standard for data unloaded from some sources; if this is the case try removing it and your data should load. Or you may have a comma in a character field and that is throwing off the count for the line in que

Re: [SQL] production parameters

You might find some things of use in these: Greg W. DBA GLobeXplorer LLC -Original Message- From: chester c young [mailto:[EMAIL PROTECTED] Sent:

[SQL] lower/upper functions and strings in searches

I am obviuously doing some newbie trick, and I ordinarily would spend time browing the archives, but the archives.postgresql.org site seems to be absurdly slow. This is 7.3.3 on a linux box. I have a bunch of data with state, city, county and country names. When our application does a search fo