[SQL] About i8n
Hi the list ! I principally use postgres with php, but I think that for my question, it remains the same... Is there a way to have all error messages returned by the back-end translated into french or another language ? If so, how to set it up ? Thanks by advance. -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] About table column names.
Hi the list ! As far as I know, column names for a table can't contain any space, tabs, and other sort of "exotic" characters. Is there a way to add a description of a table column anywhere in postgres tables, or does it have to be handled manually by creating a custum table handling this kind of datas ? Thanks by advance for any suggestion. -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] About table column names.
David BOURIAUD wrote:
>
> Hi the list !
> As far as I know, column names for a table can't contain any space,
> tabs, and other sort of "exotic" characters.
In fact, I know you can have at least spaces in your column names, like this:
mark=> create table t ("column one" text);
CREATE
Just put quotes around them.
> Is there a way to add a
> description of a table column anywhere in postgres tables, or does it
> have to be handled manually by creating a custum table handling this
> kind of datas ? Thanks by advance for any suggestion.
I'm interested in this, too. It seems more useful than having them in a
SQL file...which can sometimes get out of synch with the database. :)
-mark
http://mark.stosberg.com/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] dropping constraints
Hi, Is there to drop a constraint when you add it separately with an alter statement. alter table failuretypecategory1 add constraint fk_failuretypecategory1 FOREIGN KEY (failurecategory1id) REFERENCES failurecategory1 (id) ON UPDATE CASCADE ON DELETE CASCADE; I tried drop trigger on failurecategory1 and drop trigger on failuretypecategory1 and also drop trigger on failurecategory1 and drop trigger on failuretypecategory1 But it kept saying ERROR: DropTrigger: there is no trigger / on relation failuretypecategory1 What am I doing wrong. I tried dropping the table also and when I tried to delete from a table that it referenced above it gave the error that the table I just drop doesn't exist. Obviously because I just removed it but the constraint it still there.. Thanks -- Linh Luong ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Re: About i8n
"J.H.M. Dassen (Ray)" wrote: > > David BOURIAUD <[EMAIL PROTECTED]> wrote: > > Is there a way to have all error messages returned by the back-end > > translated into french or another language ? > > http://www.de.postgresql.org/devel-corner/docs/postgres/nls.html Thanks, but it doesn't help in any way ! It tells how to have a client program have nls support, but not postgres ! I'm running postgreSQL v7.1.2, and neither found any *.po, *.mo or *.mk files in the tree directory of either the sources or the bianries. Furthermore, when I type gmake init-op, gmake complains that there is no rule to make init-po. Well, well, well, not so good. By the way, I can speak english, so it's not a problem for me, but I think of my users ! Thanks again, for I learned something consulting theses pages anyway. -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] dropping constraints
You probably need to make sure to double quote the tgname in your drops. On Thu, 7 Jun 2001, Linh Luong wrote: > Hi, > > Is there to drop a constraint when you add it separately with an alter > statement. > > alter table failuretypecategory1 add constraint fk_failuretypecategory1 > FOREIGN KEY (failurecategory1id) > REFERENCES failurecategory1 (id) ON UPDATE CASCADE ON DELETE CASCADE; > > I tried > drop trigger on failurecategory1 > and > drop trigger on failuretypecategory1 > > and also > > drop trigger on failurecategory1 > and > drop trigger on failuretypecategory1 > > > But it kept saying > ERROR: DropTrigger: there is no trigger / on > relation failuretypecategory1 > > What am I doing wrong. > > I tried dropping the table also and when I tried to delete from a table > that it referenced above it gave the error that the table I just drop > doesn't exist. Obviously because I just removed it but the constraint > it still there.. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Getting row with id=max(id)
I'd like to retrieve a row of a table that has the maximum ID. For example, with: id | s +--- 1 | alpha 2 | beta 3 | gamma 4 | delta I'd like to get the row with ID=4. I've tried: SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable); The subquery can take a /really/ long time on a table that is large. The query: SELECT * FROM mytable ORDER BY id DESC LIMIT 1; doesn't seem to help very much. What query is the fastest at getting this row? A related question is: is there a way to time a query in psql, like the client of MySQL does? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Are SQL commands "atomic" ?
I'm using 7.1.1 right now, and have the following table:
id | s
+---
1 | alpha
2 | beta
3 | gamma
4 | delta
(4 rows)
I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip"
them). Since id is the PK, it must remain unique and so I can't just set
the two lines using two UPDATEs.
My solution is:
UPDATE t1 SET id=id#1 WHERE id=2 OR id=3; -- # is the XOR operator
where 2#1=3 and 3#1=2. One statement will change both values as I want. But
when I run the statement, the server replies with:
ERROR: Cannot insert a duplicate key into unique index t1_pkey
If the statement is "atomic", then if the statement succeeds, the IDs will
be unique and the error is incorrect. Does this imply that SQL statements
are not actually atomic?
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] UPDATE with concatenate
Hy, I try to update a table:col with take the valueof this col and concatenate it with $val. I don't want to select all value of table:col and addition $val at each one I try : UPDATE table SET col .= '$val' But it doesn't work, any suggestion ? pat ---(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] Are SQL commands "atomic" ?
On Thu, 7 Jun 2001, Gerald Gutierrez wrote:
>
> I'm using 7.1.1 right now, and have the following table:
>
> id | s
> +---
>1 | alpha
>2 | beta
>3 | gamma
>4 | delta
> (4 rows)
>
> I'd like to switch the id of "beta" to 3 and the id of "gamma" to 2 ("flip"
> them). Since id is the PK, it must remain unique and so I can't just set
> the two lines using two UPDATEs.
>
> My solution is:
>
> UPDATE t1 SET id=id#1 WHERE id=2 OR id=3; -- # is the XOR operator
>
> where 2#1=3 and 3#1=2. One statement will change both values as I want. But
> when I run the statement, the server replies with:
>
> ERROR: Cannot insert a duplicate key into unique index t1_pkey
>
> If the statement is "atomic", then if the statement succeeds, the IDs will
> be unique and the error is incorrect. Does this imply that SQL statements
> are not actually atomic?
Not exactly. It's a bug in the implementation of the unique constraint.
The unique constraint is being checked per-row rather than per-statement.
---(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] UPDATE with concatenate
Maybe this? update table set col = col || '$val'; On Thu, 7 Jun 2001, Laurent Patureau wrote: > Hy, > > I try to update a table:col with take the valueof this col and concatenate > it with $val. > I don't want to select all value of table:col and addition $val at each one > > I try : > > UPDATE table SET col .= '$val' > > But it doesn't work, any suggestion ? ---(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] Are SQL commands "atomic" ?
At 10:39 AM 6/7/2001 -0700, Stephan Szabo wrote: >Not exactly. It's a bug in the implementation of the unique constraint. >The unique constraint is being checked per-row rather than per-statement. Is this bug on a todo list, or should I submit a bug report? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Getting row with id=max(id)
At 07:31 PM 6/7/2001 +0200, Peter Eisentraut wrote: > > SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable); > > SELECT * FROM mytable ORDER BY id DESC LIMIT 1; >The second is generally thought to be faster, at least if you use the >latest version of PostgreSQL. This is quite amusing actually. To get the maximum of a column, the (much more) convoluted way is much faster than the intuitive way: => explain select id from mytable order by seed desc limit 1; NOTICE: QUERY PLAN: Index Scan Backward using mytable _pkey on mytable (cost=0.00..794189.09 rows=5358342 width=4) EXPLAIN => explain select max(id) from mytable ; NOTICE: QUERY PLAN: Aggregate (cost=103152.27..103152.27 rows=1 width=4) -> Seq Scan on mytable (cost=0.00..89756.42 rows=5358342 width=4) EXPLAIN Perhaps if the server internally rewrote the second query into the first, it would make the intuitive version much faster. The same can be done for min() and perhaps other functions as well. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Getting row with id=max(id)
>=> explain select id from mytable order by seed desc limit 1; Oops, a cut & paste mistake. That should be: explain select id from mytable order by id desc limit 1; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Forein Key Problem
Is This s bug: create table A ( key varchar(20) not null primary key ); create table B ( id serial not null primary key, col1 varchar(20) not null, col2 varchar(20) not null ); alter table B create constraint fk_col1 foreign key ( col1 ) references A ( key ) on delete cascade on update cascade; alter table B create constraint fk_col2 foreign key ( col2 ) references A ( key ) on delete cascade on update cascade; SQL Creation and operation works fine. In the case that one row in table B where col1 = col2, if I update A.key, I'll get a referential integrity violation?? ie: ERROR: fk_col1 referential integrity violation - key referenced from B not found in A. Is there a way to make this type of constraint work with the update? If not, is there a way to create a constraint so that col1 != col2 is inforced? Thanks in advance.. Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] plperl
Time Co-Ordinate Tue, 05 Jun 2001 19:45:55 -0400, The Organism labeled Tom Lane said: > clayton cottingham <[EMAIL PROTECTED]> writes: > > how does one execute an sql statement from inside a plperl function? > > At the moment, one doesn't. > > This is one of a number of features that have to be finished before > plperl can be classed as more than a proof-of-concept exercise. > However, Mark Hollomon seems to have lost interest or time to work on > it, and no one else has picked up the ball. Any volunteers out there? > > regards, tom lane > > just looked at the code i wish i could say yes but it looks way beyond my level heres hoping someone pick up the torch ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
