[GENERAL] INSERT before UPDATE?
Hello, I would like to ask you for an advice. There are two tables in my PostgreSQL database - main table with datas and second with translations for all languages of these records. When I try to UPDATE a record in the language table and this record doesn't exists there I need to INSERT into the language table (from default language version) and execute UPDATE statement again. Could you tell me if is this possible and if so - how to do it? Thank you in advance. -- Jiri Nemec http://www.meneashop.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Plpgsql function and variable substitute
Hello, I have searched the Internet for an answer but nothing works for me. There is a plpgsql function which is used in a trigger. I need to substitute a value "7" for "lifetime" (integer) SELECTed in a previous query. DECLARE lifetime integer; BEGIN SELECT foo INTO lifetime FROM tblname; UPDATE SET expiretime = expiretime + interval '7 days' WHERE . RETURN NEW; END; This doesn't work for me: SET expiretime = expiretime + interval 'lifetime days' SET expiretime = expiretime + interval || lifetime || 'days' Thank you for your help or for a some useful link. -- Jiri Nemec http://www.meneashop.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Import data from 8.2.3 into 8.0.8
Hello, I need to import PostgreSQL DB from 8.2.3 server 8.0.8 server. Is there some "compatibility mode"? When I try to import this dump PostgreSQL 8.0.8 reports errors - probably due version incompatibility. Thank you for your advices. -- Jiri Nemec http://www.menea.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Remove diacritical marks in SQL
Hello,
I would like to remove diacritical marks from a string in a SQL query.
I tried to convert a UTF8 string to ASCII but it doesn't work for me.
SELECT convert('ěščřžýáíé','UTF8','SQL_ASCII')
array(1) {
["convert"]=>
string(18) "ěščřžýáíé"
}
Thanks for any advice,
J.N.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Remove duplicate rows
Hello, I need to remove duplicates rows from a subquery but order these results by a column what is not selected. There are logically two solutions but no works. SELECT DISTINCT sub.foo FROM (SELECT ...) AS sub ORDER BY sub.bar ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list SELECT sub.foo FROM (SELECT ...) AS sub GROUP BY sub.foo ORDER BY sub.bar ERROR: column "sub.bar" must appear in the GROUP BY clause or be used in an aggregate function Does anybody know how to remove duplicate rows from a subquery and order these results by a column what is not selected but exists in a subquery? Thanks for any advice, J.N. ---(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
[GENERAL] Remove diacritic
Good morning,
I would like to remove diacritic from string like 'žluťoučký kůň'
(UTF-8) and transform it into 'zlutoucky kun'. I have used function
convert(); and SQL_ASCII as destination encoding.
Converted string still contains diacritic:
SELECT convert('žluťoučký kůň','UTF8','SQL_ASCII')
array(1) {
["convert"]=>
string(19) "žluťoučký kůň"
}
Thanks for your help.
--
Jiri Nemec
http://www.menea.cz/
---(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: [GENERAL] PostgreSQL select
16. července 2005, 16:46:59, napsal jste: > In other words: what you probably meant here is > FROM (c_custom_fields AS fieldx CROSS JOIN > j_product_groups_fields AS join_table) > LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = > fieldx.field_id Thank you, your query runs but returns weird records, returns correct records from "c_custom_fields" table but incorrect number of records from JOINed "c_custom_fields_options" table. There are 3 table such structures: c_custom_fields - field_id, field_name 1 RAM 2 HDD c_custom_fields_options - option_id, field_id, option_value 1 1128 2 1512 3 11024 4 280 5 2120 6 2160 7 2200 j_product_groups_fields - group_id, field_id 1 1 1 2 I need to select records from c_custom_fields table which belong to group_id = 1 (j_product_groups_fields) and count number of options which belong to selected field: field_id field_name COUNT(c_custom_fields_options.option_id) 1 RAM 3 2 HDD 4 -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL select
Hello, There's a problem with PostgreSQL 8.0.3 SELECT. All tables exist, all columns exist, I have no idea where's the problem :/ PostgreSQL reports this error: ERROR: relation "fieldx" does not exist... SELECT fieldx.field_id, COUNT(optionx.option_id) AS field_options FROM c_custom_fields AS fieldx, j_product_groups_fields AS join_table LEFT JOIN c_custom_fields_options AS optionx ON optionx.field_id = fieldx.field_id WHERE fieldx.field_id = join_table.field_id AND join_table.group_id = 10 GROUP BY fieldx.field_id ORDER BY fieldx.field_id Any clues? -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] PostgreSQL log analyzer
Hello, I would like to analyze PostgreSQL (8.x) system log for slow queries etc. Does anybody know any log analyzer, which works under Windows XP? Thanks for replies. -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Duplicate counting
Hello all, I wrote a function which counts price of product from retail price and discount. It works. But I need to count price with tax of same product. The best way is to use counted price and add only a tax. I would like to do by this way: SELECT count_price(retail, discount) AS price, count_price_tax(price, tax) FROM foo. But PostgreSQL reports that "price" column doesn't exist. It doesn't exist, but is counted by first calling "count_price()" function. Is there some way how I shouldn't count these prices twice and use just counted price? -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace ---(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
[GENERAL] Insufficient system resources for PostgreSQL 7.4.x?
Hello, I changed DB server assigned for developing PHP applications, from MySQL to PostgreSQL. - Celeron 566Mhz - 128MB DDR 266MHz - HDD 20GB Seagate 7200rpm - Apache 2.0.52 - PHP 4.3.10 - PostgreSQL 7.4.6 But a problem occured. PHP scripts end due fatal errors "Fatal error: Maximum execution time of 30 seconds exceeded...". When I had used MySQL there were no problems with similat queries. These queries work with 20 records tables, so I think, problem is somewhere else. Is necessary to upgrade HW or I can try to downgrade Apache, PHP and PostgreSQL but I don't know if is solution... Thank you very much for your replies. -- Jiri Nemec ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] DB modeler
Hello, I am looking for some modeler to create a database structure - tables, relations etc. I use DBDesigner, but it is primarily designated for MySQL. What tool do you use for PostgreSQL? - sheets and crayons? -- Jiří Němec, ICQ: 114651500 www.menea.cz - www stránky a aplikace ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
