[GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread Frank Millman
Hi all I want to model sales taxes in a flexible manner. I need one table to define tax categories (e.g. VAT) and a subsidiary table to define tax codes within each category (e.g. Standard Rate). CREATE TABLE tax_categories ( row_id SERIAL PRIMARY KEY, category NVARCHAR NOT NULL, de

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread amul sul
On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman wrote: > Hi all > > It is a bit ugly, because I have to use the ‘NVARCHAR code’ column from > tax_codes, not the primary key, but I think it would work. > NVARCHAR ? Are you using PostgreSQL as database server? Regards, Amul -- Sent via pgsql-gene

[GENERAL] COPY: row is too big

2017-01-02 Thread vod vos
Hi everyone, My postgresql is 9.61. When I copy data from csv file, a very long values for many columns (about 1100 columns). The errors appears: ERROR: row is too big: size 11808, maximum size 8160CONTEXT: COPY rius, line 2 rius is the table. I have searched the mailing lis

[GENERAL] Queries on very big table

2017-01-02 Thread Job
Hello guys and very good new year to everybody! We are now approaching some queries and statistics on very big table (about 180 millions of record). The table is partitioned by day (about ~3 Gb of data for every partition/day). We use Postgresql 9.6.1 I am experiencing quite important slowdown

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread Frank Millman
From: amul sul Sent: Monday, January 02, 2017 12:42 PM To: Frank Millman Cc: pgsql-general Subject: Re: [GENERAL] Difficulty modelling sales taxes > On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman wrote: > > Hi all > > > > It is a bit ugly, because I have to use the ‘NVARCHAR code’ column from

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread Melvin Davidson
On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman wrote: > > *From:* amul sul > *Sent:* Monday, January 02, 2017 12:42 PM > *To:* Frank Millman > *Cc:* pgsql-general > *Subject:* Re: [GENERAL] Difficulty modelling sales taxes > > > On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman > wrote: > > > > Hi al

Re: [GENERAL] COPY: row is too big

2017-01-02 Thread John McKown
On Mon, Jan 2, 2017 at 5:11 AM, vod vos wrote: > Hi everyone, > > My postgresql is 9.61. > > When I copy data from csv file, a very long values for many columns (about > 1100 columns). The errors appears: > > > ERROR: row is too big: size 11808, maximum size 8160CONTEXT: > > COPY rius, line 2 >

Re: [GENERAL] COPY: row is too big

2017-01-02 Thread Adrian Klaver
On 01/02/2017 03:11 AM, vod vos wrote: Hi everyone, My postgresql is 9.61. When I copy data from csv file, a very long values for many columns (about 1100 columns). The errors appears: My guess is you are tripping this: https://www.postgresql.org/about/ Maximum Columns per Table 250 -

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread Adrian Klaver
On 01/02/2017 06:38 AM, Melvin Davidson wrote: On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman mailto:fr...@chagford.com>> wrote: *From:* amul sul *Sent:* Monday, January 02, 2017 12:42 PM *To:* Frank Millman *Cc:* pgsql-general *Subject:* Re: [GENERAL] Difficulty modelling s

[GENERAL] Cannot recover from backup on barman

2017-01-02 Thread Alfredo Palhares
Hello everyone, I need to recover some partial data from a old backup. The recover went smoothly up until the time to start the server, I cannot start the server. LOG: invalid primary checkpoint record LOG: invalid secondary checkpoint record PANIC: could not locate a valid checkpoint record

Re: [GENERAL] COPY: row is too big

2017-01-02 Thread vod vos
The most of the data type are text or varhcar, and I use: COPY rius FROM "/var/www/test/aa.csv" WITH DELIMITER ';' ; And some the values in the csv file contain nulls, do this null values matter? Thanks. On 星期一, 02 一月 2017 03:11:14 -0800 vod vos wrote

Re: [GENERAL] Queries on very big table

2017-01-02 Thread Andy Colson
On 01/02/2017 05:23 AM, Job wrote: Hello guys and very good new year to everybody! We are now approaching some queries and statistics on very big table (about 180 millions of record). The table is partitioned by day (about ~3 Gb of data for every partition/day). We use Postgresql 9.6.1 I am ex

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread Melvin Davidson
On Mon, Jan 2, 2017 at 9:58 AM, Adrian Klaver wrote: > On 01/02/2017 06:38 AM, Melvin Davidson wrote: > >> >> On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman > > wrote: >> >> >> *From:* amul sul >> *Sent:* Monday, January 02, 2017 12:42 PM >> *To:* Frank Millm

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread David G. Johnston
On Mon, Jan 2, 2017 at 3:33 AM, Frank Millman wrote: > > CONSTRAINT _prod_tax_code_2 FOREIGN KEY (category_id, tax_code) > REFERENCES tax_codes (category_id, code)); > ​This is ​typical solution to this modelling problem. If you need to do other stuff (introduce redundancies) to make it pla

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread David G. Johnston
On Mon, Jan 2, 2017 at 7:38 AM, Melvin Davidson wrote: > > > *Next, DO NOT begin object names with underscores.* > ​This is only a bit arbitrary (though I do subscribe to it)...naming custom data types or tables with underscores is likely to introduce problems since an implementation detail of P

Re: [GENERAL] COPY: row is too big

2017-01-02 Thread Tom Lane
vod vos writes: > When I copy data from csv file, a very long values for many columns (about > 1100 columns). The errors appears: > ERROR: row is too big: size 11808, maximum size 8160 You need to rethink your table schema so you have fewer columns. Perhaps you can combine some of them into arr

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread Adrian Klaver
On 01/02/2017 08:02 AM, Melvin Davidson wrote: *First, there is no need to make row_id's when you already have a valid primary key. In a perfect world yes, but this is a world with ORM's as I found out the hard way: https://docs.djangoproject.com/en/1.

Re: [GENERAL] Difficulty modelling sales taxes

2017-01-02 Thread Vincent Veyron
On Mon, 2 Jan 2017 12:33:04 +0200 "Frank Millman" wrote: > > I want to model sales taxes in a flexible manner. I need one table to define > tax categories (e.g. VAT) and a subsidiary table to define tax codes within > each category (e.g. Standard Rate). > > CREATE TABLE tax_categories ( >

Re: [GENERAL] COPY: row is too big

2017-01-02 Thread vod vos
You know, the csv file was exported from other database of a machine, so I really dont want to break it for it is a hard work. Every csv file contains headers and values. If I redesign the table, then I have to cut all the csv files into pieces one by one. On 星期一, 02 一月 2017 08:21:29

Re: [GENERAL] COPY: row is too big

2017-01-02 Thread Adrian Klaver
On 01/02/2017 09:03 AM, vod vos wrote: > You know, the csv file was exported from other database of a machine, so > I really dont want to break it for it is a hard work. Every csv file > contains headers and values. If I redesign the table, then I have to cut > all the csv files into pieces one by

Re: [GENERAL] COPY: row is too big

2017-01-02 Thread Rob Sargent
> On Jan 2, 2017, at 10:13 AM, Adrian Klaver wrote: > >> On 01/02/2017 09:03 AM, vod vos wrote: >> You know, the csv file was exported from other database of a machine, so >> I really dont want to break it for it is a hard work. Every csv file >> contains headers and values. If I redesign the t

R: [GENERAL] Queries on very big table

2017-01-02 Thread Job
Hi, i am trying "materialized views" in Postgresql 9.6.1. Regarding Andy Calson suggestion: >>I do very similar thing, log all my webstats to PG, but querying millions of >>rows is always going to be slow. I use a summary table. They seems to work fine. One question: the materialized view is s

Re: [GENERAL] streaming replication protocol in Perl?

2017-01-02 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Torsten Förtsch asked: > is there a perl module that allows to speak the streaming replication > protocol? Not that I know of. > Can DBD::Pg do that anyhow? Nope. > I think I could just pipe from pg_recvlogical. But would be cool to have