[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: How do I enabled Windows 10 to be able to run PSQL etc
From: Steve Midgley <science () misuse ! org>
Date: 2019-09-12 12:22:18
Message-ID: CAJexoSLpKMS12WHe7T5Fujvw_umZ5vdt6v-cCxRQfObaHZYo7g () mail ! gmail ! com
[Download RAW message or body]
On Thu, Sep 12, 2019 at 1:17 PM Karen Goh <karenworld@yahoo.com> wrote:
>
>
>
> Sent from Yahoo Mail for iPhone
> <https://overview.mail.yahoo.com/?.src=iOS>
>
> 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
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Thu, Sep 12, 2019 at 1:17 PM Karen Goh <<a \
href="mailto:karenworld@yahoo.com">karenworld@yahoo.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div> <br><br><br><a \
href="https://overview.mail.yahoo.com/?.src=iOS" target="_blank">Sent from Yahoo Mail \
for iPhone</a><br><br><p class="gmail-m_-2527352593038415746yahoo-quoted-begin" \
style="font-size:15px;color:rgb(113,95,250);padding-top:15px;margin-top:0px">On \
Thursday, September 12, 2019, 8:07 PM, Steve Midgley <<a \
href="mailto:science@misuse.org" target="_blank">science@misuse.org</a>> \
wrote:</p><blockquote class="gmail-m_-2527352593038415746iosymail"><div \
id="gmail-m_-2527352593038415746yiv0946157834"><div><div dir="ltr"><div \
class="gmail-m_-2527352593038415746yiv0946157834gmail_quote"><div \
class="gmail-m_-2527352593038415746yiv0946157834gmail_attr" dir="ltr">On Thu, Sep 12, \
2019 at 11:50 AM Karen Goh <<a rel="nofollow" shape="rect" \
href="mailto:karenworld@yahoo.com" target="_blank">karenworld@yahoo.com</a>> \
wrote:<br clear="none"></div><blockquote \
class="gmail-m_-2527352593038415746yiv0946157834gmail_quote" style="margin:0px 0px \
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div> \
Anyway, now my problem is that i get a <br clear="none"><br \
clear="none">org.postgresql.util.PSQLException: ERROR: null value in column \
"parent_id" violates not-null constraint<br clear="none"><br \
clear="none">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 ?<br clear="none"><br \
clear="none">cos another table I had used pgAdmin4 to define it as identity, ALWAYS, \
increment 1 and it works well....<br clear="none"><br clear="none">so what is the \
difference between identity and PRIMARY KEY ?<br clear="none"><br clear="none">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 </div></blockquote><div><br clear="none"></div><div>Hi \
Karen,</div><div><br clear="none"></div><div>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.</div><div><br clear="none"></div><div>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.</div><div><br></div><div>Hi \
Steve,</div><div><br></div><div>Sorry yahoo mail is giving me lots of problems in \
typing at the bottom - the convention. </div><div><br></div><div>I would like to \
confirm how to make an ID in PostgreSQL auto increment like \
MySQL?</div><div><br></div><div>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? </div><div><br clear="none"></div><div>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:</div><div> <div \
class="gmail-m_-2527352593038415746yiv0946157834gmail-language-sql \
gmail-m_-2527352593038415746yiv0946157834gmail-highlighter-rouge"><div \
class="gmail-m_-2527352593038415746yiv0946157834gmail-highlight"><pre \
class="gmail-m_-2527352593038415746yiv0946157834gmail-highlight"><code><span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-k">CREATE</span> <span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-k">TABLE</span> sample <span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-p">(</span> <span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-n">id</span> <span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-n">SERIAL</span> <span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-k">PRIMARY</span> <span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-k">KEY</span><span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-p">,</span> <span \
class="gmail-m_-2527352593038415746yiv0946157834gmail-p">);</span><br \
clear="none"><br clear="none"></code></pre><pre \
class="gmail-m_-2527352593038415746yiv0946157834gmail-highlight"><code>Why is my \
another table, without making the id serial it is auto-incrementing?</code></pre><pre \
class="gmail-m_-2527352593038415746yiv0946157834gmail-highlight"><code><br></code></pre><pre \
class="gmail-m_-2527352593038415746yiv0946157834gmail-highlight"><code><span \
style="font-family:arial,sans-serif">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</span><div \
class="gmail-m_-2527352593038415746yiv0946157834yqt5022732543" \
id="gmail-m_-2527352593038415746yiv0946157834yqtfd17374"><br \
clear="none"></div></code></pre></div></div></div></div></div></div></div></blockquote></div></blockquote><div><br></div><div>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<br></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic