[GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

Hello, If I have a table like CREATE TABLE relationship ( obj1 INTEGER NOT NULL REFERENCES object, obj2 INTEGER NOT NULL REFERENCES object, obj3 INTEGER NOT NULL REFERENCES object, ... ) And I want to constrain that if 1,2,3 is already in the table, rows like 1,3,2 or 2,1,3 shouldn't b

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

t; wrote: > > On Wed, Mar 22, 2017 at 9:54 AM, Glen Huang <mailto:hey@gmail.com>> wrote: > Hello, > > If I have a table like > > CREATE TABLE relationship ( > obj1 INTEGER NOT NULL REFERENCES object, > obj2 INTEGER NOT NULL REFERENCES object, > obj

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

Yes, the order doesn't matter, and this approach sounds like a good idea. I'll try it out, thanks. > On 23 Mar 2017, at 3:56 PM, Alban Hertroys wrote: > >> >> On 22 Mar 2017, at 17:54, Glen Huang wrote: >> >> Hello, >> >> If I have a t

[GENERAL] Questions regarding JSON processing

Hi all, I have a RESTful API server that sends and receives JSON strings. I'm wondering what might be the best way to leverage PostgreSQL's JSON capability. For sending JSON responses to clients. I believe the best way is to ask PostgreSQL to generate the JSON string and then pass that directly t

Re: [GENERAL] Questions regarding JSON processing

.@gmail.com>> wrote: 2017-04-26 6:21 GMT+02:00 Glen Huang mailto:hey...@gmail.com>>: Hi all, I have a RESTful API server that sends and receives JSON strings. I'm wondering what might be the best way to leverage PostgreSQL's JSON capability. For sending JSON responses to c

[GENERAL] json_agg doesn't generate the smallest json possible?

Hi all, I'm trying to use json_agg to generate some json array from the rows (which contain some json columns) I selected. But it seems the returned json contains some extraneous whitespace characters. Any efficient way I can get rid of them? Also a quick question, if the final result is meant

[GENERAL] Is it possible to define a constraint based on the values in other rows in the current table?

Hi, I'd like to enforce that in a transaction, after a couple inserts & updates, a particular column has continuous values like 1, 2, 3, and never any gaps. Is it possible to do? I gave a concrete example here: https://stackoverflow.com/questions/45187113

Re: [GENERAL] Is it possible to define a constraint based on the values in other rows in the current table?

> On 20 Jul 2017, at 5:26 AM, Hannes Erven wrote: > > Hi Glen, > > >> I'd like to enforce that in a transaction, after a couple inserts & > >> updates, a particular column has continuous values like 1, 2, 3, and > > never any gaps. Is it possible to do?> > I gave a concrete example here: >

[GENERAL] Not possible to compare regrole in a view query?

I have this simple view definition: CREATE TEMP VIEW user_schema AS SELECT nspname AS name FROM pg_namespace WHERE nspname = 'public' OR nspowner = ‘rolename'::regrole; But it fails to create the view by complaining: constant of the type "regrole" cannot be used here If I run the query

Re: [GENERAL] Not possible to compare regrole in a view query?

the run time? (Probably wrong, since looks like it can happen in the parse time too) Why it could work around the "cannot be used here” issue? Thanks. > On 5 Sep 2017, at 8:07 PM, Tom Lane wrote: > > Glen Huang writes: >> I have this simple view definition: >> CREA

[GENERAL] Best way to allow column to initially be null?

Hi, I’m trying to make a column have these properties: 1. When a row is inserted, this column is allowed to be null. 2. When the row is updated, no null can be assigned to it this column. I initially thought I can drop the not null constraint before insertion and turn it back on after that, but

Re: [GENERAL] Best way to allow column to initially be null?

Good to know I’m not doing something stupid. Thanks. > On 30 Sep 2017, at 8:51 PM, Berend Tober wrote: > > Glen Huang wrote: > > I’m trying to make a column have these properties: > > > > 1. When a row is inserted, this column is allowed to be null. 2. When the