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
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
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
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
-
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
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
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
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
= 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
:
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
/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
$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
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
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
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
-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
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
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.
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
, 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
--
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
+--
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
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
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
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
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
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
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
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
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
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
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-
-
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
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
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
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
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
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
(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
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
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
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};
- -
42 matches
Mail list logo
