Re: [SQL] data import via COPY, Rules + Triggers
Thank your for your reply, Sergey.
(By the way, I noticed you are not cc'ing/replying to the list.)
On Fri, 6 May 2011 01:45:19 +0300
sergey kapustin wrote:
>INSERT ... RETURNING will not work in rules i think. You cannot nest
>INSERTs and its not possible to use variables.
And rules are not actioned by the COPY command (which I use to populate
the tables) whereas triggers are.
>you have
>to use conditional statements because you don't want to insert into
>manager table every time you insert new athlete.
That has been taking care of by an insert/update trigger on manager,
which updates a manager record, if necessary, where it exists already.
>I suggest you use plsql function (trigger on zathlete) instead of
>rules. Then you can do something like this -
>
>id_manager:=null
>select into id_manager id from manager where name=NEW.manager_name;
>if not found then
>insert into manager(name) values (NEW.manager_name);
>select into id_manager CURRVAL('manager_id_seq');
>end if;
>INSERT INTO athlete (... manager_fk...) VALUES ( ... id_manager...);
>
>good luck!
>
>
>On Thu, May 5, 2011 at 6:21 PM, Tarlika Elisabeth Schmitz <
>postgres...@numerixtechnology.de> wrote:
>
>> I was wondering whether the manager.id could maybe be obtained via
>> INSERT ... RETURNING?
>>
>> --
>>
>> Best Regards,
>> Tarlika Elisabeth Schmitz
>>
>>
>>
>> On Thu, 5 May 2011 08:45:32 +0300
>> sergey kapustin wrote:
>>
>> >Try using (select id from manager where name=NEW.manager_name) to
>> >get the newly inserted manager.
>> >The "name" column in "manager" table should have unique constraint
>> >- this will be good both for performance and consistency.
>> >
>> >
>> >
>> >CREATE OR REPLACE RULE zathlete_insert_1 AS
>> > ON INSERT TO zathlete
>> > DO ALSO
>> > (
>> > INSERT INTO athlete
>> > (id, name, _received) VALUES
>> > (NEW.dad_id, NEW.dad_name, NEW._received);
>> > INSERT INTO sponsor
>> > (id, name, _received) VALUES
>> > (NEW.sponsor_id, NEW.sponsor_name, NEW._received);
>> > INSERT INTO manager
>> > (name, _received) VALUES
>> > (NEW.manager_name, NEW._received);
>> > INSERT INTO athlete
>> > (id, name, dad_fk, sponsor_fk, manager_fk, _received)
>> > VALUES (NEW.id, NEW.name, NEW.dad_id,
>> >NEW.sponsor_id, (select id from manager where
>> >name=NEW.manager_name), NEW._received);
>> >)
>> >;
>> >
>> >On Thu, May 5, 2011 at 1:48 AM, Tarlika Elisabeth Schmitz <
>> >postgres...@numerixtechnology.de> wrote:
>> >
>> >> [...]
>> >>
>> >>
>> >> I created interim tables matching the structure of the CSV formats
>> >> (about 6 of them). I want to import via COPY and distribute the
>> >> data to the "proper" tables via rules + triggers.
>> >>
>> >> I just hit a wall with one of the rules, (see example below): how
>> >> do I populate athlete.manager_fk, which is the result of the
>> >> previous INSERT?
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> -- interim table
>> >> CREATE TABLE zathlete
>> >> (
>> >> id integer NOT NULL,
>> >> "name" character varying(50) NOT NULL,
>> >> dad_id integer,
>> >> dad_name character varying(50),
>> >> sponsor_id integer,
>> >> sponsor_name character varying(50),
>> >> manager_name character varying(50),
>> >> _received timestamp without time zone NOT NULL
>> >> )
>> >>
>> >> -- proper tables
>> >> CREATE TABLE sponsor
>> >> (
>> >> id integer NOT NULL,
>> >> "name" character varying(50) NOT NULL,
>> >> _received timestamp without time zone NOT NULL,
>> >> CONSTRAINT sponsor_pkey PRIMARY KEY (id)
>> >> )
>> >>
>> >> CREATE TABLE manager
>> >> (
>> >> id serial NOT NULL,
>> >> "name" character varying(50) NOT NULL,
>> >> _received timestamp without time zone NOT NULL,
>> >> CONSTRAINT manager_pkey PRIMARY KEY (id)
>> >> )
>> >>
>> >> CREATE TABLE athlete
>> >> (
>> >> id integer NOT NULL,
>> >> "name" character varying(50) NOT NULL,
>> >> dad_fk integer,
>> >> sponsor_fk integer,
>> >> manager_fk integer,
>> >> _received timestamp without time zone NOT NULL,
>> >> CONSTRAINT athlete_pkey PRIMARY KEY (id),
>> >> CONSTRAINT manager_athlete_fk FOREIGN KEY (manager_fk)
>> >> REFERENCES manager (id) MATCH SIMPLE
>> >> ON UPDATE CASCADE ON DELETE RESTRICT,
>> >> CONSTRAINT sponsor_athlete_fk FOREIGN KEY (sponsor_fk)
>> >> REFERENCES sponsor (id) MATCH SIMPLE
>> >> ON UPDATE CASCADE ON DELETE RESTRICT,
>> >> CONSTRAINT dad_athlete_fk FOREIGN KEY (dad_fk)
>> >> REFERENCES athlete (id) MATCH SIMPLE
>> >> ON UPDATE CASCADE ON DELETE RESTRICT
>> >> )
>> >>
>> >>
>> >> -- rules
>> >>
>> >> CREATE OR REPLACE RULE zathlete_insert_1 AS
>> >>ON INSERT TO zathlete
>> >>DO ALSO -- INSTEAD once all is working
>> >>(
>> >>INSERT INTO athlete
>> >>(id, name, _received) VALUES
>> >>(NEW.dad_id, NEW.dad_name, NEW._received);
>> >>INSERT INTO sponsor
>> >>(id, name, _received) VALUES
>> >>
Re: [SQL] data import via COPY, Rules + Triggers
On Wed, 4 May 2011 23:48:04 +0100 Tarlika Elisabeth Schmitz wrote: >I have got a database that needs to be populated, first with historical >data, then on a daily basis.[...] >Once imported, data will neither be modified nor deleted. > >Data come in denormalized CSV formats. [...] >I created interim tables matching the structure of the CSV formats >I want to import via COPY and distribute the data to >the "proper" tables via triggers. [...] I am the only user of the system and at the moment I have only one database user. I only want the insdert/update triggers to be executed when I am importing data. There might be situations where I need to intervene "manually" and fix some data without the triggers being actioned. How about creating a second user through whom the imports are done? I could query current_user in the trigger and simply skip the trigger actions for any other user. QUESTION: Or could I do something more sophisticated with privileges? -- Tarlika Elisabeth Schmitz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Specifying column level collations
Hi,
I'm playing around with 9.1beta1 and would like to create a table where one
column has a non-default collation.
But whatever I try, I can't find the correct name that I have to use.
My database is initialized as follows:
postgres=# select version();
version
PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
(1 row)
postgres=# select name, setting
postgres-# from pg_settings
postgres-# where name in ('lc_collate', 'server_encoding', 'client_encoding');
name | setting
-+-
client_encoding | WIN1252
lc_collate | German_Germany.1252
server_encoding | UTF8
(3 rows)
Now I'm trying to create a table where one column's collation is set to french:
create table foo (bar text collate "fr_FR") --> collation "fr_FR" for encoding
"UTF8" does not exist
create table foo (bar text collate "fr_FR.1252") --> collation "fr_FR" for encoding
"UTF8" does not exist
create table foo (bar text collate "fr_FR.UTF8") --> collation "fr_FR" for encoding
"UTF8" does not exist
create table foo (bar text collate "French_France.1252") --> collation
"French_France.1252" for encoding "UTF8" does not exist
So, how do I specify the collation there?
And is there a command to show me all available collations that I can use?
Thanks
Thomas
--
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] Specifying column level collations
On 05/07/2011 01:19 PM, Thomas Kellerer wrote:
> Hi,
>
> I'm playing around with 9.1beta1 and would like to create a table where
> one column has a non-default collation.
>
> But whatever I try, I can't find the correct name that I have to use.
>
> My database is initialized as follows:
>
> postgres=# select version();
> version
>
> PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
> (1 row)
>
> postgres=# select name, setting
> postgres-# from pg_settings
> postgres-# where name in ('lc_collate', 'server_encoding',
> 'client_encoding');
> name | setting
> -+-
> client_encoding | WIN1252
> lc_collate | German_Germany.1252
> server_encoding | UTF8
> (3 rows)
>
>
> Now I'm trying to create a table where one column's collation is set to
> french:
>
> create table foo (bar text collate "fr_FR") --> collation "fr_FR" for
> encoding "UTF8" does not exist
> create table foo (bar text collate "fr_FR.1252") --> collation "fr_FR"
> for encoding "UTF8" does not exist
> create table foo (bar text collate "fr_FR.UTF8") --> collation "fr_FR"
> for encoding "UTF8" does not exist
> create table foo (bar text collate "French_France.1252") --> collation
> "French_France.1252" for encoding "UTF8" does not exist
>
> So, how do I specify the collation there?
>
You first need to use "CREATE COLLATION", such as:
b1=# CREATE COLLATION fr (locale='fr_FR');
CREATE COLLATION
Then, you'll be able to create your table:
b1=# CREATE TABLE foo (bar TEXT COLLATE fr);
CREATE TABLE
b1=# \d foo
Table "public.foo"
Column | Type | Modifiers
+--+
bar| text | collate fr
> And is there a command to show me all available collations that I can use?
>
b1=# select * from pg_collation;
collname | collnamespace | collowner | collencoding | collcollate |
collctype
+---+---+--+-+
default|11 |10 | -1 | |
C |11 |10 | -1 | C | C
POSIX |11 |10 | -1 | POSIX | POSIX
en_AG |11 |10 |6 | en_AG | en_AG
en_AG.utf8 |11 |10 |6 | en_AG.utf8 | [...]
fr_FR |11 |10 |6 | fr_FR.utf8 |
fr_FR.utf8
fr_FR.utf8 |11 |10 |6 | fr_FR.utf8 |
fr_FR.utf8
fr_LU |11 |10 |6 | fr_LU.utf8 |
fr_LU.utf8
fr_LU.utf8 |11 |10 |6 | fr_LU.utf8 |
fr_LU.utf8
ucs_basic |11 |10 |6 | C | C
fr | 2200 |10 |6 | fr_FR.UTF8 |
fr_FR.UTF8
(47 rows)
Or \dO (o in uppercase) inside psql:
b1=# \dO
List of collations
Schema | Name | Collate | Ctype
+--++
public | fr | fr_FR.UTF8 | fr_FR.UTF8
(1 row)
--
Guillaume
http://www.postgresql.fr
http://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
Re: [SQL] Specifying column level collations
Guillaume Lelarge wrote on 07.05.2011 14:02: create table foo (bar text collate "fr_FR") --> collation "fr_FR" for encoding "UTF8" does not exist create table foo (bar text collate "fr_FR.1252") --> collation "fr_FR" for encoding "UTF8" does not exist create table foo (bar text collate "fr_FR.UTF8") --> collation "fr_FR" for encoding "UTF8" does not exist create table foo (bar text collate "French_France.1252") --> collation "French_France.1252" for encoding "UTF8" does not exist So, how do I specify the collation there? You first need to use "CREATE COLLATION", such as: b1=# CREATE COLLATION fr (locale='fr_FR'); CREATE COLLATION Thanks for the quick answer. It seems there is something missing with my installation: postgres=# CREATE COLLATION fr (locale='fr_FR'); ERROR: could not create locale "fr_FR": No such file or directory I used the ZIP distribution from EnterpriseDB (not the installer) so maybe the support for collations is simply not included with the "plain" binaries. postgres=# select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype --+---+---+--+-+--- default |11 |10 | -1 | | C|11 |10 | -1 | C | C POSIX|11 |10 | -1 | POSIX | POSIX (3 rows) Regards Thomas -- 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] Specifying column level collations
Thomas Kellerer writes: > My database is initialized as follows: > postgres=# select version(); > version > > PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit > (1 row) I gather this is on Windows. Windows has its own notion of locale names, which look like this: > lc_collate | German_Germany.1252 rather than the "de_DE" type of convention that's used by every other platform on the planet. There is not yet support in initdb for pre-populating pg_collation with Windows-style entries, so you will have to create your own entries. Presumably this would work for you, for instance: CREATE COLLATION german (locale='German_Germany.1252'); I don't know how to find out exactly what locale names are recognized by Windows, so can't help you much further than that. regards, tom lane -- 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] Specifying column level collations
Tom Lane wrote on 07.05.2011 18:48: Thomas Kellerer writes: My database is initialized as follows: postgres=# select version(); version PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit (1 row) I gather this is on Windows. Windows has its own notion of locale names, which look like this: lc_collate | German_Germany.1252 rather than the "de_DE" type of convention that's used by every other platform on the planet. There is not yet support in initdb for pre-populating pg_collation with Windows-style entries, so you will have to create your own entries. Presumably this would work for you, for instance: CREATE COLLATION german (locale='German_Germany.1252'); Ah! That did it. Thanks a lot. Regards Thomas -- 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] slightly OT - Using psql from Emacs with sql.el
On Thu, 05 May 2011 16:47:09 -0600, Rob Sargent wrote: [...] > Doesn't appear to. I use sql-mode alot/daily. The multiple prompts > never bothers me, though the output not starting at the left kind of > does. Then you might like this: http://www.emacswiki.org/emacs/SqlMode#toc3 bottom section -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
