There is DDL script in PostgreSQL that creates tables.
For example, if first table exists, How to stop SQL script execution for PostgreSQL (within script)?
Add a comment
|
3 Answers
If you want to abort a script based on a condition you can do that using a DO block that raises an error:
do
$$
declare
l_count integer;
begin
select count(*)
into l_count
from information_schema.tables
where table_name = 'foobar'
and table_schema = 'public';
if (l_count > 0) then
raise exception 'Table foobar already exists!';
end if;
end;
$$
This requires that your SQL client will abort a script if an error occurs.
Another option is to change your script such that it doesn't do anything if the table already exists by using create table if not exists .....
But that depends on what exactly you are trying to achieve.
1 Comment
Paul Verest
The idea was: there is no need to run whole script if the first table already exists. That is doing only simplest and quickest db initialization.
You can do something like this:
SELECT 'Step 1' as step;
DO $$
BEGIN
assert (SELECT 'B'::text) = 'A'::text, 'Expected A';
END;
$$;
SELECT 'Step 2' as step;
Returns:
ERROR: Expected A
CONTEXT: PL/pgSQL function inline_code_block line 3 at ASSERT
SQL state: P0004
You can use any type to compare assert with, not just text, apparently.
Comments
PostgreSQL database run each query in apparted process. You will need find out the process number. I use pg_activity, but you can run:
SELECT datname,procpid,current_query FROM pg_stat_activity;
once you found the PID you can kill it in shell:
kill $PID #or
kill -9 $PID
4 Comments
Paul Verest
That is great piece of information, but that can't be inside SQL script, can it?
wildplasser
And please don't advertise
kill -KILL
DMin
Do not use
kill -9 on queries it is strongly advised against.