I'm looking for a way to run a query to find the default values of the columns of a table in Postgres. For example, if I made a table with the following query:
**Editor's note: I fixed the table definition as it has no impact on the question.
CREATE TABLE mytable ( integer int DEFAULT 2, text varchar(64) DEFAULT 'I am default', moretext varchar(64) DEFAULT 'I am also default', unimportant int );
I need a query that would tell me, in some format, that the default for integer
is 2, text
is 'I am default', and moretext
is 'I am also default'. The query result can include any value for any other column that doesn't have a default, i.e., unimportant
is unimportant for my purposes and doesn't matter at all.
Default values can be NULL, or they can be a value that matches the data type of the column (number, text, date, for example).
If no default value is declared explicitly, the default value is the null value. This usually makes sense because a null value can be considered to represent unknown data.
Set Column Default using pgAdminIn the popup, go to 'Columns' tab and click on the edit icon againt a column to change the default value, as shown below. Now, go to the 'Constraints' tab and set or change the default value. Click on the Save button to save the changes.
NULL The column is allowed to contain null values. This is the default.
Use the information schema:
SELECT column_name, column_default FROM information_schema.columns WHERE (table_schema, table_name) = ('public', 'mytable') ORDER BY ordinal_position; column_name │ column_default ─────────────┼──────────────────────────────────────── integer │ 2 text │ 'I am default'::character varying moretext │ 'I am also default'::character varying unimportant │ (4 rows)
Up to the schema naming, this should work in any SQL database system.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With