[SQL] SQL update function faililed in Webmin Interface
Hello All, I am a newbie to PostgreSQL. I am using postgreSQL 7.4.5 in HP-Unix 11.11 PA , and 11.23 PA. I have a problem with postgreSQL Webmin (Webmin Version 1.070) testing in update function. This problem exists only when i create a new data base through webmin interface, and insert some values,and modify those values afterwards. For E.g : I created database called "test" and created table name called "one" for that DB, which contains filed name "Name" with varchar(10) as a type and allows Null values. I inserted values for two rows as first and second, it can be viewed perfectly. But when i select second row (Which contains string "second" as value) to edit, and change it value as "second1" instead of "second", it throws me following error when i saved it : ~~~ "SQL update "one" set Name = 'Second1' where oid = 25349 failed : column "name" of relation "one" does not exist". ~~~ But when i created the database without using this Webmin interface ($ echo "create table one(Name varchar(10))"|psql test ), and then edit with webmin interface means it works well and get updated. Is there any problem with postgreSQL or with Webmin interface ?Any idea to solve this issue ? Thanks in Advance, Kathir ---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.778 / Virus Database: 525 - Release Date: 10/15/2004 Do you Yahoo!?vote.yahoo.com - Register online to vote today!
Re: [SQL] SQL update function faililed in Webmin Interface
Kathiravan Velusamy wrote: I created database called "test" and created table name called "one" for that DB, which contains filed name "Name" with varchar(10) as a type and allows Null values. The issue here is that you have created a column "Name" with quotes, which means it is case-sensitive. "SQL update "one" set Name = 'Second1' where oid = 25349 failed : column "name" of relation "one" does not exist". You are then trying to access it without quotes which means it gets folded to lower-case "name" (look carefully at the error message). If you quote the name when you create it, ALWAYS quote it. If you never quote names then you won't have any problems. It might be that the webmin module quoted the column-name for you without your knowledge. You'll need to consult your webmin documentation for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Reuse previously calculated column in sql query?
Hello, Is it possible to reuse a previously calculated column in a following column, like: SELECT foo.val1 AS col1, long_calculation(foo.val1) AS col2, COL2 * 0.75 AS col3 FROM foo; instead of writing: SELECT foo.val1 AS col1, long_calculation(foo.val1) AS col2, long_calculation(foo.val1) * 0.75 AS col3 FROM foo; Philippe ---(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] Reuse previously calculated column in sql query?
On Wed, 2004-10-20 at 08:50, Philippe Lang wrote: > Hello, > > Is it possible to reuse a previously calculated column in a following > column, like: SELECT col1 , col2 , col2 * 0.75 AS col3 FROM (SELECT foo.val1 AS col1 , long_calculation(foo.val1) AS col2 FROM foo) AS tab; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [GENERAL] SQL update function faililed in Webmin Interface
On Wed, 2004-10-20 at 01:03, Kathiravan Velusamy wrote:
> Hello All,
>I am a newbie to PostgreSQL. I am using postgreSQL 7.4.5 in
> HP-Unix 11.11 PA , and 11.23 PA.
> I have a problem with postgreSQL Webmin (Webmin Version
> 1.070) testing in update function.
> This problem exists only when i create a new data base
> through webmin interface,
> and insert some values,and modify those values afterwards.
>
> For E.g :
> I created database called "test" and created table name called
> "one" for that DB,
> which contains filed name "Name" with varchar(10) as a type and
> allows Null values.
>I inserted values for two rows as first and second, it can be
> viewed perfectly.
> But when i select second row (Which contains string "second" as value)
> to edit,
> and change it value as "second1" instead of "second", it throws me
> following error when i saved it :
>
> ~~~
>
> "SQL update "one" set Name = 'Second1' where oid = 25349 failed :
> column "name" of relation "one" does not exist".
> ~~~
>
> But when i created the database without using this Webmin interface
> ($ echo "create table one(Name varchar(10))"|psql test ),
> and then edit with webmin interface means it works well and get
> updated.
> Is there any problem with postgreSQL or with Webmin interface ?
> Any idea to solve this issue ?
>
It looks like the table is being defined with the column quoted, like
this:
create table one ("Name" text, moredefshere...)
but accessed without quotes, like above. Whether a database folds to
upper or lower case, the columns need to be accessed consistenly, either
all quoted or never quoted. An application that mixes quoting and not
quoting identifiers is going to have problems.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] the problem of createlang!
Fang Genjie wrote: I have installed postgresql (version 7.4.5) on the Redhat linux platform. Now I want to create a database with name BBMF and create pl/pgsql procedure language to the BBMF The problem is listed follow: [EMAIL PROTECTED] postgresql-7.4.5]$ psql -l List of databases Database | Owner | Encoding ---+--+--- BBMF | develop | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows) [EMAIL PROTECTED] postgresql-7.4.5]$ createlang plpgsql BBMF ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: /usr/local/pgsql/lib/plpgsql.so: undefined symbol: error_context_stack createlang: language installation failed [EMAIL PROTECTED] postgresql-7.4.5]$ file /usr/local/pgsql/lib/plpgsql.so /usr/local/pgsql/lib/plpgsql.so: ELF 32-bit LSB shared object, Intel 80386, version 1, not stripped [EMAIL PROTECTED] postgresql-7.4.5]$ Most probably is you got two version of postgresql in you system, one from RH, one install by yourself. check the path of your installation and the system one (usually /usr/bin, /usr/lib etc for RH). regards Laser ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] time of constraint checking
Hi, http://www.postgresql.org/docs/current/static/sql-createtable.html says, down at the explanation of DEFERRABLE, that constraints are checked after every command. Why does the following not work then: CREATE TABLE foo ( pos INT UNIQUE ); INSERT INTO foo (pos) VALUES (1); INSERT INTO foo (pos) VALUES (2); UPDATE foo SET pos = CASE WHEN pos = 2 THEN 1 ELSE 2 END; ERROR: duplicate key violates unique constraint "foo_pos_key" Also, are deferrable constraints other that FK constraints in the works? I also noticed, that the docs don't state whether INITIALLY IMMEDIATE or INITIALLY DEFERRED is the default. Thanks. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] time of constraint checking
Ð ÐÑÐ, 20.10.2004, Ð 17:58, Markus Bertheau ÐÐÑÐÑ: > I also noticed, that the docs don't state whether INITIALLY IMMEDIATE or > INITIALLY DEFERRED is the default. I just overlooked that, sorry, it is stated. -- Markus Bertheau <[EMAIL PROTECTED]> ---(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] time of constraint checking
On Wed, 20 Oct 2004, Markus Bertheau wrote: > http://www.postgresql.org/docs/current/static/sql-createtable.html says, > down at the explanation of DEFERRABLE, that constraints are checked > after every command. Why does the following not work then: > > CREATE TABLE foo ( > pos INT UNIQUE > ); > > INSERT INTO foo (pos) VALUES (1); > INSERT INTO foo (pos) VALUES (2); > > UPDATE foo SET pos = CASE WHEN pos = 2 THEN 1 ELSE 2 END; > ERROR: duplicate key violates unique constraint "foo_pos_key" Unique constraints are currently non-compliant in that it checks on each changed row rather than at the end of the statement. I thought this was mentioned somewhere, but it might make sense to mention it in the compatibility section. > Also, are deferrable constraints other that FK constraints in the works? I don't think anyone's looking at it right now. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Reuse previously calculated column in sql query?
Using the the sub-select is one way, but there is another way. (B (BIf the function can be declared as strict or immutable the you can call it (Bas many times as you like in a single transaction and it will only be (Bevaluated once. As far as I know this does work as advertised. (B (BCheck the SQL commands reference section of the manual for CREATE FUNCTION (B (BRegards (BIain (B- Original Message - (BFrom: "Rod Taylor" <[EMAIL PROTECTED]> (BTo: "Philippe Lang" <[EMAIL PROTECTED]> (BCc: <[EMAIL PROTECTED]> (BSent: Wednesday, October 20, 2004 10:05 PM (BSubject: Re: [SQL] Reuse previously calculated column in sql query? (B (B (B> On Wed, 2004-10-20 at 08:50, Philippe Lang wrote: (B>> Hello, (B>> (B>> Is it possible to reuse a previously calculated column in a following (B>> column, like: (B> (B> SELECT col1 (B> , col2 (B> , col2 * 0.75 AS col3 (B> FROM (SELECT foo.val1 AS col1 (B> , long_calculation(foo.val1) AS col2 (B> FROM foo) AS tab; (B> (B> (B> (B> ---(end of broadcast)--- (B> TIP 5: Have you checked our extensive FAQ? (B> (B> http://www.postgresql.org/docs/faqs/FAQ.html (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B (B http://archives.postgresql.org
