How about dumping the contents of the database, then using grep? $ pg_dump –data-only –inserts -U postgres your-db-name > a.tmp $ grep United a.tmp INSERT INTO countries VALUES (‘US’, ‘United States’); INSERT INTO countries VALUES (‘GB’, ‘United Kingdom’); The same utility, pg_dump, can include column names in the output. Just change –inserts to –column-inserts. That … Read more
Postgres 9.5 (released since 2016-01-07) offers an “upsert” command, also known as an ON CONFLICT clause to INSERT: INSERT … ON CONFLICT DO NOTHING/UPDATE It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.
SQL treats NULL specially per its version of 3VL (3-valued logic). Normalization & other relational theory does not. However, we can translate SQL designs into relational designs and back. (Assume no duplicate rows here.) Normalization happens to relations and is defined in terms of operators that don’t treat NULL specially. The term “normalization” has two … Read more
Ways without dynamic SQL There is no cast from hex numbers in text representation to a numeric type, but we can use bit(n) as waypoint. There are undocumented casts from bit strings (bit(n)) to integer types (int2, int4, int8) – the internal representation is binary compatible. Quoting Tom Lane: This is relying on some undocumented … Read more
PostgreSQL has a guide on how to best populate a database initially, and they suggest using the COPY command for bulk loading rows. The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).
In PL/PgSQL parser, assignment operator is defined as assign_operator : ‘=’ | COLON_EQUALS ; This is a legacy feature, present in source code since 1998, when it was introduced – as we can see in the PostgreSQL Git repo. Starting from version 9.4 it is oficially documented. This idiosyncrasy – of having two operators for … Read more
Modern PostgreSQL format() has a built-in way to escape identifiers. Simpler than before: CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format(‘INSERT INTO %I.%I SELECT $1.*’ , TG_TABLE_SCHEMA, TG_TABLE_NAME || ‘shadow’) USING OLD; RETURN OLD; END $func$; Works with a VALUES expression as well. db<>fiddle here Old sqlfiddle Major points … Read more
These dollar signs ($$) are used for dollar quoting, which is in no way specific to function definitions. It can be used to replace single quotes enclosing string literals (constants) anywhere in SQL scripts. The body of a function happens to be such a string literal. Dollar-quoting is a PostgreSQL-specific substitute for single quotes to … Read more
In your data/postgresql.conf file, change the log_statement setting to ‘all’. Edit Looking at your new information, I’d say there may be a few other settings to verify: make sure you have turned on the log_destination variable make sure you turn on the logging_collector also make sure that the log_directory directory already exists inside of the … Read more
Set the PGPASSWORD environment variable inside the script before calling psql PGPASSWORD=pass1234 psql -U MyUsername myDatabaseName For reference, see http://www.postgresql.org/docs/current/static/libpq-envars.html Edit Since Postgres 9.2 there is also the option to specify a connection string or URI that can contain the username and password. Syntax is: $ psql postgresql://[user[:password]@][host][:port][,…][/dbname][?param1=value1&…] Using that is a security risk because … Read more