Обсуждение: tables not in list
Hi List,
I have a list of table names and I am trying to confirm that they are all in my postgres db. But what I want returned is a list/array of ones that are in my list but not in the db. So for example:
CREATE TABLE test
(
somecol integer
) WITHOUT OIDS;
CREATE TABLE bar
(
barcol integer
) WITHOUT OIDS;
Now if I were to have a list of table names that included 'test', 'bar', and 'foo', then how do I get 'foo' to return. Here is what I have, but it returns the ones that are in the list and I want the opposite of that.
select array(
select table_name::text from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public'
and table_name = any (array['test', 'bar', 'foo']))
Thanks in advance for any help on this.
Lee
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
Hi List,
I have a list of table names and I am trying to confirm that they are all in my postgres db. But what I want returned is a list/array of ones that are in my list but not in the db. So for example:
CREATE TABLE test
(
somecol integer
) WITHOUT OIDS;
CREATE TABLE bar
(
barcol integer
) WITHOUT OIDS;
Now if I were to have a list of table names that included 'test', 'bar', and 'foo', then how do I get 'foo' to return. Here is what I have, but it returns the ones that are in the list and I want the opposite of that.
select array(
select table_name::text from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public'
and table_name = any (array['test', 'bar', 'foo']))
Thanks in advance for any help on this.
Lee
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
Is there no way to do this without doing an insert into another table?
From: paddy carroll [mailto:paddy.carroll@gmail.com]
Sent: Monday, July 30, 2007 3:24 PM
To: Lee Keel
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] tables not in list
put all your tables in a new table (public.tables)
select table_name from public.tables
where public.tables.table_name not in (select table_name from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public')
On 30 Jul 2007, at 20:31, Lee Keel wrote:
Hi List,
I have a list of table names and I am trying to confirm that they are all in my postgres db. But what I want returned is a list/array of ones that are in my list but not in the db. So for example:
CREATE TABLE test
(
somecol integer
) WITHOUT OIDS;
CREATE TABLE bar
(
barcol integer
) WITHOUT OIDS;
Now if I were to have a list of table names that included 'test', 'bar', and 'foo', then how do I get 'foo' to return. Here is what I have, but it returns the ones that are in the list and I want the opposite of that.
select array(
select table_name::text from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public'
and table_name = any (array['test', 'bar', 'foo']))
Thanks in advance for any help on this.
Lee
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.
Lee Keel wrote: > Is there no way to do this without doing an insert into another table? > I usually resolve this as: -- this function lets you select from an array CREATE OR REPLACE FUNCTION "array_to_set" (vaarray anyarray) RETURNS SETOF anyelement AS $body$ BEGIN FOR I IN COALESCE(ARRAY_LOWER(VAARRAY, 1), 1) .. COALESCE(ARRAY_UPPER(VAARRAY, 1), 0) LOOP RETURN NEXT VAARRAY[I]; END LOOP; END $body$ LANGUAGE 'plpgsql'; select table_name from array_to_set(array['test', 'bar', 'foo']) as table_name where table_name not in (select table_name from information_schema.tables where table_catalog='postgres' and table_type='BASE TABLE' and table_schema='public') regards
> -----Original Message----- > From: Viatcheslav Kalinin [mailto:vka@ipcb.net] > Sent: Tuesday, July 31, 2007 9:16 AM > To: Lee Keel > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] tables not in list > > I usually resolve this as: > -- this function lets you select from an array > CREATE OR REPLACE FUNCTION "array_to_set" (vaarray anyarray) RETURNS > SETOF anyelement AS > $body$ > BEGIN > FOR I IN COALESCE(ARRAY_LOWER(VAARRAY, 1), 1) .. > COALESCE(ARRAY_UPPER(VAARRAY, 1), 0) LOOP > RETURN NEXT VAARRAY[I]; > END LOOP; > END > $body$ > LANGUAGE 'plpgsql'; > > select table_name > from array_to_set(array['test', 'bar', 'foo']) as table_name > where table_name not in (select table_name from > information_schema.tables where table_catalog='postgres' and > table_type='BASE TABLE' and table_schema='public') > > regards THANKS! That was exactly what I needed! This email and any files transmitted with it are confidential and intended solely for the use of the individual or entityto whom they are addressed. If you have received this email in error please notify the sender. This message containsconfidential information and is intended only for the individual named. If you are not the named addressee you shouldnot disseminate, distribute or copy this e-mail.