Concatenating values from several rows as a single value summarizing all of them is done by using an AGGREGATE in SQL. PostgreSQL comes with several aggregates that are nice to use when one needs to keep every entry in the output. For a complete list of PostgreSQL Aggregates, the documentation is once again all you need. Have a look at Aggregate Functions. PostgreSQL Arrays PostgreSQL arrays can be built from the result of a query thanks to the array_agg() aggregate.

When using PHP, be sure to use the pg_query_params function, or an higher-level API that then uses that PostgreSQL facility. Then you’re protected against any and all SQL injection attacks, by design. An SQL Injections is a security breach, one made famous by the Exploits of a Mom xkcd comic episode in which we read about little Bobby Tables. Exploits of a Mom PostgreSQL implements a protocol level facility to send the static SQL query text separately from its dynamic arguments.

When using psql, use the \du command. Here’s a sample output of running that command when using the psql command line tool: ~# \du List of roles Role name │ Attributes │ Member of ═══════════╪════════════════════════════════════════════════════════════╪═══════════ app │ │ {} appdev │ Create role │ {} cdstore │ │ {appdev} critical │ │ {app} dbowner │ │ {} dim │ Superuser, Create role, Create DB │ {} dontcare │ │ {app} f1db │ │ {appdev} notsomuch │ │ {app} postgres │ Superuser, Create role, Create DB, Replication, Bypass RLS │ {} The psql application supports readline history and completion.

Use the \i command in psql, or the command line psql -a -f file.sql The PostgreSQL psql command line tool knows how to parse a file containing SQL statements and send them all to a PostgreSQL server. Several ways are possible. If you have a magic.create.sql file that contains the following: create schema if not exists magic; create table magic.allsets(data jsonb); It is possible to connect to your target database then have psql execute the commands from the file in the following way:

PostgreSQL implements the COPY protocol and command to output data formatted in CSV. The psql command line client also knows how to produce some other formats, such as XML, asciidoc, or latex. The simplest way to have your query result in CSV is the following: copy ( select format('%s %s', drivers.forename, drivers.surname) as name, sum(points) as points from drivers join results using(driverid) join races using(raceid) where races.year = 2016 group by drivers.

In PostgreSQL, use the ALTER SEQUENCE command, or the setval function. ALTER SEQUENCE tablename_colname_seq RESTART WITH 1; Resetting All Sequences The following SQL query generates an SQL statement that allows resetting all sequences from a database to the current maximum value found in the column the sequence is attached to: SELECT 'select ' || trim(trailing ')' from replace(pg_get_expr(d.adbin, d.adrelid), 'nextval', 'setval')) || ', (select max( ' || a.

Always use timestamp with time zone, or its spelling timestamptz, when using PostgreSQL. The calendar is something very complex, and easy to get wrong. Do you know about ISO week numbers, leap years, and time zone political changes? How do compute which day is tomorrow? Did you know that the year before AD 1 in our Gregorian calendar is the year 1 BC? Yes, right, there’s no year zero in our calendar.

PostgreSQL implements UTF-8 as a server encoding and as a client encoding, so that you can use unicode all the way through. The default encoding and collation for a PostgreSQL database server are setup at initdb time. This is usually done by your packaging system at installation. Database Encoding It’s important to set your environement correctly before installing PostgreSQL, in order to avoid surprises. The following example sets the system to use en_US.

A JOIN is an operation that applies to two relations and returns a relation. The different kinds of JOINs implement different operations. The PostgreSQL documentation section Table Expressions gives all the details. In the same way that + and - and * are different operations in maths, the different kind of joins are different relational operations. The main thing is to remember that we are working with relations, and a JOIN applies to a pair of relations at a time.

In PostgreSQL, use the boolean data type. Done. When using pgloader to migrate your MySQL database to PostgreSQL, the following cast rule is the default: CAST type tinyint to boolean using tinyint-to-boolean It’s of course then possible to migrate a tinyint column from MySQL to a PostgreSQL smallint with the following specific rule: CAST column table_a.count to smallint Boolean Columns When you have a boolean column in PostgreSQL then things work as you would expect.