Thread: COL unique (CustomerID) plus COL unique (COUNT) inside CustomerID
Hello to all, after a period of silence from Debian, Courier, PHP and PostgreSQL I am half back and running into a problem... :-/ I need a table with an UNIQUE CustomerID which is working fine... ...BUT I need also a second column with a count, which must be UNIQUE inside the CustomerID. In clear this: CustID Count 1 1 1 2 1 3 2 1 2 2 3 1 3 2 3 3 ... How to do this? Thanks in avance -- Michelle Konzack ITSystems GNU/Linux Developer 0033-6-61925193
Hi,
you can use the UNIQUE constraint with two columns: UNIQUE(CustID, Count).
regards,
Szymon Lipiński
On 1 September 2016 at 11:28, Michelle Konzack <linux4michelle@gmail.com> wrote:
Hello to all,
after a period of silence from Debian, Courier, PHP and PostgreSQL I am
half back and running into a problem... :-/
I need a table with an UNIQUE CustomerID which is working fine...
...BUT I need also a second column with a count, which must be UNIQUE
inside the CustomerID.
In clear this:
CustID Count
1 1
1 2
1 3
2 1
2 2
3 1
3 2
3 3
...
How to do this?
Thanks in avance
--
Michelle Konzack ITSystems
GNU/Linux Developer 0033-6-61925193
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, 2016-09-01 at 12:28 +0300, Michelle Konzack wrote: > Hello to all, > > after a period of silence from Debian, Courier, PHP and PostgreSQL > I am > half back and running into a problem... :-/ > > I need a table with an UNIQUE CustomerID which is working fine... > > ...BUT I need also a second column with a count, > which must be UNIQUE > inside the CustomerID. > > In clear this: > > CustID Count > 1 1 > 1 2 > 1 3 > > 2 1 > 2 2 > > 3 1 > 3 2 > 3 3 > ... > > How to do this? > > Thanks in avance > > -- > Michelle Konzack ITSystems > GNU/Linux Developer 0033-6-61925193 > > Hi, (I wouldn't name a column "count"). SELECT MAX(count_er) FROM my_customer_table WHERE cust_id = $1; Add one to the result. Do INSERT. As SELECT MAX is a GROUP function it will return zero if the cust_id doesn't exist. HTH, Rob
Michelle Konzack <linux4michelle@gmail.com> wrote: > Hello to all, > > after a period of silence from Debian, Courier, PHP and PostgreSQL I am > half back and running into a problem... :-/ > > I need a table with an UNIQUE CustomerID which is working fine... > > ...BUT I need also a second column with a count, which must be UNIQUE > inside the CustomerID. > > In clear this: > > CustID Count > 1 1 > 1 2 > 1 3 > > 2 1 > 2 2 > > 3 1 > 3 2 > 3 3 > ... > > How to do this? don't store the Count-column and using row_number() over (partition by CustId) instead? Btw.: Greetings, how are you? ;-) Regards, Andreas Kretschmer -- Andreas Kretschmer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, Sep 1, 2016 at 4:28 AM, Michelle Konzack <linux4michelle@gmail.com> wrote: > I need a table with an UNIQUE CustomerID which is working fine... > > ...BUT I need also a second column with a count, which must be UNIQUE > inside the CustomerID. Just to be clear, you probably have a customer table with "CustIomerId" as its key and are talking about a "child"of that where you want a 2-column key? > In clear this: > > CustID Count > 1 1 > 1 2 > 1 3 > > 2 1 > 2 2 > > 3 1 > 3 2 > 3 3 > ... > > How to do this? If, for example, the child table is a list of customer contacts, you might add a "LastContactNo" column to the customer table, defaulting to zero on customer insert, and which you increment to get values for the second key column in the contact table. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company