Thread: How do I enabled Windows 10 to be able to run PSQL etc

Hello experts,

Ever since I found out I have a different versions of Postgresql residing in my PC, I have deleted the old version - 10
and7 altogether and installed version 11.
 

So, all look good except that I can't use PSQL at all.

Then, I found out that there is a security tied to using PSQL in Windows 10. However, I tried to alter the setting for
DESKTOPuser to Full-Control I can't find a way to do it. Neither can I find anything from the internet.
 

Please advise how do I alter the setting so that I can use PSQL freely.  

I had tried to use runpsql.batch after reading a Stackoverflow user advice.

But, when I tried to do some psql comments, it doesn't work.

What is that comment you might ask - which is my next problem - this comment is to allow me to merge a table which I
havecreated in PGAdmin 4 into a schema - membership which has 3 tables there already.
 

The thing is that I found that the table which I have created, it is not able to appear under the schema - memberships.
But, when I used runpsql.batch, and use /l , it will show up as a table but under postgres.
 

Now, in my PGAdmin 4, I did not create a user name either, it is also using postgres as user name so why is the table
notappear as a table even after I refreshed.
 

So, I hope someone can tell me the solutions to the above problem.

Thanks & regards,
Karen

> On Sep 7, 2019, at 5:26 AM, Karen Goh <karenworld@yahoo.com> wrote:
>
> Hello experts,
>
> Ever since I found out I have a different versions of Postgresql residing in my PC, I have deleted the old version -
10and 7 altogether and installed version 11. 
>
> So, all look good except that I can't use PSQL at all.
>
> Then, I found out that there is a security tied to using PSQL in Windows 10. However, I tried to alter the setting
forDESKTOP user to Full-Control I can't find a way to do it. Neither can I find anything from the internet. 
>
> Please advise how do I alter the setting so that I can use PSQL freely.
>
> I had tried to use runpsql.batch after reading a Stackoverflow user advice.
>
> But, when I tried to do some psql comments, it doesn't work.
>
> What is that comment you might ask - which is my next problem - this comment is to allow me to merge a table which I
havecreated in PGAdmin 4 into a schema - membership which has 3 tables there already. 
>
> The thing is that I found that the table which I have created, it is not able to appear under the schema -
memberships. But, when I used runpsql.batch, and use /l , it will show up as a table but under postgres. 
>
> Now, in my PGAdmin 4, I did not create a user name either, it is also using postgres as user name so why is the table
notappear as a table even after I refreshed. 
>
> So, I hope someone can tell me the solutions to the above problem.
>
> Thanks & regards,
> Karen
>
>
Without a user everything will be owned by Postgres account but that’s ok.
By ‘under postgres’ do you mean owned by Postgres in the public schema or in the postgres database. If the latter then
yourpgadmin connection settings need the name of the database to use. Your table needs to be fully qualified by adding
theschema name: 
create table schema.table
You can use the alter table statement to reset the schema for the existing table or drop table/create table if that’s
easierfor you.  

> On Sep 7, 2019, at 8:04 AM, Karen Goh <karenworld@yahoo.com> wrote:
>
> Hi Rob,
>
> At pgAdmin 4, it shows I am in postgres/postgres@PostgreSQL11
>
> I have created a table there using CREATE public.TABLE follows by name blah..
>
> When I executed the SQL, it said successful but it didn't appear together in the Schema - membership.
>
> So, I tried to use membership.TableName but it doesn't allow so I thought maybe this is the way Postgesql work -
differfrom MYSQL. 
>
> So, now, How do I add in the table that I want?
>
> So, the thing is that it is already showing me I am in postgres, how do I alter it then ? Alter it to what ?
>
> On Saturday, September 7, 2019, 10:07:56 PM GMT+8, Rob Sargent <robjsargent@gmail.com> wrote:
>
>
>
>
> > On Sep 7, 2019, at 5:26 AM, Karen Goh <karenworld@yahoo.com> wrote:
> >
> > Hello experts,
> >
> > Ever since I found out I have a different versions of Postgresql residing in my PC, I have deleted the old version
-10 and 7 altogether and installed version 11. 
> >
> > So, all look good except that I can't use PSQL at all.
> >
> > Then, I found out that there is a security tied to using PSQL in Windows 10. However, I tried to alter the setting
forDESKTOP user to Full-Control I can't find a way to do it. Neither can I find anything from the internet. 
> >
> > Please advise how do I alter the setting so that I can use PSQL freely.
> >
> > I had tried to use runpsql.batch after reading a Stackoverflow user advice.
> >
> > But, when I tried to do some psql comments, it doesn't work.
> >
> > What is that comment you might ask - which is my next problem - this comment is to allow me to merge a table which
Ihave created in PGAdmin 4 into a schema - membership which has 3 tables there already. 
> >
> > The thing is that I found that the table which I have created, it is not able to appear under the schema -
memberships.But, when I used runpsql.batch, and use /l , it will show up as a table but under postgres. 
> >
> > Now, in my PGAdmin 4, I did not create a user name either, it is also using postgres as user name so why is the
tablenot appear as a table even after I refreshed. 
> >
> > So, I hope someone can tell me the solutions to the above problem.
> >
> > Thanks & regards,
> > Karen
>
> >
> >
> Without a user everything will be owned by Postgres account but that’s ok.
> By ‘under postgres’ do you mean owned by Postgres in the public schema or in the postgres database. If the latter
thenyour pgadmin connection settings need the name of the database to use. Your table needs to be fully qualified by
addingthe schema name: 
> create table schema.table
> You can use the alter table statement to reset the schema for the existing table or drop table/create table if that’s
easierfor you. 
>
Sorry Karen I’m not at my desk or I could give you url. You’ll have to google Postgres alter table command. I use
neitherwindows nor pgadmin 
>

> Karen
> From the screenshot I think membership is one of two databases on that server. The other db is called postgres (made
byPostgres in almost every database you create) 

sorry, typo.   'every database' should be 'every instance (or installation)'

> and I suspect your table definition is in the public schema of postgres database. membership has only the default
schemawhich is called public. I can’t identify any table definitions in the screenshot.  
> I think you need to add “membership” to your pgadmin connection string

On Thursday, September 12, 2019, 8:07 PM, Steve Midgley <science@misuse.org> wrote:

On Thu, Sep 12, 2019 at 11:50 AM Karen Goh <karenworld@yahoo.com> wrote:

Anyway, now my problem is that i get a

org.postgresql.util.PSQLException: ERROR: null value in column "parent_id" violates not-null constraint

I'd like to know since I have already made parent_id a primary key, do I need to put a idenity as ALWAYS and increment by 1 ?

cos another table I had used pgAdmin4 to define it as identity, ALWAYS, increment 1 and it works well....

so what is the difference between identity and PRIMARY KEY ?

and if I follow the other table definition for id and put in the ALWAYS, it will give me another problem...that the generated_id doesn't get inserted even though it should not

Hi Karen,

Please bottom post on this forum - it's uncommon these days, but the custom on this email list to post replies at the bottom of the email.

Regarding your question about nulls and primary keys -- yes you want to define, where it makes sense, to use autoincrementing primary keys, if you want the system to manage your keys. Note that autoincrementing IDs, if exposed to the public via the web or similar, could create some security issues.

Hi Steve,

Sorry yahoo mail is giving me lots of problems in typing at the bottom - the convention. 

I would like to confirm how to make an ID  in PostgreSQL auto increment like MySQL?

And since I need to have generated key to be retrieved before inserting into the join table, if I don’t do it via web how can I do it then? 

But in many cases it makes a lot of sense to use them. To create a primary key that autoincrements, use the "serial" or "bigserial" datatype. Combined with a primary key (that enforces "not null" among other things), your create table would look like this:

CREATE TABLE sample ( id              SERIAL PRIMARY KEY,
);

Why is my another table, without making the id serial it is auto-incrementing?

I have not tested it, but I'd guess you should be able to alter a table that has an integer primary key and convert it to a serial. I hope that's helpful! Steve