Re: [SQL] Postgres performance

ere's some useful stuff here: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php and also here: http://www.powerpostgresql.com/PerfList -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Postgres performance

Mauro Bertoli wrote: Hi Richard, thank you for your apreciated answers!!! - start quote - Well, do you care whether your data is consistent or not? If not, you don't need transactions. - end quote - I don't require transaction because the query aren't complex and update a s

Re: [SQL] Query issue/8.0.1/Serendipity

OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1)) AND e.id = ec.entryid AND c.categoryid = ec.categoryid AND c.category_left BETWEEN 3 AND 4" -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] datestyle setting

near "datestyle" at character 20' I keep making this mistake, you need to include the database name: ALTER DATABASE my_db_name SET datestyle TO 'ISO'; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] datestyle setting

Kenneth Gonsalves wrote: On Wednesday 09 Mar 2005 1:40 pm, Richard Huxton wrote: I keep making this mistake, you need to include the database name: ALTER DATABASE my_db_name SET datestyle TO 'ISO'; er ... doesnt seem to be in the docs? Anyway it worked, thanx http://www.postgresql.or

Re: [SQL] order by question

),not(a=4),a; a | b | c ---+--+- 6 | ccc | BBB 4 | aaa | BBB 1 | aaa | AAA 2 | zxxx | AAA 3 | ccc | ZZZ 5 | zxxx | BBB (6 rows) Alternatively: (a<>6),(a<>4),a -- Richard Huxton Archonet Ltd ---(end of broadcast)--

Re: [SQL] Table like a field

section of the source distribution. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] [GENERAL] more execution time

',pd2.id) as accbalance, I'm guessing point 6 is actually your problem - try it without the calls to balance() and see what that does to your timings. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our

Re: [SQL] sql: "LIKE" problem

quot;LIKE 'abc\\d%'", the result is also "0 rows". You'll need to escape the backslash twice. Once for the SQL-level parsing and once for the LIKE pattern-matching itself. SELECT * FROM t_test WHERE c_name LIKE 'abcd%'; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] finding schema of table that called a trigger

- it would obviously be simple enough to add to the interface, but I presume there is some overhead for each parameter you supply. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ?

Re: [SQL] Inserting values in arrays

- {Hello1,World1} {hello2,World2} (2 rows) I think in 7.3 you might have to write your own function to assemble the array. I'm not an array expert though, so might be worth checking the mailing list archives. -- Richard Huxton Archonet Ltd ---(end of broadcast)--

Re: [SQL] select multiple immediate values, but in multiple rows

FROM foo; a --- 1 2 3 (3 rows) richardh=> SELECT * FROM foo WHERE a NOT IN (1,2); a --- 3 (1 row) Run some tests with nulls in the column and the constant list too so you understand what happens in those cases. -- Richard Huxton Archonet Ltd ---(end o

Re: [SQL] comparing 2 tables

ess? If so, how. In general terms, with MS-Access, I'd build separate queries for each step. So you'd want a query for: 1. Reset table3 2. Insert to table3 rows in table1 and not in table2 3. Insert to table3 rows in table2 and not in table1 ...etc... HTH -- Richard H

Re: [SQL] comparing 2 tables

s in large batches. In my case it was company-profile data (services, specialisations, contact personnel etc) and users would update their data at most every few months. I actually had a review phase in my system between editing and publishing a new version of a company's data. -- Richa

Re: [SQL] query

gre?* -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Query performance problem

re plenty of "amount" columns that should be not-null (total spent, total ordered etc). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] Trigger with parameters

h no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described below. See pl/pgsql - trigger procedures for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)-

Re: [SQL] date subtraction

- creation_date FROM my_table; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] index scan

ant an index scan? Do you have any evidence it will be faster than a sequential scan? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] index scan

Performance Tips Chapter 23. Monitoring Database Activity Understanding how to read EXPLAIN ANALYSE output and manage statistics are vital. Finally, details on configuration settings can be found at: http://www.powerpostgresql.com/PerfList http://www.varlena.com/varlena/GeneralBits/Tidbits/index.

Re: [SQL] Self-referencing table question

c1.to_id = c3.to_id AND c1.val > 0.5 AND c1.to_id < from_id ; I think PG should be smart enough nowadays to figure out these two queries are basically the same. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forg

Re: [SQL] Merging item codes using referential integrity

es in the function take place in the same transaction, so if there are any problems then all changes will be rolled back. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] save me from an unconstrained join

yte_limit and software.fileszie. Now, it's not an equality test, but there's nothing wrong with that. You could probably do something clever with subqueries rather than using min() but it would only complicate the query afaics. -- Richard Huxton Archonet Ltd -

Re: [SQL] Speed up slow select - was gone blind

st. Also, make sure your basic PG tuning is ok. http://www.powerpostgresql.com/PerfList -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] select & group by

ble WHERE eta IS NULL ) bb WHERE aa.part = bb.part AND aa.mfg=bb.mfg ; This is assuming you only have one row with "eta" set for each (part,mfg). If not, you'll have to identify which row you want. -- Richard Huxton Archonet Ltd

Re: [SQL] Crosstab function

, what sort). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Query Problem

FROM flight_to, country c1, country c2 WHERE dest_from = c1.id AND dest_to = c2.id ; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Table PARTITION

basically looking at some combination of - partial/conditional indexes - unions - inheritance -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail c

Re: [SQL] Table PARTITION

Dinesh Pandey wrote: Hi Richard, I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't know how to create PARTITION in postgres. Can you pls tell me little more. You can't I'm afraid. You can use inheritance / a view unioning tables to do something similar th

Re: [SQL] Table PARTITION

your log-tables by date there's not much point in partitioning by date. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PRO

Re: [SQL] Question on triggers and plpgsql

rward the message, it will resend. There is always a small window where the receiving mailserver might actually have received the message without the acknowledgement being logged by the sender. -- Richard Huxton Archonet Ltd ---(end of broadcast)--

Re: [SQL] Update aborted if trigger function fails?

es from text - even if PG understood all these languages, it couldn't determine which tables were accessed. So - how do you deal with this? Well, you test. Ideally, you should have a set of tests and re-run them to ensure all your functions work as desired. -- Richard Huxton Archonet Ltd

Re: [SQL] Very low performance on table with only 298 rows

show a lot of dead rows being removed? I'm suspecting a *lot* of dead rows need to be removed. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Prepared query ?

VALUES ...etc''; EXECUTE sql; Does that help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

long time unless you update this table a lot. -- Richard Huxton Archonet Ltd ---(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] row-attribute in EXPLAIN-output doesn't match count(*)

Andreas Joseph Krogh wrote: On Thursday 14 April 2005 19:12, Richard Huxton wrote: So - do you have an application/client that has had a single transaction open for a long time. A very long time unless you update this table a lot. Hm, it's a web-app, and I don't hold a transaction open

Re: [SQL] Query about SQL in PostgreSQL

the query Select * from "tblStudent" then it works fine. If you quote your table-names when you create them you should always quote them when you use them. If you don't quote your table-names when you create them, there is no need to quote them when you use them. -- Rich

Re: [SQL] accessing multiple database

abase from your client software. From within PostgreSQL itself, look into the dblink package in the contrib/ directory of the source distribution. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands

Re: [SQL] accessing multiple database

mohammad izwan ibrahim wrote: Hi Richard Huxton, Tq for your advice, Hope you can help me on this 1.can I create view from dblink query I believe so - see the dblink docs for details. 2.can I fetch the data simultaneous from multiple database using dblink function, how the syntax would be? Not

Re: [SQL] getting duplicate number is there a

up for that location for that year. I'd be tempted to have a case_numbers table with (year,location,max_num) and lock/read/insert to that. Makes everything explicit, and means you don't have to mess around with counts/substrings. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] postgre variable

bandeng wrote: hello all, i want to make dynamic sql query like this select * from tb_cust where name='erick' and age='20' to select * from tb_cust $1 i have tried but error comeup You have tried what? How? What error did you get? -- Richard Huxton Archonet Ltd ---

Re: [SQL] datatype conversion on postgresql 7.4.1

meric before you do this. Oh, and upgrade from 7.4.1 to 7.4.7 as soon as possible. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Transaction in plpgslq

prevent any changes to "contactos" in-between those two statements you'll want additional locks. Read the chapter on "concurrency control" for details. You might well want SELECT FOR UPDATE (and also just ORDER BY id LIMIT 1 rather than using min(id)). -- Rich

Re: [SQL] Convert int to hex

Fernando Grijalba wrote: I want to be able to change an int4 from a sequence and store it as varchar in the database as a hex number. Is this possible? Try the to_hex() function - in the "Functions and operators" chapter of the manual - "Strings" section. -- Richard H

Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 2713

easing the btree size? The big problem is "I can not add any additional column in this table." Why not? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 2713

doesn't offer any suitable candidate keys (as can well be the case) then common practice is to generate a unique number and use that as an ID - in PostgreSQL's case by use of the SERIAL pseudo-type. Does that help? -- Richard Huxton Archonet Ltd ---(end of b

Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 27

didn't mention he was using this for the speed of lookup. He'd defined the columns as being the PRIMARY KEY, presumably because he feels they are/should be unique. Given that they are rows from a logfile, I'm not convinced this is the case. -- Richard Huxton

Re: [SQL] index row size 2728 exceeds btree maximum, 27

Bruno Wolff III wrote: On Thu, Jun 02, 2005 at 13:40:53 +0100, Richard Huxton wrote: Actually, Dinesh didn't mention he was using this for the speed of lookup. He'd defined the columns as being the PRIMARY KEY, presumably because he feels they are/should be unique. Given that the

Re: [SQL] index row size 2728 exceeds btree maximum, 27

Bruno Wolff III wrote: On Thu, Jun 02, 2005 at 18:00:17 +0100, Richard Huxton wrote: Certainly, but if the text in the logfile row is the same, then hashing isn't going to make a blind bit of difference. That's the root of my concern, and something only Dinesh knows. Sure it i

Re: [SQL] how to store more than 3 MB of character data in Postgres

varchar". I think the problem is with ODBC restrictions rather than anything else. You should be able to get type "text" to map to a "Memo" type or similar, which should hold more than 64k IIRC. Failing that, try internal type "bytea" and

Re: [SQL] how to store more than 3 MB of character data in Postgres

l.org/project/psqlodbc/genpage.php?downloads First step though, should be to check whether there are any configuration settings that apply to longvarchar, because it sounds like the odbc driver should be mapping to that. I can't help you with finding those settings, since I have only ev

Re: [SQL] What is faster?

index on the table(s)? Is the table clustered? Expected to be cached in RAM? Do you have a specific problem, or reason to believe you may encounter one? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our

Re: [SQL] What is faster?

ading: http://www.powerpostgresql.com/PerfList now i need to make much faster as it is possible and have an idea just merge two tables in one - will it help me? If they hold the same information, they probably shouldn't have been split in the first place. -- Richard Huxto

Re: [SQL] to listadmin

ge to [EMAIL PROTECTED] In order to confirm the sent message, please -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] rule or trigger?

o be. You can always have triggers on tbldictionary to automatically insert into raw_words if you'd like. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Indices and user defined operators

all out for free ... Now *that* is very nice. Thanks for the example Tom - nice to know the sql list is still teaching me things within the first 5 messages I read. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you

Re: [SQL] Permission denied for language pltclu

unction as user "dbUSER" then it was a superuser too. Honest. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Permission denied for language pltclu

27;d start with: Ch 36.1. Installing Procedural Languages Ch 17.2. User Attributes Reference I - the "ALTER USER" command Note that you may want to make the client's user a superuser just long enough to install the language and/or functions. -- Rich

Re: [GENERAL] [SQL] Permission denied for language pltclu

Dinesh Pandey wrote: Hi Richard/ Michael Thanks for your great help. I got the problem. Actually, I was not getting the cause of this problem, because it was working properly at our end. Actually this problem occurs when the function is being created by the user who has not created the

Re: [GENERAL] [SQL] Permission denied for language pltclu

Tino Wildenhain wrote: No :-) But if you are able to create databases, you are a superuser :-) And as a superuser you can also create the untrusted functions. Not quite - if you can create USERS you are a superuser. -- Richard Huxton Archonet Ltd ---(end of

Re: [SQL] how can i UPDATE without dead rows

ttings are large enough in postgresql.conf and that you vacuum enough - that will keep the "dead" space being re-used. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregis

Re: [SQL] info

Luca Rasconi wrote: I need to know if somebody read this mail. Ah, but what does it mean to "know" anything? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] FW: help with serial

from time to time. Check the mailing-list archives for discussion - it's because the rule acts as a macro, duplicating the expression. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Alias to a type

ications though, make sure they cope. Some don't cope well with user-defined types. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] SQL Query question

r the join whereas the others push the condition inside the join. I'm inclined to prefer PG's way of doing things, since it means you get what you explicitly asked for (to my point of view anyway). Not sure what the SQL spec says though, and in the end I suppose that's the only way t

Re: [SQL] left joins

roblem, you'll need to make sure the queries are the same. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

[SQL] function parameters : bug?

nsert into myTable(inta) values (my_inta); return(0); end; $body$ LANGUAGE 'plpgsql' But, this means that if I change the design of my tables, I have to possibly change my function headers rather than merely the code inside them. regards Richard ---(end of bro

Re: [SQL] function parameters : bug?

Richard Hayward wrote: pg 8.0.3 This behaviour seems odd to me: CREATE TABLE mytable ( inta INTEGER ); CREATE OR REPLACE FUNCTION myfunction (inta integer) RETURNS integer AS $body$ begin insert into mytable(inta) values (inta); ERROR: syntax error at or near "$1" at ch

Re: [SQL] Possible to use a table to tell what table to select from?

archives of the hackers list. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Help With complex join

ARTDATE) ); Make your life easier and have start and end-dates. Oh, you might want a "finished-here" flag too to indicate the end-date can be checked. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] difference between all RDBMSs

orry if this isn't as much help as you'd like, but you'll need to give more detail if you want a more detailed answer. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] How to obtain the list of data table name only

_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES" Something like this perhaps? SELECT * FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'; Of course, you'll probably want to allow for multiple schemas.

Re: [SQL] Postgres for Fedora Core 2 OS ****************

ot;, look in v8.03, linux, rpms, fedora, fedora-core-2 Does that help? -- Richard Huxton Archonet Ltd ---(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] Create trigger for auto update function

h checking whether pass has been changed: IF NEW.pass IS DISTINCT FROM OLD.pass THEN ... END IF However, if you do this then you have to test TG_OP to see whether you are inserting or updating - insert ops don't have OLD defined. HTH -- Richard Huxton Archonet Ltd

Re: [SQL] echo/printf function in plpgsql

don't have a STDOUT (although you might have redirected STDERR for logging). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Is there a type like a growable array, similar Vector at

ashion is only allowed for one-dimensional arrays, not multidimensional arrays. END QUOTE Is that helpful? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Joining two large tables on a tiny subset of rows

query and the indexes, then vacuum analyse the tables and post an EXPLAIN ANALYSE. The problem will probably turn out to be poor row estimates (you can increase the statistics gathered on the mrna_acc values) or poor configuration settings (making indexes look expensive compared to sequential scans). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Why are these queries so different in time?

x27;d remove the LEFT JOINs, since your WHERE conditions seem to rule out the NULL cases anyway. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Make C file for create type

ther than malloc() if this is supposed to sit inside PostgreSQL. Bear in mind it's been 10 years since I wrote any C though, so use your own judgement on my advice. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Faster count(*)?

e other. Not always accurate enough, but it is quick. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Select problems

gt;= 'anydate'" the view lasts 7 minutes. And what are teh view definitions and the output of EXPLAIN ANALYSE? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] catch an 'update where false' ?

ossible to say whether this is sensible without knowing the precise details of your problem. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Returning with the inserted id

ublic.test_azon_seq'); And yes, it will cope with multiple concurrent connections inserting. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] how to have 2 select creteria on one column?

uggle with the two select creteria on one column. SELECT * FROM my_table WHERE code = 'k' AND code <> 'kV' ; HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Selecting count of details along with details columns

't say what results you are expecting. Do you want: 1. ALL values of T1_name (in which case what count do you want)? 2. The FIRST value of T1_name (in which case what do you mean by first)? -- Richard Huxton Archonet Ltd ---(end of broadcast)--

Re: [SQL] Selecting count of details along with details columns

Axel Rau wrote: Am 29.09.2005 um 10:30 schrieb Richard Huxton: Axel Rau wrote: SELECT T2.T2_name, COUNT(T1.id) AS xx FROM T2, T1 WHERE T2.id = T1.fk_t2 GROUP BY T2.T2_name HAVING COUNT(T1.id) > 1 ORDER BY xx DESC; t2_name | xx -+ T2-N2 | 3 T2-N3 |

Re: [SQL] Why doesn't the SERIAL data type automatically have a

es are getting burned maybe it would be useful to toss a NOTICE or maybe even WARNING when a serial is created without a unique constraint of some kind? Don't forget the NOT NULL too. Perhaps simpler to have a PGIDENT pseudo-type that implies "UNIQUE NOT NULL" and then explain the

Re: [SQL] MOVE in SQL vs PLPGSQL

Help? Andrew - your question doesn't seem to make sense - can you give some more details? Are you having some problem with cursors in plpgsql? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] creating postgres tables by passing a string to function

you want to look at the EXECUTE command if you're thinking of writing functions in plpgsql. However, you don't say how you came to the conclusion that separating your shipping-table into hundreds of smaller tables was the best design. Can I ask what testing you did and what the result

Re: [SQL] BirthDay SQL Issue

it. Oh - you might want to read up on functional indexes in the manual too, if this is a query you'll be running regularly. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Getting user created tables from SQL

uilder? I don't suppose this could be an identifer case issue? In the first query TNAME will be case-folded to 'tname' (because that's how PG does case-insensitive identifiers). Try looking up FieldByName("tname") and see if that comes up with anything. -- Ric

Re: [SQL] MOVE in SQL vs PLPGSQL

;t supported for plpgsql cursors. You could probably do something with EXECUTE and returning a refcursor from a previous function, but that sounds fiddly. I must admit, on the odd occasion I want to skip a row, I just FETCH it and move on. Anyone else? -- Richard Huxton Arc

Re: [SQL] Noob question about types and lists

client_id ..., address_id ...) Then, you can add some foreign-key constraints so PG makes sure that the ID numbers in client_address are all valid. This makes it simple to get client names, client addresses or both together. HTH -- Richard Huxton Archonet Ltd ---

Re: [SQL] Noob question about types and lists

xchris wrote: On gio, 2005-10-06 at 12:20 +0100, Richard Huxton wrote: From the manuals... I didn't search very well.Sorry! Easy enough to miss. However - I don't think you want to anyway. If you're going to use a relational database, try a relational approach! Typicall

Re: [SQL] RULES on SELECT with JDBC/perlDBI from other RDBMS products?

k() module in the contrib/ directory of the source distribution? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] How to delete Large Object from Database?

28 of the manuals. I seem to recall some other utilities in the contrib/ directory of the source distribution too. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to

Re: [SQL] How to delete Large Object from Database?

r own tables, and will not create the associated trigger(s). Also, users may not remember (or know) to create the triggers. Could this be the case with your database? There are a couple of other points in the README.lo that are worth checking too. -- Richard Huxton Archone

Re: [SQL] SEVEN cross joins?!?!?

n in rows altogether? In either case - I don't suppose you could provide a real example of the query, so we can see exactly what you're trying to do. -- Richard Huxton Archonet Ltd ---(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] Difference from average

s.avg_price - prices.price)/averages.avg_price) AS pc_diff FROM prices, (SELECT property_id, avg(price) as avg_price FROM prices) AS averages WHERE prices.property_id = averages.property_id ; That's as much to do with how I think about the problem as to any testing though.

Re: [SQL] Returning NULL results?

if COUNT(*) is zero THEN bidCount := NULL ELSE if COUNT(*) is not zero then do SELECT from the begining? Could you check the FOUND variable? As in IF NOT FOUND THEN RETURN NULL -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: H

<    1   2   3   4   5   6   7   8   9   10   >