Re: [SQL] timestamp (MS SQLServer's rowversion) functionality

On 8/11/06, Aaron Bono <[EMAIL PROTECTED]> wrote: I put a create_dt and modify_dt column on every table and set the default to now(). Then I use this trigger: CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF opaque AS ' BEGIN -- if a trigger insert or update oper

[SQL] The Right Way to manage schemas in SCM systems

I've been trying to figure out a good way to manage schema change control for a while now. Since I have a development background, I really want to find some way to check the schema into a SCM system like CVS (for example). Just using a pg_dump doesn't work very well becase there's no guarantee of c

Re: [SQL] timestamp (MS SQLServer's rowversion) functionality

Tomski wrote: > Hello! > As many of you know, SQL Server (2000) has peculiar data type "timestamp" > which is not SQL standard timestamp. In fact it is "rowversion" type. It > makes tha field to be updated with current timestamp when row is updated or > inserted. > Is there any similiar functionali

Re: [SQL] alter column type from boolean to char with default

Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > On Wed, 2006-08-02 at 09:19 -0400, Tom Lane wrote: > >> Hmm ... the way I would have expected to work is > >> > >> alter table posts > >> alter column deleted drop default, > >> alter column deleted type char(1) > >> using (case when dele

Re: [SQL] SELECT all fields except two

This does not exist in SQL. However you could achieve similar functionality by doing a suitable query against the system info tables to find out what columns are available and then building your query appropriately. For an example, try psql -E -c '\d mytable' Drew Pit M. wrote: > select *,!Blob

Re: [SQL] Help with privilages please

7.4.1 is quite old and has a number of serious known bugs. I'd suggest you either upgrade to 8.1.4 (current) or, if you can't do that, at least upgrade to 7.4.13 (latest 7.4) immediately. Hilary Forbes wrote: > Tom > > Thank you - I think that the underlying problem is that I was trying out > >

Re: [SQL] Rows with exclusive lock

Martin Marques wrote: > On Sun, 23 Jul 2006, Alvaro Herrera wrote: > > > Martin Marques escribió: > >> > >> After the SELECT FOR UPDATE other transactions can still see the locked > >> rows. I want a read/write lock, so no one can access does rows. > > > > SELECT FOR UPDATE acquires an exclusive l

Re: [SQL] reusing AS

Gregory Stewart wrote: > I am trying to do something like this: > > SELECT SUM(sales_today) AS sales_today_total, SUM(sales_lastweek) AS > sales_lastweek_total > CASE WHEN sales_today_total = '0' THEN '0'::int4 WHEN sales_lastweek_total = > '0' THEN '0'::int4 ELSE ((100/sales_today_total*sales_las

Re: [SQL] SQL generator

1) If you don't know how to write the SQL for this, then you might want to ask yourself if you have sufficient expertise to write a tool which generates such queries. 2) I have seen many attempts at query generators. I have yet to see a design which achieves a good balance between simplicity and f

[SQL] using constraint based paritioning to fix EAV type schemas? (view inheritance and constraints)

I have a client with the following EAV inspired schema. CREATE TABLE many_tables ( table_id text primary key,-- defines which virtual table is encoded attribute1 text, attribute2 text, attribute3 text, attribute4 text, ... ); I'd like to use a mix of constraint bas

Re: [SQL] Sum() rows

(-10); INSERT 60813 1 /* [EMAIL PROTECTED]:5432/ahammond =# */ SELECT * FROM tb1; id | value - +--- 1 |20 2 | -10 (2 rows) /* [EMAIL PROTECTED]:5432/ahammond =# */ SELECT * FROM tb1_real; id | value | subtot - +---+ 1 |20 | 20 2 | -10 | 10 (2 rows

Re: [SQL] Duplicated records

s; ALTER TABLE lanctos ALTER id SET NOT NULL; CREATE UNIQUE INDEX lanctos_id_idx ON lanctos (id); ALTER TABLE lanctos ADD CONSTRAINT lanctos_id_pkey PRIMARY KEY (id); COMMIT; As always, don't forget to ANALYZE the new table. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Ad

Re: [SQL] Transaction in plpgslq

e transaction ends. I included links to relevant documentation in my original post. If you read up on locking, you'll find the answer to your problem. Please post any further questions you have after reading the documentation. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database A

Re: [SQL] Transaction in plpgslq

hen you might want to take a look at the concurrency control section of the manual. http://www.postgresql.org/docs/8.0/static/mvcc.html - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A Rafa

Re: [SQL] Consecutive row count query

You could hack it using a custom aggregate. NB: you'll want to reset the categorizer_seq every now and then. And this isn't safe for concurrent queries. You could make it safe for concurrent queries by using a complex type for STYPE, but I didn't bother. I also haven't debugged this, but I thi

[SQL] date - date returns integer?

*/ SELECT ('2005-03-04'::date - '2005-01-01'::date)::interval; ERROR: cannot cast type integer to interval - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP S

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

gres log files (or syslog) on a seperate filesystem. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A Joel Fradkin wrote: | The postgres is running on Linux Fedora core 3 (production will be redhat o

Re: [SQL] Implementing queue semantics (novice)

nal | 'processed' timestamp-column? | | | | Thanks for helping me do the right. | | ---(end of broadcast)--- | TIP 4: Don't 'kill -9' the postmaster - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Admin

Re: [SQL] NULLS and string concatenation

'' || $2, $2);' LANGUAGE sql; CREATE AGGREGATE comma_concat ( ~BASETYPE=text, ~SFUNC=comma_concat, ~STYPE=text ); Which is handy for 1:n reports like SELECT grade, comma_concat($name) AS members FROM test_results GROUP BY grade; - -- Andrew Hammond416-673-4138[EMAIL

Re: [SQL] JOIN not being calculated correctly

ated. I've taken it as far as I can and don't really know where to | move from here. - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A -BEGIN PGP SIGNATURE- Version: GnuPG

Re: [SQL] CREATE TABLE AS SELECT....

TER TABLE a SET srl NOT NULL; ALTER TABLE a SET login_name NOT NULL; ALTER TABLE a SET password NOT NULL; I'll just assume that you're using hased passwords, and not storing them in cleartext... - -- Andrew Hammond416-673-4138[EMAIL PROTECTED] Database Admi

Re: [SQL] view running query

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 There are two ways to do it. The server-side approach is to increase logging levels in the config file and then "pg_ctl reload". See http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING for the stuff involved. The ot

Re: [SQL] is it easy to change the create sequence algorithm?

Well, the quickest solution I can think of off hand is to not use SERIAL. Instead, do it manually, like this: DROP SEQUENCE my_seq; CREATE SEQUENCE my_seq; DROP TABLE my_table; CREATE TABLE my_table ( my_table_id INTEGER DEFAULT nextval('my_seq') PRIMARY KEY, ... ); Kevin Branne

[SQL] simple recursive function in plpgsql fails

ians=# SELECT version(); version --- PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4 My goal is to find the last occurance of a pattern in a string. As a helper function, I wrote this: DROP F