[SQL] how to tell if column set on update
within a trigger need to know if the UPDATE statement set a column. the column might be set to the old value or a different value. (want to make sure the app is sending all necessary values) thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] unused columns in copy
is there a way for COPY FROM to ignore unused columns in CSV? in other words, if table t1 has columns c1, c2, and if csv has columns c1, c3, c2, could I do something like COPY t1( c1, null, c2 ) FROM 'file.csv' -- 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] Table inheritance problem
Gianvito Pio schrieb: Hello, I have 3 tables: persons, operators and persons_position. This is a semplified examples of their structures: CREATE TABLE persons (id varchar NOT NULL, CONSTRAINT "PK_Persons" PRIMARY KEY(id)); CREATE TABLE operators (id varchar NOT NULL, CONSTRAINT "PK_Operators" PRIMARY KEY(id)) INHERITS(persons); CREATE TABLE persons_position (id bigserial NOT NULL, person varchar NOT NULL); and then there is a FOREIGN KEY CONSTRAINT from persons_position.person TO persons.id. If I insert a tuple in operators...it results also in persons, but when I insert a tuple in persons_position, it says me I have violated the foreing key constraints. So it appears that the tuple really ISN'T in the persons table and the foreing key check fails. How could I solve it, keeping the inheritance there? Thanks Hi, sure it does throw that error. Your foreign key constraint is wrong. The check will look like this: test=# insert into persons_position values(2,'andy'); ERROR: insert or update on table "persons_position" violates foreign key constraint "persons_position_persons_fkey" DETAIL: Key (person)=(andy) is not present in table "persons". You could solve it by altering the table persons: test=# alter table persons add column person varchar(255); ALTER TABLE test=# insert into persons_position values(2,'andy'); INSERT 0 1 Or change the foreign key constraint in persons_position to id (be careful - the datatypes are different) Cheers Andy -- 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] unused columns in copy
On Mon, Jul 20, 2009 at 3:22 PM, chester c young wrote: > > is there a way for COPY FROM to ignore unused columns in CSV? > > in other words, if table t1 has columns c1, c2, and if csv has columns c1, > c3, c2, could I do something like > > COPY t1( c1, null, c2 ) FROM 'file.csv' No, but you can use pgloader which can easily deal with your problem and much more. http://pgfoundry.org/projects/pgloader/ -- 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] how to tell if column set on update
Hello, Le 20/07/09 15:19, chester c young a écrit : > within a trigger need to know if the UPDATE statement set a column. the > column might be set to the old value or a different value. > > (want to make sure the app is sending all necessary values) > > thanks > If the column to test is known -- e.g. column MyCol --, NEW.MyCol and OLD.MyCol -- respectively value of MyCol after UPDATE and value of MyCol before UPDATE -- can be compared. I may be wrong if you are looking for the column(s) that are really updated, not if such a column is updated or not. In this case, each column of the target table may be tested with their respective new and old values as well. Regards. -- nha / Lyon / France. -- 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] unused columns in copy
Hello, Le 21/07/09 0:59, Marcin Stępnicki a écrit : > On Mon, Jul 20, 2009 at 3:22 PM, chester c young > wrote: >> is there a way for COPY FROM to ignore unused columns in CSV? >> >> in other words, if table t1 has columns c1, c2, and if csv has columns c1, >> c3, c2, could I do something like >> >> COPY t1( c1, null, c2 ) FROM 'file.csv' > > No, but you can use pgloader which can easily deal with your problem > and much more. http://pgfoundry.org/projects/pgloader/ > Depending on PostgreSQL version, copy restrictions may be performed (see also documentation of PostgreSQL 8.4): http://www.postgresql.org/docs/8.4/interactive/sql-copy.html Assuming that a CSV file contains data with column headers c1, c2, and c3, the following statement would retrieve only columns c1 and c2 from this file to a table t1: COPY t1(c1, c2) FROM '/path/to/file.csv'; Regards. -- nha / Lyon / France. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Storing/sorting hex data in PostgreSQL
I am parsing a map file and storing the hi, mid and low byte values of symbol addresses in a comma separated value (.csv) file. I am then importing this data into a PostgreSQL database with the copy command. The problem is that I want to sort and query the database based on the hex addresses; from within another program using Npgsql (a .Net data server for PostgreSQL). How can I store hex values in PostgreSQL and sort and query them? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
