How can I find out that bytea column in PostgreSQL contains any data?

Question

I have IMAGE table that has bytea column. The value of bytea column can be null. I have three rows in this table:

• id: 1, name: "some string", data: null
• id: 2, name: "small.png", data: contains small image(460 B)
• id: 3, name: "large.png", data: contains larger image(4.78 KB)

When I view data in this table in pgAdmin I see:

From the output I do not know which row contains binary data in bytea column and which one does not. When I run SQL select:

select id, name, data from image;

I get following result from which I can say that row with id 2 contains some binary data but I cannot distinguish if other rows (row with id 1 and 3) have some data or are null:

Questions

• Is there any SQL select option that enables to view if there are any data in bytea column?
• Are there any pgAdmin settings that enables viewing bytea column data?

---

For clarification I enclose Java test code that saves and retrieves data to and from IMAGE table. Image small.png has size 460B and large.png has 4.78KB.

private static final String JDBC_POSTGRESQL = "jdbc:postgresql://localhost/testDB?user=username&password=passwd";
private static File[] files = {new File("small.png"), new File("large.png")};

public static void main(String[] args) {
    // stores just the string
    try (Connection con = DriverManager.getConnection(JDBC_POSTGRESQL)) {
        PreparedStatement ps = con.prepareStatement("insert into image (name) values (?)");
        ps.setString(1, "some string");
        ps.executeUpdate();
    } catch (SQLException e1) {
        e1.printStackTrace();
    }

    // store images
    for (File file : files) {
        try (Connection con = DriverManager.getConnection(JDBC_POSTGRESQL)) {
            byte[] bytes = Files.readAllBytes(file.toPath());
            PreparedStatement ps = con.prepareStatement("insert into image (name, data) values (?, ?)");
            FileInputStream fis = new FileInputStream(file);
            ps.setString(1, file.getName());
            ps.setBinaryStream(2, fis, bytes.length);
            ps.executeUpdate();
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    // read from image table and create files
    try (Connection con = DriverManager.getConnection(JDBC_POSTGRESQL)) {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("select name, data from image");
        while (rs.next()) {
            File outputFile = new File("output_" + rs.getString("name"));
            FileOutputStream fos = new FileOutputStream(outputFile);
            if (rs.getBytes("data") != null) {
                fos.write(rs.getBytes("data"));
            }
        }
    } catch (SQLException | IOException e) {
        e.printStackTrace();
    }
}

Solution

You can use the is null operator to check for NULL values and octet_length() to get the real length of the bytea column:

select id, 
       name, 
       data is null as data_is_null, 
       octet_length(data) as data_length 
from image;

Note that octet_length() will also return NULL if data is null, so you probably only need that (for a zero length bytea it will return 0, so you can distinguish null values from empty values)

As I don't use pgAdmin, I can't tell you if it has any special features to view the binary data