On Tue, 22 Oct 2024, Rossana Ocampos wrote:
ERROR: invalid input syntax for type date: "CURRENT_DATE" LINE 1:
...extsupplydate ('1085018'::bigint, '5278'::bigint, 'CURRENT_D... ^ ERROR:
invalid input syntax for type date: "CURRENT_DATE" SQL status: 22007
Characters: 78
Rossana,
Also, I sugges
On Tue, 22 Oct 2024, Rossana Ocampos wrote:
I am new with PostgreSQL and I have a bug. I have created a function that
has an input variable of type date , in case it does not receive value ,
it has to assume by default the current date.
ERROR: invalid input syntax for type date: "CURRENT_DATE
On Thu, 12 Sep 2024, Adrian Klaver wrote:
Quick and dirty:
people_table
person_id PK
name_last
name_first
email_address
ph_number
...
location_table
loc_id PK
person_id_fk FK <--> people_table(person_id)
loc_name
loc_st_addr
loc_st_city
loc_st_st_prov
...
contact_
On Fri, 13 Sep 2024, Tony Shelver wrote:
Or if you want to get even more flexible, where a dairy could have more
than one owner as well as one owner having more than one dairy, you could
create an intersection / relationship table.
Something like
-- Create people table (one entry per person)
C
On Fri, 13 Sep 2024, Muhammad Usman Khan wrote:
To handle this situation in PostgreSQL, you can model the data in a way
that maintains a single entry for each owner in the people table while
linking the owner to multiple dairies through a separate dairies table.
This is a typical one-to-many rel
On Thu, 12 Sep 2024, Adrian Klaver wrote:
Quick and dirty:
people_table
person_id PK
name_last
name_first
email_address
ph_number
...
location_table
loc_id PK
person_id_fk FK <--> people_table(person_id)
loc_name
loc_st_addr
loc_st_city
loc_st_st_prov
...
contact_
On Thu, 12 Sep 2024, David G. Johnston wrote:
Read up on “many-to-many” data models. In SQL they involve a linking
table, one row per bidirectional edge, in addition to the two node tables.
David,
Thanks very much. I knew about those a long time ago but haven't needed them
in a long time so I
I have one name in the people table who owns 5 different dairies with three
different phone numbers, but all 5 have the the same email address.
The five dairies each has its own name and location while the people table
has five rows with the same last and first names and email address.
Is there
On Thu, 12 Sep 2024, Muhammad Usman Khan wrote:
You can try the following CTE which removes all the identical rows and only
leave single row
Thank you, Muhammed.
Rich
On Tue, 10 Sep 2024, Rich Shepard wrote:
to see how big a problem it is. If it is only a few projects it could just
a matter of manually deleting the extras.
Not knowing how to do that is why I wrote.
A web search (which I should have done before posting this thread) shows me
how to do
On Tue, 10 Sep 2024, Adrian Klaver wrote:
You might want to do something like:
select proj_nbr, count(*) as ct from projects group by proj_nbr;
to see how big a problem it is. If it is only a few projects it could just a
matter of manually deleting the extras.
Adrian,
It's a small table, n
On Tue, 10 Sep 2024, Andreas Kretschmer wrote:
you can use the hidden ctid-column:
postgres=# create table demo (id int, val text);
CREATE TABLE
postgres=# insert into demo values (1, 'test1');
INSERT 0 1
postgres=# insert into demo values (1, 'test1');
INSERT 0 1
postgres=# insert into demo va
On Tue, 10 Sep 2024, Christophe Pettus wrote:
If you don't mind taking the time to swap tables, you can always do an
INSERT ... SELECT DISTINCT into a new table, and then swap it
with the existing table.
Christophe,
I'll make the proj_nbr table the PK then do as you recommend.
Thank you,
R
On Tue, 10 Sep 2024, Francisco Olarte wrote:
Do you have any kid of corruption (i.e, unique index violation) or is
it just a duplicate problem?
Francisco,
Only a duplicate problem because when I created this table I didn't make the
proj_nbr column a PK.
Also, if you do not have any uniquene
On Tue, 10 Sep 2024, Adrian Klaver wrote:
Is there a Primary Key or Unique index on this table?
Adrian,
No. It didn't occur to me to make the project number a PK as this table is
not related to others in the database.
But, yesterday it occurred to me to make the proj_nbr a PK to eliminate
fu
I've no idea how I entered multiple, identical rows in a table but I want to
delete all but one of these rows.
Here's an example:
bustrac=# select * from projects where proj_nbr = '4242.01';
proj_nbr | proj_name| start_date | end_date | description | notes
--+
On Wed, 10 Jul 2024, Rob Sargent wrote:
I'm an emacs user too. Do you have show-paren enabled? This would show
that your file was ill-formed. M-p and M-n go previous/next matching
parentheses of all types.
Rob,
No, I haven't enabled show-paren.
Thanks for the tip,
Rich
On Wed, 10 Jul 2024, David G. Johnston wrote:
My first easy look for this setup is for any single quotes not adjacent to
either a comma or a parenthesis. Syntax highlighting should ideally have
caught this but I’d look anyway.
David,
I found an error on line 21 that I missed seeing every tim
On Wed, 10 Jul 2024, Hans Schou wrote:
If the file has these line breaks you show, then can make it to multiple
'INSERT INTO' instead.
Hans,
I thought of that, but forgot it. This makes more sense than dividing the
file in small chunks.
Thanks,
Rich
On Wed, 10 Jul 2024, David G. Johnston wrote:
And what are the first few lines of the file? Use text, not screenshots.
David,
insert into locations
(company_nbr,loc_nbr,loc_name,addr1,city,state_code,postcode) values
(2564,1,default,'4250 Hopkins Rd','Ontario','OR','97914'),
(2565,1,default,
On Tue, 9 Jul 2024, Craig McIlwee wrote:
The input file is 488 lines (presumably, since Rich said the file should
insert 488 rows). It seems like too much of a coincidence that the last
character of the last line is really the error. My guess is that there is
an unmatched character, perhaps a pa
On Tue, 9 Jul 2024, Adrian Klaver wrote:
The error:
LINE 488: ...2832,1,default,'85250 Red House Rd','Paisley','OR','97636')
is giving you the line number and the data:
a) Navigate to that line number using whatever method Joe has for that.
b) Search for '85250 Red House Rd'.
Adrian,
With t
On Tue, 9 Jul 2024, Rich Shepard wrote:
Should I run BEGIN: at the psql prompt prior to entering the insert
command? Would that tell me if any rows were entered and, if so, where
that stopped?
Began a transction, ran the script, checked the locations table for a couple
of rows to be inserted
On Tue, 9 Jul 2024, Adrian Klaver wrote:
bustrac=#\e scripts/insert-addrs.sql 488
If that raises this error:
environment variable PSQL_EDITOR_LINENUMBER_ARG must be set to specify a line
number
Adrian,
It doesn't; it prints the contents of the file to the virtual terminal.
Using my default s
On Tue, 9 Jul 2024, Ray O'Donnell wrote:
Did you run the entire thing inside a transaction? If so, then it will have
been rolled back after the error, and no rows will have been inserted.
Ray,
When I tried using transactions they froze the application. Probably because
I don't know to properl
I've a file with 488 rows to be input into a table. I run the script using
psql with the `\i' option.
After psql found a few missing commas I thought the script was clean. But,
psql returned this error:
bustrac=# \i scripts/insert-addrs.sql
psql:scripts/insert-addrs.sql:488: ERROR: syntax error
On Wed, 3 Jul 2024, Adrian Klaver wrote:
alter table array_conv alter column email type varchar[] using array[email];
select * from array_conv ;
id |email
+-
1 | {adrian.kla...@aklaver.com}
2 | {akla...@example.com}
Adrian,
Given my inexperience
On Wed, 3 Jul 2024, David G. Johnston wrote:
Yeah, the simply cast suggested will not work. You’d have to apply an
expression that turns the current contents into an array. The current
contents are not likely to be an array literal.
David,
No, it's not now an array.
I thought that this expre
On Wed, 3 Jul 2024, Rich Shepard wrote:
What I've tried:
bustrac=# alter table people alter column email set data type varchar(64) [];
ERROR: column "email" cannot be cast automatically to type character
varying[]
HINT: You might need to specify "USING email::
On Tue, 2 Jul 2024, Christophe Pettus wrote:
To be clear, I wasn't suggesting stuffing them all into a text column with
a delimiter, but storing them in a text *array* field, each email address
one component of the array.
Christophe,
I'm not using the proper syntax and the postgres alter tabl
On Tue, 2 Jul 2024, Christophe Pettus wrote:
To be clear, I wasn't suggesting stuffing them all into a text column with
a delimiter, but storing them in a text *array* field, each email address
one component of the array.
Okay. I've not before done that and will learn how. This data set is the
On Tue, 2 Jul 2024, Christophe Pettus wrote:
If you are absolutely 100% sure there will never be any metadata
associated with each email address (like a "valid" flag), you can use
TEXT[] array to store them. Otherwise, it's best to move them into a table
with a foreign key back to the owning rec
In data made available from a state regulatory agency I find a few instances
where a facility contact has two email addresses. While multiple locations
are accommodated because they're in a table separate from the facility name
and details, all people associated with a facility and location are in
On Thu, 20 Jun 2024, Adrian Klaver wrote:
From one of my previous posts(modified):
At this point I think you need to create a simple test case where:
1) You have script with
BEGIN;
COMMIT; --optional
2) In psql do \i
3) Do what you did before to 'recover' from the error.
Shows us the conte
On Thu, 20 Jun 2024, Rob Sargent wrote:
Is psql running in emacs (which is my preferred sql shell. M-x sql-postgres)?
Rob,
Not when I'm entering new data or updating existing tables. Otherwise, yes.
Regards,
Rich
On Wed, 19 Jun 2024, Ron Johnson wrote:
In addition, manually run the "BEGIN;" before the "\i insert-blarge.sql"
command.
That way, insert-blarge.sql just inserts. My reasoning: since you control
the ROLLBACK, you should also control the BEGIN.
Ron,
Hadn't thought of doing that, but now will
On Thu, 20 Jun 2024, Karsten Hilbert wrote:
Shot in the dark: are you be any chance using tab-completion
when running the SELECT before the COMMIT ?
Karsten,
Nope. I prepare DDL, DML, and DQL scripts in emacs, then run them from the
psql prompt using \i .
Regards,
Rich
On Wed, 19 Jun 2024, Ron Johnson wrote:
The problem is that you don't know where it's failing.
Ron,
True that. There's no specificity to why the transaction didn't complete.
I suggest you run "\echo all" before running "\i
insert-law-offices-addr.sql". That way, you'll see which line it b
On Wed, 19 Jun 2024, Adrian Klaver wrote:
I should have added to previous post:
What is the exact command string you are using to launch psql?
$ psql bustrac
Rich
On Wed, 19 Jun 2024, Adrian Klaver wrote:
It shouldn't:
cat transaction_test.sql
BEGIN;
insert into transaction_test values(1, 'test'), (2, 'dog'), (3, 'cat');
test=# create table transaction_test(id integer, fld_1 varchar);
test=# \i transaction_test.sql
BEGIN
INSERT 0 3
test=*# commit ;
CO
On Wed, 19 Jun 2024, Adrian Klaver wrote:
Looks to me you have a left over unresolved transaction in your psql session.
The easiest solution if that is the case is to exit the session and start a
new session to run the script.
Adrian, et al.:
That's what I've done. This time I commented out
On Wed, 19 Jun 2024, Alban Hertroys wrote:
The error prior to those statements is what you need to look at. That’s
what’s causing the transaction to fail.
Alban/Adrian,
I get the impression that you’re executing shell scripts that run the psql
command-line utility.
That's what I do as I've
On Wed, 19 Jun 2024, Adrian Klaver wrote:
Does it have a COMMIT; at the end?
Adrian,
No. I won't commit until I see the results are as intended.
At this point I think you need to create a simple test case where:
I killed the psql process and restarted it to allow me to work on other
issue
On Wed, 19 Jun 2024, Adrian Klaver wrote:
Are there transaction statements e.g. BEGIN;, COMMIT; in the script?
Adrian,
Yes, each script has BEGIN; on line 1.
Rich
On Wed, 19 Jun 2024, David G. Johnston wrote:
Simplest process, after rollback you fix the problem and start again from
the top of the transaction.
David,
That's what I thought I was doing when I re-entered the command to run the
script. That produced the same error because the transaction wa
On Wed, 19 Jun 2024, Adrian Klaver wrote:
Is this being done in a script fed to psql?
Adrian,
Yes. At the psql prompt I use the \i command to run the script.
Rich
I now insert rows using a transaction. Sometimes psql halts with an error:
ERROR: current transaction is aborted, commands ignored until end of
transaction block
I issue a rollback; command but cannot continue processing. What is the
appropriate way to respond to that error after fixing the syn
On Wed, 19 Jun 2024, Ron Johnson wrote:
"Click select" stops at dash in some ssh clients, but not in others.
That's what drives *my* decision making.
(Also, "%F" is equivalent to "%Y-%m-%d".)
Thanks, Ron.
Rich
On Wed, 19 Jun 2024, David G. Johnston wrote:
If you are doing a custom format dump using .sql as the extension is
objectively wrong. it is correct if you are doing an sql dump, but those
are generally best avoided.
David,
I use the default text format so restoration is by psql.
Rich
On Wed, 19 Jun 2024, Adrian Klaver wrote:
That is your choice, whatever makes sense to you.
Thank you, Adrian.
Regards,
Rich
Is the correct date format for pg_dump
-$(date +%Y-%m-%d).sql
or
--MM-DD.sql
or something else?
TIA,
Rich
On Thu, 13 Jun 2024, Adrian Klaver wrote:
Not with:
Table "public.companies"
[...]
Indexes:
"organizations_pkey" PRIMARY KEY, btree (company_nbr)
That would throw duplicate key errors.
Are you sure that you did not do this on the contacts table as the company FK
back to companies
Two tables have a sequence for the PK. Over time I manually entered the PK
numbers not being aware of applying DEFAULT to generate the next number.
I just tried to set one table's PK sequence to the current max(PK) value
using this expression from a stackexchange thread:
SELECT setval('', , true)
On Thu, 13 Jun 2024, David G. Johnston wrote:
You need to show your work here. As your PK is a number it cannot have a
company name as a value and so this doesn't make sense.
David,
insert into companies (company_nbr,company_name,industry,status) values
(DEFAULT,'new company name','Industry'
On Thu, 13 Jun 2024, Jeremy Smith wrote:
There's no need to specify the column if it has a default value, but
specifying it did not cause the issue that you saw.
Jeremy,
I did not know this. While the reason for the issue shall remain unknown, it
did happen and my update script restored order
On Thu, 13 Jun 2024, David G. Johnston wrote:
There is no way, in the absence of a user trigger, that the above insert
command changed pre-existing rows. And if you cannot reproduce the
behavior you claim to have seen I will continue to just assume you have
faulty memory.
David,
While there m
On Thu, 13 Jun 2024, Ron Johnson wrote:
No need to do that. Just write:
INSERT INTO public.companies (company_name, , industry, status)
VALUES ('Berkshire Hathaway', 'Conglomerate', 'Mumble');
The next value of companies_org_nbr_seq will automatically be taken and
inserted into the table.
On Thu, 13 Jun 2024, David G. Johnston wrote:
Because you specified company_name in the column listing for the things
you are inserting values for. So in column position 2 you must have a
value than can be inserted into the company_name column. It is utterly
immaterial how you specified the valu
On Thu, 13 Jun 2024, Adrian Klaver wrote:
You sure you did not actually do an UPDATE without a WHERE?
Adrian,
Yep. There was no row to update as I was adding a new company.
Regards,
Rich
On Thu, 13 Jun 2024, Ron Johnson wrote:
If the table has a primary key, then the command *should* have failed with
a duplicate key error as soon as the first dupe was discovered.
Ron,
I had manually set the PKs (column: company_nbr) which has a sequence
defined for it when I added about 50 ro
On Wed, 12 Jun 2024, Adrian Klaver wrote:
The 'Examples' section at the bottom of this page:
https://www.postgresql.org/docs/current/sql-insert.html
is a useful resource.
Adrian,
That's good to know. Thank you.
Regards,
Rich
On Wed, 12 Jun 2024, Adrian Klaver wrote:
Assuming 'people_person_nbr_seq' is the sequence attached to person_nbr
and the other DEFAULTs are the column defaults then the syntax would be:
INSERT INTO people
(person_nbr, lname, fname, job_title, company_nbr,loc_nbr, direct_phone,
cell_phone,emai
On Wed, 12 Jun 2024, Rob Sargent wrote:
Add "begin;" to that and try it. If you don't get exactly UPDATE 295
reported, then "rollback;";
Got it, thanks.
Rich
On Wed, 12 Jun 2024, Ron Johnson wrote:
A plain UPDATE might work.
UPDATE to_be_updated a
SET bool_col = true
FROM other_table b
WHERE a.pk = b.pk
AND b.field3 = mumble;
(You can join them, right?)
Thanks, Ron.
Rich
On Wed, 12 Jun 2024, David G. Johnston wrote:
INSERT INTO people (person_nbr
DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT
'Contact',company_nbr,loc_nbr,direct_phone,cell_phone,email,active
DEFAULT('true')) VALUES
https://www.postgresql.org/docs/current/sql-insert.html
Not s
On Wed, 12 Jun 2024, David G. Johnston wrote:
I'll often just use a spreadsheet to build the 295 update commands and
copy-paste them into psql or whatnot.
David,
I'll create and use a temporary table.
Thanks,
Rich
On Wed, 12 Jun 2024, Rich Shepard wrote:
VALUES (nextval('people_person_nbr_seq'), ...
Correction.
Rich
On Thu, 13 Jun 2024, Muhammad Salahuddin Manzoor wrote:
You can use Temporary table. You could create a temporary table with one
column containing the condition values and then use it to update your main
table. This approach can be more flexible and cleaner than writing a
script with multiple up
I have a table with 3492 rows. I want to update a boolean column from
'false' to 'true' for 295 rows based on the value of another column.
Is there a way to access a file with those condition values? If not, should
I create a temporary table with one column containing those values, or do I
write
I have > 100 rows to add to a table using INSERT INTO statements. I want the
PK to be the next value in the sequence. Would this be the appropriate
syntax for the columns to be entered?
INSERT INTO people (person_nbr
DEFAULT('people_person_nbr_seq'),lname,fname,job_title DEFAULT
'Contact',compa
On Mon, 10 Jun 2024, Christophe Pettus wrote:
The sequence functions are documented here:
https://www.postgresql.org/docs/current/functions-sequence.html
setval is the function you want. You can use a SELECT so you don't have to
copy values around:
select setval('t_pk_seq', (sele
On Mon, 10 Jun 2024, David G. Johnston wrote:
For efficiency the only thing used to determine the next value of a
sequence is the stored value of the last sequence value issued. Where that
value may have been used, in a table as a PK or some other purpose, does
not enter into it. Using a sequenc
On Mon, 10 Jun 2024, Christophe Pettus wrote:
Strictly speaking, the sequence underlying nextval() has no idea what
primary keys are or are not in use. It's just a transaction-ignoring
counter that increases with each nextval() call. The only reason that
you'd get duplicate key errors in this ca
Over the years I've deleted rows from tables leaving gaps in the PK sequence
numbers. I've now learned that using nextval('sequencename') finds those
gaps and tells me that the value after the gap is already assigned.
I found a web page that explains how to find the gaps in a sequence, yet I
want
On Mon, 10 Jun 2024, David G. Johnston wrote:
Values introduces literal records/rows. Rows are written within
parentheses. Values (..., ...), (..., ...)
You seem to have the closing parenthesis but not the opening one.
David,
Duh! Of course. For whatever the reason I kept not seeing that.
M
On Mon, 10 Jun 2024, Peter J. Holzer wrote:
My question is whether I can create new rows for all three tables in the
same sql source file.
Yes, of course.
Since the location and contact tables require sequence
numbers from the company and location tables is there a way to specify,
e.g., cur
On Mon, 10 Jun 2024, Torsten Förtsch wrote:
Something along these lines perhaps:
=# create table a( id bigserial primary key, x text );
CREATE TABLE
=# create table b( fk bigint references a(id), y text );
CREATE TABLE
=# with ins_a as (insert into a (x) values ('a row') returning *)
insert i
On Mon, 10 Jun 2024, Ron Johnson wrote:
With enough clever scripting you can create a .sql file that does almost
anything.
Ron,
My projects don't all use SQL so I'm far from a clever scripter. :-)
Most useful to you will be some number of "ALTER TABLE DISABLE
TRIGGER ALL;" statements near
My business tracking database has three main tables: company, location,
contact. The company and contact primary keys are sequences.
I've been adding new rows using INSERT INTO files separately for each table
after manually finding the last PK for the company and contact tables. The
location tabl
On Mon, 20 May 2024, Alban Hertroys wrote:
That aside, while you’re not absolutely 100% definitely sure that an
UPDATE or DELETE statement is going to do exactly what you intended, and
for good measure if you are, wrapping such statements in a transaction
allows you to ROLLBACK to get back to th
On Sun, 19 May 2024, Christophe Pettus wrote:
Of course, you can probably also shorten the query to:
UPDATE people SET active=true WHERE ...
Where ... is the predicate you would have used in the SELECT id WHERE ...
Ah, yes. Hadn't thought of that. The statement would be
UPDAT
On Sun, 19 May 2024, Muhammad Salahuddin Manzoor wrote:
I think triggers are a good option.
Salahuddin,
I need to update the table with all the designated rows only once. But, I'll
look at using triggers.
Thanks,
Rich
On Sun, 19 May 2024, Ray O'Donnell wrote:
Could you create a table with just person_id values whose rows are to be
updated? Then you could do something like this:
update people set active = true where exists (
select 1 from temporary_table where person_id = people.person_id
);
That's just o
On Sun, 19 May 2024, Christophe Pettus wrote:
UPDATE people SET active=true WHERE id IN (...);
The ... can either be an explicit list of the ids, or a SELECT id WHERE if
you have a predicate that selects the appropriate ids.
Christophe,
That's a good idea; I can use a predicate to identify t
Searching the postgresql doc for UPDATE the examples I find show updating
one or a few rows in a table. I have 457 rows to update in a table.
I could write a .sql script with 457 lines, each updating one row of the
table. My web search for `sql: update table rows from a file of column
values' fin
On Sat, 18 May 2024, Adrian Klaver wrote:
Show the complete query.
Take the error message as correct, you are specifying 'companies as c' more
then once.
Adrian,
I saw that but didn't know how to specify the alias only one time.
Thanks,
Rich
On Sat, 18 May 2024, Ray O'Donnell wrote:
Look again at Shammat's example! -
SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.company_name
FROM people as p
LEFT JOIN companies as c ON c.company_nbr = p.company_nbr
NB - "... from people as p left join companies as c on " -
On Sat, 18 May 2024, Adrian Klaver wrote:
The query needs to be:
SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.company_name
FROM people as p
LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
Only reference companies as c once.
Thanks, Adrian. I mis-read your origi
On Sat, 18 May 2024, Erik Wienhold wrote:
Yes, Rich probably just wants the left join.
Eric,
You're correct: I want certain colums from the people table with their
company name from the companies table.
But I wonder if the implicit cross join syntax ("FROM peoples, companies")
should actual
On Sat, 18 May 2024, Adrian Klaver wrote:
... LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
Adrian,
Tried that:
bustrac-# LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
ERROR: table name "c" specified more than once
Thanks,
Rich
On Sat, 18 May 2024, Ray O'Donnell wrote:
You need to include the alias for the table also - see "...from companies as
c..." in Shammat's example.
Ray,
That didn't work:
bustrac-# FROM people as p, companies as c
bustrac-# LEFT JOIN companies as c ON c.company_nbr = p.company_nbr;
ERROR: tab
On Sat, 18 May 2024, Shammat wrote:
Don't put the second table in the FROM part
SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.company_name
FROM people as p
LEFT JOIN companies as c ON c.company_nbr = p.company_nbr
Shammat,
I tried this with this result:
ERROR: missing FR
It's been a _very_ long time since I wrote a SQL script and, despite looking
at my SQL books and web pages, I don't know how to fix the error.
The three line script is:
-
SELECT p.lname, p.fname, p.job_title, p.company_nbr, p.email, c.company_name
FROM people as p, companies as c
LEFT JOIN
On Wed, 25 May 2022, Thomas Kellerer wrote:
If you want to prevent such a situation in the future, you might want to
consider defining those columns as "generated always as identity" instead
of "serial".
Then you'd get an error if you try to bypass the database generated values.
Thanks, Thoma
On Wed, 25 May 2022, Adrian Klaver wrote:
From:
https://www.postgresql.org/docs/current/functions-sequence.html
SELECT setval('people_person_nbr_seq', 965);
Adrian,
Thanks. I didn't know where to look in the docs.
Regards,
Rich
On Wed, 25 May 2022, Adrian Klaver wrote:
What is max(person_nbr)?
bustrac=# select max(person_nbr) from people;
max
-
965
(1 row)
Rich
On Wed, 25 May 2022, David G. Johnston wrote:
The value the sequence provides next is wholly independent of everything
except the state of the sequence. It doesn’t care how many rows any table,
even its owner, has. The very existence of the delete command should make
this self-evident.
David J
On Wed, 25 May 2022, Adrian Klaver wrote:
Do:
select * from people_person_nbr_seq;
and report back the results.
Adrian,
Huh!
bustrac=# select * from people_person_nbr_seq;
last_value | log_cnt | is_called
+-+---
683 | 32 | t
(1 row)
It's out of syn
The People table has 965 rows; the table structure is:
Table "public.people"
Column | Type | Collation | Nullable |
Default
---+---+---+--+
On Mon, 2 May 2022, Tom Lane wrote:
Perhaps your psql is built against libedit rather than readline.
Tom,
Could be I use the SlackBuilds.org build script.
Regards,
Rich
1 - 100 of 472 matches
Mail list logo
