AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Postgresql serial repeat1/3/2024 ![]() This will install the latest PostgreSQL version along with the newest extensions and additions that are not yet officially part of the PostgreSQL core. Now is the time to do the actual PostgreSQL installation. ![]() It is always a good idea to download information about all packages available for installation from your configured sources before the actual installation. Sudo sh -c 'echo "deb $(lsb_release -cs)-pgdg main" > /etc/apt//pgdg.list'. Then, get the certificate, add it to apt-key management utility and create a new configuration file with an official PostgreSQL repository address inside. You may want to install PostgreSQL from an official repository, since it is updated more frequently than official Ubuntu sources.įirst, you should install prerequisite software packages that will be used to download and install software certificates for a secure SSL connection. Let‘s now go through a step-by-step guide of how to install and setup PostgreSQL on your Ubuntu 20.04 machine. PostgreSQL is one of the most popular databases in the industry that is used for various web, mobile and analytics applications. It is backed by 20 years of open-source development, and supports both SQL (relational) and JSON (non-relational) querying. It’s a little tricky to use, but in practice it’s often the most concise way to get the results you need.Ĭonsider a customers table with a row per customer, and a purchases table with one row per purchases made by (some) customers.PostgreSQL is a fully featured database management system (DBMS) with a strong emphasis on extensibility and SQL compliance. While this is standard SQL, Postgres provides an extension, the “DISTINCT ON”. distinct onĭuplicate rows returned by a SELECT can be combined (that is, only unique rows are returned) by adding the DISTINCT keyword after SELECT. If you’d rather have duplicate rows retained, use “UNION ALL” instead of UNION.Ĭomplimenting UNION, there is also INTERSECT and EXCEPT, see the PostgreSQL docs for more info. The two queries should have the same select list, that is, should return the same number and type of columns. SELECT id, name, address, FALSE AS is_deleted FROM users WHERE email = ? UNION SELECT id, name, address, TRUE AS is_deleted FROM deleted_users WHERE email = ? The COALESCE function can turn NULL values into any specific value. If it is possible to handle, say NULLs as empty strings, or NULL integers as 0, in the context of a specific query, you can use the COALESCE function. In Go, for example, you’d need to use types like sql.NullString in Java/JDBC, functions like resultSet.wasNull(). Handing NULL values in application code usually takes extra steps. The set of columns in the source and destination tables do not have to be identical, you can of course reorder, rearrange and use functions to manipulate the values in the select/returning lists. Granted, moving rows is maybe not a common use case, but if the business logic calls for it, the savings of application memory and database round trips presented by this approach make it the ideal solution. WITH items AS ( DELETE FROM todos_2021 WHERE NOT done RETURNING * ) INSERT INTO todos_2021 SELECT * FROM items ĭoing the equivalent in application code can be very verbose, involving storing the entire result of the delete in memory and using that to do multiple INSERTs. For example, given a table kv with key and value columns, the statement below will insert a new row (if the table does not have a row with key=’host’) or update the value (if the table has a row with key=’host’): This feature can be used for simplifying insert-or-update application logic into a single SQL statement. The conflict can either be a violation of a unique index (including a primary key) or any constraint (created earlier using CREATE CONSTRAINT). Since Postgres v9.5, it is possible to specify what should happen when an insert fails because of a “conflict”. ![]() ![]() Here are a few tips and tricks that can help your application code outsource more work to PostgreSQL, and make your application slimmer and faster. More often that not, such a change also improves the performance of the application code too. With just a little bit of tweaking and improving your Postgres SQL queries, you can cut down on the amount of repetitive, error-prone application code required to interface with your database. ![]()
0 Comments
Read More
Leave a Reply. |