How to reset postgres’ primary key sequence when it falls out of sync?
How to reset postgres’ primary key sequence when it falls out of sync?
postgresql
How does the search_path influence identifier resolution and the “current schema”
What is the schema search path search_path? The manual: […] tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in. Bold emphasis mine. This explains identifier resolution. The “current … Read more
How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
9.5 and newer: PostgreSQL 9.5 and newer support INSERT … ON CONFLICT (key) DO UPDATE (and ON CONFLICT (key) DO NOTHING), i.e. upsert. Comparison with ON DUPLICATE KEY UPDATE. Quick explanation. For usage see the manual – specifically the conflict_action clause in the syntax diagram, and the explanatory text. Unlike the solutions for 9.4 and … Read more
Insert, on duplicate update in PostgreSQL?
PostgreSQL since version 9.5 has UPSERT syntax, with ON CONFLICT clause. with the following syntax (similar to MySQL) INSERT INTO the_table (id, column_1, column_2) VALUES (1, ‘A’, ‘X’), (2, ‘B’, ‘Y’), (3, ‘C’, ‘Z’) ON CONFLICT (id) DO UPDATE SET column_1 = excluded.column_1, column_2 = excluded.column_2; Searching postgresql’s email group archives for “upsert” leads to … Read more
The infamous java.sql.SQLException: No suitable driver found
The infamous java.sql.SQLException: No suitable driver found This exception can have basically two causes: #1. JDBC driver is not loaded You need to ensure that the JDBC driver is placed in server’s own /lib folder. Or, when you’re actually not using a server-managed connection pool data source, but are manually fiddling around with DriverManager#getConnection() in … Read more
How to exit from PostgreSQL command line utility: psql
Type \q and then press ENTER to quit psql. UPDATE: 19-OCT-2018 As of PostgreSQL 11, the keywords “quit” and “exit” in the PostgreSQL command-line interface have been included to help make it easier to leave the command-line tool.
PostgreSQL “DESCRIBE TABLE”
Try this (in the psql command-line tool): \d+ tablename See the manual for more info.
PostgreSQL: Show tables in PostgreSQL
From the psql command line interface, First, choose your database \c database_name Then, this shows all tables in the current schema: \dt Programmatically (or from the psql interface too, of course): SELECT * FROM pg_catalog.pg_tables; The system tables live in the pg_catalog database.
Why the “Group BY” by month didn’t work? [closed]
The group by clause, unlike the order by clause, doesn’t take column ordinals. In this clause, 1 and 2 are just integer literals, and since every row of your query has the same value for these two literals, they are all grouped in to a single result row. Instead, you should use the actual columns … Read more