Re: Question about copy from with timestamp format
От | Adrian Klaver |
---|---|
Тема | Re: Question about copy from with timestamp format |
Дата | |
Msg-id | 55BA5749.50105@aklaver.com обсуждение исходный текст |
Ответ на | Re: Question about copy from with timestamp format (Sherrylyn Branchaw <sbranchaw@gmail.com>) |
Список | pgsql-general |
On 07/30/2015 08:59 AM, Sherrylyn Branchaw wrote: > From here: > > http://www.postgresql.org/docs/9.4/interactive/sql-copy.html > > "COPY can only be used with plain tables, not with views. However, you > can write COPY (SELECT * FROM viewname) TO ...." > > Right, so you can COPY FROM a view, but not, as far as I can tell, TO a > view, unless Alban found a workaround. Just to be clear COPY FROM is from file to table and COPY TO is from table/view to file. > On Thu, Jul 30, 2015 at 11:48 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 07/30/2015 08:44 AM, Sherrylyn Branchaw wrote: > > I was thinking that perhaps an updatable view might do the trick? > > Interesting idea! Are you able to get it to work? I keep getting > 'ERROR: > cannot copy to view "view_ts_test"' even before my trigger fires. > Inserting, though, works fine. > > > From here: > > http://www.postgresql.org/docs/9.4/interactive/sql-copy.html > > "COPY can only be used with plain tables, not with views. However, > you can write COPY (SELECT * FROM viewname) TO ...." > > > Still curious why the triggers I'm writing won't fire before my > statement errors out on copying to a view, or inserting an > out-of-range > timestamp, when the trigger would resolve all the illegal > operations if > it just fired first. > > > > On Thu, Jul 30, 2015 at 5:57 AM, Alban Hertroys > <haramrae@gmail.com <mailto:haramrae@gmail.com> > <mailto:haramrae@gmail.com <mailto:haramrae@gmail.com>>> wrote: > > > > On 30 Jul 2015, at 2:27, Sherrylyn Branchaw > <sbranchaw@gmail.com <mailto:sbranchaw@gmail.com> > <mailto:sbranchaw@gmail.com <mailto:sbranchaw@gmail.com>>> wrote: > > > > Based on your PS asking about data types and commenting > that you don't want to put hour in a separate column, it sounds > like this is a brand-new table you're creating. If so, and if > this is a one-time COPY operation, you can create a text column > for the initial import. Then after you're done importing, you > can execute > > > > ALTER TABLE ts_test ALTER COLUMN ts_fld TYPE TIMESTAMP > USING (to_timestamp(ts_fld, 'YYYYMMDDHH24')); > > > > to convert the format of the imported data to a > timestamp. Then you're set. > > > > If there will be ongoing imports of more files like this, > though, you'll need the intermediate table solution offered by > Adrian. > > Or keep both columns and update those where the text-column > is NOT > NULL and the timestamp column is NULL. > > > I was going to suggest a trigger, but it turns out that > the data type checking happens even before the BEFORE trigger > fires, so you don't get a chance to massage your data before > actually inserting it. I got 'ERROR: date/time field value out > of range: "2015072913 <tel:2015072913> <tel:2015072913 > <tel:2015072913>>"' before the trigger even fired. I > wonder if that's deliberate? I was able to implement a > workaround by > adding a raw_ts_fld column of type text, but an extra > column might > be too ugly for you relative to a temp table, I don't know. > > I was thinking that perhaps an updatable view might do the > trick? > > You would need to create a view with the timestamp column > converted > to text in the format in your CSV file. Next you add an > INSERT rule > that does the conversion from text to timestamp and inserts > the row > in the actual table. Finally, you use the view in the COPY > statement > instead of the table. > Added bonus, you can now also use the view to export your > table to > the same CSV format. > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find there is no forest. > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Sherrylyn BranchawДата:
Сообщение: Re: Question about copy from with timestamp format