Re: [SQL] \copy multiline
On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: > Hi, > > I use \copy to output tables into CSV files: > > \copy (SELECT ...) TO 'a.csv' CSV > > but for long and complex SELECT statements, it is cumbersome and > confusing to write everything in a single line, and multiline statements > don't seem to be accepted. Is there an alternative, or am I missing an > continuation-character/option/variable that would allow multiline > statements in this case? > A simple way to workaround this issue is to create a view with your query and use the view in the \copy meta-command of psql. Of course, it means you need to have the permission to create views in the database. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] unique keys / foreign keys on two tables
I'm designing the schema to store a config from our switchboards. As with all PBX's the key is the dialed number which is either an extension number or a group (hunt/ring/pickup) number. I have two tables, one for extensions and one for groups, basically ext_id int4 primary key ext_desctext and grp_id int4 primary key grp_desctext . . . I now need to be able to ensure the id field is unique across both tables. Presumably I can do this with a function and a constraint for each table. Does anyone have examples of this? Next I have other tables that refer to *destinations* which will be an ID that could be either an extension or a group. Examples are 'Direct Dial In' numbers which could point to either. How would I do that? -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] unique keys / foreign keys on two tables
Hi Gary,
The most straightforward way to ensure that the two tables have unique
IDs would be to create one sequence called something like
"destination_seq" and have the id column in both tables default to
NEXTVAL('destination_seq').
As far as storing the destinations go, I'm guessing that you're looking
for a good way to tell what type of id it is without checking both
tables to see which one it exists in. If you need to be able to extract
the destination type from the ID, you could go with something more
robust like:
CREATE OR REPLACE FUNCTION dest_nextval (dest_type int) RETURNS int4 AS
$$
BEGIN
RETURN (nextval('destination_seq') << 1) | dest_type;
END;
$$LANGUAGE plpgsql;
That would use the same destination_seq value, but it it would shift the
number 1 bit to the left. So you could use DEFAULT dest_nextval(0) for
Extensions and DEFAULT dest_nextval(1) for Groups. Your IDs would still
be 100% unique, but you could test for the type by checking IF (id & 1)
= 1 or 0.
The | is a "bitwise" operator for "OR", and the & is the bitwise
operator for AND. They're covered a bit in section 9.3 of the
documentation, but if you went this route you'd probably want to look up
a more thorough explanation of bitwise operations in general.
The same principle works with larger numbers if you needed more types.
If you shifted two bits, you could | by 0, 1, 2, or 3. Shifting 3 bits
would give you 8 possibilities and so on. It's a pretty neat way to
"encode" other information into one field.
I hope that helps!
Best,
Wolfe
--
Wolfe Whalen
wo...@quios.net
On Thu, Nov 29, 2012, at 02:14 AM, Gary Stainburn wrote:
> I'm designing the schema to store a config from our switchboards.
>
> As with all PBX's the key is the dialed number which is either an
> extension
> number or a group (hunt/ring/pickup) number.
>
> I have two tables, one for extensions and one for groups, basically
>
> ext_id int4 primary key
> ext_desctext
>
>
>
>
> and
>
>
>
> grp_id int4 primary key
> grp_desctext
> .
> .
> .
>
> I now need to be able to ensure the id field is unique across both
> tables.
> Presumably I can do this with a function and a constraint for each table.
> Does anyone have examples of this?
>
>
> Next I have other tables that refer to *destinations* which will be an ID
> that
> could be either an extension or a group. Examples are 'Direct Dial In'
> numbers which could point to either. How would I do that?
>
> --
> Gary Stainburn
> Group I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] \copy multiline
On 11/29/2012 02:33 AM, Guillaume Lelarge wrote: On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: Hi, I use \copy to output tables into CSV files: \copy (SELECT ...) TO 'a.csv' CSV but for long and complex SELECT statements, it is cumbersome and confusing to write everything in a single line, and multiline statements don't seem to be accepted. Is there an alternative, or am I missing an continuation-character/option/variable that would allow multiline statements in this case? A simple way to workaround this issue is to create a view with your query and use the view in the \copy meta-command of psql. Of course, it means you need to have the permission to create views in the database. Or maybe a function returning a table or set of records. Might be slightly more flexible than the view. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] \copy multiline
On Thu, 29 Nov 2012 08:01:31 +, Ben Morrow wrote: > Quoth splu...@gmail.com (Seb): >> I use \copy to output tables into CSV files: >> \copy (SELECT ...) TO 'a.csv' CSV >> but for long and complex SELECT statements, it is cumbersome and >> confusing to write everything in a single line, and multiline >> statements don't seem to be accepted. Is there an alternative, or am >> I missing an continuation-character/option/variable that would allow >> multiline statements in this case? > CREATE TEMPORARY VIEW? Of course, that's perfect. Thanks! -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] \copy multiline
On Thu, 29 Nov 2012 10:33:37 +0100, Guillaume Lelarge wrote: > On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: >> Hi, >> I use \copy to output tables into CSV files: >> \copy (SELECT ...) TO 'a.csv' CSV >> but for long and complex SELECT statements, it is cumbersome and >> confusing to write everything in a single line, and multiline >> statements don't seem to be accepted. Is there an alternative, or am >> I missing an continuation-character/option/variable that would allow >> multiline statements in this case? > A simple way to workaround this issue is to create a view with your > query and use the view in the \copy meta-command of psql. Of course, > it means you need to have the permission to create views in the > database. Thanks. Someone also suggested creating a temporary view, which helps keep the schema sane and clean. Cheers, -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
