t;boyd" writes:
> $info =~ s/0//g; # the get_info adds extraneous '0' to the version number
It is not extraneous: it is required per the ODBC spec. For a simpler
number, try using this:
my $pgversion = $dbh->{private_dbdpg}{server_version};
- -
get nextval as x
set sequence to x+20
get nextval as y
repeat if y != x+20 (i.e. someone else grabbed a value)
- --
Greg Sabino Mullane [EMAIL PROTECTED
LOOP
tomorrow := tomorrow + \'24 hours\'::interval;
IF (1 = EXTRACT(\'day\' FROM tomorrow) ) THEN
RETURN match;
END IF;
IF ($2 = EXTRACT(\'dow\' FROM tomorrow)) THEN
match := tomorrow;
END IF;
END LOOP;
END;
' LA
(SELECT COUNT(*) FROM sub_a WHERE sub_a.main_id=main.id),
(SELECT COUNT(*) FROM sub_b WHERE sub_b.main_id=main.id)
FROM main ORDER BY id;
- --
Greg Sabino Mullane [EMAIL PROTECT
L
SELECT NULL AS q2, 0 AS s2, NULL AS cs, nextval('bb') AS v2
) AS dos
WHERE v1 = v2 AND q2 IS NOT NULL
AND (
(CASE WHEN q1 != q2 THEN setval('cc',1,'f') ELSE 0 END > 0)
OR
(CASE WHEN currval('cc')<10 THEN 1 ELSE 0 END >0)
);
- --
Greg Sab
ect may be the case),
you can do something like this:
SELECT DISTINCT TO_CHAR(add_date,'-MM-DD') AS bb FROM tt
ORDER BY bb DESC;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200404240716
-BEGIN PGP SIGNATURE-
iD8DBQFAikzOvJuQZxSWSsgRAgqbAKDC75SQd2aE
N UPDATE CASCADE;
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_service_fk"
FOREIGN KEY (service) REFERENCES service(id)
ON DELETE RESTRICT ON UPDATE CASCADE;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200404241255
-BEGIN PGP SIGNATURE-
iD8DBQFAipwPvJuQ
dm_indiv_mast SET WITHOUT OIDS;
Of course, vacuuming completely and often is recommended for a table this
size as well, especially when updating this many rows at once. I'd
recommend a VACUUM FULL immediately before running it.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8
WHERE cid=bob.cid LIMIT 1 OFFSET 7) AS tk8,
FROM (SELECT DISTINCT cid FROM ats) AS bob;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200408172335
-BEGIN PGP SIGNATURE-
iD8DBQFBIs7AvJuQZxSWSsgRAkglAJ9mNEmOYlLPynygMmelvzlqkYoHlwCeJqTb
g5gyh9LztONPCZj32aOEuGI=
=Yy7m
-END
ant,
I can be lazy and get away with it in this case. An order by
would not hurt, of course.
* As far as PG goes, this is not an ideal abbreviation! :)
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200408180745
-BEGIN PGP SIGNATURE-
iD8DBQFBI0InvJuQZxSWSsgRApW7AKCpFN6TMQ3W
re the
same plan is guaranteed to be used?
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200408192216
-BEGIN PGP SIGNATURE-
iD8DBQFBJV9xvJuQZxSWSsgRAp74AJ96mtrKC1J53y0TPqTPdq2Xost0fACg4DnJ
7P+dgpHWBazGNE9+SR7uxLY=
=MZuM
-END PGP SIGNATURE-
-
greSQL highlighting. Unfortunately, the config
> is XML so it's not transferrable to Emacs ...
Send me (or the list, of it's short) a copy, I'll see if I can
do anything with it.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 20
tgres:
my @dbs = DBI->data_sources('Pg');
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200411012240
-BEGIN PGP SIGNATURE-
iD8DBQFBhwIHvJuQZxSWSsgRArd1AJ9hKXD+cSaM2L3RUXQdabuRofNFjwCfaHT0
+bRPuYhuED0mnlp1FRtvQQw=
=tsqe
-END PGP SIGNATURE
tablespace
FROM pg_catalog.pg_database
JOIN pg_catalog.pg_tablespace t ON (dattablespace=t.oid)
ORDER BY 1;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200411022120
-BEGIN PGP SIGNATURE-
iD8DBQFBiFTSvJuQZxSWSsgRApflAJ0RiVndbc6u//cXX/S7uM8K91lWbgCfYVbC
rt_time IS NULL THEN 0 ELSE 1 END, start_time
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200502152309
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCEseYvJuQZxSWSsgRAlipAJwKAyqAyLbo9hfpoWkz0SOlTY3feACfa+ng
DqNY4DAJ5T
ct equivalent to rank(), but there are certainly
other ways to get the results. The above query can be written in
PostgreSQL as:
SELECT employee_id, last_name, salary
FROM employees
WHERE salary =
(SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 2 LIMIT 1);
- --
Greg Sabino Mul
27;),'/') FROM mytable;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503232033
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCQhkMvJuQZxSWSsgRAjTaAJ9zmgSOBH/Nre/XMw+JajBni8YyDwCg1DyV
Cd5rIhi026KFoFZEFjMOY
x27;)::int AS newid, * FROM
fruit ORDER BY lower(description);
ALTER TABLE newfruit DROP COLUMN id;
ALTER TABLE newfruit RENAME COLUMN newid TO id;
DROP TABLE fruit;
ALTER TABLE newfruit RENAME TO fruit;
DROP SEQUENCE fruit_seq;
COMMIT;
SELECT * FROM fruit ORDER BY id ASC;
- --
Greg Sabino Mulla
ll create implicit index
"unitest_column_a_is_not_unique" for table "unitest"
greg=# insert into unitest (a) values (1);
INSERT 0 1
greg=# insert into unitest (a) values (1);
ERROR: duplicate key violates unique constraint
"unitest_column_a_is_not_unique"
- --
Gre
st: just come up with a standard naming scheme, such as:
"tablename|colname|is_not_unique"
which should be human and machine parseable (perl example):
if ($error =~ m#^(.+)\|(.+)\|is_not_unique$#o) {
die qq{Whoops : looks like column "$2" of table "$1" needs
+--
99120 | 338
92110 | 120
92190 | 41
If you do need the other rows, you will have to specify a way of ordering
the rows within a code group.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506161458
http://biglumber.com/x/web?pk=2529DF6AB8F79407E944
good practice for the 8.1.0 jump, right? :)
Overall, I was able to get the query to go about a third faster
than when I started. Hope this helps.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506242328
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9
, but would this do what you want?:
SELECT fkey, uid, seq2, min(seq1)
FROM my_table
WHERE seq2 > 2
GROUP BY fkey, uid, seq2
ORDER BY 1,2,3;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506250019
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
--
E seq2 > 2
GROUP BY fkey, seq2
ORDER BY 1,2,3;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506250237
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCvPwJvJuQZxSWSsgRAtcHAKDzl67Va8ABP4qyNpvFtWDpjmT/iwCg3D5J
k
the high number of combinations and large
potential for change.
> table ordered_products: order_id, product_id, quantity
I'm not sure where you are getting "quantity" from: as near as I
can tell, this will always be a quantity of 1: one person ordering
one item.
work, you will have to be more specific and send us
the exact query.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506282010
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCwedPvJuQZxSWSsgRAsC0AKD2UrMtQJ6RRxbeZ8J2n6
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> fair enough. but a simple order by id would never work.
That was me, sorry, I must have been asleep when I wrote it. :)
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506300636
http://biglumber.com/x/web
easiest to
pg_dump the whole database and then drop/rename columns in the new
database.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200508310915
https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEARECAAYFAkMVrhIACgkQvJuQ
SET parent_fk = 4 WHERE child_fk = 3 AND parent_fk = 2;
SELECT 'Add non-mother/father' AS "Test should pass";
INSERT INTO relations VALUES (3,5);
SELECT 'Change non-mother/father to mother' AS "Test should fail";
UPDATE relations SET parent_fk = 2 WHER
add another column. But generally, this
should be the exception and not the rule.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200509122031
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFDJh99vJuQZxSWSsgRAiRFAJwKiGVsJhcbxI
$filename" does not exist\n});
return localtime($^T - (60*60*24* -M _));
$$;
SELECT filemodtime('/var/log/messages');
SELECT filemodtime('/dark/matter');
--
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 20051457
http://biglumber.com/x/web?pk=252
possibly use advisory locks if you really need to
know what another session is listening to.
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201106011822
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-
d be 'less' than 0102 because
it's January, etc. Assuming you change it to MMDD, you could run
a simple query like this:
SELECT tablename
FROM pg_tables
WHERE tablename ~ '^tmp_staging'
AND substring(tablename from '\d+')::date < now() - '10 days&#x
of Postgres and the latter does carry a
potential performance penalty).
- --
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201204251322
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAk+YM6IACgkQv
st create separate indexes and you will be fine, especially given
the very small size of the table. If you find your queries going slow, you
could start investigating compound indexes (or in this case, partial
indexes).
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.end
/greg/index.php?/archives/45-Making-a-copy-of-a-unique-row.html
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200702081114
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFFy0xcvJuQZxSWSsgRA8vFAJsHMFhngWGCSSi8okO9
:
SELECT * FROM yourtable
ORDER BY
CASE
WHEN X::date = now()::date THEN 1
WHEN X::date < now()::date THEN 2
WHEN X IS NULL THEN 3
ELSE4
END,
CASE
WHEN X::date-now()::date < 0 THEN now()-X
ELSE X-now()
END;
- --
G
= 456;
INSERT INTO foo SELECT * FROM tempfoo;
COMMIT;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200803200737
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIG
COUNT(hit_id) AS total_hits
FROM (
SELECT partner_id, hit_id, NULL AS view_id FROM hits
UNION ALL
SELECT partner_id, NULL, view_id FROM views
) AS foo
GROUP BY 1;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200806251019
http://biglum
ENABLE TRIGGER update_assoc_count_insert");
## x3 etc.
spi_exec_query("ALTER TABLE assoc DISABLE TRIGGER update_assoc_count_perl");
-- Put FK magic here
return 'Bulk load complete';
$_$;
- -- Usage:
SELECT start_bulkload_assoc_count();
- -- Lots of inserts a
ECT ..." | perl -pe 's/^\n// if $.<2'
This strips a newline from the first line only of the output, and
only if the line consists of nothing else. Highly recommended
for cron.
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 20090
ng the
temporary hash we build up - I think the version I emailed neglected to do that.
Wouldn't want those numbers to stick around in the session.
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200904061028
http://big
42 matches
Mail list logo
