[SQL] Transaction-specific global variable

hstore greatly simplifies creating triggers for logging table changes, which is great. However, when creating a log record, I would like to include information about the party who made this change. We generally do not allow direct database access for application code, so the PostgreSQL user does

[SQL] UNIQUE on everything except primary key

I have a table with 11 columns and want to eliminate duplication on INSERTs. What's the best method? The obvious solution would be to create a UNIQUE index on everything except the primary key - 'id' - but that's a 10-column index and doesn't sound very efficient. Am I missing something more ob

Re: [SQL] UNIQUE on everything except primary key

Did you check check constraints? http://www.postgresql.org/docs/8.4/static/ddl-constraints.html Best Andreas -Ursprüngliche Nachricht- Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im Auftrag von gvim Gesendet: Donnerstag, 3. Februar 2011 16:44 An: pgsql sql

Re: [SQL] Transaction-specific global variable

Florian Weimer writes: > hstore greatly simplifies creating triggers for logging table changes, > which is great. However, when creating a log record, I would like to > include information about the party who made this change. We > generally do not allow direct database access for application co

[SQL] using of select (myfunction()).* is so slow

Hi all, im using a function of my own in a subquery, and when wonderig about the slowliness of this one, y relalize that: test=# SELECT (_xxfunction(854,'711H',11,false)).* ; (4 filas) --Result DELETED Duración: 1069,465 ms glyms=# SELECT * from _xxfunction(854,'711H',11,false) ; (4 filas) Durac

Re: [SQL] using of select (myfunction()).* is so slow

Hello If you use a record expansion over function's result, then function is called once for record's field. so don't do it on slow functions. Regards Pavel 2011/2/3 Gerardo Herzig : > Hi all, im using a function of my own in a subquery, and when wonderig > about the slowliness of this one, y

Re: [SQL] UNIQUE on everything except primary key

Do all the 10 columns make a natural key? 03.02.2011, 18:44, "gvim" : > I have a table with 11 columns and want to eliminate duplication on INSERTs. > What's the best method? The obvious solution would be to create a UNIQUE > index on everything except the primary key - 'id' - but that's a 10-co

[SQL] How to checking the existance of constraints for a table?

Hello, I have a sample table describe as follows, anyone knows how to checking the existence of a constraint? oviddb=# \d myTable Column | Type | Modifiers -+--+--- orderid | smallint | not null modelid | smallint | not null Indexes: "mytable_orderid_key" UN

[SQL] How to workaround DROP CONSTRAINT [ IF EXISTS ] in Postgres version 8.1?

Hello, I found that DROP CONSTRAINT [ IF EXISTS ] is available in Postgres 9.1 http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html But it is not avaiable in Postgres 8.1 http://www.postgresql.org/docs/8.1/inter

[SQL] out of shared memory - find temporary tables

Hi there, I got "out of shared memory" error. Searching on postgresql forums, I found this it occurs probably because of intensive use of temporary tables in one transaction. I'm locking in pg_locks table, and I found some rows with the following modes: "ShareLock", "AccessExclusiveLock", "Exclu

[SQL] how control update rows

Hi there, I'd like to control the rows which are updated. I found useful the option RETURNING in UPDATE syntaxt. Can I process the rows wich are returning there ? I think to something like that: SELECT * FROM ( UPDATE "T" SET "C" = 1 WHERE "ID" > 100 RETURNING * ) x TIA, S

Re: [SQL] how control update rows

Hello 2011/2/3 Sabin Coanda : > Hi there, > > I'd like to control the rows which are updated. I found useful the option > RETURNING in UPDATE syntaxt. Can I process the rows wich are returning there > ? > > I think to something like that: > > SELECT * > FROM ( >    UPDATE "T" SET >        "C" = 1