On Wed, 9 Dec 2020, Michael Lewis wrote:
What application is taking the user input and needs to include the
parameters in the query string?
Michael,
My business tracking tool. Yes, the GUI will have text entry widgets for
user input but I want to apply these queries using psql on the command
On Wed, 9 Dec 2020, Laurenz Albe wrote:
You probably need the \prompt psql command:
\prompt 'What is "p.lname"' p_lname
\prompt 'What is "p.fname"' p_fname
SELECT ... WHERE p.lname = :p_lname AND p.fname = :p_fname;
Laurenz,
Ah! I have not before encountered that command. Yes, this will do th
On Wed, 9 Dec 2020, Rob Sargent wrote:
Put the query in a file, set the desired name, then from psql
\i filename
Edit pfname, repeat
Thanks, Rob.
Stay well,
Rich
On Wed, 9 Dec 2020, Paul Förster wrote:
maybe you're looking for this?
https://stackoverflow.com/a/7389606
Paul,
That looks very useful and I'll try the provided answers.
Looking at the postgres-12 doc I cannot find a command 'PROMPT' anywhere in
it.
Thanks,
Rich
On Wed, 9 Dec 2020, Paul Förster wrote:
but 12 has it:
postgres=# \prompt 'input: ' input
input: this is test input
postgres=# select version(), :'input';
Paul,
Okay. I use mupdf to view the document and my search string were 'prompt',
and 'prompt command'. I didn't use '\prompt',
Thanks ag
On Sat, 16 Jan 2021, Paul Förster wrote:
Or use duckduckgo or some other search engine.
+1
Rich
On Sat, 16 Jan 2021, Bruce Momjian wrote:
That "quoting entire messages on reply" is something I see far too often
here. I have been meaning to mention this problem. Thousands of people are
reading postings here, so it pays to take time to trim down what others
have to view.
Bruce,
This has b
I'm getting a syntax error with an input into statement and I cannot see the
cause.
A MWE and the result (N.B.: line one wrapped for readability; it ends with
'values'):
insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,emai
On Tue, 26 Apr 2022, Adrian Klaver wrote:
I'm guessing some sort of hidden character.
Adrian,
The 'insert into ...' string is a template I've been using for years. It's
worked earlier today, too.
What client are you using to run this?
psql in a linux virtual terminal.
Where is the query
On Tue, 26 Apr 2022, Adrian Klaver wrote:
I'm guessing some sort of hidden character.
Adrian,
Oh, ... forgot to mention in my response that the MWE values were added to
the template in emacs while I get the same error using psql -d -f
in a v.t. or entering 'insert into people values (...)'
On Tue, 26 Apr 2022, David G. Johnston wrote:
What version of PostgreSQL?
David,
12.7
Testing on HEAD (and by inspection) nothing you've shown us provokes a
syntax error in PostgreSQL.
regression=> insert into people
(person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
regression(>
loc_p
On Tue, 26 Apr 2022, Tom Lane wrote:
so there's nothing wrong with the syntax as-presented. I agree with
Adrian's guess about invisible characters in your input file; perhaps a
control-D or ASCII NUL would produce that symptom.
tom,
It's probably a transient glitch that will go away after I l
On Tue, 26 Apr 2022, Rich Shepard wrote:
It's probably a transient glitch that will go away after I log out.
Yep. I logged out and back in just now. The insert script worked as it
always had before and both rows were added to the table.
I've seen these sort of hang-ups before and d
On Tue, 26 Apr 2022, Bruce Momjian wrote:
I am curious what OS psql was using that was fixed by a re-login?
Bruce,
This desktop's running Slackware64-14.2.
Regards,
Rich
On Wed, 27 Apr 2022, Laurenz Albe wrote:
test=> insert
test-> insert into people (person_nbr,lname,fname,job_title,company_nbr,loc_nbr,
loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values
(6000,'No','Name',null,404,1,null,null,null,null,null,null,null);
ERROR: syntax e
On Wed, 27 Apr 2022, Rob Sargent wrote:
Have you tried \r instead of C-c?
Rob,
No, I haven't. I'll try it to reset the query buffer the next time I make a
mistake entering a command.
Thanks,
Rich
On Wed, 27 Apr 2022, Francisco Olarte wrote:
Where you using cut&paste / selection or something similar? Lately I've
been getting weird errors on paste operations due to bracketed paste,
which are puzzling, It seems to have interactions with readline and other
stuff depending on where the cursor
I do all my postgres work using the psql shell. Editing a command reguires
moving character-by-character and I'd like to use my small text editor (joe)
because it allows more control over line movement.
A web search found a stackexchange thread that suggested adding to
~/.bash_profile the line:
e
On Fri, 29 Apr 2022, Jan Wieck wrote:
What you are missing is that even though the PSQL_EDITOR env variable is
set, psql itself doesn't emulate that editor's behavior natively. You need
to actually launch the editor (possibly while having a partial query in
the buffer) with the \e command.
Jan
On Fri, 29 Apr 2022, Francisco Olarte wrote:
I do a similar thing, but normally edit queries in an editor window and
just use selection or clipboard to paste them into the xterm where I have
psql running. I also used joe a lot ( its key sequences where easy coming
from wordstar(cp/m->msdos) ).
Looking at the postgresql.org web site I could not find the mail list
manager for direct contact so I apologize for writing here.
I use procmail to sort incoming messages to the proper file. Messages from
the mail list come with varying information at the bottom of the headers; it
could be From:,
On Fri, 29 Apr 2022, Jan Wieck wrote:
Not that I know of. \e starts the external editor and you have to save and
exit that editor to get back to psql in order to execute it. IMHO the
whole construct has very limited usability.
Jan,
I tried, unsuccessily, to use \e. Entering it while a command
On Fri, 29 Apr 2022, Kris Deugau wrote:
At a not-entirely-wild guess, based on replies to your recent couple of
posts, the messages that went astray were the direct mail copies that
never passed through the list in the first place. I use an almost
identical recipe myself (and a matching one for
On Fri, 29 Apr 2022, Jan Wieck wrote:
Did you hit Enter after \e ?
Jan,
Yes. For example, I put a previous command at the prompt to be modified. It
began with 'insert ...' so I added an initial \e to the command. psql told
me that \einsert is not a valid command after I pressed the [Enter] ke
On Fri, 29 Apr 2022, Reid Thompson wrote:
https://linuxgazette.net/issue14/bashtip.html may of of use.
Reid,
I've had no issues using joe in any v.t. running an application (e.g.,
alpine) or by itself. Apparently, psql is different.
Rich
On Fri, 29 Apr 2022, Jan Wieck wrote:
It is the other way around, like in
postgres-# select now()\e
Jan,
That does make a difference. Now I'm learning how to end the edit and return
from joe to the psql shell. The [Enter] key wraps the long line; probably
C-x will do the job.
Many thanks,
R
On Fri, 29 Apr 2022, David G. Johnston wrote:
You type "insert", realize you want an editor for this, hit enter
(multi-line mode is psql), type \e, hit enter again, your editor appears
with "insert" already in place from the query buffer. Upon returning you
are given a new buffer with the conten
On Fri, 29 Apr 2022, Rich Shepard wrote:
... probably C-x will do the job.
Actually, it's C-k x, the usual joe save command.
My thanks to all because this new skill is saving me much time and effort.
Regards,
Rich
On Mon, 2 May 2022, Reid Thompson wrote:
Apparently, psql is different.
I believe that psql also uses readline, so my thought was that maybe these
instructions could enable you to map the 'move' keystrokes that you're
familiar with to be used while on the psql command line. A very quick test
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
The People table has 965 rows; the table structure is:
Table "public.people"
Column | Type | Collation | Nullable |
Default
---+---+---+--+
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
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:
What is max(person_nbr)?
bustrac=# select max(person_nbr) from people;
max
-
965
(1 row)
Rich
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, 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
This is puzzling and I've no idea what to do to fix it.
The table looks like this:
# select * from concentrations limit 2;
site_nbr | sampdate | medium | form | param | quant | unit | cen | floor | ceiling
--++-+---+---+---+--+-+-
On Mon, 29 Oct 2018, Rich Shepard wrote:
But, when I try to redirect output to a disk file like this,
\o data-summary-by-form.txt
the result is an error:
The \o came from a stackexchange thread I found with a web search. Within
psql the \? help command indicates there is no \o option, but
On Mon, 29 Oct 2018, David G. Johnston wrote:
You seem to need to distinguish between the command line options to psql
and the meta commands that can be used within a script that is being
executed by psql.
David,
I'm running psql in a shell buffer within emacs, not at the shell prompt. The
On Mon, 29 Oct 2018, David G. Johnston wrote:
You can either use \out or \copy within psql script or redirect the shell
output using shell features.
David,
willamette-river-hg-# \out data-summary-by-form.txt
willamette-river-hg-# select param, site_nbr, sampdate, min(quant), max(quant),
unit
On Mon, 29 Oct 2018, David G. Johnston wrote:
I'd say that emacs is doing something funky then. Running your script
using:
David,
Yes, it is something related to running the commands within an emacs bash
shell.
Thanks for isolating the problem,
Rich
On Mon, 29 Oct 2018, Rich Shepard wrote:
willamette-river-hg-# select param, site_nbr, sampdate, min(quant),
Yep. I kept missing this. Closed that status and it does work within
emacs. Thanks all.
Rich
I managed to mess up postgresql-10.3 on this Slackware-14.2 desktop
server/workstation. It worked OK until I tried adding access to an another
application.
For a reason I don't know, adding that listening address revealed that
many sym links are looking for 10.2 directories. I've found and fi
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What was the listening address you added?
Adrian,
I added the host name.
What happens if you remove the listening address?
I don't think this makes a difference. pg_ctl is calling a program that
looks for timezonesets in the wrong directory
On Wed, 31 Oct 2018, Adrian Klaver wrote:
You said it made a difference when you added it, just trying to figure out if
removing it also makes a difference. If not then we need to look elsewhere
for an explanation.
Adrian,
Each time I hit a broken symlink pg_ctl told me which link was brok
On Wed, 31 Oct 2018, Adrian Klaver wrote:
If you refuse to implement the suggestions I asked for then I cannot help
you, as you are now off on a different tangent. One that on the face of it is
dangerous.
In var/lib/pgsql/10.3/data/postgresql.conf:
# - Connection Settings -
_addresses = '
On Wed, 31 Oct 2018, Andrew Gierth wrote:
Is there a pg_config binary in /usr/lib/postgresql/10.3/bin/ and if so,
what is the output of /usr/lib/postgresql/10.3/bin/pg_config --sharedir
Andrew,
Yes, pg_config is present but pointing to the wrong directory:
# /usr/lib/postgresql/10.3/bin/pg_
On Wed, 31 Oct 2018, Adrian Klaver wrote:
listen_addresses = ''
Adrian,
#listen_addresses = ''
$ pg_ctl start -D /var/lib/pgsql/10.3/data/
waiting for server to start2018-10-31 12:12:39.530 PDT [4398] FATAL: could not open
directory "/usr/share/postgresql-10.2/timezonesets": No such fi
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What does:
/usr/lib/postgresql/10.3/bin/pg_config --version
show?
# /usr/lib/postgresql/10.3/bin/pg_config --version
PostgreSQL 10.3
What does:
ps ax | grep post
show?
# ps ax | grep post
1307 ?Ss 1:29 /usr/libexec/postfix/master -
On Wed, 31 Oct 2018, Adrian Klaver wrote:
What does:
pg_ctl --version
show?
# pg_ctl --version
pg_ctl (PostgreSQL) 10.3
So when you added the new application did you make any other changes?
I did not add another application; grass has been installed here for
decades. Because I could not
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Are there actually 10.2/ directories or is that just what you are seeing in
the error messages and the pg_config output?
Adrian,
No 10.2/ directories, only what is shown in the error messages and
pg_config output.
Previously you used:
/usr/lib/pos
On Wed, 31 Oct 2018, Rich Shepard wrote:
[1] This prompted me to look for more pg_config files, and I found a symlink
in /usr/bin/ that pointed to /usr/lib/postgresql/10.2/bin/pg_config which
does not exist. I changed that symlink to point to the 10.3/ pg_config
version but there's st
On Wed, 31 Oct 2018, Rich Shepard wrote:
I'll fix those links and report the results of running pg_ctl start.
Still bad links remaining. Some of those symlinks in /usr/bin/ dated back
to versons 9.4 and 9.6. Why they were not removed during upgrades remains a
mystery.
Rich
On Wed, 31 Oct 2018, Rich Shepard wrote:
Still bad links remaining.
Every pg_* not in /usr/lib/postgresql/10.3/bin/ now points to its namesake
there.
Question: if pg_dump, pg_dumpall, pg_restore, pg_ctl, and pg_controldata
have symlinks in /usr/bin/ do they also need symlinks in /bin
On Wed, 31 Oct 2018, Adrian Klaver wrote:
So no, as I presume you rebooted on the kernel upgrade which caused the
Postgres server to stop/start.
True. It stopped for the time it took the server to reboot.
Rich
On Wed, 31 Oct 2018, Andrew Gierth wrote:
What this says is that you somehow have a pg 10.3 binary which has been
compiled with ./configure --datadir=/usr/share/postgresql-10.2
which seems, to say the least, somewhat odd.
Andrew,
Quite odd rather than somewhat odd because the configure opt
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Well there is something strange going. From a previous post:
Andrew,
Yet it ran without a whimper from the upgrade last March 1st to this
morning when I modified postgresql.conf.
It's the middle of the night in central Europe so I expect to hear
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Hmm in the build script the difference is:
VERSION=${VERSION:-10.3}
PG_VERSION=${PG_VERSION:-10.3}
--docdir=/usr/doc/$PRGNAM-$VERSION \
--datadir=/usr/share/$PRGNAM-$PG_VERSION \
Wonder where the script is finding PG_VERSION?
Do you have env variable
On Wed, 31 Oct 2018, Adrian Klaver wrote:
Even with assurances I would back up that directory(assuming space available)
before proceeding with a rebuild. Or do you have a recent dump of the
cluster?
Adrian,
That's my thinking, too. No, an explicit pg_dumpall is too old to be
useful. I have
On Wed, 31 Oct 2018, Rich Shepard wrote:
I managed to mess up postgresql-10.3 on this Slackware-14.2 desktop
server/workstation. It worked OK until I tried adding access to an another
application.
The problems have been resolved by upgrading 10.3 to 10.5 using the
SlackBuild.org script
I have the following code in a script:
alter table stations add column start_date date;
alter table stations add column end_date date;
alter table stations add column howmany integer;
alter table stations add column bin_col char(8);
insert into stations (start_date, end_date, howmany, bin_col)
On Thu, 1 Nov 2018, Adrian Klaver wrote:
alter table stations add column start_date date;
alter table stations add column end_date date;
alter table stations add column howmany integer;
alter table stations add column bin_col char(8);
insert into stations (start_date, end_date, howmany, bin_col
On Thu, 1 Nov 2018, David G. Johnston wrote:
That makes no sense to me...you already have 82 rows on the table so if you
insert 82 more you'll have 164 which doesn't seem like what you would
want...
I would probably do:
David,
The table already exists with four attribute columns and their
On Thu, 1 Nov 2018, Adrian Klaver wrote:
Why? As David said that will get you an additional 82 rows with data that is
dissociated from the existing data. You want to update the existing rows with
the new field data for each station.
D'oh! Of course!
Thanks both,
Rich
On Fri, 2 Nov 2018, Tim Cross wrote:
3. Is it really insert or update you need?
Tim,
Update.
It's been a long time since I did any database manipulation (I just use
the existing databases as each project needs its data) so I sometimes make
these types of mistakes.
Your idea of manipul
On Thu, 1 Nov 2018, Rich Shepard wrote:
I'll use gawk to extract the relevant fields from the text file in which
they reside (in the correct order), then use emacs keyboard macros to add
the appropriate update text to each line. Must more efficient (and less
tedious) than manually writing
On Thu, 1 Nov 2018, Ken Tanzer wrote:
I'm not sure what format your text file is in, but you could probably use
\copy to bring it into a (temporary) table in Postgres. As long as it had
your four new columns and the site_nbr, you could then update from that
table in one swoop:
Ken,
Thank yo
My current desktop server/workstation is running version 10.5. I'm
configuring a replacement desktop and have installed version 11.1 on it. To
copy all databases from the 10.5 version to the 11.1 version I assume that I
should do a pg_dumpall on the current host and read in that file on the
repl
On Sun, 11 Nov 2018, Adrian Klaver wrote:
Not sure if you have any extensions or not, but the part I often skip is
installing extensions in the new cluster before running the dump restore.
Thanks, Adrian. No extensions here.
Regards,
Rich
On Sun, 11 Nov 2018, Andrew Gierth wrote:
The most reliable and preferred procedure is to use the _new_ version's
pg_dumpall, for example by allowing access to the old host from the new
one (possibly using an ssh port forward), or (on OSes that make it easy to
do package installs of multiple ver
On Sun, 11 Nov 2018, Adrian Klaver wrote:
pg_dumpall is going to need to run against a Postgres server not just a data
directory.
Adrian,
Of course. Yet it's the data directory that's written to the .sql file.
If both your old and new machines are on the same network, why not just
point t
On Sun, 11 Nov 2018, Rich Shepard wrote:
Haven't tried to run an application on one host using data on another
host. I'll look at which tool will do that.
Looks like the pg_dumpall '-h' option will act on the other host's data
directory.
Regards,
Rich
On Sun, 11 Nov 2018, Ron wrote:
Unless your db is small, do a parallel dump. Even then, do a "-Fc" backup
instead. That's been the recommended method for many years.
Ron,
I've several databases, none 'large.' When I've used pg_dumpall in the
past it's always with the clean option (-c) and t
On Sun, 11 Nov 2018, Rich Shepard wrote:
Looks like the pg_dumpall '-h' option will act on the other host's data
directory.
Worked as advertised. Just read the dumped file into the new 11.1 data
directory.
The new installation is asking for my password to access my databa
On Tue, 13 Nov 2018, Adrian Klaver wrote:
You have two options:
1) The preferred one. Keep the password and create a .pgpass file to hold the
password:
https://www.postgresql.org/docs/10/libpq-pgpass.html
Adrian,
That's database-specific if I read the manual page correctly.
My guess is
On Tue, 13 Nov 2018, Adrian Klaver wrote:
No:
" Each of the first four fields can be a literal value, or *, which matches
anything. "
Adrian,
Okay.
If the record starts with local then that is for socket connections.
If you are connecting to a host e.g -h localhost then you need to look
On Tue, 13 Nov 2018, Adrian Klaver wrote:
Just realized the question I should have asked is:
How did you get the pg_dumpall file processed by Postgres?
In other words how did you do it without a password?
As user postgres I entered the command
$ psql -f dump-all.sql
In any case, I need to
On Wed, 14 Nov 2018, Tom Lane wrote:
Perhaps those are associated with some other data directory?
Tom/Adrian,
I think that I FUBARed the upgrade from 10.3 to 11.1. I've removed both
packages and am rebuilding 11.1. I'll install it, run initdb on it, then
re-run pg_dumpall on the 10.5 data d
On Wed, 14 Nov 2018, Rich Shepard wrote:
I think that I FUBARed the upgrade from 10.3 to 11.1. I've removed both
packages and am rebuilding 11.1. I'll install it, run initdb on it, then
re-run pg_dumpall on the 10.5 data directory on the old desktop. Starting
from scratch on this n
I have a new database with five tables, and text files with data to populate
the tables using the insert command.
1. Each table has a sequential primary key. Should I manually add keys to
each insert row or will postgres generate it automatically? Example, for the
companies table:
org_id| in
On Sat, 1 Dec 2018, Adrian Klaver wrote:
If you don't supply the key it will be generated as the default for the PK
column is a sequence.
Thanks, Adrian. I thought this to be the case and did not find
confirmation in the manual (perhaps I just missed seeing it.)
So are the tables you are I
On Sat, 1 Dec 2018, Adrian Klaver wrote:
Forgot to add that if you pre-assign the keys to the serial field you will
need to advance the sequence to a value past the last key value to avoid a
duplicate key error when you let the sequence assign numbers.
Adrian,
Got it, thanks.
Carpe weekend
I want to alter a term in a column's constraint to allow only specified
strings as attributes and have not found how to do this in the docs (using
version 10 docs now). There is an alter table command that allows renaming a
constraint but I've not seen how to modify the constraint itself.
Poi
On Mon, 17 Dec 2018, Rich Shepard wrote:
I want to alter a term in a column's constraint to allow only specified
strings as attributes and have not found how to do this in the docs (using
version 10 docs now). There is an alter table command that allows renaming
a constraint but I'v
On Mon, 17 Dec 2018, Melvin Davidson wrote:
Yes, you must drop then add the revised constraint. However, from your
statement above, it sounds to me as if you would be better off using A
FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
Melvin,
I don't follow. Here's the DDL for that co
On Mon, 17 Dec 2018, Ron wrote:
Melvin is saying to:
1. create a table named valid_industry,
2. populate it with the valid industries,
3. create an FK constraint on your main table's industry column to
valid_industry.industry, and then
4. drop the constraint invalid_industry.
Got it. Hadn'
Happy New Year all,
My readings taught me that standard SQL has a domain constraint that checks
for the same valid characters in a column common to multiple tables.
Example:
CREATE DOMAIN state_code AS char(2)
DEFAULT '??'
CONSTRAINT valid_state_code
CHECK (value IN ('AL', 'AK', 'AZ', ...));
Th
On Wed, 2 Jan 2019, David G. Johnston wrote:
There is no magic name logic involved. A domain is just a type with
inherent constraints that are user definable. You make use of it like any
other type.
Create table tbl (
column_name state_code not null
)
Values stored in column_name are now of ty
On Wed, 2 Jan 2019, David G. Johnston wrote:
I'm not following you. I have two tables each with a column,
state_code char(2) NOT NULL.
That is a char(2) column for which ‘??’ is a valid value. The fact that it
is named state_code is immaterial; the domain that you created doesn’t get
used.
On Wed, 2 Jan 2019, David G. Johnston wrote:
You add the create domain command once before any objects that make use of
it.
David,
This is the answer I sought: postgres supports the create domain command.
I did not see this in your first response.
Thanks very much,
Rich
On Wed, 2 Jan 2019, Ron wrote:
Note that a CHECK constraint with 50 items is a *Very Bad Idea*, since
changing such a constraint is very painful. Use a FK constraint instead.
Ron,
It's even longer with Canadian provinces included. I gratefully accept
your advice and will use a table and for
Reading the manual I saw that 'today' is a special value, but it did not
work when I used it as a column default; e.g.,
start_date date DEFAULT today,
Appending parentheses also failed. But, changing today to CURRENT_DATE
worked. I've not found an explanation and would appreciate learning why
On Wed, 2 Jan 2019, Tom Lane wrote:
'today' is special as a date input string, so you can use it as a literal:
regression=# select 'today'::date;
date
2019-01-02
(1 row)
But it's not a SQL keyword, nor a function name, so you can't write it
without quotes.
Tom,
Now I under
I have a projects table that includes these two columns:
start_date date DEFAULT CURRENT_DATE,
end_date date
CONSTRAINT valid_start_date
CHECK (start_date <= end_date),
1. Do I need a DEFAULT value for the end_date?
2. If so, please suggest a value for it.
TIA,
Rich
On Fri, 4 Jan 2019, Igor Korot wrote:
1. Do I need a DEFAULT value for the end_date?
2. If so, please suggest a value for it.
start_date.day() + 1?
Thanks, Igor. I did not pick up this syntax when I looked at data types
and their DDL usage.
Regards,
Rich
On Fri, 4 Jan 2019, Rob Sargent wrote:
Is the end_date always knowable at record insert?
Rob,
Not always. Sometimes projects have known end dates, other times the end
is interminate until it happens.
CHECK(end_date is null or start_date <= end_date)
So a default of NULL should be appl
On Fri, 4 Jan 2019, David G. Johnston wrote:
The is null expression is redundant since check constraints pass when the
result is unknown.
David,
I wondered about this since NULL can be missing, unknown, or otherwise
defined. Are there benefits to allowing an empty value in that column when
On Fri, 4 Jan 2019, Andreas Kretschmer wrote:
no, you can use NULL, for instance. You don't need an explicit value.
But maybe you want to set the start_date to NOT NULL.
Andreas,
Yes, I added NOT NULL to the start_date column.
2. If so, please suggest a value for it.
other solution for
On Fri, 4 Jan 2019, David G. Johnston wrote:
I wondered about this since NULL can be missing, unknown, or otherwise
defined. Are there benefits to allowing an empty value in that column when
checking that it's later than the start date rather than explicitly setting
a default date after the st
On Fri, 4 Jan 2019, David G. Johnston wrote:
No. If no default is available and a value for the field is not provided
the stored value will be null; a default of null is thus also redundant
specification.
David,
Thanks for clarifying.
Regards,
Rich
101 - 200 of 500 matches
Mail list logo
