Postgresql – Amend the value of a row

For starters, I wouldn’t use either key or value as field names – if you look here, you will see that both words occur in that list. They also appear in the PostgreSQL list (but as non-reserved) – again, avoid any words on this list – belt and braces! I would recommend that you change the names of your fields to (something like) sc_key and sc_value – these are also more meaningful.

All of the code below is available on the fiddle here.

From here (manual):

ALTER TABLE table_name 
RENAME COLUMN column_name TO new_column_name;

so,

ALTER TABLE system_config
RENAME COLUMN key TO sc_key;

and the same for value.

Also, rename – (manual – search for RENAME CONSTRAINT) that PRIMARY KEY – an error message telling a user that there has been a violation of PK_aab69295b445016f56731f4d535 is absolutely meaningless – change it to something like sc_pk (or system_config_pk).

BTW, there is never any need to (double) quote your identifiers (e.g. table, column or constraint) names unless for some reason, you wish to, (and/or) are obliged to, respect case, punctuation or spaces in identifiers.

If not, my strong recommendation is to use snake_case for everything (lower case with underscores separating words to make it readable). There are various SQL style guides on the internet (this one is good) – but pick a style and stick to it – consistency in this area helps legibility and makes debugging easier. I would also never put punctuation or spaces in my identifier names – you’re just making life difficult for yourself by doing this – it also means that in the event of a change of database server, your code will be less- or completely non-portable!

Run a couple of queries to double check our changes (found code here & here) – column names:

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns 
WHERE table_name ='system_config';

Result:

column_name  data_type          character_maximum_length
sc_key       character varying                      null
sc_value     character varying                      null

the null is because there is no length specified – a VARCHAR can be up to 1GB.

and PRIMARY KEY:

SELECT c.column_name, c.data_type
FROM information_schema.table_constraints tc 
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_name) 
JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema
  AND tc.table_name = c.table_name AND ccu.column_name = c.column_name
WHERE constraint_type = 'PRIMARY KEY' and tc.table_name = 'system_config';

Result:

column_name   data_type
sc_key        character varying

Now, to your question proper.

Double quotes are used for identifiers (table and column names) – to specify a string (in this case CHARACTER VARYING), you use single quotes!

To resolve your issue, I did the following:

I created and then modified the table as specified above (see fiddle), and then I inserted your data:

INSERT INTO system_config VALUES
('ffmpeg.transcode'                    , 'disabled'),
('job.objectTagging.concurrency'       , 4),
('job.clipEncoding.concurrency'        , 4),
('job.sidecar.concurrency'             , 4),
('job.migration.concurrency'           , 4),
('job.library.concurrency'             , 80),
('job.thumbnailGeneration.concurrency' , 20),
('job.metadataExtraction.concurrency'  , 50),
('job.recognizeFaces.concurrency'      , 6),
('storageTemplate.enabled'             , true),
('ffmpeg.accel'                        , 'qsv'),
('library.scan.cronExpression'         , '0 */6 * * *'),
('job.smartSearch.concurrency'         , 60),
('job.faceDetection.concurrency'       , 60),
('library.watch.enabled'               , true),
('library.watch.usePolling'            , true);

Note the use of single quotes to delineate strings – PostgreSQL appears to recognise numbers and true as valid strings (which surprised me, I must confess).

See the query using the PG_TYPEOF() function in the fiddle – they all come out as CHARACTER VARYING.

SELECT
  sc_key, sc_value, PG_TYPEOF(sc_key), PG_TYPEOF(sc_value)
FROM
  system_config;

Result (sample):

sc_key                         sc_value  pg_typeof          pg_typeof
ffmpeg.transcode               disabled  character varying  character varying
job.objectTagging.concurrency         4  character varying  character varying
job.clipEncoding.concurrency          4  character varying  character varying
...
... snipped for brevity
...

So, now to modify the value of library.watch.usePolling.

UPDATE system_config
SET sc_value = 'false'   -- also works with just false (no single quotes)
WHERE sc_key = 'library.watch.usePolling';

and again, we check:

SELECT
  sc_key, sc_value
FROM
  system_config
WHERE
  sc_key = 'library.watch.usePolling';

Result:

sc_key                    sc_value
library.watch.usePolling     false

So, you have to be careful to distinguish between double quotes for identifiers and single quotes for delineating strings!

On a final note, unless there’s a compelling reason not to do so, I would always use the TEXT datatype (maximum size also 1GB) for any sort of, well…, text data. From here:

Generally, there is no downside to using text in terms of
performance/memory. On the contrary: text is the optimum. Other types
have more or less relevant downsides. text is literally the
"preferred" type among string types in the Postgres type system, which
can affect function or operator type resolution.

See the fiddle for this query:

SELECT 
  typname, typlen, typispreferred
FROM
  pg_type
WHERE 
  typname IN ('char', 'text', 'varchar', 'cstring');

Result:

typname  typlen  typispreferred
char          1               f
text         -1               t
varchar      -1               f
cstring      -2               f

So, we see that TEXT is indeed the preferred type when it comes to character/text data.

And from the same post (read it in full – and follow up on the links – all of this poster’s contributions are very instructive):

I rarely use anything but text for character data in Postgres.

Check out the poster’s rep – so my final piece of advice is to change your column types (manual) to TEXT rather than VARCHAR/CHARACTER VARYING.

ALTER TABLE system_config
ALTER COLUMN sc_key
SET DATA TYPE TEXT;

and the same for sc_value.

Check using the PG_TYPEOF() query from before – result (sample):

sc_key                        sc_value  pg_typeof  pg_typeof
ffmpeg.transcode              disabled       text       text
job.objectTagging.concurrency        4       text       text
job.clipEncoding.concurrency         4       text       text
...
... snipped for brevity
...