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.endpoint.com/
PGP
performance penalty).
- --
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201204251322
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAk+YM6IACgkQvJuQZxSWSshlSQCg8qVLbaYcEEJ9vOU43f2Irawv
2nwAnAxidDPeAohXOOTPa7mK0ORz2wc9
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'::interval;
- --
Greg Sabino Mullane g...@turnstep.com
End Point
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
-BEGIN PGP
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://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE
/^\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 200904052221
http://biglumber.com/x/web?pk
update_assoc_count_perl);
-- Put FK magic here
return 'Bulk load complete';
$_$;
- -- Usage:
SELECT start_bulkload_assoc_count();
- -- Lots of inserts and updates
SELECT end_bulkload_assoc_count();
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200904021644
http
) 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://biglumber.com/x/web?pk
;
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 SIGNATURE
= 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;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP
?/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-
iD8DBQFFy0xcvJuQZxSWSsgRA8vFAJsHMFhngWGCSSi8okO9j9H0++hajgCgrUz
($^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=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE
, 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-
iD8DBQFDJh99vJuQZxSWSsgRAiRFAJwKiGVsJhcbxIe0nQ3bnxJUZupucACgnUa/
57e9UDfVkv
AND parent_fk = 5;
SELECT * FROM relations;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200509110958
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFDJDkzvJuQZxSWSsgRAryTAJ90oT0LWl2ch6c7T7tPsj1/+JpRFwCeOLsV
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-
iEYEARECAAYFAkMVrhIACgkQvJuQZxSWSsg3ZwCgs5kG/5P
-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?pk
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-
iD8DBQFCwedPvJuQZxSWSsgRAsC0AKD2UrMtQJ6RRxbeZ8J2n68ewRt+EgCeN2UP
? :)
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=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE
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=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE
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 to be
unique\n};
}
- --
Greg Sabino Mullane [EMAIL PROTECTED
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
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200506121520
http
, * 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 Mullane [EMAIL PROTECTED]
PGP
Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200503232033
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCQhkMvJuQZxSWSsgRAjTaAJ9zmgSOBH/Nre/XMw+JajBni8YyDwCg1DyV
Cd5rIhi026KFoFZEFjMOYvI=
=V9ov
-END PGP SIGNATURE
(), 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 Mullane [EMAIL PROTECTED]
PGP
, start_time
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200502152309
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFCEseYvJuQZxSWSsgRAlipAJwKAyqAyLbo9hfpoWkz0SOlTY3feACfa+ng
DqNY4DAJ5TeeGQbI+smNilg=
=LRhP
-END PGP
) AS 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
');
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200411012240
-BEGIN PGP SIGNATURE-
iD8DBQFBhwIHvJuQZxSWSsgRArd1AJ9hKXD+cSaM2L3RUXQdabuRofNFjwCfaHT0
+bRPuYhuED0mnlp1FRtvQQw=
=tsqe
-END PGP SIGNATURE-
---(end of broadcast
. 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 200408311920
-BEGIN PGP SIGNATURE
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-
---(end of broadcast
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-
iD8DBQFBI0InvJuQZxSWSsgRApW7AKCpFN6TMQ3WjcJgZse5f+Ap6
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
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 200406162303
-BEGIN PGP SIGNATURE-
iD8DBQFA0QpdvJuQZxSWSsgRAk88AKDtGrs6
),
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-
iD8DBQFAikzOvJuQZxSWSsgRAgqbAKDC75SQd2aExYaniSJIzovOlVjvCACgyOAl
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-
iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh
',1,'f') ELSE 0 END 0)
OR
(CASE WHEN currval('cc')10 THEN 1 ELSE 0 END 0)
);
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200404101029
-BEGIN PGP SIGNATURE-
iD8DBQFAeAZ1vJuQZxSWSsgRAqYuAJ9HaYLotPYkyi1U76I9xnvi8AhLTQCfUyJq
+iVdbz5U7HKep89z0kp49U0=
=6+OH
-END
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 PROTECTED]
PGP Key: 0x14964AC8 200404092128
;
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;
' LANGUAGE plpgsql;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200402252206
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]
PGP Key: 0x14964AC8
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};
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 20040401
-BEGIN PGP SIGNATURE
39 matches
Mail list logo
