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
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
-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
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
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
'' || $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
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
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
*/ 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
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
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
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
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
(-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
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
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
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
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
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
>
>
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
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
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
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
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
24 matches
Mail list logo
