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

[Glen Huang]

Good to know I’m not doing something stupid. Thanks.

> On 30 Sep 2017, at 8:51 PM, Berend Tober <bto...@computer.org> 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 
> > 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 after reading the doc it seems turning on not null constraint 
> > requires not columns
> > contain null value, so looks like it won’t work.
> >
> > My current approach is to not set the not null constraint in the table and 
> > use a before update
> > trigger to manually raise exception when the column is null. But it doesn’t 
> > seem as elegant.
> >
> > Is there a better way?
> >
> 
> Sounds to me like a BEFORE UPDATE trigger is exactly the way to handle this. 
> Rejecting invalid data input values is an ideal use case for such a facility.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Glen Huang]

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 after reading the doc it seems turning on not 
null constraint requires not columns contain null value, so looks like it won’t 
work.

My current approach is to not set the not null constraint in the table and use 
a before update trigger to manually raise exception when the column is null. 
But it doesn’t seem as elegant.

Is there a better way?

Regards,
Glen

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Glen Huang]

Thank you! It works.

I’d very much like to know why this solution works. Could you please elaborate 
a bit more? I can’t quite understand what you mean by "can’t store the 
constant”. Since a view can’t store data, do you mean it can’t have a query 
that contains a constant? But isn’t 'public'  a constant too?

Also what’s the difference between ‘rolename'::regrole and 
'rolename'::text::regrole? Is it correct that in the former case, the 
conversion between the constant string and regrole happens in the sql parse 
time whereas in the latter case it happens in 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 <t...@sss.pgh.pa.us> wrote:
> 
> Glen Huang <hey...@gmail.com> writes:
>> 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
> 
> It's not that you can't compare it, it's that you can't store the
> constant, for arcane reasons having to do with how the view's dependency
> on the role name would need to be represented.
> 
> You can work around it like this:
> 
> CREATE TEMP VIEW user_schema AS
>SELECT nspname AS name FROM pg_namespace
>WHERE nspname = 'public' OR nspowner = 'rolename'::text::regrole;
> 
> Here, the stored constant is just a string of type text, and the lookup
> in pg_authid will happen at runtime (and throw an error then, if you've
> dropped the role).
> 
>   regards, tom lane



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Glen Huang]

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 directly, I get the correct result.

Does that mean I can’t compare role name this way in view? Should I use join to 
work around it?

Thanks.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Glen Huang]

> 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: > 
> > https://stackoverflow.com/questions/45187113 didn't get any answers yet.
> I've posted a comment to SO; basically what I'm saying is yes, use a 
> transaction constraint trigger to enforce your requirement.
> 
> This previous question has an example of such a trigger:
> 
> https://stackoverflow.com/a/37277421/1980653
> 
> 
> HTH & best regards
> 
>   -hannes

Using a trigger sounds like the right approach. I’ll try it out. Thanks guys.

Hannes, thanks for posting an answer to SO, I’ve accepted it. :)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Glen Huang]

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 
 didn't get any answers yet.

Am I looking at the wrong direction? Should such feature be implemented with 
constraints?

Thanks.

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

[Glen Huang]

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 to be sent to a client, 
should I use json_agg or jsonb_agg?

Thanks.

Glen

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Questions regarding JSON processing

[Glen Huang]

@Pavel

Thanks for bringing PLV8 to my attention. Wasn't aware of it. Sounds like the 
right tool to for the job. I'll try it out. Do you think it makes sense to use 
PLV8 to also generate JSON? Can it beat SQL?

Good to know functions are executed under transaction, I think that should be 
enough for me.

@John

Only data is inside JSON, but it does have keys like "added", "updated" that 
contain objected to be added and updated inside it. I think this kind of 
branching should be safe though?

On Wed, Apr 26, 2017 at 12:41 PM, Pavel Stehule <pavel.steh...@gmail.com 
<mailto:pavel.steh...@gmail.com>> wrote:


2017-04-26 6:21 GMT+02:00 Glen Huang <hey...@gmail.com 
<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 clients. I believe the best way is to ask 
PostgreSQL to generate the JSON string and then pass that directly to clients, 
instead of making multiple queries to construct the JSON and then send it,  
which doesn't seem optimal. Is that the case?

For updating db using JSON requests from clients, that I'm not so sure. Should 
I directly pass the request JSON to PostgreSQL and ask it to parse this JSON 
and execute a transaction all by itself, or should I parse it in the server and 
generate the transaction SQL and execute that on PostgreSQL? The former sounds 
optimal, but I'm not sure if PostgreSQL is able to walk a JSON structure and 
run a transaction along the way? Should I do it with PL/pgSQL? It seems 
functions can't execute a transaction?

The PLpgSQL is static language and is good for static processing JSON doc, but 
it is unfit for iteration over any generic nested document. You can use 
PLPerlu, PLPythonu. Lot of people uses PLV8 for JSON processing.

The functions in PostgreSQL are executed under transaction - you cannot to 
explicitly control transaction, but there are possibility to implicitly handle 
transactions with exception handling. There is workaround via dblink to emulate 
autonomous transactions.

Regards

Pavel
 

Would like to hear some thoughts on this. Thanks.

Glen

[GENERAL] Questions regarding JSON processing

[Glen Huang]

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 to
clients, instead of making multiple queries to construct the JSON and then
send it,  which doesn't seem optimal. Is that the case?

For updating db using JSON requests from clients, that I'm not so sure.
Should I directly pass the request JSON to PostgreSQL and ask it to parse
this JSON and execute a transaction all by itself, or should I parse it in
the server and generate the transaction SQL and execute that on PostgreSQL?
The former sounds optimal, but I'm not sure if PostgreSQL is able to walk a
JSON structure and run a transaction along the way? Should I do it with
PL/pgSQL? It seems functions can't execute a transaction?

Would like to hear some thoughts on this. Thanks.

Glen

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

[Glen Huang]

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 <haram...@gmail.com> wrote:
> 
>> 
>> On 22 Mar 2017, at 17:54, Glen Huang <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,
>> 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 be allowed.
>> 
>> Is there a general solution to this problem?
> 
> Does the order of the values of (obj1, obj2, obj3) in relationship matter? If 
> not, you could swap them around on INSERT/UPDATE to be in sorted order. I'd 
> probably go with a BEFORE INSERT OR UPDATE trigger.
> 
> In addition, to prevent unsorted entry, on obj2 add CHECK (obj2 > obj1) and 
> on obj3 add CHECK (obj3 > obj2).
> 
> Now you can create a normal PK or unique key on (obj1, obj2, obj3) as the 
> order of their values is not variable anymore.
> 
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.

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

[Glen Huang]

Thanks.

Didn't realize it could be implemented with a exclusion constraint. The 
comparing between any two row definitely sounds like the right direction. But 
I'm still having a hard time figuring out how i should write the 
`exclude_element WITH operator` part, which I think, should detect if specified 
columns consist of the same items, regardless the order? could 
`exclude_element` contains multiple columns? (from the syntax it looks like 
it's impossible) And is there such an operator to compare multiple columns?

> On 23 Mar 2017, at 1:04 AM, David G. Johnston <david.g.johns...@gmail.com> 
> wrote:
> 
> On Wed, Mar 22, 2017 at 9:54 AM, Glen Huang <hey@gmail.com 
> <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,
>   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 be allowed.
> 
> Is there a general solution to this problem?
> 
> Sorry if the question is too basic, but I couldn't find the answer in the 
> doc, at least not in the chapter on unique index.
> 
> The most direct option to consider is a exclusion constraint.
> 
> https://www.postgresql.org/docs/current/static/ddl-constraints.html 
> <https://www.postgresql.org/docs/current/static/ddl-constraints.html> (bottom 
> of page)
> 
> David J.

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

[Glen Huang]

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 be allowed.

Is there a general solution to this problem?

Sorry if the question is too basic, but I couldn't find the answer in the doc, 
at least not in the chapter on unique index.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general