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

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

From
Karen Goh
Date:
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



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

From
Rob Sargent
Date:

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


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

From
Karen Goh
Date:
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 - differ from 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 for DESKTOP 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 have 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 table not 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 then your pgadmin connection settings need the name of the database to use. Your table needs to be fully qualified by adding the 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 easier for you.


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

From
Rob Sargent
Date:

> 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 
>



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

From
Karen Goh
Date:

Hi Rob,

Your suggestion to use create table schema.table doesn't work and I have already tried this and exhausted my own solving skill before I posted it here.

And when the table is not there, what is the point of using Alter statement ?

By ‘under postgres’ do you mean owned by Postgres in the public schema or in the postgres database - Please see attached picture - that PGAdmin 4 is using postgres - it is the same username as what I have created and as an access when using runpsql.batch.

Oyeah...does anybody know how do I access psql in Winodws 10 ? So far, no reply here... Please let me know if I should write to the committee or ?

On Saturday, September 7, 2019, 11:51:23 PM GMT+8, Rob Sargent <robjsargent@gmail.com> wrote:




> 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 - differ from 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 for DESKTOP 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 have 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 table not 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 then your pgadmin connection settings need the name of the database to use. Your table needs to be fully qualified by adding the 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 easier for 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 neither windows nor pgadmin

>
Attachment

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

From
Rob Sargent
Date:


On Sep 7, 2019, at 10:11 PM, Karen Goh <karenworld@yahoo.com> wrote:


Hi Rob,

Your suggestion to use create table schema.table doesn't work and I have already tried this and exhausted my own solving skill before I posted it here.

And when the table is not there, what is the point of using Alter statement ?

By ‘under postgres’ do you mean owned by Postgres in the public schema or in the postgres database - Please see attached picture - that PGAdmin 4 is using postgres - it is the same username as what I have created and as an access when using runpsql.batch.

Oyeah...does anybody know how do I access psql in Winodws 10 ? So far, no reply here... Please let me know if I should write to the committee or ?

On Saturday, September 7, 2019, 11:51:23 PM GMT+8, Rob Sargent <robjsargent@gmail.com> wrote:




> 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 - differ from 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 for DESKTOP 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 have 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 table not 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 then your pgadmin connection settings need the name of the database to use. Your table needs to be fully qualified by adding the 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 easier for 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 neither windows nor pgadmin

>
<picture to show postges in PGAdmin4.jpg>
Karen
From the screenshot I think membership is one of two databases on that server. The other db is called postgres (made by Postgres in almost every database you create) and I suspect your table definition is in the public schema of postgres database. membership has only the default schema which 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

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

From
Karen Goh
Date:
Hi Rob,

I refer to your comment -
I think you need to add “membership” to your pgadmin connection string.

How do I add in the pgadmin connection string at pgAdmin4 ?

Before that, I was creating table using the sql script in version 10 and there was no problem at all.

So, it really baffled me it is not working out this time.

And tomorrow I have an important demo and it seems that I have to take out this table in my app to show...which will be less impressive in a not so impressive app that i have created ....:(

I have made some more screenshots which shows some more error when I tried to retrieve the table under the table postgres which is already created not it is not showing the table.

Please help,

Tks & regards,
Karen

On Monday, September 9, 2019, 10:30:04 AM GMT+8, Rob Sargent <robjsargent@gmail.com> wrote:




On Sep 7, 2019, at 10:11 PM, Karen Goh <karenworld@yahoo.com> wrote:


Hi Rob,

Your suggestion to use create table schema.table doesn't work and I have already tried this and exhausted my own solving skill before I posted it here.

And when the table is not there, what is the point of using Alter statement ?

By ‘under postgres’ do you mean owned by Postgres in the public schema or in the postgres database - Please see attached picture - that PGAdmin 4 is using postgres - it is the same username as what I have created and as an access when using runpsql.batch.

Oyeah...does anybody know how do I access psql in Winodws 10 ? So far, no reply here... Please let me know if I should write to the committee or ?

On Saturday, September 7, 2019, 11:51:23 PM GMT+8, Rob Sargent <robjsargent@gmail.com> wrote:




> 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 - differ from 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 for DESKTOP 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 have 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 table not 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 then your pgadmin connection settings need the name of the database to use. Your table needs to be fully qualified by adding the 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 easier for 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 neither windows nor pgadmin

>
<picture to show postges in PGAdmin4.jpg>
Karen
From the screenshot I think membership is one of two databases on that server. The other db is called postgres (made by Postgres in almost every database you create) and I suspect your table definition is in the public schema of postgres database. membership has only the default schema which 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
Attachment

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

From
Steve Midgley
Date:


On Mon, Sep 9, 2019 at 6:07 AM Karen Goh <karenworld@yahoo.com> wrote:
Hi Rob,

I refer to your comment -
I think you need to add “membership” to your pgadmin connection string.

How do I add in the pgadmin connection string at pgAdmin4 ?

Before that, I was creating table using the sql script in version 10 and there was no problem at all.

So, it really baffled me it is not working out this time.

And tomorrow I have an important demo and it seems that I have to take out this table in my app to show...which will be less impressive in a not so impressive app that i have created ....:(

I have made some more screenshots which shows some more error when I tried to retrieve the table under the table postgres which is already created not it is not showing the table.

Please help,

Your second screenshot seems to indicate your connection between PgAdmin and Pg 11 Server is broken in some ways. If I were taking over the keyboard at your workstation and had a critical deadline for tomorrow, here are some steps I would take:
  • Dump my databases, if I can
  • Backup whatever work I have on a usb stick (create sql files, etc)
  • Uninstall Pg Server and Pg Admin
  • Reinstall and see if things start working better
Then on more detailed level, my understanding of Postgres is that when you prefix a word in front of a table you are referring to a schema so
create table membership.parents...

Will never work unless you first create the schema that parents sit within:
create schema membership

Is it possible your problem is that you aren't creating membership schema in your database? (Postgres treats schemas in much the same way that other database engines treat actual databases).

Finally, if you are desperate for a working Pg SQL server over the next 24 hours consider spinning up an AWS RDS Pg instance and connecting PgAdmin to that - that way you know the database is reliable and correctly configured for sure. There might even be a "free tier" that gives you free service for that time frame..

Steve

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

From
Rob Sargent
Date:

> 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




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

From
Karen Goh
Date:

Hi Steve,

What I did was I manually created the table and then pgAdmin4 then allowed me to use a script to insert the columns !

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

Another problem is that I found most 'developers' are using psql and don't know what I am talking when relating PGAdmin4 problem but I just can't get PSQL to work...
On Monday, September 9, 2019, 11:08:18 PM GMT+8, Steve Midgley <science@misuse.org> wrote:




On Mon, Sep 9, 2019 at 6:07 AM Karen Goh <karenworld@yahoo.com> wrote:
Hi Rob,

I refer to your comment -
I think you need to add “membership” to your pgadmin connection string.

How do I add in the pgadmin connection string at pgAdmin4 ?

Before that, I was creating table using the sql script in version 10 and there was no problem at all.

So, it really baffled me it is not working out this time.

And tomorrow I have an important demo and it seems that I have to take out this table in my app to show...which will be less impressive in a not so impressive app that i have created ....:(

I have made some more screenshots which shows some more error when I tried to retrieve the table under the table postgres which is already created not it is not showing the table.

Please help,


Your second screenshot seems to indicate your connection between PgAdmin and Pg 11 Server is broken in some ways. If I were taking over the keyboard at your workstation and had a critical deadline for tomorrow, here are some steps I would take:
  • Dump my databases, if I can
  • Backup whatever work I have on a usb stick (create sql files, etc)
  • Uninstall Pg Server and Pg Admin
  • Reinstall and see if things start working better
Then on more detailed level, my understanding of Postgres is that when you prefix a word in front of a table you are referring to a schema so
create table membership.parents...

Will never work unless you first create the schema that parents sit within:
create schema membership

Is it possible your problem is that you aren't creating membership schema in your database? (Postgres treats schemas in much the same way that other database engines treat actual databases).

Finally, if you are desperate for a working Pg SQL server over the next 24 hours consider spinning up an AWS RDS Pg instance and connecting PgAdmin to that - that way you know the database is reliable and correctly configured for sure. There might even be a "free tier" that gives you free service for that time frame..

Steve

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

From
Steve Midgley
Date:
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.

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,
);

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

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

From
Karen Goh
Date:



Sent from Yahoo Mail for iPhone

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

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

From
Steve Midgley
Date:


On Thu, Sep 12, 2019 at 1:17 PM Karen Goh <karenworld@yahoo.com> wrote:



Sent from Yahoo Mail for iPhone

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


Send us the "DDL" for your table and we might be able to help. PgAdmin4 should be able to give you the create table statement that would create the table that is autoincrementing without a serial datatype that you can share with us, and probably someone can answer that question. It is possible to make fields autoincrement using other methods, iirc. Steve