![]() Also, psql only prints the result of the last SQL command in the string. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/ COMMIT commands included in the string to divide it into multiple transactions. Or echo '\x \\ SELECT * FROM foo ' | psqlĮach SQL command string passed to -c is sent to the server as a single query. ![]() To achieve that, you could use repeated -c options or pipe the string into psql, for example: psql -c '\x' -c 'SELECT * FROM foo ' Thus you cannot mix SQL and psql meta-commands within a -c option. When either -c or -f is specified, psql does not read commands from standard input instead it terminates after processing all the -c and -f options in sequence.Ĭommand must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single backslash command. This option can be repeated and combined in any order with the -f option. Specifies that psql is to execute the given command string, command. This is equivalent to setting the variable ECHO to errors. Print failed SQL commands to standard error output. (The default output mode is otherwise aligned.) This is equivalent to \pset format unaligned. (This does not apply to lines read interactively.) This is equivalent to setting the variable ECHO to all. Here's the GREATEST / LEAST documentation for MySQL, MariaDB, Oracle, and Db2 - with credit to this StackExchange post for those links.Print all nonempty input lines to standard output as they are read. Just another crazy thing that you can do with PostgreSQL. Apparently these functions even work for strings - in effect, providing alphabetical comparison (though I don't know if that's necessarily the "right tool for the job"). Last_seen_at = GREATEST (last_seen_at, :new_value )Ĭredit where it's due - this post on EBD goes into a lot of detail, with examples, about how you can use GREATEST and LEAST - I found it practical and informative. Using GREATEST simplified the logic needed during the update process - I could use a single query to increment the count every time, but only change the "most recent" date column when a newer timestamp appeared: UPDATE my_table The logs being parsed were not in chronological order and came from various sources. This was my use case I had a table with columns tracking a value's total number of occurrences and the date/time of the latest. last_seen_at is changed only if :new_value is more recent ![]() SET last_seen_at = GREATEST (last_seen_at, :new_value ) max_age is changed only if :new_value is largerĪs you'd expect, GREATEST can be used to compare numeric values, but I was very surprised to learn that it also works for comparing timestamps (which you wouldn't necessarily realize just by reading the documentation): - comparing date/times! SET max_age = GREATEST (max_age, :new_value ) That is, GREATEST functions almost like an if statement - update this column only if the provided value is greater than the current value: - comparing numeric values Because the greater of the two is returned, this comparison can be used to conditionally update a column. While this is helpful to know, what I found even more useful was that you can use GREATEST to compare the current value of a column with an incoming value. The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. I'm coming from a MS SQL Server background, so I wasn't familiar with GREATEST / LEAST, which apparently are available in most other database engines, though they are not part of the SQL standard. A quick note on a very convenient PostgreSQL function that I learned today - GREATEST - which can be used when you want a database column updated only if the incoming value is greater (more recent) than the existing value in the column.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |