Res: [SQL] Finding all tables that have foreign keys referencing a table
Try this
select table_schema, table_name
from information_schema.columns
where table_schema not in
('information_schema','pg_catalog')
and column_name = '?'
De: Andreas Joseph Krogh <[EMAIL PROTECTED]>
Para: [email protected]
Enviadas: Quinta-feira, 6 de Novembro de 2008 19:35:23
Assunto: [SQL] Finding all tables that have foreign keys referencing a table
Hi.
I see the last comment here suggests a solution for $subject:
http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html
But it turns out it assumes constraint_name is unique, which is not the case.
It is only unique pr. table. In other words, it produces a lot of false results.
I'm trying to make a function which finds all my old "$1" constraints and
replaces those names with proper names (_fkey).
So - anybody who knows a good solution for how to find all tables with
constraint-names that have foreign keys referencing a table's particluar colum?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / CEO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment.|
NORWAY | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
+-+
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua
cara @ymail.com ou @rocketmail.com.
http://br.new.mail.yahoo.com/addresses
[SQL] sum timestamp result in hours
Select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento)) from atendimentopublico.registro_atendimento rgat inner join cadastro.localidade loca on loca.loca_id=rgat.loca_id inner join atendimentopublico.solicitacao_tipo_especificacao step on step.step_id=rgat.step_id where date(rgat_tmregistroatendimento) between '2007-01-01' and '2007-12-31' and rgat.step_id in (706,710,717,718,719,722,725,728,730,732,733,720,721,727,714,729) and rgat.rgat_cdsituacao=2 and date(rgat_tmencerramento) between '2007-01-01' and '2007-12-31' and rgat.rgat_tmencerramento > rgat.rgat_tmregistroatendimento and rgat.loca_id=339 and rgat.rgat_id in (20111305, 30102409, 30102096, 30097214, 30102396, 20057815, 20325895) Result: 1 year 4 mons 88 days 51:42:00 I need help to view result query only in hours, the timestamp columns in bold above. thanks Paulo Moraes Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @ymail.com ou @rocketmail.com. http://br.new.mail.yahoo.com/addresses
Re: [SQL] sum timestamp result in hours
On Fri, Nov 7, 2008 at 9:36 AM, paulo matadr <[EMAIL PROTECTED]> wrote:
> I change form to create query but basically need
> sum(rgat.rgat_tmencerramento - rgat.rgat_tmregistroatendimento) = result in
> hours
> sumary:
> rgat.rgat_tmencerrament = timestamp column
> gat.rgat_tmregistroatendimento = timestamp column
select extract('epoch' from '4 days 6 hours 3 minutes'::interval)*
interval '1 sec';
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Res: [SQL] sum timestamp result in hours
I change form to create query but basically need sum(rgat.rgat_tmencerramento - rgat.rgat_tmregistroatendimento) = result in hours sumary: rgat.rgat_tmencerrament = timestamp column gat.rgat_tmregistroatendimento = timestamp column De: Osvaldo Kussama <[EMAIL PROTECTED]> Para: paulo matadr <[EMAIL PROTECTED]> Enviadas: Sexta-feira, 7 de Novembro de 2008 12:50:14 Assunto: Re: [SQL] sum timestamp result in hours 2008/11/7, paulo matadr <[EMAIL PROTECTED]>: > Select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento)) > from atendimentopublico.registro_atendimento rgat > inner join cadastro.localidade loca on loca.loca_id=rgat.loca_id > inner join atendimentopublico.solicitacao_tipo_especificacao step on > step.step_id=rgat.step_id > where > date(rgat_tmregistroatendimento) between '2007-01-01' and '2007-12-31' > and rgat.step_id in > (706,710,717,718,719,722,725,728,730,732,733,720,721,727,714,729) > and rgat.rgat_cdsituacao=2 > and date(rgat_tmencerramento) between '2007-01-01' and '2007-12-31' > and rgat.rgat_tmencerramento > rgat.rgat_tmregistroatendimento > and rgat.loca_id=339 > and rgat.rgat_id in > (20111305, > 30102409, > 30102096, > 30097214, > 30102396, > 20057815, > 20325895) > Result: 1 year 4 mons 88 days 51:42:00 > > I need help to view result query only in hours, the timestamp columns in > bold above. > 88 days? 51 hours? Try: bdteste=# SELECT EXTRACT(EPOCH FROM INTERVAL '1 year 4 mons 88 days 51:42:00')/3600; ?column? -- 13809.7 (1 registro) Osvaldo Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua cara @ymail.com ou @rocketmail.com. http://br.new.mail.yahoo.com/addresses
Re: [SQL] need help in building a query
its like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the entry that has sum > $500. If there is some user who didnt even make 500 till now in my shop return the first date of transaction/order. ex: Orders(order_id, user_id, amount_paid, create_timestamp) values: (1, 1, 100, 1) (2, 1, 300, 2) (3, 2, 100, 2) (4, 2, 100, 3) (5, 1, 100, 4) (6, 1, 200, 5) (7, 2, 150, 5) for user 1: the order_id = 2 for user 2: the order_id = 3 (coz he couldnt make 500) On Thu, Nov 6, 2008 at 10:40 PM, Harold A. Giménez Ch. < [EMAIL PROTECTED]> wrote: > I personally would help if I understood what you need. I'm sure others feel > the same way. Provide DDL, sample data, and expected result of the query. > Maybe you'll have better luck... > > > On Thu, Nov 6, 2008 at 11:15 AM, Devil™ Dhuvader <[EMAIL PROTECTED]>wrote: > >> none can help me? >> >> On Tue, Nov 4, 2008 at 9:08 PM, Devil™ Dhuvader <[EMAIL PROTECTED]>wrote: >> >>> hi, >>> I need some help in creating a sql. >>> the problem is as below. >>> >>> assume that: >>> I am a store keeper >>> and I have the list of customer(user_id) transactions in my order table. >>> schema: Orders(order_id, user_id, amount_paid, create_timestamp) >>> >>> I want to give discount of 10% for the customer who made orders of worth >>> (sum) at least $500 in the least time from now (i.e last few days, but NOT >>> ALL TIME LEAST TIME TO BUY $500 WORTH GOODS). >>> and 9% discount for the customer who made $500 in second least time from >>> now. >>> and so on >>> >>> there is no time constraint. >>> it customer could take any amount of time to make $500. >>> the customer might not even made $500 bill till now in that case I should >>> get the first date. >>> >>> the result can take upto the first entry time of the table itself. >>> >>> in short its like sum up entries of each user in order table backwards >>> (i.e from last entry to the first) and find the entry that has sum > $500. >>> If there is some user who didnt even make 500 till now in my shop return >>> the first date of transaction/order. >>> >>> can anyone help me on this? >> >> >> >
[SQL] Creating input/output_functions
Is there a way to create custom type input/output functions for user-defined types in some other language except C (e.g. plythonu)? Best regards -- Markus Schatten, MSc Faculty of Organization and Informatics Varaždin, Croatia http://autopoiesis.foi.hr -- Ova poruka poslana je s CARNetovog webmail sustava --- http://webmail.carnet.hr/ --- -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: Res: [SQL] Finding all tables that have foreign keys referencing a table
On Friday 07 November 2008 15:15:49 paulo matadr wrote:
> Try this
>
> select table_schema, table_name
> from information_schema.columns
> where table_schema not in
> ('information_schema','pg_catalog')
> and column_name = '?'
AFAICS this lists all tables which have a column named '?', which is not what
I'm after. I'm after listing all columns referencing a certain column as a
FOREIGN KEY.
Example, making the FK's names "$1" to mimic old PG-behaviour, and proving my
point about the FK-names not needing to be globaly unique:
CREATE TABLE test1(
id INTEGER PRIMARY KEY
);
CREATE TABLE test2(
id INTEGER PRIMARY KEY
);
CREATE TABLE test_ref1(
id INTEGER PRIMARY KEY,
entity_id INTEGER,
CONSTRAINT "$1" FOREIGN KEY (entity_id) REFERENCES test1(id)
);
CREATE TABLE test_ref2(
id INTEGER PRIMARY KEY,
entity_id INTEGER,
CONSTRAINT "$1" FOREIGN KEY (entity_id) REFERENCES test2(id)
);
andreak=# \d test_ref1
Table "public.test_ref1"
Column | Type | Modifiers
---+-+---
id| integer | not null
entity_id | integer |
Indexes:
"test_ref1_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (entity_id) REFERENCES test1(id)
andreak=# \d test_ref2
Table "public.test_ref2"
Column | Type | Modifiers
---+-+---
id| integer | not null
entity_id | integer |
Indexes:
"test_ref2_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"$1" FOREIGN KEY (entity_id) REFERENCES test2(id)
andreak=#
Now, any idea about how to safely get all columns which reference test1.id?
-hackers; Any hints?
BTW:
andreak=# select version();
version
---
PostgreSQL 8.3.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu
4.2.3-2ubuntu7)
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / CEO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment.|
NORWAY | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
+-+
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: Res: [SQL] Finding all tables that have foreign keys referencing a table
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > AFAICS this lists all tables which have a column named '?', which is not what > I'm after. I'm after listing all columns referencing a certain column as a > FOREIGN KEY. Should be possible to dredge that out of pg_constraint ... about like this: select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname as col from pg_attribute af, pg_attribute a, (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = 'f') ss) ss2 where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid; Deconstructing those arrays in parallel is a bit of a pain :-( regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: Res: [SQL] Finding all tables that have foreign keys referencing a table
On Friday 07 November 2008 21:09:33 Tom Lane wrote: > Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > > AFAICS this lists all tables which have a column named '?', which is not > > what I'm after. I'm after listing all columns referencing a certain column > > as a FOREIGN KEY. > > Should be possible to dredge that out of pg_constraint ... about like > this: > > select confrelid::regclass, af.attname as fcol, >conrelid::regclass, a.attname as col > from pg_attribute af, pg_attribute a, > (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey >from (select conrelid,confrelid,conkey,confkey, > generate_series(1,array_upper(conkey,1)) as i > from pg_constraint where contype = 'f') ss) ss2 > where af.attnum = confkey and af.attrelid = confrelid and > a.attnum = conkey and a.attrelid = conrelid; > > Deconstructing those arrays in parallel is a bit of a pain :-( What can I say, you're the man. Thank you very much! Needless to say that it would be nice if this information was available in the information_schema, I'm probably not the only one spending lots of time in there. It's probably not in in the standard, but i would be a nice PG-extention to allow retrieving that info in a more intuitive way. For the archive, here is a complete example with table_name and column_name: select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname as col from pg_attribute af, pg_attribute a, (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = 'f') ss) ss2 where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid AND confrelid::regclass = 'my_table'::regclass AND af.attname = 'my_referenced_column'; -- Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / CEO +-+ OfficeNet AS| The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment.| NORWAY | | Tlf:+47 24 15 38 90 | | Fax:+47 24 15 38 91 | | Mobile: +47 909 56 963 | | +-+ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: Res: [SQL] Finding all tables that have foreign keys referencing a table
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes: > Needless to say that it would be nice if this information was available in > the information_schema, I'm probably not the only one spending lots of time > in there. It's probably not in in the standard, but i would be a nice > PG-extention to allow retrieving that info in a more intuitive way. Unfortunately, the whole point of information_schema is to be standardized; the notion of a "PG extension" to it is just wrong. Feel free to lobby the SQL committee to fix their oversight ... regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Resp.: [SQL] need help in building a query
2008/11/7, Devil™ Dhuvader <[EMAIL PROTECTED]>: > its like sum up entries of each user in order table backwards (i.e from last > entry to the first) and find the entry that has sum > $500. > If there is some user who didnt even make 500 till now in my shop return the > first date of transaction/order. > > ex: > Orders(order_id, user_id, amount_paid, create_timestamp) > values: > (1, 1, 100, 1) > (2, 1, 300, 2) > (3, 2, 100, 2) > (4, 2, 100, 3) > (5, 1, 100, 4) > (6, 1, 200, 5) > (7, 2, 150, 5) > > for user 1: the order_id = 2 > for user 2: the order_id = 3 (coz he couldnt make 500) > Try: bdteste=# SELECT o1.user_id, o1.order_id, '>= 500' AS cond FROM Orders o1 bdteste-# WHERE (SELECT sum(o2.amount_paid) FROM Orders o2 WHERE o2.user_id = o1.user_id AND o2.order_id > o1.order_id) < 500 AND bdteste-#(SELECT sum(o2.amount_paid) FROM Orders o2 WHERE o2.user_id = o1.user_id AND o2.order_id >= o1.order_id) >=500 bdteste-# UNION bdteste-# SELECT user_id, min(order_id) AS "min order id", '< 500' AS cond FROM Orders bdteste-# WHERE user_id IN (SELECT user_id FROM Orders GROUP BY user_id HAVING sum(amount_paid) < 500) bdteste-# GROUP BY user_id; user_id | order_id | cond -+--+ 1 |2 | >= 500 2 |3 | < 500 Osvaldo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need help in building a query
Devil™ Dhuvader wrote: its like sum up entries of each user in order table backwards (i.e from last entry to the first) and find the entry that has sum > $500. If there is some user who didnt even make 500 till now in my shop return the first date of transaction/order . ex: Orders(order_id, user_id, amount_paid, create_timestamp) values: (1, 1, 100, 1) (2, 1, 300, 2) (3, 2, 100, 2) (4, 2, 100, 3) (5, 1, 100, 4) (6, 1, 200, 5) (7, 2, 150, 5) for user 1: the order_id = 2 for user 2: the order_id = 3 (coz he couldnt make 500) I might also take a crack at the SQL if I understood the question; but I still don't know what you want. You're initial question indicated you were trying to calculate the proper discount for each customer. This example mentions neither customers nor discounts at all and appears to be looking for a specific transaction instead. Frank -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
