Re: [SQL] Updating column to link one table to another

amp is the same? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] linux clusters and pgsql

ure if anyone's seen one in the wild as it were. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Parsing a Calculation from a field

, pl/tcl. Perhaps pl/php too. The only way I can think to do it in pl/pgsql would be to have a line like: eval_qry := ''SELECT ('' || $1 || '')::integer AS result'' Then use FOR..IN..EXECUTE to get the results. HTH -- Richard Huxton

Re: [SQL] SQL design question: null vs. boolean values

t selected coded as 'n' Any advice, dear SQL experts ? First option. I'm not convinced the choice is optional - you've presented the tickbox to them so you have to assume they've read it and chosen not to tick it. -- Richard Huxton Archonet Ltd -

Re: [SQL] Inserting or Deleting conditionally

a WHERE ...) or similar. How is this correctly formulated? Or is there no other way than PL/xxx? Sounds like you want a middle-ware layer to provide an API for your applications. Plenty of options, but it's difficult for people to make suggestions without knowing more about your situation.

Re: [SQL] Returning a bool on DELETE in a proc.

7;ll need to use plpgsql (or similar) if you want a result status returned. -- 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] OFFSET impact on Performance???

r if the OFFSET is bigger. How can I somehow improve the performance on this? There's really only one way to do an offset of 1000 and that's to fetch 1000 rows and then some and discard the first 1000. If you're using this to provide "pages" of results, could you use a curso

Re: [SQL] OFFSET impact on Performance???

* FROM ... FETCH FORWARD 10 IN mycursor; CLOSE mycursor; Repeated FETCHes would let you step through your results. That won't work if you have a web-app making repeated connections. If you've got a web-application then you'll probably want to insert the results into a cache table for l

Re: [SQL] automatic table locking on too many locked records?

if too many records are beein updated? Or something elses? PostgreSQL's MVCC system means updates generally don't lock at all. For more info, Google for MVCC and check the manual for Transaction Isolation Levels. -- Richard Huxton Archonet Ltd ---(end of

Re: [PERFORM] [SQL] OFFSET impact on Performance???

expect: 1. Temp tables don't fsync 2. A cursor will spill to disk beyond a certain size -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister Your

Re: [SQL] OFFSET impact on Performance???

r select. Also, since you are repeating the query you could get different results as people insert/delete rows. This might or might not be what you want. A similar solution is to partition by date/alphabet or similar, then page those results. That can reduce your re

Re: [SQL] ERROR: row is too big: size 9856, maximum size 8136

-text columns to breach 8kB. It is a definite limit, but you shouldn't see it until you have hundreds of columns. If you can post the table definitions along with the view definition, that should let people see if they can reproduce the problem. -- Richard Huxton Ar

Re: [SQL] OID's

ry. I think what system of OID's is very useful for application! MF> What problem are you trying to solve? For example, I want to fetching all rows of the several tables in one query by means of LEFT JOIN, but not use UNION operator. Joins are designed to be over primary keys, you should make

Re: [SQL] Sorry I see my first question did not get posted (maybe

post there, mention you've tuned as per GeneralBits and provide an example of the query, view definition and the output from explain. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your

Re: [SQL] private table

ains their rows and if they log in as a different user they will see different data. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command

Re: [SQL] working with multidimensional arrays in plpgsql

erved here, the actual problem is how to do assignment to multidimensional array locations using the subscript operater. Actually, the problem is that the array "x" has been initialised to NULL. Therefore, you can't set individual values. Try a line like: x := '{}';

Re: [PERFORM] [SQL] OFFSET impact on Performance???

problem is not RDBMS IMHO. It's less the RDBMS than the web application. You're trying to mix a stateful setup (the application) with a stateless presentation layer (the web). If you're using PHP (which doesn't offer a "real" middle layer) you might want to look at me

Re: [SQL] same question little different test MSSQL vrs Postgres

return 22,636 rows. Not 27 seconds, as in the original post. You'll never persuade PG to use the index when some 75% of your rows match the filter - it just doesn't make sense. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6:

Re: [SQL] same question little different test MSSQL vrs Postgres

ake sure the figures are reasonable (rather than the best they can be). I have a lot of time now (two weeks) in this conversion and do not wish to give up, I will see if I can learn what is needed to get the maximum performance. I have seen much information available and this list has been a huge

Re: [SQL] same question little different test MSSQL vrs Postgres

between 2 and 4. Then, judge how much RAM your box is using to cache disk-space (free -m) and set effective-cache-size accordingly. That's it - you may want to play around with the figures slightly, but pick the lowest numbers above and restart PG and it'll run O

Re: [SQL] same question little different test MSSQL vrs Postgres

tid bits. Note that the effective_cache_size (if I've spelt it right) just tells PG what your cache size is. You should set it based on what "free" tells you about your system's use of memory. -- Richard Huxton Archonet Ltd

Re: [SQL] Complete instruction in a trigger

: update phones set number = '-'; Do you know if maybe exists a tg_stantement? No, and the situation isn't quite as simple as that. Think about foreign-keys with a cascading update. What precisely are you trying to do? -- Richard Huxton Archonet Ltd ---

Re: [SQL] Function Doubt

return a value inserted into the field. I do not want to use static command new.campo1, new.campo2 Ah - you can't. Not in plpgsql, anyway. You should perhaps look at pltcl or plperl. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- T

Re: [SQL] hardware mod based on feedback from the list

the additional drive systems will help the most for IO is what I am told is the big issue and hopefully utilizing the recommendation will help minimize the bottleneck. I believe there may well be an issue with multiple Xeon's - check the mailing list archives for details. -- Richard H

Re: [SQL]

/pgcrypto in the source distribution. Some encryption is one-way (so you can't recover the original password, just confirm a provided password matches it). Other encryption is two-way, but you usually have a separate key to encrypt the passwords then. -- Richard Huxton Archone

Re: [SQL] problem while converting sybase quries to postgres

ND t1.sr_tran_head_pk = t3.sr_tran_head_pk But you'll want to test it because I'm not clear what your query is doing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate s

Re: [SQL] problem while converting sybase quries to postgres

t3 ON t2.po_header_pk = t3.po_header_pk AND t1.sr_tran_head_pk = t3.sr_tran_head_pk But you'll want to test it because I'm not clear what your query is doing. -- Richard Huxton Archonet Ltd Thanking for you reply the problem is that i have a "or" condition and left ou

Re: [SQL] [PERFORM] Tunning postgresql on linux (fedora core 3)

2:04 +0000, Richard Huxton wrote: I'll repeat myself: Please CC the mailing list as well as replying to me, so that others can help too. Din Adrian wrote: On Thu, 03 Feb 2005 13:56:50 +, Richard Huxton wrote: Please CC the mailing list as well as replying to me, so that others can

Re: [SQL] pg_restore problem

and then base views on them there's probably no way for pg_dump to ever figure out the correct dependencies. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (sen

Re: [SQL] problem with backup and restore (probaly stupit newb thing)

m set up to store UTF-8 or was it SQL-ASCII or ISO? I suppose it might be something to do with a linux<=>windows transfer, but the place to start is running "psql -l" on the server. -- 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] postgres 8 data directory other then default?

a new version of PG. I'd recommend sticking with the RPMs for the moment. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PR

Re: [SQL] [GENERAL] How to view the list of tables?

f how to operate the psql application, or when in it try "\?" and "\h" to get help. We also now support the SQL-standard "information schema". You'll also find the manuals have this information - available with your installation and also online at http://www.

Re: [SQL] LOOP?

at you want in plpgsql, perhaps look at pltcl or plperl for this sort of thing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddres

Re: [SQL] Comments on subquery performance

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

Re: [SQL] query for records based on date

earching the mailing lists for "ago()" and "volatile" to learn about the issues with indexing. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] schemas and paths with the alter statement

est); ERROR: syntax error at or near "." at character 46 Are you sure it's not the constraint that is causing the problem (he says, counting 46 characters in). I think a table constraint is by definition in the same schema as its table. -- Richard Huxton Archonet Ltd -

Re: [SQL] Speeds using a transaction vrs not

MSSQL) in 2 hours using the .net, but 12 hours with the odbc and transaction. You *are* using transactions, you don't have a choice. Did you do the transfer of all 4GB in ONE transaction with the ODBC? Please describe the process in more detail. -- Richard Huxton Archone

Re: [SQL] Postgres performance

the questions above and I'm sure we'll be able to get your database server running smoothly. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomai

Re: [SQL] Postgres 8 - problem: invalid input syntax for integer

ne help me to create a CAST to re-use this feature? Well, you could create a function: CREATE FUNCTION empty_string_is_zero(text) RETURNS integer AS ' SELECT CASE WHEN $1='''' THEN 0 ELSE $1::integer END; ' LANGUAGE SQL; UPDATE my_table SET my

Re: [SQL] Software for database-visualisation

lable) Red-Hat db tools. Can't remember if there's a schema visualiser in there, but there might well be. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (s

Re: [SQL] Junk queries with variables?

PREPARE/EXECUTE PREPARE my_query(int4) AS INSERT INTO foo VALUES ($1); EXECUTE my_query(1); EXECUTE my_query(7); ... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Advanced SELECT

). Warning - I don't think you can guarantee the order of elements in the aggregated sectors. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Speeds using a transaction vrs not

pying data you'll find a huge improvement grouping rows together in batches of 100 - 10,000. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Speeds using a transaction vrs not

the system. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Junk queries with variables?

if psql vars, prepare or functions don't meet your needs, I'm not sure we've got anything that will. Have you got a specific example where these don't suit your needs? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Postgres 8 - problem: invalid input syntax for integer

the ''=>0 conversion - you could probably identify the old code from CVS. Obviously the null+trigger option is better than these three. Of these three though, number 2 is probably the cleanest solution. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] SQL error: function round(double precision, integer) does

marts)),2) as round((...)::numeric, 2) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message ca

Re: [SQL] Postgres performance

e is spent in Java/PHP etc. vs time in the database? Best of luck Mauro, realistic testing is not a simple process and you've got a lot of work ahead of you. Don't forget there's the performance list that can help with specific problems too. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

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

then "Smith" occurs more than "zgwasq". In some cases reading the whole table might be quicker than going to the index many times. The analyse scans (a percentage of) the whole table to see if these statistics have changed. This is different from a VACUUM which recovers space w

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

ough. Check the mailing-list archives for notes on these. -- 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

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

<    1   2   3   4   5   6   7   8   9   >