Delete selected tables from Postgres sql
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I need to remove selected tables from the Postgres SQL. It would better to use like or where clause.
Like I have
TABLE_A
TABLE_B
TABLE_C
-
-
-
TABLE_N
I need to delete
TABLE_A to TABLE_X
postgresql
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
add a comment |
I need to remove selected tables from the Postgres SQL. It would better to use like or where clause.
Like I have
TABLE_A
TABLE_B
TABLE_C
-
-
-
TABLE_N
I need to delete
TABLE_A to TABLE_X
postgresql
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
add a comment |
I need to remove selected tables from the Postgres SQL. It would better to use like or where clause.
Like I have
TABLE_A
TABLE_B
TABLE_C
-
-
-
TABLE_N
I need to delete
TABLE_A to TABLE_X
postgresql
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
I need to remove selected tables from the Postgres SQL. It would better to use like or where clause.
Like I have
TABLE_A
TABLE_B
TABLE_C
-
-
-
TABLE_N
I need to delete
TABLE_A to TABLE_X
postgresql
postgresql
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
edited Nov 29 '18 at 2:55
Elshan
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
asked Nov 29 '18 at 2:35
ElshanElshan
3,87624177
3,87624177
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT oid::REGCLASS table_name
FROM pg_class
WHERE relname <= 'table_x'
AND relkind = 'r'
LOOP
EXECUTE 'DROP TABLE' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
ERROR: missing FROM-clause entry for table "c"
– Elshan
Nov 29 '18 at 2:54
Sorry, I use it with more parameters
– Abdel P.
Nov 29 '18 at 3:31
add a comment |
Can be done with a single command, which is faster - in case this is a recurring task.
Add IF EXISTS if the existence of any tables is uncertain. This way we save an extra trip to the system catalogs (information_schema.tables or pg_catalog.pg_tables).
And you may want to add CASCADE:
DO
$do$
BEGIN
-- child safety device: quote RAISE instead of EXECUTE to prime the bomb
-- EXECUTE (
RAISE NOTICE '%', (
SELECT 'DROP TABLE IF EXISTS'
|| string_agg('table_' || chr(ascii('a') + g) , ', ')
|| ' CASCADE;'
FROM generate_series(0,13) g
);
END
$do$;
Generates a command of the form:
DROP TABLE IF EXISTS table_a, table_b, ... , table_n CASCADE;
Using generate_series() to generate requested table names. More here:
- How to drop many (but not all) tables in one fell swoop?
- How to check if a table exists in a given schema
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
add a comment |
I got some idea from the @Abdel and made this for users who want this.
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name like 'YOUR_LIKE_QURY_GOES_HERE'
LOOP
EXECUTE 'DROP TABLE ' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53531015%2fdelete-selected-tables-from-postgres-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT oid::REGCLASS table_name
FROM pg_class
WHERE relname <= 'table_x'
AND relkind = 'r'
LOOP
EXECUTE 'DROP TABLE' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
ERROR: missing FROM-clause entry for table "c"
– Elshan
Nov 29 '18 at 2:54
Sorry, I use it with more parameters
– Abdel P.
Nov 29 '18 at 3:31
add a comment |
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT oid::REGCLASS table_name
FROM pg_class
WHERE relname <= 'table_x'
AND relkind = 'r'
LOOP
EXECUTE 'DROP TABLE' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
ERROR: missing FROM-clause entry for table "c"
– Elshan
Nov 29 '18 at 2:54
Sorry, I use it with more parameters
– Abdel P.
Nov 29 '18 at 3:31
add a comment |
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT oid::REGCLASS table_name
FROM pg_class
WHERE relname <= 'table_x'
AND relkind = 'r'
LOOP
EXECUTE 'DROP TABLE' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT oid::REGCLASS table_name
FROM pg_class
WHERE relname <= 'table_x'
AND relkind = 'r'
LOOP
EXECUTE 'DROP TABLE' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
edited Nov 29 '18 at 3:32
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
answered Nov 29 '18 at 2:46
Abdel P.Abdel P.
208211
208211
ERROR: missing FROM-clause entry for table "c"
– Elshan
Nov 29 '18 at 2:54
Sorry, I use it with more parameters
– Abdel P.
Nov 29 '18 at 3:31
add a comment |
ERROR: missing FROM-clause entry for table "c"
– Elshan
Nov 29 '18 at 2:54
Sorry, I use it with more parameters
– Abdel P.
Nov 29 '18 at 3:31
ERROR: missing FROM-clause entry for table "c"
– Elshan
Nov 29 '18 at 2:54
ERROR: missing FROM-clause entry for table "c"
– Elshan
Nov 29 '18 at 2:54
Sorry, I use it with more parameters
– Abdel P.
Nov 29 '18 at 3:31
Sorry, I use it with more parameters
– Abdel P.
Nov 29 '18 at 3:31
add a comment |
Can be done with a single command, which is faster - in case this is a recurring task.
Add IF EXISTS if the existence of any tables is uncertain. This way we save an extra trip to the system catalogs (information_schema.tables or pg_catalog.pg_tables).
And you may want to add CASCADE:
DO
$do$
BEGIN
-- child safety device: quote RAISE instead of EXECUTE to prime the bomb
-- EXECUTE (
RAISE NOTICE '%', (
SELECT 'DROP TABLE IF EXISTS'
|| string_agg('table_' || chr(ascii('a') + g) , ', ')
|| ' CASCADE;'
FROM generate_series(0,13) g
);
END
$do$;
Generates a command of the form:
DROP TABLE IF EXISTS table_a, table_b, ... , table_n CASCADE;
Using generate_series() to generate requested table names. More here:
- How to drop many (but not all) tables in one fell swoop?
- How to check if a table exists in a given schema
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
add a comment |
Can be done with a single command, which is faster - in case this is a recurring task.
Add IF EXISTS if the existence of any tables is uncertain. This way we save an extra trip to the system catalogs (information_schema.tables or pg_catalog.pg_tables).
And you may want to add CASCADE:
DO
$do$
BEGIN
-- child safety device: quote RAISE instead of EXECUTE to prime the bomb
-- EXECUTE (
RAISE NOTICE '%', (
SELECT 'DROP TABLE IF EXISTS'
|| string_agg('table_' || chr(ascii('a') + g) , ', ')
|| ' CASCADE;'
FROM generate_series(0,13) g
);
END
$do$;
Generates a command of the form:
DROP TABLE IF EXISTS table_a, table_b, ... , table_n CASCADE;
Using generate_series() to generate requested table names. More here:
- How to drop many (but not all) tables in one fell swoop?
- How to check if a table exists in a given schema
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
add a comment |
Can be done with a single command, which is faster - in case this is a recurring task.
Add IF EXISTS if the existence of any tables is uncertain. This way we save an extra trip to the system catalogs (information_schema.tables or pg_catalog.pg_tables).
And you may want to add CASCADE:
DO
$do$
BEGIN
-- child safety device: quote RAISE instead of EXECUTE to prime the bomb
-- EXECUTE (
RAISE NOTICE '%', (
SELECT 'DROP TABLE IF EXISTS'
|| string_agg('table_' || chr(ascii('a') + g) , ', ')
|| ' CASCADE;'
FROM generate_series(0,13) g
);
END
$do$;
Generates a command of the form:
DROP TABLE IF EXISTS table_a, table_b, ... , table_n CASCADE;
Using generate_series() to generate requested table names. More here:
- How to drop many (but not all) tables in one fell swoop?
- How to check if a table exists in a given schema
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
Can be done with a single command, which is faster - in case this is a recurring task.
Add IF EXISTS if the existence of any tables is uncertain. This way we save an extra trip to the system catalogs (information_schema.tables or pg_catalog.pg_tables).
And you may want to add CASCADE:
DO
$do$
BEGIN
-- child safety device: quote RAISE instead of EXECUTE to prime the bomb
-- EXECUTE (
RAISE NOTICE '%', (
SELECT 'DROP TABLE IF EXISTS'
|| string_agg('table_' || chr(ascii('a') + g) , ', ')
|| ' CASCADE;'
FROM generate_series(0,13) g
);
END
$do$;
Generates a command of the form:
DROP TABLE IF EXISTS table_a, table_b, ... , table_n CASCADE;
Using generate_series() to generate requested table names. More here:
- How to drop many (but not all) tables in one fell swoop?
- How to check if a table exists in a given schema
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
edited Nov 29 '18 at 4:00
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
answered Nov 29 '18 at 3:54
Erwin BrandstetterErwin Brandstetter
354k69642822
354k69642822
add a comment |
add a comment |
I got some idea from the @Abdel and made this for users who want this.
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name like 'YOUR_LIKE_QURY_GOES_HERE'
LOOP
EXECUTE 'DROP TABLE ' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
add a comment |
I got some idea from the @Abdel and made this for users who want this.
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name like 'YOUR_LIKE_QURY_GOES_HERE'
LOOP
EXECUTE 'DROP TABLE ' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
add a comment |
I got some idea from the @Abdel and made this for users who want this.
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name like 'YOUR_LIKE_QURY_GOES_HERE'
LOOP
EXECUTE 'DROP TABLE ' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
I got some idea from the @Abdel and made this for users who want this.
DO
$$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name like 'YOUR_LIKE_QURY_GOES_HERE'
LOOP
EXECUTE 'DROP TABLE ' || r.table_name;
END LOOP;
END;
$$ LANGUAGE plpgsql;
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
answered Nov 29 '18 at 3:10
ElshanElshan
3,87624177
3,87624177
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53531015%2fdelete-selected-tables-from-postgres-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
